Re: Dynamic SQL

Try using OWNER.TABLENAME instead of just the table name

Ferdinand Swaters
Incore Automatisering B.V.

Adam Gresh heeft geschreven in bericht <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 - 13:41:42 CET