JL Computer Consultancy

Dynamic Selects in PL/SQL

Prior to Aug 1999


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:

open_cursor

opens a cursor (generates a meaningless number as a handle)

parse

parse a string, and associate it with a cursor handle

describe_columns

build an array describing the columns selected by a cursor handle

define_columns

associates the Nth column of a cursor with a local variable

column_value

transfers the Nth column of a fetched cursor into the local variable

close_cursor

close a cursor

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.

Back to Main Index of Topics