Re: Question about dynamic SQL

From: Steve Johnson <robertoc_at_fyiowa.infi.net>
Date: 1997/11/07
Message-ID: <34633A5F.602E_at_fyiowa.infi.net>#1/1

You'll need to use DBMS_SQL from forms. Everything in DBMS_SQL can be called from forms except DBMS_SQL.COLUMN_VALUE.

Here's what I did and it works with no problem:

 PROCEDURE exec_dynamic_sql

   col1		VARCHAR2(100);
   curs_result	INTEGER;
   curs_handle	INTEGER;
   rows_fetched	INTEGER;

 Begin
   curs_handle := dbms_sql.open_cursor;
   dbms_sql.parse(curs_handle,your_select_statement ,1); -- I know that I have one column and that it is a character length 100

   dbms_sql.define_column(curs_handle,1,col1,100)    curs_result := dbms_sql.execute(curs_handle);  

   LOOP

     rows_fetched := dbms_sql.fetch_rows(curs_handle);
     exit when rows_fetched = 0;
-- dbms_column_value is a stored procedure on the database
     dbms_column_value(curs_handle,1,col1);
     .
     .
     .

   END LOOP;
 End;

PROCEDURE dbms_column_value
  (cursnum integer, colnum integer, colval in out VARCHAR2) IS BEGIN
  DBMS_SQL.COLUMN_VALUE(cursnum,colnum,colval); END; I hope this is enough to get you started.

Steve J.
Wolfgang Roehl wrote:
>
> Hi!
>
> In Forms 4.5 I want to create a dynamic SQL statement where the
> olumn name was primary selected from the sys.dba_tab_columns table.
> The whole thing should run in a cursor loop. I don't know how to put
> the resulting column name into the statement.
>
> Example:
>
> Declare
> CURSOR C1 IS
> SELECT COLUMN_NAME
> FROM SYS.DBA_TAB_COLUMNS
> WHERE TABLE_NAME = 'TAB'
> ORDER BY COLUMN_ID;
> Begin
> OPEN C1;
> LOOP
> FETCH C1 INTO :PARAMETER.COLUMN;
> EXIT WHEN C1%NOTFOUND;
> SELECT 'X'
> FROM TAB_USER
> WHERE Seq_Nr_User = 1
> AND :PARAMETER.COLUMN = (SELECT :PARAMETER.COLUMN
> FROM TAB
> WHERE Seq_Nr = 1);
> IF SQL%NOTFOUND THEN
> <do something>
> END IF;
> END LOOP;
> CLOSE C1;
> End;
>
> I would appreciate any comment or hint.
> Thx in advance
>
> Wolfgang
> --
> ////////////////////////////////////////////////////////////////////////
> / DLR, Central Data Processing Division, CASE Service /
> / __/|__ Wolgang Roehl, Softwareentwicklung und -wartung /
> / /_/_/_/ Lilienthalplatz 7 /
> / |/ D-38108 Braunschweig /
> / Telephone: ++49 (0)531-295-2789, Fax ++49 (0)531-295-2880 /
> ////////////////////////////////////////////////////////////////////////
Received on Fri Nov 07 1997 - 00:00:00 CET