Re: Can anyone help me with some dbms_sql dynamic sql coding...

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 29 Jul 1999 19:09:05 GMT
Message-ID: <37ada406.115094086@newshost.us.oracle.com>

A copy of this was sent to "Simon" <jjosserand_at_email.msn.com> (if that email address didn't require changing) On Thu, 29 Jul 1999 14:47:54 -0500, you wrote:

>I just need to quickly open a cursor, keep it open for reuse, parse a sql
>query that returns a single varchar2 value from a temporary interface table
>into a plsql routine so that value can be validated.
>Briefly the picture is this...
>
>Temp Int Table contains column names to a target table whose columns contain
>data to be validated.
>I must get the name of the column which contains that data from the temp
>table then use that column name (which is in a variable at this point) to
>qualify the data in the target table. In plsql I cannot append a variable
>to a table as in TABLENAME.varname to qualify a column and have been told by
>Oracle support that I need to use dbms_sql dynamic sql to do the job and
>they won't write the code for me.
>
>I spent 50 bucks on the built-in packages book and spent 9 hours yesterday
>studying and trying various scenarios with no luck and lots of syntax and
>other obscure errors.
>
>Please help ASAP!!!
>:)
>Thanks
>
>

I think something like the following should help you get going. The routine dynquery will return any column (p_cname_to_retrieve) from a table (p_tname) such that some column (p_cname_to_qualify) is equal to a specific value (p_value_to_restrict).

It opens a cursor but once and parses individual queries against it...

SQL> create or replace package demo
  2 as
  3

  3  function  dynquery( p_tname             in varchar2,
  4                      p_cname_to_retrieve in varchar2,
  5                      p_cname_to_qualify  in varchar2,
  6                      p_value_to_restrict in varchar2 ) return varchar2;

  7
  7 end;
  8 /

Package created.

SQL> 
SQL> 
SQL> create or replace package body demo

  2 as
  3
  3 g_theCursor integer default dbms_sql.open_cursor;   4
  4

  4  function  dynquery( p_tname             in varchar2,
  5                      p_cname_to_retrieve in varchar2,
  6                      p_cname_to_qualify  in varchar2,
  7                      p_value_to_restrict in varchar2 ) return varchar2
  8  is
  9      l_columnValue   varchar2(2000);
 10      l_status        integer;
 11      l_query         varchar2(1000) default 'select ' || p_cname_to_retrieve
||
 12                                             '  from ' || p_tname ||
 13                                             ' where ' || p_cname_to_qualify

|| ' = :x';
 14 begin

 15      dbms_sql.parse(  g_theCursor,  l_query, dbms_sql.native );
 16          dbms_sql.bind_variable( g_theCursor, ':x', p_value_to_restrict );
 17      dbms_sql.define_column( g_theCursor, 1, l_columnValue, 2000 );
 18  
 18      l_status := dbms_sql.execute(g_theCursor);
 19  
 19      if ( dbms_sql.fetch_rows(g_theCursor) <= 0 )
 20      then
 21          l_columnValue := NULL;
 22      else
 23          dbms_sql.column_value( g_theCursor, 1, l_columnValue );
 24      end if;
 25  
 25      return l_columnValue;

 26 end ;
 27
 27
 27 end;
 28 /

Package body created.

SQL>
SQL> exec dbms_output.put_line( demo.dynquery( 'emp', 'empno', 'ename', 'KING' ) )
7839

PL/SQL procedure successfully completed.

SQL>
SQL> exec dbms_output.put_line( demo.dynquery( 'emp', 'ename', 'empno', '7844' ) )
TURNER PL/SQL procedure successfully completed.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

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

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Jul 29 1999 - 14:09:05 CDT