Re: Returning results from a Stored procedure

From: Stephan Born <stephan.born_at_beusen.de>
Date: Mon, 25 Oct 1999 15:07:52 +0200
Message-ID: <38145628.E2464BB9@beusen.de>

Pinne schrieb:

> Are these types of statement possible from within an SP:
> SELECT col1, col2 FROM Table1;
> ?
> Ie. would it produce the same result as executing it from within SqlPlus
>
> SqlPlus gives me an error when i try to compile.
> Unless, i've cludged up the syntax. I doesn't seem possible.
>
> Is there another way to return result-sets from SP:s
>
> /Regards

You have to do this

create procedure test

    val1        <type>;
    val2        <type>;

begin

    select col1, col2
    into val1, val2
    from table1;
end test;

But the SQL-statement must not return more then 1 line otherwise it will result in an exception.
Less than 1 will raise an exception, too.

To process statements which will return mor than one line, you have to use cursors:

create procedure test

    Cursor CurTest is

        select col1, col2
        from table1;

    x varchar2(1000);
begin

    for RecTest in CurTest loop

        x := RecTest.col1........;
    end loop;
end test;

Hope I understood your problem right. Let me know.

Regards, Stephan
--

---

Dipl.-Inf. (FH) Stephan Born   | beusen Consulting GmbH
fon: +49 30 549932-17          | Landsberger Allee 392
fax: +49 30 549932-29          | 12681 Berlin
mailto:stephan.born_at_beusen.de  | Germany
---------------------------------------------------------------

Received on Mon Oct 25 1999 - 08:07:52 CDT