Re: [Q] How to return a result set from a stored procedure through ODBC?

Thomas Kyte wrote:

> With 7.2 and 7.3 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 a 7.2 / 7.3 example in SQL*Plus and PRO*C. It should be noted
> that 7.3
> introduced the concept of "weakly" typed cursors so the forward
> declare of the
> result set in the empCurType package would not have to include the
> defining
> query. (in 7.3 the cursor type can simply be "type empCur is ref
> cursor;", you
> don't need a 'shape' or record or result set to declare the cursor)
>
> I don't use ODBC so not sure what drivers support the cursor type.
>
> create or replace package empCurType
> as
> cursor c1 is select ename, mgr, dept.deptno, dname, loc from
> emp, dept;
>
> type empCur is ref cursor return c1%rowtype;
>
> procedure open_cursor( p_cursor in out empCur,
> p_deptno in
> number );
> end;
> /
> show errors
>
> create or replace package body empCurType
> as
>
> procedure open_cursor( p_cursor in out empcur, p_deptno in number )
> is
> begin
>
> if ( p_deptno in ( 10, 20 ) ) then -- open local query
> open p_cursor for
> select ename, mgr, dept.deptno, dname, loc
> from emp, dept
> where emp.deptno = dept.deptno and
> dept.deptno = p_deptno;
> else
> open p_cursor for
> select ename, mgr, dept.deptno, dname, loc
> from emp_at_aria.world, dept_at_aria.world
> where emp.deptno = dept.deptno and
> dept.deptno = p_deptno;
> end if;
> end open_cursor;
>
> end empCurType;
> /
> show errors
>
> REM --------------- SQL*Plus using RefCursor to display results ------
>
> variable C refcursor
> exec empCurType.open_cursor( :C, 10 )
>
> print C
>
> REM ----------- PRO*C Getting the cursor ------------------
> REM
> REM And then the pro*c would look like 'normal' pro*c code except that
> the EXEC
> REM SQL OPEN Cursor statement would now be a pl/sql call as follows:
> REM
> REM
> REM static void process()
> REM {
> REM EXEC SQL BEGIN DECLARE SECTION;
> REM typedef char asciiz;
> REM EXEC SQL TYPE asciiz IS STRING(100);
> REM SQL_CURSOR my_cursor;
> REM asciiz ename[40];
> REM int mgr;
> REM short mgr_i;
> REM int deptno;
> REM asciiz dname[50];
> REM asciiz loc[50];
> REM int i;
> REM EXEC SQL END DECLARE SECTION;
> REM
> REM EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
> REM
> REM
> REM EXEC SQL ALLOCATE :my_cursor;
> REM
> REM EXEC SQL EXECUTE BEGIN empCurType.open_cursor( :my_cursor, 10
> ); end;
> REM END-EXEC;
> REM
> REM for( ;; )
> REM {
> REM EXEC SQL WHENEVER NOTFOUND DO BREAK;
> REM EXEC SQL FETCH :my_cursor
> REM INTO :ename, :mgr:mgr_i, :deptno, :dname,
> :loc;
> REM
> REM printf( "%s, %d, %d, %s, %s\n",
> REM ename, mgr_i?-1:mgr, deptno, dname,
> loc );
> REM }
> REM EXEC SQL CLOSE :my_cursor;
> REM }
>
> On Thu, 26 Jun 1997 22:20:29 GMT, iarld_at_connectnet.com (Lee Doty)
> wrote:
>
> >Our company is porting a large product (wharehouse automation system)
 

> >from MS-SQL Server to Oracle.
> >
> >Here's the problem: In order to perform many operations within their
 

> >contractual time limits, we used quite a few stored procedures. Many
 

> >of these procedures return a result set (or 2), and we simply use
> >SQLFetch and SQLMoreResults to get at these results.
> >
> >According to the Oracle Docs, this isn't kosher in Oracle. (hassle)
> >
> >2 questions:
> >
> >Why? Is this an encapsulation issue? couldn't they have handled
 this
> >better? Perhaps restricting it to only (maybe explicitly declared)
> >one result set?
> >
> >What is an efficient way around this? Lets assume you have a complex
 

> >stored procedure (actually several interconnected SPs) that does a
 lot
> >of manipulation (side effects) of data, then returns a result set of
> >10 rows to the C++ app through ODBC.... how would you implement this?
 

> >
> >
> >Any help from the oracle experts would be very much appreciated!
> >
> >thanks.
> >
> >
> >-Lee
> >----
> >"It can't rain all the time"
>
> Thomas Kyte
> tkyte_at_us.oracle.com
> Oracle Government
> Bethesda MD
>
> http://govt.us.oracle.com/ -- downloadable utilities
>
> -----------------------------
> ----------------------------------------------
> Opinions are mine and do not necessarily reflect those of Oracle
> Corporation

How would you do that with ORacle Objects and VB 5.0? Thanks. Received on Tue Jul 01 1997 - 00:00:00 CDT