Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Help,Declare a cursor in stored procedure.
In article <5g9qf9$g98$1_at_spasmolytic.openix.com>, <shu_at_openix.com> wrote:
> Inside stored procudure, how do I declare a cursor based
> on a table to be create before the cursor is declared ?
> Since the table doesn't exist, I got compiling error.
> Is there a way work out this? Here is what I did:
>
>CREATE OR REPLACE PROCEDURE DoSomething([Parameter list])
>IS
>BEGIN
> .....
> /* Dynamic create table by using DBMS_SQL package */
> create_object('table', 'my_table_a', sub_query);
>
> DECLARE
> CURSOR a_cur
> IS
> SELECT a1,a2,..,an FROM my_table_a
> WHERE .....;
> BEGIN
> /* Do something here */
> END;
>END
If you have just created the table, I assume you are inserting some
records before trying to select some.
Create the table, then create the procedure, then drop the table. I'm not sure, but I think this will work.
If that doesn't work, then you will have to use DBMS_SQL to read from the table.
Steve Cosner Received on Sat Mar 15 1997 - 00:00:00 CST