DBowley
asked on
Crystal Reports 8.5 with Oracle Stored Procedures
I'm trying to use CR with an Oracle stored procedure.
I'm running:
CR 8.5 developer edition
Oracle 8.1.7
and I'm using the odbc drivers that crystal recommend (contained in the Cror815.zip download).
I'm just trying to use the following simple stored procedure:
--------------------------
CREATE OR REPLACE PACKAGE b2b_report
AS TYPE b2bcur IS REF CURSOR RETURN table_contract%rowtype;
PROCEDURE GetRecords(p_cursor IN OUT b2b_report.b2bcur, startdate IN DATE);
END b2b_report;
/
CREATE OR REPLACE PACKAGE BODY b2b_report
AS
PROCEDURE GetRecords(p_cursor IN OUT b2b_report.b2bcur, startdate IN DATE)
IS
BEGIN
OPEN p_cursor FOR
SELECT * FROM table_contract WHERE table_contract.create_dt > To_Date(startdate, 'DD/MM/YYYY');
END GetRecords;
END b2b_report;
/
--------------------------
but when I create a new report and try and reference the stored procedure via the data explorer, I get the following error message:
ODBC error: [MERANT][ODBC Oracle 8 driver][Oracle 8]ORA-06550: line 1, column 27: PLS-00306: wrong number or types of arguments in call to 'GETRECORDS' ORA-06550: line1, column 8:
PL/SQL: Statement Ignored
I'm running:
CR 8.5 developer edition
Oracle 8.1.7
and I'm using the odbc drivers that crystal recommend (contained in the Cror815.zip download).
I'm just trying to use the following simple stored procedure:
--------------------------
CREATE OR REPLACE PACKAGE b2b_report
AS TYPE b2bcur IS REF CURSOR RETURN table_contract%rowtype;
PROCEDURE GetRecords(p_cursor IN OUT b2b_report.b2bcur, startdate IN DATE);
END b2b_report;
/
CREATE OR REPLACE PACKAGE BODY b2b_report
AS
PROCEDURE GetRecords(p_cursor IN OUT b2b_report.b2bcur, startdate IN DATE)
IS
BEGIN
OPEN p_cursor FOR
SELECT * FROM table_contract WHERE table_contract.create_dt > To_Date(startdate, 'DD/MM/YYYY');
END GetRecords;
END b2b_report;
/
--------------------------
but when I create a new report and try and reference the stored procedure via the data explorer, I get the following error message:
ODBC error: [MERANT][ODBC Oracle 8 driver][Oracle 8]ORA-06550: line 1, column 27: PLS-00306: wrong number or types of arguments in call to 'GETRECORDS' ORA-06550: line1, column 8:
PL/SQL: Statement Ignored
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I have that same white paper sitting at my desk and highlighted all over the place. However, I was never able to get it to work that way. I then took one of the existing stored procedures from elsewhere in my project and attempted to create a report from it and it worked.
The only difference was having the cursor as the last parameter and specified as OUT.
Also, you will want to go into your ODBC admin and set an option on the CROR8V36 DSN called "Procedure returns results".
I hope this helps more than that white paper.
Mike.
The only difference was having the cursor as the last parameter and specified as OUT.
Also, you will want to go into your ODBC admin and set an option on the CROR8V36 DSN called "Procedure returns results".
I hope this helps more than that white paper.
Mike.
ASKER