Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Accessing a cursor using dynamic SQL

Re: Accessing a cursor using dynamic SQL

From: Jim Kennedy <jim>
Date: Tue, 25 Jul 2006 07:39:21 -0700
Message-ID: <ZvCdnSHbcKD4sVvZnZ2dnUVZ_tudnZ2d@comcast.com>

<mikeg13_at_gmail.com> wrote in message
news:1153836387.177135.244110_at_i3g2000cwc.googlegroups.com...
> I am creating dynamic SQL, from the ALL_TAB_COLS table, and the output
> is unknown until runtime. My SELECT statement may select 4 columns, 20
> columns, or 200. Once I have built this SQL, how can I run it into a
> cursor? How do I define that cursor?
>
> It would be easy enough to do if I could get this to work, but
> evidently it does not, saying that it is finding LOOP when it is not
> expected.
>
> FOR x IN (v_sql_stmt)
> LOOP
> IF x.variable_name THEN dbms_output.put_line(x.variable_name); END
> IF;
> END LOOP;
>
> Using the OPEN-FETCH-CLOSE for cursors, how can I define the cursor
> without knowing the structure of the output record? It seems the
> %ROWTYPE is what I need, but that only works if you have a table to
> reference. I would like to create the rowtype based on the out put
> that my dynamic SQL would return. Any help or pointers would be
> appreciated.
>
> Mike
>

In general you should try to not do this type of thing if it is frequently executed sql. However, the way to do it is with the dbms_sql package. That package offers a nice set of functions and procedures to deal with dynamic sql. (sort of an oci interface for pl/sql) Jim Received on Tue Jul 25 2006 - 09:39:21 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US