SQL*Engine

NOTE - Use your Browsers BACK Button to return to prior page or CLICK here.

SQL*Engine - Dynamic SQL Processor

This is a useful 'PeopleSoft' utility I developed to execute SQL Statements in a special script file. Custom SQL Directives are used to control Error Handling and Conditional Execution of the SQL Statements. I use this when I don't have access to an SQL tool such as SQL*Plus... Combining a little ingenuity with the 'awesome power of SQR' I can accomplish just about any required task I may encounter...

Click on the Lizard to download the SQL*Engine program. Below you will find sample output and a description of the SQL*Engine Directives... See program documentation for more details...

Click on the 'other' lizard to download the TDFUNC.SQC file used in SQL*Engine. This Function library is loaded with custom routines which will be featured in future editions of SQRTOOLS.COM...

Update 12/31/1999 - Additional SQL*Engine Directives

Two powerful SQL*Engine directives have been added. @SET= assigns values to substitution variables. @SCRIPT= allows additional SQL scripts to be called from within the current SQL*Engine script. Use @SET to pass parameters (in the form of substitution variables) to the embedded script. See Program Documentation for more details... ALSO... See SAMPLE page (added 01/02/2000) to see @SET/@SCRIPT in action... More pictures too!

Update 04/11/2000 - More SQL*Engine Directives/Parameters

Some serious enhancements have been made to SQL*Engine... Database values can be assigned to substitution variables using the @SET= directive with the new parameters @ROWS and @COL... and the @SELECT=/@LIST= directives... Upper and Lower case can be applied with the @UPPER/@LOWER parameters of the @SET= directive... The @PROMPT parameter allows substitution variables to be set via input prompt (ideal for passwords)... Boolean parameters @AND, @XOR and @OR allow bit masking of substitution variables... report output is redirected with the @REPORT= directive... The @MSG= directive displays messages with text and resolved variables... SQL*Engine has become a language in itself while utilizing the dynamic SQL capabilities of SQR...

SQL Directives

@STEP=<Step Name>

Assign a Step Name (Used to reference STEP in subsequent statement)

@COND=<Step Condition> [.<Step Name>]

Control Execution based on results of a prior step.

<Step Name> is optional - default is previous step.

CONDITIONS: PASS, FAIL, SKIP, ZERO or SOME.

@COUNT=<Table Name> <followed by WHERE Clause lines>

Selects Row Count from <Table Name> and assigns ROW Status of

ZERO or SOME. This can be used as @COND condition in later step.

@ON_ERROR=STOP

Halts execution of SQL script - unless Warning Level error.

@ON_ERROR=CONTINUE

Continues processing SQL script - unless Fatal Level error.

@ON_ERROR=WARNING=<Error Code>

Assign Warning Level to a particular error. This is actually a

pattern that is matched to the $sql-error SQR variable.

@ON_ERROR=FATAL=<Error Code>

Assign Fatal Level Errors... this overrides @ON_ERROR=CONTINUE.

@ON_ERROR=CLEAR

Clears all prior WARNING and FATAL Error Levels assigned.

New SQL*ENGINE Directives - Added 12/31/1999:

@SET=<Variable Name>=<Value>

Create substitution variable. Any occurances will be resolved within script.

@SCRIPT=<Path/Filename>

Call another SQL script within the current script.

MORE SQL*Engine Directives - Added 01/04/2000 thru 06/04/2000

See program documentation for more details...

@REPORT=<Report Path/Filename>

Redirect SQL*Engine report output.

@MSG=<message text/substitution variable(s)>

Display messages using text and resolved variables.

@LIST=<column; column; column; etc>

Builds SELECT list for subsequent @SELECT directive.

@SELECT=<Table Name> <followed by WHERE clause lines>

Selects columns from table (columns specified by @LIST directive)

	@SET=<variable>=@UPPER
	@SET=<variable>=@LOWER
	@SET=<variable>=@ROWS[:step]
	@SET=<variable>=@PROMPT[:message_id]
	@SET=<variable>=@COL:seq
	@SET=<variable>=@AND[:mask]
	@SET=<variable>=@XOR[:mask]
	@SET=<variable>=@OR[:mask]

        Additional @SET directive parameters. Sets values to upper or lower
        case... assign variable values via user prompts (see documentation for
        pre-defined message identifiers)... set value to row count of any prior
        step... Set value to column returned by @LIST/@SELECT directives
        (Column specified by sequence number from 1 to n)... Bit Mask (Boolean)
        operators can mask substitution variable values (@AND/@XOR/@OR) -
        Masking the values can be useful in option validation...

@TIMING=<ON/OFF>

SQL Statement Timings... Report lists Start/End/Elapsed time.

SQL Execution Summary included at end of report.

Each Step listed with SQL status, Row status, Row count and conditions.

Sample SQL*Engine Script to Create a Table - with SQL Directives

*
* SQL*Engine Script - Create SQLBase Table
*
@ON_ERROR=CONTINUE
@STEP=CREATE
CREATE TABLE PS_TD_TEST_TBL (OPRID CHAR(8) NOT NULL,
   RUN_CNTL_ID CHAR(30) NOT NULL,
   SETID CHAR(5) NOT NULL,
   DEPTID CHAR(10) NOT NULL)
/
@STEP=COMMIT1
@COND=PASS.CREATE
COMMIT
/
@STEP=INDEX
@COND=PASS.CREATE
CREATE UNIQUE INDEX PS_TD_TEST_TBL ON PS_TD_TEST_TBL (OPRID,
   RUN_CNTL_ID,
   SETID,
   DEPTID)
/
@STEP=COMMIT2
@COND=PASS.INDEX
COMMIT
/

SQL*Engine Script Results...

Report ID:  TDSQL     SQL*ENGINE - DYNAMIC SQL SCRIPT PROCESSOR


=======================================================================
SQL Command/Results
=======================================================================

DIR> @ON_ERROR=CONTINUE

DIR> @STEP=CREATE

SQL> CREATE TABLE PS_TD_TEST_TBL (OPRID CHAR(8) NOT NULL,
SQL>    RUN_CNTL_ID CHAR(30) NOT NULL,
SQL>    SETID CHAR(5) NOT NULL,
SQL>    DEPTID CHAR(10) NOT NULL)

MSG> SQL Executed - #Rows:                0
MSG> SQL Execution Status: PASS

-----------------------------------------------------------------------

DIR> @STEP=COMMIT1

DIR> @COND=PASS.CREATE

SQL> COMMIT

MSG> SQL Executed - #Rows:                0
MSG> SQL Execution Status: PASS

-----------------------------------------------------------------------

DIR> @STEP=INDEX

DIR> @COND=PASS.CREATE

SQL> CREATE UNIQUE INDEX PS_TD_TEST_TBL ON PS_TD_TEST_TBL (OPRID,
SQL>    RUN_CNTL_ID,
SQL>    SETID,
SQL>    DEPTID)

MSG> SQL Executed - #Rows:                0
MSG> SQL Execution Status: PASS

-----------------------------------------------------------------------

DIR> @STEP=COMMIT2

DIR> @COND=PASS.INDEX

SQL> COMMIT

MSG> SQL Executed - #Rows:                0
MSG> SQL Execution Status: PASS

-----------------------------------------------------------------------


     *** STEP Execution Results ***

           Step Name    SQL Status     ROW Status     Condition

RPT> @STEP.CREATE       PASS           ZERO
RPT> @STEP.COMMIT1      PASS           ZERO           PASS.CREATE
RPT> @STEP.INDEX        PASS           ZERO           PASS.CREATE
RPT> @STEP.COMMIT2      PASS           ZERO           PASS.INDEX

=======================================================================
SQL*Engine  by Tony DeLia     tdelia@erols.com

SQL*Engine STEP Execution Results (See Report Above)...

	STEP1 - CREATE Executed successfully... assigned status of PASS.
	STEP2 - COMMIT conditionally executed (thru PASS.CREATE)
	STEP3 - INDEX executed also due to PASS.CREATE condition.
	STEP4 - COMMIT2 executed due to INDEX step having PASS status.

        If STEP1 (assigned stepname CREATE) had FAILed none of the subsequent
        steps would have executed due to conditional processing. An SQL status of
        SKIP would have been assigned to any steps 'skipped' due to @COND.

SQL*Engine... it started small... and keeps growing...

The other day I wrote this utility to process dynamic SQL statements in a file... I added the @ON_ERROR= directive to STOP or CONTINUE when an error was detected... Next I decided to add WARNING and FATAL levels (and the CLEAR capability)... I added conditional processing with the @COND= directive... Each statement encountered is assigned the status codes PASS, FAIL and SKIP... By adding the @STEP= directive I could specify any step in my condition... Finally I added the @COUNT= directive to set an additional condition of ZERO or SOME... The count can be used as an 'Exists' condition... similar to the DO conditions found in Application Engine... What can I add next?

More Sample Scripts...


**********************************************************************
*  Sample 1 - Execute SQL based on current database                  *
**********************************************************************
@ON_ERROR=CONTINUE
@STEP=DB_CHECK
@COUNT=PSDBOWNER
 WHERE DBNAME='TEST'
/
@STEP=DELETE_ALL
@COND=SOME
DELETE FROM PS_SOME_TABLE
/
@STEP=DELETE_WHERE
@COND=SKIP
DELETE FROM PS_SOME_TABLE
 WHERE TYPE = 'A'
/
**********************************************************************
*  Sample 2 - Setting Warning Levels                                 *
**********************************************************************
@ON_ERROR=STOP
@ON_ERROR=WARNING=ORA-00942
DROP TABLE PS_TD_TEST_TBL
/
**********************************************************************
*  Sample 3 - Executing PL/SQL Blocks through SQL*Engine             *
**********************************************************************
@ON_ERROR=STOP
@ON_ERROR=CLEAR
@ON_ERROR=FATAL=ORA-06500
@ON_ERROR=WARNING=ORA-065
*
DECLARE

   cursor c1 is
      select a.rowid,
             lpad(a.zif_activity,6,' ')
        from ps_dummy_table             a
       where a.zif_func               = '98'
         and length(a.zif_activity)   = 4;

   a_rowid                    ROWID;
   a_zif_activity             ps_zgl_cb_prod.zif_activity%TYPE;

   PROCEDURE Update_Activity (u_rowid        ROWID,
                              u_zif_activity VARCHAR2)
   IS
   BEGIN

      update ps_dummy_table       u
         set zif_activity       = u_zif_activity
       where u.rowid            = u_rowid;

   END Update_Activity;

BEGIN

   open c1;

   loop

      fetch c1 into a_rowid,
                    a_zif_activity;

      exit when c1%NOTFOUND;

      Update_Activity(a_rowid,
                      a_zif_activity);

      end loop;

   close c1;

   commit;

END;

/
**********************************************************************
*  Sample 4 - Setting Fatal Error Levels                             *
**********************************************************************
@ON_ERROR=CLEAR
@ON_ERROR=FATAL=ORA-00942
@ON_ERROR=CONTINUE
*
INSERT INTO TRASHCAN
VALUES ('CRAP', 'GARBAGE', 'JUNK', null)
/
**********************************************************************

Feedback

I would appreciate any feedback you may have on this site. Send mail to tdelia@erols.com or click on the Octopus.

Technical difficulties?

Please report any technical difficulties you may encounter to the address above OR click on the Octopus. Thanks.

Tony DeLia - Updated June 04, 2000