CODEWITHSUNDEEP
oracle-databasereporting-services
user11204030
user11204030

Reputation: 1

How to create a dataset in SSRS using Oracle stored procedure

I am trying to create a SSRS report using an Oracle stored procedure which has one input parameter and two output refcursors. The input parameter takes XML as its datatype.

How to run this stored procedure from SSRS?

This is the code which I am trying to use to create the dataset

PROCEDURE get_summary_rpt (
    p_xmlrequest            IN  CLOB,
    p_summary              OUT sys_refcursor,
    p_outputs               OUT sys_refcursor
);

Please advise

Upvotes: 0

Views: 987

Answers (2)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89091

SSRS will automagically map a single OUT REFCURSOR parameter and give you the results. Two OUT REFCURSOR parameters probably won't work.

Upvotes: 1

StevenWhite
StevenWhite

Reputation: 6034

SSRS is not set up to work with OUTPUT procedure parameters directly. Ideally, the procedure should have a SELECT statement in it that returns the results for your dataset. Then you would just tell it the procedure name, you don't have to write any SQL.

If you are unable to do that, you would need to write some PL/SQL to declare the variables, assign values to them using the procedure, and then use a SELECT statement to return them. If you write that all out and still have issues, post a specific question with the issue that you run into.

Upvotes: 0

Related Questions