(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???
>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
3 type refCur is ref cursor;
SQL> drop table demo;
SQL> create table demo as select * from all_users where 1=0;
SQL> variable r refcursor
SQL> create or replace procedure get_rs( p_cursor in out types.refCur )
4 insert into demo select * from all_users where rownum < 10;
5 open p_cursor for select * from demo;
6 delete from demo;
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'
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