SQL Parser allows you to benefit from rules and forecast capabilities for Oracle SQL Scripts that are being packaged through the DIRECT, DDL_DIRECT, or SQLFILE package methods.

Properties of the SQL Parser

• SQL Parser only applies to changes that are:
  • Packaged using the DIRECT, DDL_DIRECT, or SQLFILE method
  • Created in the ChangeSet Wizard as 'Execute a SQL script file using JDBC'/'Custom SQL (External File)'. NOTE: Changes created as 'Custom SQL' will NOT be processed by SQL Parser.
  • Created in the ChangeSet Wizard as 'Execute with SQLPlus'
• SQL Parser does not change how these scripts are ultimately executed during a Deploy operation. The following package methods all result in execution of a SQL script:
  • DIRECT packaging method
  • DDL_DIRECT packaging method
  • SQLFILE packaging method
  • changes created as 'Execute a SQL script file using JDBC'/'Custom SQL (External File)'
  • changes created as 'Execute with SQLPlus' 
• SQL Parser does not validate SQL statements. Instead, SQL Parser simply parses provided SQL scripts and models them into Datical's object model for subsequent validation with rules and forecast.
  • In the event the SQL Parser is unable to parse a statement that is included in a provided SQL script:
    • The Forecast Report will include a warning for the statement(s) that cannot be parsed. The warning will read: "WARNING - Will not be forecast"
    • SQL Parser will continue parsing the provided SQL script and attempt to parse all other statements
  • In the event the SQL Parser is unable to parse the provided SQL script file or otherwise encounters an error:
    • The Forecast Report will include a warning that will read: `SQL Parser could not parse sql script file <script>`
• We recommend that you do not put large DML scripts with many insert or update statements in folders that have SQL Parser enabled. 
  • See Recommendations for Working with Large DML Scripts (SQL Parser forecasting)

Configuring Projects to Use SQL Parser

SQL Parser is a global setting that applies to the entire project. As a global setting, there is no way to selectively enable SQL Parser. Instead, SQL Parser can be selectively disabled for specific folders after the global setting has been enabled. To enable the SQL Parser:

• There were fixes and improvements to SQL Parser for Oracle in Liquibase Enterprise/Datical DB version 7.12.  Please upgrade to version 7.12 or higher if you are using SQL Parser.

• In the GUI, navigate to the Deployment Settings section of the project settings (Settings tab), then check the checkbox next to 'Enable Parser'. NOTE: As a project-level setting, checking the checkbox for 'Enable Parser' will enable SQL Parser globally for the entire project.
  
  The setting is represented in the  datical.project file by enableSqlParser=true.

  enableSqlParser=[true | false]

  
  

Disabling SQL Parser

You can selectively disable the SQL Parser after it has been enabled, either at the folder level or on the change set level.

At the Folder level

You can disable SQL Parser at the folder level either temporarily or permanently in the metadata.properties file for that folder:

Set disableSqlParser=true in the metadata.properties file for any folders that you do not want SQL Parser to process. 

disableSqlParser=[true | false]

At the Change Set level

You can disable SQL Parser for a specific change set by manually editing the changelog.xml to add disableSqlParser="true":

Add disableSqlParser=true in the appdba:sqlplus section of the change set for a specific change set you do not want SQL Parser to process.

<changeSet appdba:scriptChecksum="9f8a11051d1478faf2752baacb76cf0e" author="J Doe" created="2020-08-10 18:37+0000" datical:origFileName="adding_test_new_001.sql" datical:origFilePath="scripts/sql_direct" datical:version="1" datical:versionStrategy="DEPLOY_ALL" id="20200810183704986_adding_test_new_001" labels="509,deploy-test1,deploy-test1,adding_test_new_001.sql">
    <appdba:sqlplus disableSqlParser="true" enableRollbackOnError="true" path="sql/20200810183704986_adding_test_new_001.sql" removeSpools="First" schemaName="SCHEMA1"/>
    <comment>Test script</comment>

Procedures for SQL Parser to work around Forecast failures

If a Forecast fails for a script, you may be able to temporarily turn off SQL Parser to work around it. Determine which script caused the failure first (Forecast Report).  See which directory the script with the forecast failure is in (such as sql_direct or sql or ddl_direct folder).

To work around the problem, turn off SQL Parser  temporarily using disableSqlParser=true in metadata.properties for the folder where the script failed, and then run deployPackager again.

IMPORTANT: You should reset disableSqlParser=false in metadata.properties after the script is packaged successfully.  

If you have a directory hierarchy for SQL scripts remember that metadata.properties effects the directory it is in and all subdirectories. Set the property in the directory that is most local to the script that causes the packaging error. The directory hierarchy root is as follows:

• For fixed folders, the ddl_direct, sql_direct, or sql folder
• For flexible folders, the highest directory that has the packageMethod=DDL_DIRECT, packageMethod=DIRECT, or packageMethod=SQLFILE 

Follow this procedure after you encounter the Forecast failure and identify the script causing the problem:

1. Go to the directory that contains the script that causes the problem (such as the ddl_direct, sql_direct, or sql folder). 
2. Set disableSqlParser=true in the metadata.properties file (create the file if it does not exist).  Check it in. 
3. Run Deployment Packager again. 
4. Check that the file is packaged successfully. 
5. Set disableSqlParser=false in the metadata.properties file. Check it in. 

If the reason for the Forecast failure was that a statement earlier in the script was unable to be forecast and a statement later in the script depended on it, there may be a different work around possibility.  If it is appropriate for this type of script, you may consider splitting the single script into multiple smaller scripts so the statement with the forecast failure is in a separate script from the other statements that depend on it.

See Using the metadata.properties file. 

Changeset Wizard and SQL Parser

Additional Files Produced

When SQL parsing is enabled, a file is produced after any of the following operations:

• Package
• Forecast
• Deploy

The file contains the modeled changes produced by the SQL Parser. It can be used for debugging. The produced file is stored in the project in the following directory:

Reports/<year>/<date>/<dbdef>/[forecast | deploy]/parser_output/<filename>_<timestamp>.xml

Supported Databases

Using SQL Parser is available only for the following databases. See each section for additional information about object support and limitations. 

• Oracle Database

Limitations of SQL Parser

Syntax and Modeling Limitations:

• SQL Parser requires statement blocks to be separated by a forward slash "/". Expand for examples:

  CREATE OR REPLACE PACKAGE test_methods IS ... END test_methods; CREATE OR REPLACE PACKAGE emp_mgmt AS ... END emp_mgmt; / CREATE OR REPLACE PACKAGE test_methods IS ... END test_methods; / CREATE OR REPLACE PACKAGE emp_mgmt AS ... END emp_mgmt; /

  
  

• For constraints that require a unique index (such as addPrimaryKey or addUniqueConstraint), the SQL Parser does not model creating a unique index. Expand for example:

  ALTER TABLE SCHEMA1.COUNTRIES ADD CONSTRAINT COUNTRY_C_ID_PK PRIMARY KEY ( COUNTRY_ID ) ; <addPrimaryKey columnNames="COUNTRY_ID" constraintName="COUNTRY_C_ID_PK" schemaName="SCHEMA1" tableName="COUNTRIES"/> In this example, it might be the case that the unique index "COUNTRY_C_ID_PKX" had to be first created in schema "SCHEMA1" in table "COUNTRIES" before the primary key can be added. The limitation is that the unique index creation is not modeled by SQL Parser because that cannot be determined without first interacting with the target database and finding out whether the unique index already exists or not. Same goes to the statements with USING INDEX with index name following it. ALTER TABLE "MYSCHEMA"."AWESOME_TABLE" ADD CONSTRAINT "AWESOME_TABLE_PK1" PRIMARY KEY ("AWESOME_TABLE_ID") USING INDEX MY_NEW_IDX TABLESPACE TS1; is modeled to <addPrimaryKey columnNames="AWESOME_TABLE_ID" constraintName="AWESOME_TABLE_PK1" forIndexName="MY_NEW_IDX" schemaName="MYSCHEMA" tableName="AWESOME_TABLE" tablespace="TS1"/> In this same example, if the statement makes explicit use of the USING INDEX without the name of the index then the SQL Parser will model the index creation as well as the primary key. ALTER TABLE "MYSCHEMA"."AWESOME_TABLE" ADD CONSTRAINT "AWESOME_TABLE_PK1" PRIMARY KEY ("AWESOME_TABLE_ID") USING INDEX TABLESPACE TS1; is modeled to <createIndex indexName="AWESOME_TABLE_PK1" schemaName="MYSCHEMA" unique="true"><column name="AWESOME_TABLE_ID"/><appdba:storageOptions dbms="oracle">TABLESPACE TS1</appdba:storageOptions></createIndex> <addPrimaryKey columnNames="AWESOME_TABLE_ID" constraintName="AWESOME_TABLE_PK1" forIndexName="AWESOME_TABLE_PK1" forIndexSchemaName="MYSCHEMA" schemaName="MYSCHEMA" tableName="AWESOME_TABLE"/> The exception to this is when the USING INDEX has a CREATE INDEX statement inline. These statements will NOT be modeled by SQL Parser and may cause Forecast errors as a result. ALTER TABLE "MYSCHEMA"."AWESOME_TABLE" ADD CONSTRAINT "AWESOME_TABLE_PK1" PRIMARY KEY ("AWESOME_TABLE_ID") USING INDEX (CREATE UNIQUE INDEX "MYSCHEMA"."AWESOME_TABLE_PK1" ON "MYSCHEMA"."AWESOME_TABLE" ("AWESOME_TABLE_ID") TABLESPACE "SOME_TABLESPACE") ENABLE;

  
  

SQL Statements Not Modeled

The following statements will not be modeled by the SQL Parser and will instead just be run as provided.  Note that this list is applicable for Datical DB versions 6.15 and higher.  (The list for Datical DB versions 6.14 and lower was somewhat different.)

• Anonymous SQL Block (BEGIN ... END)
• ALTER TABLE ENABLE CONSTRAINT
• ALTER TABLE DISABLE CONSTRAINT
• ALTER TABLE MODIFY PRIMARY KEY
• ALTER TABLE with inline create index statement after USING INDEX
  
  • This is illustrated in the Syntax and Modeling Limitations section
• ALTER CLUSTER
• ALTER DATABASE
• ALTER DIMENSION
• ALTER DISKGROUP
• ALTER FLASHBACK ARCHIVE
• ALTER FUNCTION
• ALTER INDEXTYPE
• ALTER JAVA
• ALTER MATERIALIZED VIEW
• ALTER MATERIALIZED VIEW LOG
• ALTER OPERATOR
• ALTER OUTLINE
• ALTER PACKAGE
• ALTER PROFILE
• ALTER RESOURCE COST
• ALTER ROLE
• ALTER ROLLBACK SEGMENT
• ALTER TRIGGER
• ALTER SYSTEM
• ALTER TABLESPACE
• ALTER USER
• ALTER VIEW
• ANALYZE
• ASSOCIATE STATISTICS
• AUDIT
• CALL
• COMMIT
• CREATE CLUSTER
• CREATE CONTEXT
• CREATE CONTROLFILE
• CREATE DATABASE
• CREATE DATABASE LINK
• CREATE DIMENSION
• CREATE DIRECTORY
• CREATE DISKGROUP
• CREATE FLASHBACK ARCHIVE
• CREATE INDEXTYPE
• CREATE LIBRARY
• CREATE MATERIALIZED VIEW LOG
• CREATE OPERATOR
• CREATE OUTLINE
• CREATE PACKAGE
• CREATE PACKAGE BODY
• CREATE PFILE
• CREATE PROFILE
• CREATE RESTORE POINT
• CREATE ROLLBACK SEGMENT
• CREATE SCHEMA
• CREATE SPFILE
• CREATE TABLE of XMLTYPE
• CREATE TABLESPACE
• CREATE TYPE
• CREATE TYPE BODY
• CREATE USER
• DISASSOCIATE STATISTICS
• DROP CLUSTER
• DROP CONTEXT
• DROP DATABASE
• DROP DATABASE LINK
• DROP DIMENSION
• DROP DIRECTORY
• DROP DISKGROUP
• DROP FLASHBACK ARCHIVE
• DROP INDEXTYPE
• DROP JAVA
• DROP LIBRARY
• DROP MATERIALIZED VIEW LOG
• DROP OPERATOR
• DROP OUTLINE
• DROP PACKAGE
• DROP PACKAGE BODY
• DROP PROFILE
• DROP RESTORE POINT
• DROP ROLE
• DROP ROLLBACK SEGMENT
• DROP TABLESPACE
• DROP TYPE
• DROP TYPE BODY
• DROP USER
• EXPLAIN PLAN
• FLASHBACK DATABASE
• FLASHBACK TABLE
• GRANT
• INSERT
• LOCK TABLE
• MERGE
• NOAUDIT
• PURGE
• RENAME without object type (ambiguous Table, View, Sequence, Private Synonym)
• REVOKE
• ROLLBACK
• SAVEPOINT
• SELECT
• SET CONSTRAINT
• SET CONSTRAINTS
• SET ROLE
• SET TRANSACTION
• TRUNCATE CLUSTER
• TRUNCATE TABLE
• UPDATE

SQL Statements to Avoid

Do not use SQL Parser with the following statements. 

• CREATE JAVA - produces unpredictable results, including incorrect forecasts and forecast failure