JL Computer Consultancy

Some time in June 1999 a question came up on the comp.databases.oracle.server newsgroup about how to write a PL/SQL package using dbms_sql that could execute an arbitrary select statement, possibly defined at run-time by a user process. In the jargon of the manuals this would have to be an example of 'dynamic SQL, type 4'.

A couple of weeks later I found myself on a long train journey, feeling bored, and with my laptop PC at my side. Having nothing better to do I whipped up the following package as an example of what could be done. Frankly I can't think of any good reason for using this package (let me know if you do), and I only did it for the challenge (Oracle 8.1.5 can actually handle the requirement much more easily than this Oracle 7 - style approach). However, for those interested in what dbms_sql can do there is one script to create a package, and the sample at the bottom of the page shows how to use it

Key Features of the package:

The package has a few public variables to make some information about the dynamic SQL statement visible, temporarily, to the user, and some private variables which are used to 'stack' local copies of this information for use internally.

In particular, immediately after a statement is parsed, the array variable execute_select.v_col_names holds the list of column names, v_col_types holds the internal column type, v_types holds the external column type, and v_col_sizes holds the maximum defined length for the column.

The variable execute_sql.v_types is refreshed after each call to fetch a row, and the columns of the single row fetched are copied into the relevant slot in one of three array variables execute_sql.v_dates, execute_sql.v_numbers, and execute_sql.v_varchar2s. The other arrays are not refreshed so they will become invalid if you parse more than one SQL statement.

To access the data returned, you will need a loop to traverse the v_types array and use the Nth item in that array to determine which of the three 'results' arrays holds the Nth column of data.

The key functions from the dbms_sql package used in the execute_sql package are:

The code is only a quick and dirty job, so it restricts itself to handling only three data types explicitly, date, number, and varchar2; other types are left as an exercise to the reader, but I have include a little table showing how to translate the commones internal types to external types in the comments.

Remember - I only wrote this for fun: I would advise most strongly against using it in a production system, especially if you need to handle large volumes of data.

Sample script showing use of type 4 sql in dbms_sql

rem

rem     Script:        test_dyn.sql

rem     Author:        Jonathan Lewis

rem     Dated:         June 1999

rem     Purpose:       Test dynamic sql package

rem

rem     Notes:

rem     Simple example of use.

rem     Tests particularly that two cursors can be open and do not

rem     interfere with each other.  The loop executes until one of

rem     the cursors tries to fetch past the end of the data 

rem

spool test_dyn.lst

set serveroutput on size 100000

declare

        v_cid1 number;

        v_cid2 number;

        v_test boolean;

begin

        v_cid1 := execute_select.open_cursor('select * from all_users');

        v_cid2 := execute_select.open_cursor('select * from user_tables');

        loop

               exit when not (execute_select.fetch_row(v_cid1));

               if execute_select.v_types(1) = 'V' then

                       dbms_output.put_line(execute_select.v_varchar2s(1));

               elsif execute_select.v_types(1) = 'N' then

                       dbms_output.put_line(execute_select.v_numbers(1));

               elsif execute_select.v_types(1) = 'D' then 

                       dbms_output.put_line(execute_select.v_dates(1));

               end if;

               exit when not (execute_select.fetch_row(v_cid2));

               dbms_output.put('---      ');

               if execute_select.v_types(1) = 'V' then

                       dbms_output.put_line(execute_select.v_varchar2s(1));

               elsif execute_select.v_types(1) = 'N' then

                       dbms_output.put_line(execute_select.v_numbers(1));

               elsif execute_select.v_types(1) = 'D' then 

                       dbms_output.put_line(execute_select.v_dates(1));

               end if;

        end loop;

        execute_select.close_cursor(v_cid2);

        execute_select.close_cursor(v_cid1);

end;

/

spool off

The Output from the sample above:

SYS                       

---      DEPARTMENTS      

SYSTEM                    

---      GROUPS           

OUTLN                     

---      JPL_DEMO         

DBSNMP                    

---      PRODUCTS         

MTSSYS                    

---      PRODUCT_HIERARCHY

AURORA$ORB$UNAUTHENTICATED

---      SALES            

SCOTT                     

---      SALES_SUM        

DEMO                       

PL/SQL procedure successfully completed.