Call Oracle Store Procedure from Reporting services
Can u please tell me how to call a oracle stored procedure that accepts 2 in parms and returns a Ref cursor as out parameter exmple:
CREATE OR REPLACE PROCEDURE GetReportData
(
Id1 IN NUMBER,
Id2 IN NUMBER,
resultSet OUT sys_refcursor
)
Software/Hardware used:
Software/Hardware used:
ASKED:
September 30, 2008 2:56 PM
UPDATED:
September 30, 2008 5:34 PM
Answer Wiki:
CREATE OR REPLACE PACKAGE CURSPKG AS
TYPE T_CURSOR IS REF CURSOR;
PROCEDURE OPEN_TWO_CURSORS (EMPCURSOR OUT T_CURSOR,
DEPTCURSOR OUT T_CURSOR);
END CURSPKG;
CREATE OR REPLACE PACKAGE BODY CURSPKG AS
PROCEDURE OPEN_TWO_CURSORS (EMPCURSOR OUT T_CURSOR,
DEPTCURSOR OUT T_CURSOR)
IS
BEGIN
OPEN EMPCURSOR FOR SELECT * FROM DEMO.EMPLOYEE;
OPEN DEPTCURSOR FOR SELECT * FROM DEMO.DEPARTMENT;
END OPEN_TWO_CURSORS;
END CURSPKG;
The out param type should be Ref Cursor only. I modified the procedure accordingly.
Then put the name of the procedure PackageName.GetReportData in RS. Then ran it in
the RS. CommandType :Stored Proc
RS then automatically listed the params (only 2 according to signature of sp; out param will not be listed). Data Source type is Oracle.
Rest of the stuff like any other query.
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
