Class SqlUtils

java.lang.Object
com.mcpdbwizard.pub.SqlUtils
public class SqlUtils extends Object
A set of useful static methods for working with SQL.

This class changes for different versions of Oracle. Copyright 2003-2026 ATB Consultancy Services Ltd (formerly Orinda Software Ltd, Dublin, Ireland)

Version:
7
Author:
devteam@mcpdbwizard.com

  • Field Details

    • SELECT

      public static final int SELECT
      Constant for identifying statements as being queries
      See Also:

    • INSERT

      public static final int INSERT
      Constant for identifying statements as being inserts
      See Also:

    • UPDATE

      public static final int UPDATE
      Constant for identifying statements as being updates
      See Also:

    • DELETE

      public static final int DELETE
      Constant for identifying statements as being deletes
      See Also:

    • 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
      See Also:

    • 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
      See Also:

    • 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
      See Also:

    • 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
      See Also:

    • 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
      See Also:

    • 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
      See Also:

    • 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
      See Also:

    • 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
      See Also:

    • 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
      See Also:

    • UNKNOWN

      public static final int UNKNOWN
      Constant for identifying statements as being unidentifiable
      See Also:

    • RESERVED_WORDS

      public static final String[] RESERVED_WORDS
      Array containing words which are not legal identifers...

    • ORACLE_TEXT_DATATYPE

      public static final int ORACLE_TEXT_DATATYPE
      Constant for identifiying oracle Text datatypes
      See Also:

    • ORACLE_NUMBER_DATATYPE

      public static final int ORACLE_NUMBER_DATATYPE
      Constant for identifiying oracle Number datatypes
      See Also:

    • ORACLE_DATE_DATATYPE

      public static final int ORACLE_DATE_DATATYPE
      Constant for identifiying oracle Date datatypes
      See Also:

    • ORACLE_LONGTEXT_DATATYPE

      public static final int ORACLE_LONGTEXT_DATATYPE
      Constant for identifiying oracle Long Text datatypes
      See Also:

    • ORACLE_LONG_BINARY_DATATYPE

      public static final int ORACLE_LONG_BINARY_DATATYPE
      Constant for identifiying oracle Long Binary datatypes
      See Also:

    • ORACLE_BINARY_DATATYPE

      public static final int ORACLE_BINARY_DATATYPE
      Constant for identifiying oracle Binary datatypes
      See Also:

    • ORACLE_CLOB_DATATYPE

      public static final int ORACLE_CLOB_DATATYPE
      Constant for identifiying oracle CLOB datatypes
      See Also:

    • ORACLE_BLOB_DATATYPE

      public static final int ORACLE_BLOB_DATATYPE
      Constant for identifiying oracle BLOB datatypes
      See Also:

    • ORACLE_BFILE_DATATYPE

      public static final int ORACLE_BFILE_DATATYPE
      Constant for identifiying oracle BFILE datatypes
      See Also:

    • ORACLE_REFCURSOR_DATATYPE

      public static final int ORACLE_REFCURSOR_DATATYPE
      Constant for identifiying oracle ref cursors
      See Also:

    • ORACLE_BOOLEAN_DATATYPE

      public static final int ORACLE_BOOLEAN_DATATYPE
      Constant for identifiying PL/SQL Boolean
      See Also:

    • MCPDBWIZARD_READONLYROWSET

      public static final int MCPDBWIZARD_READONLYROWSET
      Constant for identifiying PL/SQL Boolean
      See Also:

    • ORACLE_ROWID_DATATYPE

      public static final int ORACLE_ROWID_DATATYPE
      Constant for identifiying ROWID
      See Also:

    • ORACLE_UROWID_DATATYPE

      public static final int ORACLE_UROWID_DATATYPE
      Constant for identifiying UROWID
      See Also:

    • ORACLE_TIMESTAMP_DATATYPE

      public static final int ORACLE_TIMESTAMP_DATATYPE
      Constant for identifiying TIMESTAMP
      See Also:

    • ORACLE_TIMESTAMPTZ_DATATYPE

      public static final int ORACLE_TIMESTAMPTZ_DATATYPE
      Constant for identifiying TIMESTAMPTZ
      See Also:

    • ORACLE_TIMESTAMPLTZ_DATATYPE

      public static final int ORACLE_TIMESTAMPLTZ_DATATYPE
      Constant for identifiying TIMESTAMPLTZ
      See Also:

    • ORACLE_TABLE_DATATYPE

      public static final int ORACLE_TABLE_DATATYPE
      Constant for identifiying oracle Collection's TABLE
      See Also:

    • ORACLE_VARRAY_DATATYPE

      public static final int ORACLE_VARRAY_DATATYPE
      Constant for identifiying oracle Collection's VARRAY
      See Also:

    • ORACLE_OBJECT_DATATYPE

      public static final int ORACLE_OBJECT_DATATYPE
      Constant for identifiying Oracle OBJECT Datatype
      See Also:

    • ORACLE_ROWTYPE_DATATYPE

      public static final int ORACLE_ROWTYPE_DATATYPE
      Constant for identifiying PL/SQL Rowtype Datatype
      See Also:

    • ORACLE_INTERVAL_YEAR_TO_MONTH_DATATYPE

      public static final int ORACLE_INTERVAL_YEAR_TO_MONTH_DATATYPE
      Constant for identifiying INTERVAL YEAR TO MONTH Datatype
      See Also:

    • ORACLE_INTERVAL_DAY_TO_SECOND_DATATYPE

      public static final int ORACLE_INTERVAL_DAY_TO_SECOND_DATATYPE
      Constant for identifiying INTERVAL DAY TO SECOND Datatype
      Since:
      Oracle 10.1.0
      See Also:

    • ORACLE_PLSQL_INDEXBY_DATATYPE

      public static final int ORACLE_PLSQL_INDEXBY_DATATYPE
      Constant for identifiying PL/SQL Index By tables
      Since:
      Oracle 10.1.0
      See Also:

    • ORACLE_PLSQL_INDEXBY_ROWTYPE_DATATYPE

      public static final int ORACLE_PLSQL_INDEXBY_ROWTYPE_DATATYPE
      Constant for identifiying PL/SQL Index By tables
      Since:
      Oracle 10.1.0
      See Also:

    • ORACLE_XMLTYPE_DATATYPE

      public static final int ORACLE_XMLTYPE_DATATYPE
      Constant for identifiying XMLType
      Since:
      Oracle 10.2.0
      See Also:

    • ORACLE_SDO_GEOMETRY_DATATYPE

      public static final int ORACLE_SDO_GEOMETRY_DATATYPE
      Constant for identifiying ORACLE_SDO_GEOMETRY_DATATYPE
      Since:
      Oracle 10.2.0
      See Also:

    • ORACLE_JSON_DATATYPE

      public static final int ORACLE_JSON_DATATYPE
      Constant for identifiying the native JSON datatype.

      The binary (OSON) JSON column type. Read through the JDBC driver as an oracle.sql.json.OracleJsonValue.

      Since:
      Oracle 21c
      See Also:

    • ORACLE_NATIVE_BOOLEAN_DATATYPE

      public static final int ORACLE_NATIVE_BOOLEAN_DATATYPE
      Constant for identifiying the native (ISO-SQL) BOOLEAN column datatype.

      This is the real BOOLEAN column type, distinct from the older PL/SQL-only boolean (ORACLE_BOOLEAN_DATATYPE). It arrives from the driver as a Boolean.

      Since:
      Oracle 23ai
      See Also:

    • ORACLE_VECTOR_DATATYPE

      public static final int ORACLE_VECTOR_DATATYPE
      Constant for identifiying the VECTOR datatype (AI vector embeddings).

      Covers VECTOR and its dimensioned/typed forms (VECTOR(3, FLOAT32), VECTOR_FLOAT64, VECTOR_INT8, ...). Read through the JDBC driver as a double[] (or float[] / byte[] for the storage-typed accessors).

      Since:
      Oracle 23ai
      See Also:

    • ORACLE_VECTOR_BINARY_DATATYPE

      public static final int ORACLE_VECTOR_BINARY_DATATYPE
      Constant for identifiying a native VECTOR whose storage format is BINARY (bit-packed).

      Distinct from the dense-numeric ORACLE_VECTOR_DATATYPE because a binary vector cannot be read/bound as a double[] (the driver throws ORA-17004); its natural Java representation is a byte[] (n bits = n/8 bytes), bound via oracle.jdbc.OracleType.VECTOR_BINARY. The format is not visible in DATA_TYPE (always "VECTOR"); it is derived from the USER/ALL_TAB_COLUMNS.VECTOR_INFO descriptor — see getVectorDatatypeFromInfo(String).

      Since:
      Oracle 23ai
      See Also:

    • ORACLE_VECTOR_SPARSE_DATATYPE

      public static final int ORACLE_VECTOR_SPARSE_DATATYPE
      Constant for identifiying a native VECTOR whose storage is SPARSE (index/value pairs rather than a dense array).

      Recognised so the generator can detect and skip/flag such a column rather than emit the dense double[] path that fails on it. NOTE: sparse vectors are not readable by the currently bundled Oracle JDBC driver (ojdbc11 23.7 throws ORA-17004 for every Java type and provides no VECTOR_SPARSE bind type), so no read/write support is wired for this constant yet — see docs/oracle23ai-vector-subformats-plan.md. Derived from VECTOR_INFO — see getVectorDatatypeFromInfo(String).

      Since:
      Oracle 23ai
      See Also:

    • ORACLE_OTHER_DATATYPE

      public static final int ORACLE_OTHER_DATATYPE
      Constant for identifiying unrecognized oracle datatypes
      See Also:

    • ORACLE_NULL_DATATYPE

      public static final int ORACLE_NULL_DATATYPE
      Constant for identifiying null datatypes
      See Also:

    • OBJECT_DOES_NOT_EXIST

      public static final int OBJECT_DOES_NOT_EXIST
      Constant for ORA-4043 - Object does not exist
      See Also:

    • INVALID_NAME_PATTERN

      public static final int INVALID_NAME_PATTERN
      Constant for ORA-17074 message - invalid name pattern
      See Also:

    • 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
      See Also:

    • 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
      See Also:

    • GENERATED_BY_COMMENT

      public static final String GENERATED_BY_COMMENT
      The comment every generated statement carries, so a DBA can find this product's SQL in V$SQL without knowing what the application was called.

      Deliberately a plain comment rather than anything Oracle interprets: it costs a few bytes per cursor, survives into the shared pool exactly as written, and cannot change a plan. It is emitted by BOTH halves of the generator — the PL/SQL call builder and the SQL statement builder — so it is declared once here rather than typed twice.

      Anything matching on it should match this constant, not a literal: it read "Created By" with two spaces until 2026-08-16, so a hand-written sql_text LIKE against old and new output has to allow for both.

      See Also:

  • Constructor Details

    • SqlUtils

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

  • Method Details

    • normaliseTypeSpec

      public static String normaliseTypeSpec(String theSpec)
      Reduce an Oracle TYPE specification to the form that two spellings of the same type share.

      Generated code uses this to tell a type that merely could not be replaced (ORA-2303 is the ordinary outcome once the _A collection depends on the _T object type) from one whose definition has actually changed underneath it. Oracle keeps a type's specification text in USER_SOURCE exactly as submitted, so the comparison is against the DDL itself rather than against USER_TYPE_ATTRS - which would need the DDL's datatype spellings mapped onto the data dictionary's, and would report a difference wherever that mapping was imperfect.

      Discarded: everything up to and including the leading TYPE keyword (so CREATE OR REPLACE TYPE x ... and the stored TYPE x ... agree), semicolons, case, and all whitespace - the same statement arrives as one concatenated line from generated Java and as several lines from extraObjects.sql. Removing whitespace outright is safe because a type specification contains no string literals.

      Parameters:
      theSpec - a CREATE TYPE statement or a stored specification; null is treated as empty
      Returns:
      the comparable form, never null

    • getStatementType

      public static int getStatementType(String theStatement)
      Classify a SQL statement as a SELECT, INSERT, UPDATE, etc.

      Leading comments and whitespace are skipped before the first keyword is read. That was once a known bug, and fixing it is what allows GENERATED_BY_COMMENT to be put at the START of a generated statement: without it every marked statement classified as UNKNOWN, which in DmlStatement means a statement that no longer knows whether it is a query. Block comments may nest neither in SQL nor here; a comment that is never closed consumes the rest of the string, which then classifies as UNKNOWN — correctly, since such a statement would not parse either.

      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(String aSqlStatement)
      Count the JDBC bind parameters in a SQL statement.

      Comments are skipped, so a ? inside one is not counted and -- the case that actually bit -- an apostrophe inside one does not open a phantom string literal. The demo statement CustomerDelete carries the comment "If you don't have a comment after a parameter"; that lone apostrophe used to swallow the rest of the statement, so its real ? went uncounted, StatementParameters2 was sized 0, and every call died with "Attempt to set parameter number '1' even though statement doesn't take parameters."

      A doubled '' escape inside a literal needs no special case: the toggle turns off then straight back on, which lands in the right state at the closing quote.

      Parameters:
      aSqlStatement - a SQL Statement; null is treated as empty
      Returns:
      an int The number of JDBC parameters in this SQL statement

    • getUnderlyingOracleDatatype

      public static int getUnderlyingOracleDatatype(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:
      theColumnDataType -
      Returns:
      int A SqlUtils constant that represents the underlying oracle data type.

    • getVectorDatatypeFromInfo

      public static int getVectorDatatypeFromInfo(String vectorInfo)
      Refine a native VECTOR column's datatype code from its USER/ALL_TAB_COLUMNS.VECTOR_INFO descriptor.

      On Oracle 23ai every VECTOR column reports DATA_TYPE = "VECTOR" regardless of storage format, so getUnderlyingOracleDatatype(String) cannot tell dense from binary from sparse. The format is exposed for a table/view column in the VECTOR_INFO descriptor, which looks like VECTOR(<dim>,<format>,<storage>) — e.g. VECTOR(3,FLOAT32,DENSE), VECTOR(16,BINARY,DENSE), VECTOR(4,FLOAT32,SPARSE), VECTOR(*,*,DENSE). This method maps that descriptor to the specific constant:

      A null, blank, or unrecognised descriptor falls back to the dense ORACLE_VECTOR_DATATYPE — the pre-existing behaviour — so callers that have no VECTOR_INFO (e.g. proc parameters, which do not expose it) are unaffected. Sparse takes precedence over binary because it is the harder limitation (unreadable by the current driver); the two are not combinable in practice.
      Parameters:
      vectorInfo - the VECTOR_INFO descriptor, or null
      Returns:
      one of ORACLE_VECTOR_DATATYPE, ORACLE_VECTOR_BINARY_DATATYPE, ORACLE_VECTOR_SPARSE_DATATYPE
      Since:
      Oracle 23ai

    • createBfileLocator

      public static oracle.sql.BFILE createBfileLocator(String theOracleDirectory, String theFileName, 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:
      theOracleDirectory -
      theFileName -
      theConnection -
      theLog - a logging mechanism
      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 / 4.0.2108