Possible to create a table from an oracle cursor?

[cjard]

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?

[ales]

There's no access to cursor's metadata in pl/sql.
A java stored procedure may be the way to go. The ResultSetMetaData class gives you the description of the cursor's columns that you can use in the create table statement.

Creating tables on the fly is a strange design, by the way.

HTH

[cjard]

Creating tables on the fly is a strange design, by the way.

Yes.. for the original app, it was the only way the person designing the crystal report could have some data to work with and preview the report.

I have since discovered that I can save the output from the cursor as an xml or xsd file and crystal can use this.. Temp tables no longer required! Thanks for the tip re java though.. i;d forgotten all about it!

[simply_dba]

using plsql tables may give better performance than ref cursor

Anybody like to hammer me on this ?

[ales]

I have since discovered that I can save the output from the cursor as an xml or xsd file and crystal can use this.. Temp tables no longer required!

Looks interesting, could you point out how to do that, please?
Thanks,

[dgcampos]

Looks interesting, could you point out how to do that, please?
Thanks,

i want to know that too, how did yo manage to create your XML from this scenario?

thanks

daniel