Database connector error:'cannot obtain error message from server.'

[Former Member]

I have created a new stored procedure and when i tried to add it in crystal report 2011 from Databse expert i get an error message as above.

Could Someone help. I have used PL/SQL to develop stored procedure.

[Former Member]

Hi Sam,

You will find a useful help in this document:

Requirements of Oracle Stored Procedures:

In order for Crystal Reports to report off Oracle stored procedures, all of the following requirements must be satisfied:

• To use an ODBC connection to access an Oracle stored procedure, you must create a package that defines the REF CURSOR. This REF CURSOR must be strongly bound to a static pre-defined structure (see Strongly Bound REF CURSORs vs. Weakly Bound REF CURSORs). This package must be created separately and before the creation of the stored procedure.

• A native connection to Oracle in Crystal Reports 9 and Crystal Reports 10 can be used to access an Oracle stored procedure that was created within a package and also an Oracle stored procedure that references a weakly bound REF CURSOR

• The stored procedure must have a parameter that is a REF CURSOR type. Crystal Reports uses this parameter to access and define the result set that the stored procedure returns.

• The REF CURSOR parameter must be defined as IN OUT (read/write mode). After the stored procedure has opened and assigned a query to the REF CURSOR, Crystal Reports will perform a FETCH call for every row from the query’s result. This is why the parameter must be defined as IN OUT.

• The parameters can only be defined as IN (input) parameters. Crystal Reports is not designed to work with OUT parameters.

• The REF CURSOR variable must be opened and assigned its query within the procedure.

• The stored procedure can only return one record set. The structure of this record set must not change based on parameters.

• The stored procedure cannot call another stored procedure.

• If you are using the CR ODBC driver, verify that the option Procedure Return Results is selected as On in the ODBC Driver Configuration setup under the Advanced tab.

• If you are using the native Oracle driver and using hard-coded date selection within the stored procedure, the date selection must use either a string representation format of YYYY-DD-MM (where the date field = 2004-01-01) or the To_Date formula function with the same format specified (where date field = To_Date(2004-01-01’,’YYYY-MM-DD’).

• Most importantly, the stored procedure must be able to execute successfully in Oracle’s SQL *Plus utility.

If all of these requirements have been met, verify that the database driver that you are using works with that version of Oracle.

I hope the above is useful helps to resolve your issue.

Regards,
István