Post reply

Get data from oracle function that returns refcursor

  • April 17, 2012 at 9:03 am

    #260154

    Hi,

    I am trying to execute the following statement in SSIS execute sql task/data flow task->oledb source but not able to succeed.

    When I run this in Oracle sql developer, I am getting the resultset.

    Why I am unable to execute this in SSIS?

    It is a function in Oracle package that returns a REFCURSOR.

    select ose.rpt_pkg.get_report('01-Jan-12') from dual

    I dont have any permission on oracle only read.

    Oracle data is not in our control. It is from third party giving data in this way.

    I want to save this data to my sql server.

    thanks

  • April 17, 2012 at 9:14 am

    #1474800

    Haven't had to do this, when I wrote SQL Code for Oracle, I returned the reference cursors, others had to deal with the information.

    What I can tell you is that you will probably have to write code that loops through the information returned in the reference cursor. Hoepfully the 3rd party has provided you with the definition of the columns being returned in the reference cursor.

  • April 17, 2012 at 10:19 am

    #1474865

    HI

    Thanks for the reply. Could you reply with some example please?

  • April 17, 2012 at 10:26 am

    #1474872

    a2zwd (4/17/2012)

    HI

    Thanks for the reply. Could you reply with some example please?

    Nope, I wasn't involved in the consuption of the reference cursors. All I did was provide the other developers with the definition of the cursor, column names, data types, and the order in which the columns were defined. They wrote the c# code that used the reference cursor, which did require that they loop through the data.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply