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

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. Received on Wed Aug 04 1999 - 11:30:01 CDT