Re: Dynamic SQL

A copy of this was sent to Adam Gresh <adamzg_at_home.com> (if that email address didn't require changing) On Wed, 24 Mar 1999 03:44:25 GMT, you wrote:

>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...

You do not 'select .. into ...' in dynamic sql. You will

1. parse a statement
2. bind input variables/values (eg: select empno from emp where ename = :x, :x is a bind variable)
3. you will bind OUTPUT variables to the query (in the above, empno is an output variable)
4. you will execute the statement
5. you will fetch rows and get the values of the bound column

Here is an example, it does 1, 3, 4, and 5 from above (no inputs to bind in this example):

create or replace function countem( p_tname in varchar2 ) return number is

    l_theCursor     integer default dbms_sql.open_cursor;
    l_columnValue   number default NULL;
    l_status        integer;
begin
    dbms_sql.parse(  l_theCursor,                               -- step 1
                    'select count(*) from ' || p_tname,
                     dbms_sql.native );

    dbms_sql.define_column( l_theCursor, 1, l_columnValue ); -- step 3

    l_status := dbms_sql.execute(l_theCursor);                  -- step 4

    if ( dbms_sql.fetch_rows(l_theCursor) > 0 )
     then
        dbms_sql.column_value( l_theCursor, 1, l_columnValue ); -- step 5

    dbms_sql.close_cursor(l_theCursor);

    return l_columnValue;
end countem;
/

SQL> exec dbms_output.put_line( countem( 'emp' ) ) 15

PL/SQL procedure successfully completed.

>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

no it won't. It will assume the column you are selecting is a bind variable. For example if you code:

declare

    my_variable varchar2(25);
begin

    for x in ( select my_variable from dual )     loop
      ...
    end loop;
end;

it will *NOT* use the value of my_variable to find a column name, rather it will select the value of my_variable from the table (the above for example selects NULL from dual since my_variable is NULL. If I set my_variable := 'something' and then reexecute, it will select the constant 'something' from dual -- not a column named something).

>statement 'select keyfielddata into data from CLIENT' and get a result,
>but not 'select keyfielddata into data from clientvalue' (see sample
>below).
>

see above for an example of using bind variables with dbms_sql....

Also, you might want to:

SQL> select text from all_source where name = 'DBMS_SQL' and type = 'PACKAGE' order by line;

to see the 'readme' (has all the steps you need). As well, see the section in the application developers guide on dynamic sql -- it goes over dbms_sql as well.

Lastly, something to keep in mind:

roles are never enabled during the execution of a procedure.

Try this:

SQL> set role none;
SQL> "statement you want to test to see if it'll work in a procedure"

If you can do it in plus with no roles you can do it in a procedure. If you can't, you must have the privelege from a role and hence won't be able to do it in a procedure.

You probably have the privelege to do what you are trying to do in the procedure via a role. Grant the privelege directly to the owner of the procedure and it'll work.

So, if you create a procedure that uses dbms_sql and get "insufficient privs" or "identifier X does not exist", you probably have the priv to do what you are trying to do via a role... Do the above trick in sqlplus to confirm that and then get the priv granted to you...

>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?
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/    -- downloadable utilities
 
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation