Using Oracle8's Updated DBMS_SQL Package to Parse Large SQL Strings

By Mahesh Vallampati

Take advantage of Oracle8's updated DBMS_SQL package that places no limits on the size of PL/SQL statements.

The DBMS_SQL package, one of the packages supplied with Oracle Data Server, is a powerful tool for evaluating and executing dynamic SQL expressions. With the DBMS_SQL package, you can write stored procedures and anonymous PL/SQL blocks using dynamic SQL and parse any DML or DDL statement.

The most important component of the DBMS_SQL package is the PARSE procedure. In previous releases of Oracle Data Server, the PARSE procedure's statement parameter was VARCHAR, which limited the size of the SQL statement to 2,000 characters. However, with the release of Oracle8, this procedure has been overloaded to a TABLE OF VARCHAR2, thus setting no limit on the size of the dynamic SQL statement. There are many uses for the updated package, one of which I'll demonstrate. As part of this demonstration, I'll explain a method for executing anonymous PL/SQL blocks and extracting the result from the block to the calling program.

New Overloaded Procedure DBMS_SQL.PARSE

Oracle7 Release 7.3.3.3.0 described the DBMS_SQL.PARSE procedure as follows:

PROCEDURE dbms_sql.parse

Argument	NameType	In/Out Default?
--------	--------	----------------
C	NUMBER(38)	IN
STATEMENT	VARCHAR2	IN
LANGUAGE_FLAG	NUMBER(38)	IN

The following is Oracle8's description of the updated PARSE procedure:

PROCEDURE dbms_sql.parse

Argument Name	Type	In/Out Default?
-------------	----	---------------
C	NUMBER(38)	IN
STATEMENT	TABLE OF VARCHAR2(256)	IN
LB	NUMBER(38)	IN
UB	NUMBER(38)	IN
LFFLG	BOOLEAN	IN
LANGUAGE_FLAG	NUMBER(38)	IN

Notice that the statement type is now TABLE OF VARCHAR2, which is defined as:

TYPE VARCHAR2S TABLE OF VARCHAR2(256)
INDEX BY BINARY_INTEGER;

The parameters of the new PARSE version are:

c	An integer that holds the familiar cursor handle.
LB	An integer designating the lower boundary of the PL/SQL table.
UB	An integer designating the upper boundary of the PL/SQL table
LLFLG	A boolean, which if TRUE, inserts a line after each statement table element. Setting to TRUE is recommended to prevent the PL/SQL compiler from wrapping lines. All database source code is stored in DBA_SOURCE view and its underlying table. Because the text column of that table is limited to 2,000 characters, any code in excess of 2000 characters will wrap.
language_flag	Determines how the database parses the statement. Statements can be parsed as version 6, version 7, or the current database version. The corresponding parameter values are DBMS_SQL.V6, DBMS_SQL.V7, and DBMS_SQL.NATIVE.

By stating an upper and lower boundary, you can build a PL/SQL table and specify the upper and lower boundaries of the table as input arguments. For example, you could specify LB as 3 and UB as 7. The SQL code in the PL/SQL table from 3 to 7 will be parsed and executed. Conceptually, the SQL string is put together as follows:

string:=statement(lb) || statement (lb+1) ||   ||statement(ub);

The ability to build a PL/SQL TABLE OF VARCHAR2s(256) and specify the upper and lower boundary of the table as input arguments to DBMS_SQL.PARSE lets you exceed the 2,000-character limitation. This is particularly useful when evaluating complicated business rules and expressions.

Using the New Version of DBMS_SQL.PARSE

One use for the new DBMS_SQL procedure is to extract a value from an anonymous PL/SQL block. I've included code to demonstrate this procedure. First, however, you must create both a packaged variable and a function to retrieve it.

To create a packaged variable:

CREATE OR REPLACE package myvar AS
        PRAGMA RESTRICT_REFERENCES(myvar,WNDS,WNPS,RNDS, RNPS);
        RESULT number;
END myvar;

Notice that I've subjected the package to a PRAGMA RESTRICT_REFERENCE restriction. Without this restriction, the function that retrieves the value of the variable will return the familiar ORA-06571 error, which is, "Function %s does not guarantee not to update database."

To create a function to retrieve the value of the variable:

CREATE OR REPLACE FUNCTION check_calc
        RETURN number IS
BEGIN
        RETURN myvar.result;
END;

The function above merely reads the myvar.result package variable and returns it to the calling program.

Now, to demonstrate the use of the new overloaded version of DBMS_SQL.PARSE: In the example below, I create a TABLE OF VARCHAR2 and an anonymous PL/SQL block. I declare two variables A and B, assign them some arbitrary values, and then pass an expression as an argument to the procedure. This expression could be any valid PL/SQL expression not exceeding 256 characters. (You can evaluate expressions greater than 256 characters by breaking up the character string into multiple pieces of 256 characters and then appending it to the PL/SQL table.)

You could also use any valid user-defined SQL function to assign values to the variables A and B, meaning that A and B can be assigned dynamically. (I decided not to do that in my example below for the sake of simplicity.) For example, A and B could be the age and gender of an individual stored in a database, as follows:

a:=getage(855855855);
b:=getgender(855855855); 
where 855855855 uniquely identifies an individual in a database table.
[/code]

Even the two statements above could be generated dynamically if the information is stored in a table. If these attributes are stored in a database, you could potentially generate the statements in the anonymous PL/SQL block.

If you've used the DBMS_SQL package to execute queries in the past, you used the DEFINE_COLUMN and COLUMN_VALUE procedures to define the output variables and return them to PL/SQL variables. That's fine if you're accessing database data and cursor data, respectively, but, because our example uses an anonymous PL/SQL block available throughout the session to update the package variable, you are not subjected to the limitations of the DEFINE_COLUMN and COLUMN_VALUE procedures.

And finally, note that although sqlstring is a TABLE OF VARCHAR2(256), it should be cast as DBMS_SQL.VARCHAR2s.

CREATE OR REPLACE PROCEDURE democalc (
        /* The calculation string, for example: "result:=A*B;"*/
        calc_text in VARCHAR2,
        return_result IN OUT NUMBER, 
        retval IN OUT NUMBER 
        ) IS
/* Create to hold the PL/SQL Block */
sqlstring dbms_sql.varchar2s;  
cursor1 INTEGER; 
i, j INTEGER;
new_line_flag BOOLEAN:=TRUE;
BEGIN 
        /* Populate the sqlstring array  with the anonymous PL/SQL block */
        sqlstring(1):='declare '; 
        sqlstring(2):='result number(38,2):=0.0;'; 
        sqlstring(3):='a number(38,2):=2;'; 
        sqlstring(4):='b number(38,2):=3;'; 
        sqlstring(5):='begin'; 
        sqlstring(6):=calc_text; 
        sqlstring(7):='myvar.result:=result;'; 
        sqlstring(8):='end;'; 
        i=1;
        j=8;
        /* Assign the cursor */
        cursor1:=DBMS_SQL.OPEN_CURSOR; 
        /* Parse the PL/SQL array block */
        DBMS_SQL.PARSE(cursor1,sqlstring,i,j,new_line_flag,DBMS_SQL.V7); 
        /* Execute the cursor */
        retval:=DBMX_SQL.EXECUTE(cursor1); 
        /* Close the cursor */
        DBMS_SQL.CLOSE_CURSOR(cursor1); 
        /* Execute the function check_calc to obtain the result from the anonymous
        PL/SQL block. */
        RETURN_RESULT:=check_calc; 
END; 
/

As you can see, the new overloaded version of DBMS_SQL.PARSE obviates the previous limits of the DBMS_SQL package and provides even more flexibility to the developer. A system that provides a framework for evaluating simple, dynamic PL/SQL expressions and that uses the above version of DBMS_SQL.PARSE is in development at an Oracle client site by Oracle Consulting and is expected to go live soon.

Acknowledgments

The brilliant idea of using packaged variables to exchange values from anonymous PL/SQL blocks was suggested by Roger Raj, a technical manager with Oracle.