Re: Geting the resultset out of a PROCEDURE INTO A select statement

Can you return a resultset through ODBC?

Thanks in Advance,
Randy Brokaw
rbrokaw_at_tanningtech.com

Thomas Kyte wrote in message <35ebd09a.15155933_at_192.86.155.100>...
>A copy of this was sent to dfrank <dfrank_at_fallet.com>
>(if that email address didn't require changing)
>On Mon, 31 Aug 1998 17:25:56 +0200, you wrote:
>
>>Hi,
>>
>>I need help for the following problem:
>>I have a procedure which returns a CURSOR. I would
>>like to issue a SELECT in SQLPlus which displays its contents.
>>Something like this:
>>/* My Type as a CURSOR Variable*/
>>TYPE MYCUR IS REF CURSOR;
>>
>>/* MY Procedure doing the select */
>>CREATE PROCEDURE GET_CUR(db_mycur IN OUT MYCUR) AS
>>BEGIN
>> OPEN db_mycur for
>> SELECT * FROM A;
>>END;
>>
>>/* MY SELECT Statement which calls the PROCEDURE and returns
>> the result of the CURSOR */
>>SELECT * FROM GET_CUR; ????
>>
>>As you can see, I don't know how this select has to look like.
>>
>>Thanks for your help
>>
>>Stefan Fallet (sfallet_at_fallet.com)
>
>
>With 7.2 on up of the database you have cursor variables. Cursor variables
>are cursors opened by a pl/sql routine and fetched from by another
application
>or pl/sql routine (in 7.3 pl/sql routines can fetch from cursor variables
as
>well as open them). The cursor variables are opened with the privelegs of
the
>owner of the procedure and behave just like they were completely contained
>within the pl/sql routine. It uses the inputs to decide what database it
will
>run a query on.
>
>Here is an example:
>
>
>
>create or replace package types
>as
> type cursorType is ref cursor;
>end;
>/
>
>create or replace function sp_ListEmp return types.cursortype
>as
> l_cursor types.cursorType;
>begin
> open l_cursor for select ename, empno from emp order by ename;
>
> return l_cursor;
>end;
>/
>
>
>REM SQL*Plus commands to use a cursor variable
>
>variable c refcursor
>exec :c := sp_ListEmp
>print c
>
>
>
>-----------------------------------------------------
>
>and the Pro*c to use this would look like:
>
>
>static void process()
>{
>EXEC SQL BEGIN DECLARE SECTION;
> SQL_CURSOR my_cursor;
> VARCHAR ename[40];
> int empno;
>EXEC SQL END DECLARE SECTION;
>
> EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
>
> EXEC SQL ALLOCATE :my_cursor;
>
> EXEC SQL EXECUTE BEGIN
> :my_cursor := sp_listEmp;
> END; END-EXEC;
>
> for( ;; )
> {
> EXEC SQL WHENEVER NOTFOUND DO break;
> EXEC SQL FETCH :my_cursor INTO :ename, empno;
>
> printf( "'%.*s', %d\n", ename.len, ename.arr, empno );
> }
> EXEC SQL CLOSE :my_cursor;
>}
>
>
>
>Thomas Kyte
>tkyte_at_us.oracle.com
>Oracle Government
>Herndon VA
>
>--
>http://govt.us.oracle.com/ -- downloadable utilities
>
>---------------------------------------------------------------------------
-
>Opinions are mine and do not necessarily reflect those of Oracle
Corporation
>
>Anti-Anti Spam Msg: if you want an answer emailed to you,
>you have to make it easy to get email to you. Any bounced
>email will be treated the same way i treat SPAM-- I delete it.
Received on Tue Sep 01 1998 - 10:06:39 CDT