Crystal Reports 14.0.2.364 + Oracle 11g Stored Proc + ODBC Connection Error 42000

Question

I have developed Stored Procedures in Oracle 11g and need to access them through Crystal Reports 14.0.2.364. When I developed the reports, I used a direct Oracle server connection (server address, username/password, SID, port, etc.) and the report worked perfectly. As a limit on our LMS, we're told that we must use ODBC connections instead of a direct Oracle Server connection, but, when I connect the Crystal Report to the Stored Procedure in this manner, I get a persistent Error 42000:[Oracle][ODBC] Syntax Error or Access Violation error.

We have double-checked the drivers and we're up to date. We have verified there are no actual syntax errors in the code. We have verified the username we're logging in as has the correct privileges to run the Oracle Stored Procedure. We've verified that, when supplied with the correct parameters, the Stored Procedure runs.

What I'm seeing that I cannot explain is this. There are 4 input parameters to the report and there are 36 returned columns in the recordset that comes back from the stored procedure. Inside Crystal Reports, when I got to Database > Show SQL Query, I get the following:

{CALL "SABA_REPORT"."HDT_CORE_GETDATA_DDS_V110"(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'STR0115', '4260', NULL, 
'course')}

Which shows the 36 returned columns as INPUT parameters (which it should not) as NULL values and then the 4 input parameters: 'STR0115', '4260', NULL, 'course' which are correctly listed.

So, I think there's a mismatch between the parameters that are being passed to the Stored Procedure and what the Stored Procedure expects, generating the error.

For reference, here's the header of the Stored Procedure:

create or replace PROCEDURE HDT_CORE_GETDATA_DDS_V110
 (
      DataReturn IN OUT HDT_CORE_MAIN_V110.CORE_REC_CURSOR
      , ParamOrgCode IN VARCHAR2 DEFAULT NULL
      , ParamCourseNumber IN VARCHAR2 DEFAULT NULL
      , ParamNameKeyword IN VARCHAR2 DEFAULT NULL
      , ParamReportType IN VARCHAR2 DEFAULT 'course'
 )

The DataReturn SysRefCursor is what contains the 36 returned columns.

How can this error be cleared?

Thanks in advance for any insight.

Can you call the stored procedure with static content form SQLPlus or SQLDeveloper? — mmmmmpie
Yes...I've verified the stored procedure operate from within SQL Developer using manually entered parameter values. When we do it this way, we get expected results. — rcfmonarch
And you've verified its execution as the user that CR uses to run it, right? — mmmmmpie
Correct. Both inside SQL Developer and when the report is run through Crystal Reports. When you create the connection, you have to specify the username/password, so it's the same as when we ran it directly inside SQL Dev — rcfmonarch
So the driver is clear and Oracle is clear. Where is CR getting those parameters to pass into the procedure? — mmmmmpie

rcfmonarch rcfmonarch · Accepted Answer · 2014-11-20T15:48:51

My research showed that the RPT file was adding a lot of extra columns into the SQL Call Statement of the Stored Procedure. When the Stored Procedure was chosen directly from the list of available Stored Procedures in the connection menu, this happened. So, I replaced the call statement with a manually entered SQL pull using only the correct parameters, and it worked. Not sure why the extra columns were added to the parameter list, but managed this workaround.

Crystal Reports 14.0.2.364 + Oracle 11g Stored Proc + ODBC Connection Error 42000

1 Answers