Re: Parameterized cursors in PL/SQL

From: Ed Bruce <edward_at_igate1.hac.com>
Date: 1995/11/15
Message-ID: <edward-1511950916500001_at_r-618macip18.hac.com>#1/1

In article <48d59a$f44_001_at_trout.rivm.nl>, Rene.Hoekstra_at_rivm.nl (Rene Hoekstra) wrote:

> Hi all,
>
> Can I pass a table name as a parameter to a cursor in
> PL/SQL as in :
>
> declare
> cursor c_1 (tname IN VARCHAR2) is
> select <col1>, <col2> ... <coln>
> from tname
> where <where_clause>;
> ...
> begin
> ...
> open c_1 (<tablename>)
> ...
> end;
>
> I've tried the above statement, but it doesn't work. Anyone
> have a solution to this ?

Use DBMS_SQL and create a dynamic SQL statement, something like:

procedure do_it (

   Table_Name in VARCHAR2,

   col1_type  in out INTEGER,
   col2_type  in out VARCHAR2,
   coln_type  in out DATE )

is

   Statement VARCHAR2(200) := 'select <col1>, <col2>, ... <coln> ' ||
                              'from ' || Table_Name ||
                              'where <where_claues';

   Statement_Cursor INTEGER := DBMS_SQL.Open_Cursor;
   Results          INTEGER;

begin

   DBMS_SQL.Parse(

      Statement_Cursor,
      Statement,
      DBMS_SQL.V7);

   DBMS_SQL.Define_Column( Statement_Cursor, 1, col1_type );

   DBMS_SQL.Define_Column( Statement_Cursor, 2, col2_type, 30 );    DBMS_SQL.Define_Column( Statement_Cursor, 3, coln_type );

• Assume this returns only one row Results := DBMS_SQL.Execute_And_Query( Statement_Cursor );

   DBMS_SQL.Column_Value( Statement_Cursor, 1, col1_type );
   DBMS_SQL.Column_Value( Statement_Cursor, 2, col2_type );
   DBMS_SQL.Column_Value( Statement_Cursor, 3, coln_type );

   DBMS_SQL.Close_Cursor( Statement_Cursor ); end;

This example isn't guaranteed to work (I don't have access to SQL+ while reading news). But it is at least correct in the basic steps neccesary to generate dynamic SQL in PL/SQL.

-- 
Ed Bruce
edward_at_igate1.hac.com
Key fingerprint =  62 8D FC 8F 27 2F 89 D0  8B 38 7E 34 33 74 C2 36

Received on Wed Nov 15 1995 - 00:00:00 CET