Connecting to Oracle Stored Procedure (00302: component must be declared)

[cboothroyd]

Hi,

I am experiencing an error while trying to connect to an Oracle stored procedure.  This happens only in the report designer, not at run-time.  The scenario is this:

1. Open an existing report (that works at run time)
2. Open Field Explorer
3. Right-Click and select Set Data Source Location
4. Click on the stored procedure name in the Current Data Source panel
5. Open existing connection to Oracle (or add a new one)
6. Navigate through the list of entities, to the correct schema and stored procedure
7. Click Update
8. Enter report parameter values
9. Click OK
10. Failed to retrieve data from the database. Details: ORA-06550: line 1, column 16: PLS-00302: component 'xxxx' must be declared ORA-006650: line 1, column 8: PL/SQL: Statement ignored [Database Vendor Code: 6550]

My setup is:

• Visual Studio 2022 (17.8.5) - 64 bit
• Crystal Reports for Visual Studio (13.035.4870) - 64 bit
• ODAC 19c (19.16) - 64 bit

I have also tried the desktop version of Crystal (Patch 300 - 64 bit).

Furthermore, I have tried installing desktop version + ODAC on a clean machine.  I tried both the 32 bit versions initially and then the 64 bit versions.

In all instances the same error occurred.

NOTE: I have seen this before (and even raised a question about it here).  The remedy at the time was to make everything 64 bit - but in this instance everything already IS 64 bit.

Chris

[DellSC]

I have found that the Oracle Native Driver is MUCH more stable and functional with Crystal than any of the Microsoft drivers for Oracle.  If you must use ODAC and you have the Oracle data client installed, you could try the following:

1.  Use Set Data Location to convert the report over to using the Oracle Native Client with the existing database.  Save the report.

2.  Use Set Data Location to connect to the new Stored Proc. Save the report.

3.  Use Set Data Location to go back to the ODAC driver.

You might also be able to skip step 1 and go directly to the stored proc using Native Oracle instead of ODAC.

-Dell

[cboothroyd]

Thanks for the response. Just FYI, all the components I am using are issued by Oracle (downloaded from here: https://www.oracle.com/database/technologies/net-downloads.html#).

[cboothroyd]

Also, we just figured out the problem earlier. With the help of an Oracle DB we were able to establish that the user account was unable to execute the stored procedure when it is prefaced with the schema name - but Crystal forces the schema name. e.g. the user account CAN execute EXEC spMyReportProc but CANNOT execute MYSCHEMA.spMyReportProc - even though the user owns the schema. This appears to be a relatively new behavior introduced (we surmise) during Oracle patching. The short term solution was to login as an Admin/SYS user just to refresh the database. We have a ticket in to Oracle to try and get a better long-term solution.