Post reply

Reporting Services with Oracle

  • March 28, 2007 at 7:31 am

    #119265

    I have been all over the web trying to find info on using Reporting Services with Oracle.  Specifically Oracle stored procs.  Oracle stored procs. do not return result sets to the requestor unless the PL/SQL code is setup to use OUT reference cursors.  I was able to find info on this and believe I have the stored proc. setup correctly (it compiles cleanly).  However, I was not able to find any explicit examples of the Reporting Services side of this and how to get the OUT parameter from Oracle to be recognized in Reporting Services.  By all that I can find Reporting Services and Oracle stored procs. work find together but there were no concrete examples of the Reporting Services side of things so I am hoping that someone in this forum has done this and has explicit examples of the interaction between Reporting Services and Oracle stored procs.  Thanks for any help in advance.

    Gary

  • March 28, 2007 at 8:05 pm

    #697566

    SSRS sees the sysout cursor just as it sees a SELECT statement from a T-SQL stored proc. Where exactly are you having problems? Are you getting errors, etc?

    If you are getting errors that don't make sense to you, I'd start out small and work up. For instance, don't call a stored proc, just set the dataset for text and use a simple SELECT statement. If that doesn't work, then your problem is with the datasource connection, not a stored proc, for instance. It could be something as simple as a different tnsnames.ora file on the reporting server than on your local machine, firewall issues, etc.

    Tons of potential issues, but most of them are fairly easy to overcome, once identified.

     

  • March 31, 2007 at 12:58 pm

    #697976

    First setup Oracle 9i or 10g  client odbc driver on Reporting service application server.

    Define ODBC from Oracle TNS names.In reporting service set datasource this odbc defitinition.

    And on RS {call sp_hello(?)} .

     

    Oracle proc should return only one cursor.And you call proc with ? from odbc if you have two input parameters

    you write (?,?) .

    bye

     

     

     

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

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