I have created Crystal Reports that call Oracle Stored Procedures to perform database Updates.
A requirement of Crystal Rpts is that the Stored Procedure return a REF Cursor object. It doesn't really matter if you do anything with it but it needs to be defined.
In my case we needed multiple procedures for different reports so I created a Crystal Reports Package in Oracle which contains all of the Stored Procedures. Below is a sample of the declaration of one of the procedures showing the input and output parameters.
On the Crystal Rpts side, I referenced the Stored Procedure via Database Expert. One weird thing with my configuration was that Stored Procedures were listed under the Qualifiers branch instead of the Stored Procedures branch in the Database Expert but I think this might be because the procedures are in a package.
Also, the stored procedure call was put in a sub-report in the Crystal Report. The input to the sub-report was a variable defined in the main report which was referenced in the subreport links.
PROCEDURE UPDT_LST_RUN_PROC
(
InRptProcName IN VARCHAR2,
OutPtrRecordset OUT SYS_REFCURSOR -- Rtrn Record Set
)
IS
-- LOCAL VAR DECLARATIONS
BEGIN
-- PROCEDURE BODY
blah blah blah
END;
Edited by statey603 - 22 Apr 2014 at 4:16am
IP Logged
