REF Cursors - Can you build result set within PL/SQL

REF Cursors - Can you build result set within PL/SQL

Post by Julie Alle » Thu, 05 Aug 1999 04:00:00



We are working on a Sybase to Oracle Conversion, with PowerBuilder as
our front end.  When converting the procedures, we are passing our
result sets to Powerbuilder via the REF Cursor.  We have a few Sybase
stored procedures that use temporary tables.  It is our preference to
replace the temporary tables with PL/SQL code (Instead of creating
permanent "temp" tables using a session id).  The problem: we can
create reproduce the logic by using cursors and PL/SQL code, but we
can't figure out how to pass the results back to Powerbuilder.  As far
as I can tell, a REF Cursor must be populated by a SQL statement
against physical tables.  We can build a result set and put in into a
collection, but we can't return the result set to Powerbuilder via a
REF cursor.  Does anyone know if there is a way to do this???

Example:
In an existing stored procedure (sybase) they display a matrix of a
professors' office hours in a Powerbuilder datawindow.  This result set
is populated in three steps:
1.  A temporary table sched_mtx is created and populated containing one
record for each hour of the day.
 CREATE TABLE #sched_mtx
 (HOUR int NOT NULL,
  TIME char(10) NOT NULL,
  MONDAY tinyint NOT NULL,
  TUESDAY tinyint NOT NULL,
  WEDENSDAY tinyint NOT NULL,
  THURSDAY tinyint NOT NULL,
  FRIDAY tinyint NOT NULL,
  SATURDAY tinyint NOT NULL,
  SUNDAY tinyint NOT NULL)
2.  A serious of updates are performed gathering data from several
different sources to update the schedule matrix with the office hours
(fairly complex business rules).
3. The records are select from the schedule matrix (ordered by Hour)

We've come up with a few ways that we could rewrite this logic via
standard PL/SQL, but we can't figure out how to pass the results back
to Powerbuilder (via REF cursor).

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.

 
 
 

REF Cursors - Can you build result set within PL/SQL

Post by Thomas Ky » Thu, 05 Aug 1999 04:00:00



(if that email address didn't require changing)


>We are working on a Sybase to Oracle Conversion, with PowerBuilder as
>our front end.  When converting the procedures, we are passing our
>result sets to Powerbuilder via the REF Cursor.  We have a few Sybase
>stored procedures that use temporary tables.  It is our preference to
>replace the temporary tables with PL/SQL code (Instead of creating
>permanent "temp" tables using a session id).  The problem: we can
>create reproduce the logic by using cursors and PL/SQL code, but we
>can't figure out how to pass the results back to Powerbuilder.  As far
>as I can tell, a REF Cursor must be populated by a SQL statement
>against physical tables.  We can build a result set and put in into a
>collection, but we can't return the result set to Powerbuilder via a
>REF cursor.  Does anyone know if there is a way to do this???

>Example:
>In an existing stored procedure (sybase) they display a matrix of a
>professors' office hours in a Powerbuilder datawindow.  This result set
>is populated in three steps:
>1.  A temporary table sched_mtx is created and populated containing one
>record for each hour of the day.
> CREATE TABLE #sched_mtx
> (HOUR int NOT NULL,
>  TIME char(10) NOT NULL,
>  MONDAY tinyint NOT NULL,
>  TUESDAY tinyint NOT NULL,
>  WEDENSDAY tinyint NOT NULL,
>  THURSDAY tinyint NOT NULL,
>  FRIDAY tinyint NOT NULL,
>  SATURDAY tinyint NOT NULL,
>  SUNDAY tinyint NOT NULL)
>2.  A serious of updates are performed gathering data from several
>different sources to update the schedule matrix with the office hours
>(fairly complex business rules).
>3. The records are select from the schedule matrix (ordered by Hour)

>We've come up with a few ways that we could rewrite this logic via
>standard PL/SQL, but we can't figure out how to pass the results back
>to Powerbuilder (via REF cursor).

>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.

one possible solution:

create a real table sched_mtx as above (permanent).

Write the procedure to populate it and do your work.

open a ref cursor against it.

delete all rows in sched_mtx.

return the ref cursor.  due to our multi-versioning and read consistency ,the
ref cursor will see the table as it existed when the ref cursor was opened.
even though you deleted the data, the ref cursor will still see it.

here is a small example showing this at work:

SQL> create or replace package types
  2  as
  3          type refCur is ref cursor;
  4  end;
  5  /

Package created.

SQL>
SQL> drop table demo;

Table dropped.

SQL> create table demo as select * from all_users where 1=0;

Table created.

SQL>
SQL>
SQL> variable r refcursor
SQL>
SQL> create or replace procedure get_rs( p_cursor in out types.refCur )
  2  as
  3  begin
  4          insert into demo select * from all_users where rownum < 10;
  5  
  5          open p_cursor for select * from demo;
  6  
  6          delete from demo;
  7  
  7  end;
  8  /

Procedure created.

SQL> select * from demo;

no rows selected

SQL> exec get_rs( :r )

PL/SQL procedure successfully completed.

SQL> print r

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
SYS                                     0 01-SEP-97
SYSTEM                                  5 01-SEP-97
DBSNMP                                 17 01-SEP-97
TRACESVR                               19 01-SEP-97
WEB$RPPRASAD                         1791 01-SEP-97
WEB$GBRADSHA                         1792 01-SEP-97
WEB$JBROTHER                         1813 01-SEP-97
WEB$KKISER                           1793 01-SEP-97
WEB$GDEYOUNG                         1794 01-SEP-97

9 rows selected.

so the demo table always *appears* to be empty in all cases, only the ref cursor
can "see" data in it.  The data will be available in the ref cursor for at least
as long as the transaction is open (until you commit or rollback) and in most
every case, for much longer (if you hit an ORA-1555 it means you committed and
the data we need is no longer in the rollback segment -- as long as you don't
commit, it'll be there)

In Oracle8i, release 8.1, there are temporary tables as well.  The only
difference is you will not have to issue the delete your self -- otherwise the
above would still be the way to do it (Oracle temporary tables are 'permanently'
created)

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st


Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation

 
 
 

1. PL/SQL: Applying Functions within Ref Cursors.

I have created a package as follows:

CREATE OR REPLACE
PACKAGE server_pkg
IS
TYPE curvar_type IS REF CURSOR RETURN server%ROWTYPE;
END;

and a procedure as follows:

CREATE OR REPLACE
PROCEDURE test_server(curvar_out IN OUT server_pkg.curvar_type)
IS
BEGIN
    open curvar_out FOR SELECT serverid,
                                                          dblink,
                                                          servertype,
                                                          priority
                                            FROM  server
END;

And I am calling and displaying the results as follows.

 declare
 keith_cursor server_pkg.curvar_type;
 keith_rec server%ROWTYPE;
 begin
 DBMS_OUTPUT.ENABLE;
 test_server(keith_cursor);
 LOOP
     FETCH keith_cursor INTO keith_rec;
     EXIT WHEN keith_cursor%NOTFOUND;
     DBMS_OUTPUT.PUT_LINE(keith_rec.serverid);
     DBMS_OUTPUT.PUT_LINE(keith_rec.dblink);
     DBMS_OUTPUT.PUT_LINE(keith_rec.servertype);
     DBMS_OUTPUT.PUT_LINE(keith_rec.priority);
     DBMS_OUTPUT.PUT_LINE('Hello Keith');
 END LOOP;
 end;

Everything here works okay.
What I want to do is to have the cursor defined as
follows:

    open curvar_out FOR SELECT a_function(serverid),
                                                               b_function(serverid),
                                                              priority
                                                FROM  server

                                       ORDER BY priority;

The  functions return varchar2 values. serverid is a numeric column.
I am using Oracle version 7.3.4 on NT.

Can someone please tell me how this can be achieved. I need to be able to return the output of these functions in a result set.

2. dbm-analog for Java?

3. Building temporary result set in PL/SQL

4. System table SYSKEYS

5. Returning a ref cursor with dynamic SQL in PL/SQL

6. looking for a databound control for windowsCE

7. working with PL/SQL REF CURSORs

8. Need V$ Table from PL/SQL

9. PL/SQL REF CURSOR Question

10. REF cursor and PL/SQL table

11. XML from REF CURSOR in PL/SQL

12. JavaSP ResultSet to PL/SQL REF CURSOR

13. PL/SQL function core dumps when returning un-assigned REF CURSOR variable