Re: executing a query in a Stored Procedure

From: kbatsche <kbatsche_at_yahoo.com>
Date: Wed, 23 Jun 1999 20:53:32 -0700
Message-ID: <BXhc3.1377$3C3.48504@news.uswest.net>

Yes it does. A PL/SQL procedure can only handle a single row at a time. You need to set up a cursor like..
Create Procedure GetInfo AS

    field1_var mytable.field1%type;
    cursor c1 is select field1 from my table; begin

    open c1;
    loop

        fetch c1 into field1_var;
        exit when c1%notfound;
        dbms_output.put_line(field1_var);

    end loop;
end GetInfo;

this will write the data in field1 to the std_output(screen) alternatively you could create a new table and (in the loop) place the contents of field_var into this new table

Hope this helps!

nealgran_at_my-deja.com wrote in message <7krcta$g8j$1_at_nnrp1.deja.com>...
>When I try to perform a query in a stored procedure it does not compile.
>
>The PL/SQL syntax I used is as follows:
>
> Create Procedure GetInfo AS
> BEGIN
> select Field1 from MyTable;
> END;
>
>Does this have to do with the handling of the resultant table from the
>query? If I replace the query with a directive the Stored procedure
>compiles.
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.
Received on Wed Jun 23 1999 - 22:53:32 CDT