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.