Dynamic SQL

From: Adam Gresh <adamzg_at_home.com>
Date: Wed, 24 Mar 1999 03:44:25 GMT
Message-ID: <36F86224.76C4E0F7_at_home.com>

I looked over the documentation on ORACLE's site and found a small amount of information on dynamic SQL, but I couldn't make it work. Can anybody tell me how to run a simple string as SQL? The reason is that I would like to dynamically specify table and column names at run time.

Something along the lines of...
declare

	clientvalue varchar(20);
	keyfielddata varchar(64);
	data	varchar(64);
	selectclause varchar(300);

begin
data := 'Unassigned';
clientvalue:='CLIENT';
keyfielddata:='CLIENTID';
selectclause := 'select ' || keyfielddata ||

        ' into data from ' || clientvalue;
--select keyfielddata into data from "client";
dbms_output.put_line (selectclause);
--yields 'select CLIENTID into data from CLIENT'
dbms_output.put_line (data);
--yields 'Unassigned' since I can't get the query to execute.
pause;
EXEC selectclause;
--EXECUTE selectclause;

end;
...but, of course, this does everything but execute the string as a query. I did notice that PL/SQL will resolve a variable if used as a column name, but not if used as a table name. That is, I can use the statement 'select keyfielddata into data from CLIENT' and get a result, but not 'select keyfielddata into data from clientvalue' (see sample below).

Sample output...
SQL> set serveroutput on;
SQL> declare
  2 clientvalue varchar(20);
  3 keyfielddata varchar(64);
  4 data varchar(64);
  5 selectclause varchar(300);
  6 begin
  7 --data := 'Unassigned';
  8 clientvalue:='CLIENT';
  9 keyfielddata:='CLIENTID';
 10 select keyfielddata into data from CLIENT;  11 dbms_output.put_line (selectclause);  12 dbms_output.put_line (data);
 13 --EXECUTE :selectclause;
 14 end;
 15 /
CLIENTID PL/SQL procedure successfully completed.

---

set serveroutput on;
declare

	clientvalue varchar(20);
	keyfielddata varchar(64);
	data	varchar(64);
	selectclause varchar(300);

begin
--data := 'Unassigned';

clientvalue:='CLIENT';
keyfielddata:='CLIENTID';
select keyfielddata into data from clientvalue; dbms_output.put_line (selectclause);
dbms_output.put_line (data);
--EXECUTE :selectclause;

end;

ORA-06550: line 10, column 36:
PLS-00356: 'CLIENTVALUE' must name a table to which the user has access
ORA-06550: line 10, column 1:

PL/SQL: SQL Statement ignored

Anyone? Received on Wed Mar 24 1999 - 04:44:25 CET