Possible to create a table from an oracle cursor?
I'm writing an app that wil be used to create and view crystal reports. In order to add some intelligence to the report datasource, it has been decided to use stored procedures that return, as the first argument, a system ref cursor (weak cursor) pointing to the result set.
A .NET app will(can) read from the cursor and cache the data locally (it might be output to excel too) and give the data over to Crystal. This works fine..
However, one sticking point is in the design of the report in the first place. The existing system that works purely on dynamic sql text gets round this easily:
The developer writes the SQL
The query text is prefixed with "CREATE TABLE tmp_report_data AS "
Thus a table is created with all the data the report will contain
The crystal reports designer (not my software, BusinessObjects.com wrote it) is loaded and linked to the temp table
The report is designed
Because I dont know how to create a temp table from my weak cursor, i'm stuck!
I have two options at the moment:
Somehow implement the designer myself (it doesnt seem to just be a component that can be referenced and dropped on a form in my .NET app though) and supply it the data I have successfully downloaded from oracle
Mimick the old behaviour; some code or SQL script that can turn my weak cursor's data into a strongly typed temp table
Somehow connect the designer to the stored procedure (Either programmatically or by list of instructions to the user, i dont mind).
So far, this has failed, because our version of crystal doesnt seem to have Oracle native drivers, and it wont access stored procedures any other way. Also, the CURSOR parameter to the sproc needs to be declared INOUT for crystal, but .NET cant download data from an INOUT cursor; it must be OUT only
I think the middle option is the easiest, but I'm stuck becvause I dont know how to (in PLSQL) read all the data out of my cursor and dump it into a table (whose schema is defined by the cursor schema)
Can anyone help?