Possible to create a table from an oracle cursor?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Possible to create a table from an oracle cursor?

  1. #1
    Join Date
    Mar 2006
    Posts
    74

    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?

  2. #2
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    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
    Ales
    The whole difference between a little boy and an adult man is the price of toys

  3. #3
    Join Date
    Mar 2006
    Posts
    74
    Quote Originally Posted by ales
    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!

  4. #4
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452
    using plsql tables may give better performance than ref cursor

    Anybody like to hammer me on this ?
    There are three kinds of lies: Lies, damned lies, and benchmarks...

    Unix is user friendly. It's just very particular about who it's friends are.

    Oracle DBA

  5. #5
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Quote Originally Posted by cjard
    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,
    Ales
    The whole difference between a little boy and an adult man is the price of toys

  6. #6
    Join Date
    Mar 2006
    Location
    Buenos Aires, Argentina
    Posts
    25
    Quote Originally Posted by ales
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width