Hi folks,
I am using an Oracle 8 backend with CR 8.0.1.0 and the cror815.dll driver as suggested by Crystal Decisions. I have set up a stored procedure that pulls data from a generic package. The code is below. The reason I have to use a stored procedure is because my parameters are in an embedded SELECT statement in the WHERE clause of my main SELECT statement.
When I try to create a new .rpt file using the stored procedure, I get the following error: "ODBC error:[MERANT][ODBC Oracle 8 driver] Procedures contains a resultset, but Procedure Returns Results is not check." Does anybody know what this means?! Thanks for any help you may be able to provide!
-- Package CRYSTALR_PKG
CREATE OR REPLACE PACKAGE CRYSTALR_PKG
AS TYPE CR_Type IS REF CURSOR RETURN INVOICE_PRINT_V01%ROWTYPE;
END CRYSTALR_PKG;
-- End of DDL script for CRYSTALR_PKG
-- Procedure INVOICE_PROC
CREATE OR REPLACE
PROCEDURE INVOICE_PROC (
Invoice_Cursor IN OUT CRYSTALR_PKG.CR_Type,
Start_Date IN VARCHAR2,
End_Date IN VARCHAR2)
AS
BEGIN
OPEN Invoice_Cursor FOR
SELECT INVOICE_PRINT_V01."SUBMISSION_ID",
INVOICE_PRINT_V01."INVOICE_PAYMENT_CD",
INVOICE_PRINT_V01."MFIRST_NM",
INVOICE_PRINT_V01."MSUR_NM",
INVOICE_PRINT_V01."AFIRST_NM",
INVOICE_PRINT_V01."ASUR_NM"
FROM "INVOICE_PRINT_V01" INVOICE_PRINT_V01
WHERE INVOICE_PRINT_V01.SUBMISSION_ID IN
(SELECT DISTINCT SI.SUBMISSION_ID
FROM SUBMISSION_INVOICE SI
WHERE SI.CREATION_DT >= TO_DATE
(Start_Date,'DD-MON-YYYY')
AND SI.CREATION_DT <= TO_DATE
(End_Date,'DD-MON-YYYY' ))
ORDER BY 1,2;
END INVOICE_PROC;
/
-- End of DDL script for INVOICE_PROC
I am using an Oracle 8 backend with CR 8.0.1.0 and the cror815.dll driver as suggested by Crystal Decisions. I have set up a stored procedure that pulls data from a generic package. The code is below. The reason I have to use a stored procedure is because my parameters are in an embedded SELECT statement in the WHERE clause of my main SELECT statement.
When I try to create a new .rpt file using the stored procedure, I get the following error: "ODBC error:[MERANT][ODBC Oracle 8 driver] Procedures contains a resultset, but Procedure Returns Results is not check." Does anybody know what this means?! Thanks for any help you may be able to provide!
-- Package CRYSTALR_PKG
CREATE OR REPLACE PACKAGE CRYSTALR_PKG
AS TYPE CR_Type IS REF CURSOR RETURN INVOICE_PRINT_V01%ROWTYPE;
END CRYSTALR_PKG;
-- End of DDL script for CRYSTALR_PKG
-- Procedure INVOICE_PROC
CREATE OR REPLACE
PROCEDURE INVOICE_PROC (
Invoice_Cursor IN OUT CRYSTALR_PKG.CR_Type,
Start_Date IN VARCHAR2,
End_Date IN VARCHAR2)
AS
BEGIN
OPEN Invoice_Cursor FOR
SELECT INVOICE_PRINT_V01."SUBMISSION_ID",
INVOICE_PRINT_V01."INVOICE_PAYMENT_CD",
INVOICE_PRINT_V01."MFIRST_NM",
INVOICE_PRINT_V01."MSUR_NM",
INVOICE_PRINT_V01."AFIRST_NM",
INVOICE_PRINT_V01."ASUR_NM"
FROM "INVOICE_PRINT_V01" INVOICE_PRINT_V01
WHERE INVOICE_PRINT_V01.SUBMISSION_ID IN
(SELECT DISTINCT SI.SUBMISSION_ID
FROM SUBMISSION_INVOICE SI
WHERE SI.CREATION_DT >= TO_DATE
(Start_Date,'DD-MON-YYYY')
AND SI.CREATION_DT <= TO_DATE
(End_Date,'DD-MON-YYYY' ))
ORDER BY 1,2;
END INVOICE_PROC;
/
-- End of DDL script for INVOICE_PROC
but not on the "User DSN". Now that it is checked off in both places, it works fine. Thanks for pointing me in the right direction.