com.orindasoft.pub
Class SqlUtils

java.lang.Object
  |
  +--com.orindasoft.pub.SqlUtils

public class SqlUtils

extends java.lang.Object

A set of useful static methods for working with SQL.

This class changes for different versions of Oracle.
(c) Copyright 2003 - 2011 Orinda Software Ltd

Version:

6.0

Author:

Orinda Software

Field Summary

static int	DDL Constant for identifying statements as being DDL statements
static int	DELETE Constant for identifying statements as being deletes
static int	FAILED_TO_CONVERT_INTERNAL Constant for ORA-17059 message - invalid name pattern The most common cause for this is a DB/Driver version mismatch
static int	INSERT Constant for identifying statements as being inserts
static int	INVALID_NAME_PATTERN Constant for ORA-17074 message - invalid name pattern
static int	LOCK Constant for identifying statements as being Lock statements
static int	MERGE Constant for identifying statements as being Merge statements
static int	OBJECT_DOES_NOT_EXIST Constant for ORA-4043 - Object does not exist
static int	ORACLE_BFILE_DATATYPE Constant for identifying oracle BFILE datatypes
static int	ORACLE_BINARY_DATATYPE Constant for identifying oracle Binary datatypes
static int	ORACLE_BLOB_DATATYPE Constant for identifying oracle BLOB datatypes
static int	ORACLE_BOOLEAN_DATATYPE Constant for identifying PL/SQL Boolean
static int	ORACLE_CLOB_DATATYPE Constant for identifying oracle CLOB datatypes
static int	ORACLE_DATE_DATATYPE Constant for identifying oracle Date datatypes
static int	ORACLE_INTERVAL_DAY_TO_SECOND_DATATYPE Constant for identifying INTERVAL DAY TO SECOND Datatype
static int	ORACLE_INTERVAL_YEAR_TO_MONTH_DATATYPE Constant for identifying INTERVAL YEAR TO MONTH Datatype
static int	ORACLE_LONG_BINARY_DATATYPE Constant for identifying oracle Long Binary datatypes
static int	ORACLE_LONGTEXT_DATATYPE Constant for identifying oracle Long Text datatypes
static int	ORACLE_NULL_DATATYPE Constant for identifying null datatypes
static int	ORACLE_NUMBER_DATATYPE Constant for identifying oracle Number datatypes
static int	ORACLE_OBJECT_DATATYPE Constant for identifying Oracle OBJECT Datatype
static int	ORACLE_OTHER_DATATYPE Constant for identifying unrecognized oracle datatypes
static int	ORACLE_PLSQL_INDEXBY_DATATYPE Constant for identifying PL/SQL Index By tables
static int	ORACLE_PLSQL_INDEXBY_ROWTYPE_DATATYPE Constant for identifying PL/SQL Index By tables
static int	ORACLE_REFCURSOR_DATATYPE Constant for identifying oracle ref cursors
static int	ORACLE_ROWID_DATATYPE Constant for identifying ROWID
static int	ORACLE_ROWTYPE_DATATYPE Constant for identifying PL/SQL Rowtype Datatype
static int	ORACLE_SDO_GEOMETRY_DATATYPE Constant for identifying ORACLE_SDO_GEOMETRY_DATATYPE
static int	ORACLE_TABLE_DATATYPE Constant for identifying oracle Collection's TABLE
static int	ORACLE_TEXT_DATATYPE Constant for identifying oracle Text datatypes
static int	ORACLE_TIMESTAMP_DATATYPE Constant for identifying TIMESTAMP
static int	ORACLE_TIMESTAMPLTZ_DATATYPE Constant for identifying TIMESTAMPLTZ
static int	ORACLE_TIMESTAMPTZ_DATATYPE Constant for identifying TIMESTAMPTZ
static int	ORACLE_UROWID_DATATYPE Constant for identifying UROWID
static int	ORACLE_VARRAY_DATATYPE Constant for identifying oracle Collection's VARRAY
static int	ORACLE_XMLTYPE_DATATYPE Constant for identifying XMLType
static int	ORINDASOFT_READONLYROWSET Constant for identifying PL/SQL Boolean
static int	PLSQL Constant for identifying statements as being PL/SQL statements
static java.lang.String[]	RESERVED_WORDS Array containing words which are not legal identifers...
static int	SAVEPOINT Constant for identifying statements as being Lock statements
static int	SELECT Constant for identifying statements as being queries
static int	SET_CONSTRAINTS Constant for identifying statements as being Lock statements
static int	SET_ROLE Constant for identifying statements as being Lock statements
static int	SET_TRANSACTION Constant for identifying statements as being Lock statements
static int	TYPE_HAS_DEPENDENTS Constant for ORA-2303 message - cannot drop or replace a type with type or table dependents
static int	UNKNOWN Constant for identifying statements as being unidentifiable
static int	UPDATE Constant for identifying statements as being updates
static int	XPLAN Constant for identifying statements as being Lock statements

Constructor Summary

SqlUtils()
A set of useful static methods for working with SQL.

Method Summary

static int	countParameters(java.lang.String aSqlStatement) Count parameters in a SQL Statement This method has a known bug
static oracle.sql.BFILE	createBfileLocator(java.lang.String theOracleDirectory, java.lang.String theFileName, java.sql.Connection theConnection, LogInterface theLog, boolean debugMessages) Create and return a BFILE locator.
static int	getStatementType(java.lang.String theStatement) Classify a SQL statement as a SELECT, INSERT, UPDATE, etc.
static int	getUnderlyingOracleDatatype(java.lang.String theColumnDataType) Return an int that represents the underlying oracle data type.

Methods inherited from class java.lang.Object

equals, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Field Detail

SELECT

public static final int SELECT

Constant for identifying statements as being queries

INSERT

public static final int INSERT

Constant for identifying statements as being inserts

UPDATE

public static final int UPDATE

Constant for identifying statements as being updates

DELETE

public static final int DELETE

Constant for identifying statements as being deletes

DDL

public static final int DDL

Constant for identifying statements as being DDL statements

Since:

2.0.1527 Support for Lock, Merge and DDL statements

LOCK

public static final int LOCK

Constant for identifying statements as being Lock statements

Since:

2.0.1527 Support for Lock, Merge and DDL statements

MERGE

public static final int MERGE

Constant for identifying statements as being Merge statements

Since:

2.0.1527 Support for Lock, Merge and DDL statements

XPLAN

public static final int XPLAN

Constant for identifying statements as being Lock statements

Since:

2.0.1527 Support for Lock, Merge and DDL statements

SAVEPOINT

public static final int SAVEPOINT

Constant for identifying statements as being Lock statements

Since:

2.0.1527 Support for Lock, Merge and DDL statements

SET_CONSTRAINTS

public static final int SET_CONSTRAINTS

Constant for identifying statements as being Lock statements

Since:

2.0.1527 Support for Lock, Merge and DDL statements

SET_TRANSACTION

public static final int SET_TRANSACTION

Constant for identifying statements as being Lock statements

Since:

2.0.1527 Support for Lock, Merge and DDL statements

SET_ROLE

public static final int SET_ROLE

Constant for identifying statements as being Lock statements

Since:

2.0.1527 Support for Lock, Merge and DDL statements

PLSQL

public static final int PLSQL

Constant for identifying statements as being PL/SQL statements

Since:

2.0.1527 Support for Lock, Merge and DDL statements

UNKNOWN

public static final int UNKNOWN

Constant for identifying statements as being unidentifiable

RESERVED_WORDS

public static final java.lang.String[] RESERVED_WORDS

Array containing words which are not legal identifers...

ORACLE_TEXT_DATATYPE

public static final int ORACLE_TEXT_DATATYPE

Constant for identifying oracle Text datatypes

ORACLE_NUMBER_DATATYPE

public static final int ORACLE_NUMBER_DATATYPE

Constant for identifying oracle Number datatypes

ORACLE_DATE_DATATYPE

public static final int ORACLE_DATE_DATATYPE

Constant for identifying oracle Date datatypes

ORACLE_LONGTEXT_DATATYPE

public static final int ORACLE_LONGTEXT_DATATYPE

Constant for identifying oracle Long Text datatypes

ORACLE_LONG_BINARY_DATATYPE

public static final int ORACLE_LONG_BINARY_DATATYPE

Constant for identifying oracle Long Binary datatypes

ORACLE_BINARY_DATATYPE

public static final int ORACLE_BINARY_DATATYPE

Constant for identifying oracle Binary datatypes

ORACLE_CLOB_DATATYPE

public static final int ORACLE_CLOB_DATATYPE

Constant for identifying oracle CLOB datatypes

ORACLE_BLOB_DATATYPE

public static final int ORACLE_BLOB_DATATYPE

Constant for identifying oracle BLOB datatypes

ORACLE_BFILE_DATATYPE

public static final int ORACLE_BFILE_DATATYPE

Constant for identifying oracle BFILE datatypes

ORACLE_REFCURSOR_DATATYPE

public static final int ORACLE_REFCURSOR_DATATYPE

Constant for identifying oracle ref cursors

ORACLE_BOOLEAN_DATATYPE

public static final int ORACLE_BOOLEAN_DATATYPE

Constant for identifying PL/SQL Boolean

ORINDASOFT_READONLYROWSET

public static final int ORINDASOFT_READONLYROWSET

Constant for identifying PL/SQL Boolean

ORACLE_ROWID_DATATYPE

public static final int ORACLE_ROWID_DATATYPE

Constant for identifying ROWID

ORACLE_UROWID_DATATYPE

public static final int ORACLE_UROWID_DATATYPE

Constant for identifying UROWID

ORACLE_TIMESTAMP_DATATYPE

public static final int ORACLE_TIMESTAMP_DATATYPE

Constant for identifying TIMESTAMP

ORACLE_TIMESTAMPTZ_DATATYPE

public static final int ORACLE_TIMESTAMPTZ_DATATYPE

Constant for identifying TIMESTAMPTZ

ORACLE_TIMESTAMPLTZ_DATATYPE

public static final int ORACLE_TIMESTAMPLTZ_DATATYPE

Constant for identifying TIMESTAMPLTZ

ORACLE_TABLE_DATATYPE

public static final int ORACLE_TABLE_DATATYPE

Constant for identifying oracle Collection's TABLE

ORACLE_VARRAY_DATATYPE

public static final int ORACLE_VARRAY_DATATYPE

Constant for identifying oracle Collection's VARRAY

ORACLE_OBJECT_DATATYPE

public static final int ORACLE_OBJECT_DATATYPE

Constant for identifying Oracle OBJECT Datatype

ORACLE_ROWTYPE_DATATYPE

public static final int ORACLE_ROWTYPE_DATATYPE

Constant for identifying PL/SQL Rowtype Datatype

ORACLE_INTERVAL_YEAR_TO_MONTH_DATATYPE

public static final int ORACLE_INTERVAL_YEAR_TO_MONTH_DATATYPE

Constant for identifying INTERVAL YEAR TO MONTH Datatype

ORACLE_INTERVAL_DAY_TO_SECOND_DATATYPE

public static final int ORACLE_INTERVAL_DAY_TO_SECOND_DATATYPE

Constant for identifying INTERVAL DAY TO SECOND Datatype

Since:

Oracle 10.1.0

ORACLE_PLSQL_INDEXBY_DATATYPE

public static final int ORACLE_PLSQL_INDEXBY_DATATYPE

Constant for identifying PL/SQL Index By tables

Since:

Oracle 10.1.0

ORACLE_PLSQL_INDEXBY_ROWTYPE_DATATYPE

public static final int ORACLE_PLSQL_INDEXBY_ROWTYPE_DATATYPE

Constant for identifying PL/SQL Index By tables

Since:

Oracle 10.1.0

ORACLE_XMLTYPE_DATATYPE

public static final int ORACLE_XMLTYPE_DATATYPE

Constant for identifying XMLType

Since:

Oracle 10.2.0

ORACLE_SDO_GEOMETRY_DATATYPE

public static final int ORACLE_SDO_GEOMETRY_DATATYPE

Constant for identifying ORACLE_SDO_GEOMETRY_DATATYPE

Since:

Oracle 10.2.0

ORACLE_OTHER_DATATYPE

public static final int ORACLE_OTHER_DATATYPE

Constant for identifying unrecognized oracle datatypes

ORACLE_NULL_DATATYPE

public static final int ORACLE_NULL_DATATYPE

Constant for identifying null datatypes

OBJECT_DOES_NOT_EXIST

public static final int OBJECT_DOES_NOT_EXIST

Constant for ORA-4043 - Object does not exist

INVALID_NAME_PATTERN

public static final int INVALID_NAME_PATTERN

Constant for ORA-17074 message - invalid name pattern

FAILED_TO_CONVERT_INTERNAL

public static final int FAILED_TO_CONVERT_INTERNAL

Constant for ORA-17059 message - invalid name pattern The most common cause for this is a DB/Driver version mismatch

TYPE_HAS_DEPENDENTS

public static final int TYPE_HAS_DEPENDENTS

Constant for ORA-2303 message - cannot drop or replace a type with type or table dependents

Constructor Detail

SqlUtils

public SqlUtils()

A set of useful static methods for working with SQL.

Method Detail

getStatementType

public static int getStatementType(java.lang.String theStatement)

Classify a SQL statement as a SELECT, INSERT, UPDATE, etc.

This method has a known bug

Parameters:

theStatement - a SQL Statement

Returns:

an int that will be a SqlUtils constant such as SqlUtils.SELECT, SqlUtils.UPDATE, etc.

Since:

2.0.1527 Support for Lock, Merge and DDL statements

countParameters

public static int countParameters(java.lang.String aSqlStatement)

Count parameters in a SQL Statement This method has a known bug

Parameters:

String - aSqlStatement a SQL Statement

Returns:

an int The number of JDBC parameters in this SQL statement

getUnderlyingOracleDatatype

public static int getUnderlyingOracleDatatype(java.lang.String theColumnDataType)

Return an int that represents the underlying oracle data type. This method takes an oracle data type and classifies it as Text, Number, Date Long Text or Other.

Parameters:

String - An oracle data type

Returns:

int A SqlUtils constant that represents the underlying oracle data type.

createBfileLocator

public static oracle.sql.BFILE createBfileLocator(java.lang.String theOracleDirectory,
                                                  java.lang.String theFileName,
                                                  java.sql.Connection theConnection,
                                                  LogInterface theLog,
                                                  boolean debugMessages)
                                           throws CSException

Create and return a BFILE locator. This static method creates an oracle.sql.BFILE object by accessing the database. Note that a 'valid' BFILE will be returned even if theOracleDirectory does not exist or theFileName does not refer to an existing file.

Parameters:

String - theOracleDirectory

String - theFileName

Connection - a database Connection

com.orindasoft.pub.LogInterface - a logging mechanism

boolean - debugMessages Create debug messages in log

Returns:

oracle.sql.BFILE A BFILE which may or may not be usable.

Throws:

CSException - If we can't create a BFILE.

Since:

Oracle 8.1.7 / JDBCWizard 4.0.2108