Can i create a cursor without a select statement?

I'm working on a cold fusion project that needs a report combining data from two different databases. I cannot setup the databases as remote servers to each other for security limitations within our company and i cannot fight the system.

Essentially, i need to pass a cursor to a package, process it, and pass a cursor back.

First question, i know i can create an "Array" in PL/SQL by using TYPE myArray IS TABLE OF VARCHAR2(64) and pass the array as a paramter of the procedure in my package, but what if i want to send a cursor as a parameter? The cursor is not coming from a select statement, but as an arugement to the procedure.

For example, I have queryA on DB1 returing this.

Machine1    1/1/02   Office1
Machine2    1/1/02   Office2
Machine3    1/1/02   Office3
Machine4    1/1/02   Office4
Machine5    1/1/02   Office5

I want to pass the entire recordset to the procudure for processing.

Second question, when i get the recordset there, how do i create the cursor to send back? It too is not created from a select statement, but a combination of the recordset sent and a cursor opened in the procedure. What i am trying to do is find machines in one table and not the other.

Example: QueryB on DB2 returns this.

Machine1    1/1/02   Office1
Machine3    1/1/02   Office3
Machine4    1/1/02   Office4
Machine5    1/1/02   Office5
Machine6    1/1/02   Office6

What i want to return is this.

Machine2   NULL     1/1/02     NULL     Office2     NULL
NULL       Machine6  NULL      1/1/02    NULL       Office6

I guess another way is to do a new cursor that is a union query of two existing cursors, but to do that, i still need to somehow pass the first cursor as a parameter.

Please help,

Rob Edwards Received on Wed Feb 26 2003 - 15:52:33 CET