Re: HOWTO: STORED PROCEDURE RETURNING MANY ROWS

From: Tim X <timx_at_nospam.dev.null>
Date: Fri, 17 Jun 2011 16:35:51 +1000
Message-ID: <877h8ldkxk.fsf_at_puma.rapttech.com.au>

Andreas Mosmann <mosmann_at_expires-30-06-2011.news-group.org> writes:

> Hi,
>
> I look for a way to write a stored procedure, that returns more than 1 "row"s.
>
> I want to do something like
>
> select MyProc('A','B','C') from dual;
>
> and get
>
> ID TEXT
> 1 1stRow
> 2 2ndRow
> 3 3rdRow
> .
> 999 999thRow
>
> and so on.
>
> If Possible it would be nice to do something like
>
> select * from MyProc('A','B','C')
>
> or
>
> select * from MyProc('A','B','C') X
> join MyTable Y on X.ID=Y.ID
>
> Is there a way? (Oracle 9/11)

How are you wanting to do this i.e. from within PL/SQL, from java over jdbc, from another language via odbc, dbi? The answers are going to be different depending on your language/environment.

If your using pl/sql, java or perl, you can use a ref cursor, but there is no need to do the select from dual, you can just call the procedure to get the refcursor (as an IN/OUT parameter (or in the case of perl, but it between begin ... end; - you could use a function that returns a refcursor as well) and then just use the refcursor to query as a resultset.

For the second two - well, I've never wanted to do anything like that and suspect perhaps you may be asking the wrong question? Maybe explain why you want to do this as I suspect there is possibly a better solution that will achieve what you want.

Tim

-- 
tcross (at) rapttech dot com dot au

Received on Fri Jun 17 2011 - 01:35:51 CDT