Appendix A Common Issues

This chapter covers issues that commonly occur when using Crystal Reports with Oracle. Problems arising in the Crystal development environment will be described, specific Oracle errors will be listed, the proper use of date literals will be explained, and methods to report on user private data will be explored. Other common problems will also be described, along with solutions to those problems.

The list of issues given in this chapter is not exhaustive, of course. If you have a problem that is not covered here, you should browse the Crystal Decisions website or other third-party websites such as tek-tips.com for possible solutions.

Crystal Problems

This section lists problems that might occur inside the Crystal Reports environment but which are not native Oracle errors or ODBC errors.

Oracle Server Not Listed in the Database Explorer

This problem will occur if the Oracle option is not chosen during the installation of Crystal Reports. See Chapter 1 for installation instructions.

This problem may also occur if the Oracle client DLLs are not properly installed. Of particular concern is the OCIW32.DLL, which should exist in the system’s environment path statement. However, there should not be multiple versions of this DLL on the system.

Selection Formulas Containing String Comparisons to Parameter Values NotFunctioning as Expected

If you have a selection formula that contains a string comparison to a parameter and your results are not as you expect, the problem may be related to the parameter value case compared to the database field value’s case. If you want the condition to match regardless of case, you must wrap the UPPER function around both the parameter (because you do not want to force the user to enter uppercase) and the database field in the selection formula as shown here:

UPPERCASE({database field}) = UPPERCASE({?parameter})

Query Engine Error An Invalid Join Type Has Been Encountered

This error is displayed if you attempt to change a database link to full outer join. To use an Oracle full outer join, you must use a SQL Command.

Oracle Errors

Oracle errors can occur at a couple of different points in the reporting process. First, there may be errors in connecting to Oracle, and second, there may be some problem executing the query sent by Crystal Reports to Oracle.

ORA 00904 invalid identifier

This error means that a SELECT statement contains a field name that Oracle does not recognize. Other than the obvious problem of entering an incorrect field name, there is possibly a more subtle problem. Oracle field names are usually defined entirely in uppercase. If an Oracle field name is created as uppercase, then queries against it may use any case. For example, if the field name is FIELD_NAME, then you can query it as FIELD_NAME, Field_Name, or field_name, and each query will succeed. However, if the field name was defined in mixed case, it must be referenced exactly as it was defined and it must be surrounded by double quotes; for example, if the field name was created as Field_Name, you may only reference it as “Field_Name.”

This is never a problem when choosing fields using the Database Explorer because Crystal Reports will use the proper syntax. However, you may see this problem when using SQL Commands where the field name is not properly formed. This error is also more likely to occur when the database has been created by converting from some other database type where the use of mixed case is more common.

ORA 00942 Table or view does not exist

This error usually occurs when the logged in user does not have the SELECT privilege on the report objects. Granting the logged in user the proper privileges should resolve the problem. You may also receive this error even if your report is based on a stored procedure if you do not have the EXECUTE privilege on the stored procedure.

This error can also occur when mixed case table names exist. As with field names, if a table name is defined with mixed case, it must be referenced exactly as it was defined and surrounded by double quotes.

ORA 04068 Existing state of packages has been discarded

This error will occur when the package containing a stored procedure is invalid.

A package may become invalid because a database object used in the package has changed. If this is the case, recompiling the package should solve the problem.

(A second call to the package procedure will usually succeed because Oracle will automatically try to recompile the package.) If the package fails to compile, you must troubleshoot the code to discover the root of the error.

ORA 06575 Package or function <> is in an invalid state

This error will occur if an Oracle function being called from a SQL Expression field is invalid. Recompile the function to correct the problem. If the function will not compile, you will need to resolve the error condition that is causing the compile to fail.

ORA 01722 Invalid number

This error occurs when a conversion from a string to a number fails because the string is not of the proper form; for example, it may contain alphabetic characters or punctuation or in some other manner be non-numeric. This error usually arises during an implicit conversion where you may not be expecting a conversion to take place. Review the SQL query and determine where the conversion is happening.

This error will also occur if you attempt to use (+) as a concatenation operator in a SQL Expression. If you use (+) Oracle will assume that you want to do addition and attempt to convert the operands into numbers. If you need to concatenate, use the double pipes concatenation operator (||).

ORA 01841 (Full) year must be between 4713 and +9999 and not be 0

This error occurs if a date literal is used in a SQL Expression or SQL Command and is improperly formed; specifically, if a month or day number is where Oracle expects the year to be. One solution is to fix the format of the date literal, but a better solution is to avoid the use of date literals entirely and use the TO_DATE function instead.

ORA 01858 A non numeric character was found where a numeric was expected

This error occurs if a date literal is used in a SQL Expression or SQL Command and is improperly formed; specifically if a month abbreviation is found where a year or day is expected. One solution is to fix the format of the date literal, but a better solution is to avoid the use of date literals entirely and use the TO_DATE function instead.

ORA 12154 TNS Could not resolve service name

This error indicates that the Oracle client is not properly installed or configured; see your DBA for resolution. Most commonly, the service name does not exist in the TNSnames.ORA file. The proper TNS name must exist in this file before it can be used for Crystal Reports connections. For a native connection, this is used as the service name and is entered in the Crystal environment. For ODBC connections, the TNS name is entered in the ODBC DSN configuration. For a CR ODBC connection, the TNS name is used as the server name. For a Wire Protocol connection, it is used as the SID. For an Oracle ODBC connection, it is used as the TNS service name. See Chapter 1 for step-by-step setup instructions.

Other conditions that might cause this error include the TNSnames.ORA file being in the wrong location or not existing, a syntax error in the TNSnames.ORA file, the client not being properly installed, or the name in the Crystal connection not matching the name in the TNSnames.ORA file.

If your Oracle environment does not use local TNSnames files, see your DBA for assistance.

ORA 24338 Statement handle not executed

This error will result when using a REF CURSOR stored procedure as a data source if the REF CURSOR is not in an open state when the end of the procedure is reached. Verify that the REF CURSOR used in the stored procedure is opened and not subsequently closed or fetched from within the stored procedure.

ORA 24372 Invalid object for describe

This error will occur if you are using a standalone stored procedure and the procedure is invalid. A procedure may become invalid because a database object used in the procedure has changed. If this is the case, recompiling the procedure should solve the problem. If the procedure fails to compile, you must troubleshoot the code to discover the root of the error.

PLS 00306 Wrong number or types of arguments in call to

This error occurs when using stored procedures with the Crystal-supplied ODBC drivers for Oracle, either the regular driver or the Wire Protocol driver, and the Procedure Returns Results checkbox on the Advanced tab of the ODBC Data Source Administrator is unchecked. Check the Procedure Returns Results option and the error will be resolved.

This error also occurs when attempting to use the Oracle OLE DB driver with stored procedures. Switch to a different driver to solve the problem.

This error may also occur if a procedure call attempts to use named notation but the call is improperly formed. If you need to use named notation make sure that your references are properly formed.

PLS 00363 cannot be used as an assignment target

This error occurs when using the Oracle ODBC driver with stored procedures. Switch to a different driver to solve the problem.

Crystal Reports Nuances

This section contains several little-known tips that are useful to report writers who are developing against Oracle databases.

Using Date Literals

You might use date literals in SQL Expressions or SQL Commands. If so, be aware that Crystal Reports sets the Oracle NLS_DATE_FORMAT for the session to ‘YYYY/MM/DD HH24:MI:SS’. If you use any other date format, errors will result, with the following exceptions:

• The time part is optional.
• The month part can be MON (three character month abbreviation) as well as MM.
• The separator character can be – as well as /.

For example, the following WHERE clause in a SQL Command will perform as expected:

WHERE "ORDERS"."ORDER_DATE">'2002/05/01 00:00:01'

But the following clause will not:

WHERE "ORDERS"."ORDER_DATE">'01/05/2002 00:00:01'

It is recommended that you avoid the use of date literals and use the TO_DATE function instead.

Using Timestamp Fields

Crystal Reports does not currently support Oracle 9i Timestamp and Interval datatypes. You must convert these datatypes to dates and numbers respectively in order to use them with Crystal. This conversion can be done in SQL Expressions or SQL Commands, but if you have many of these fields, you should consider creating an Oracle function to do the conversion. You might also want to create views that contain the converted fields for easy use with Crystal.

See Appendix B for example functions.

Reporting on User Private Data or Moving Reports Between Schemas

Some applications are written so that each user has a copy of the application tables in their own schema. In this case, when reporting is needed, it is desirable to distribute the same report to each user, with the report defaulting to the tables in the proper schema, depending on the logged in user. In other cases, identical tables may exist in both a test schema and a production schema, and you should use the same reports for each schema.

Experienced Oracle users are often dismayed at how difficult this is to accomplish with Crystal Reports. The difficulty arises because Crystal embeds the schema name into the queries that it generates and does not supply a mechanism for easily changing it. The Crystal capability to change locations can be used to switch each data source to an identical data source in a different schema, if the developer has access to both schemas. However, that requires significant labor and results in multiple reports, one for each schema.

Solutions to this problem take advantage of how Oracle resolves table names. When an Oracle query does not include the schema name prefixed to the table names, Oracle will go through several steps to determine which schema to use. It will first look in the schema that belongs to the session user. If it does not find a table match there, it will look for private synonyms matching the table name. If the table is still not found, it will look for public synonyms matching the table name. If the table name is not found in any of these sources, an error will be returned.

In the case where each user has their own copies of the tables, if you use a SQL Command as your data source and do not append schema names to the table names, then the report will function as discussed in the preceding paragraph. Oracle will resolve the table names as described and each user will see the result of the query for their own tables. In the case where you want to point the report at different schemas, but not necessarily at the logged in user’s schema, you should still use a SQL Command without embedded schema names, but you must also use either private or public synonyms to ensure that the correct schema is queried.

Left Outer Join Loses Records

If you are using a left outer join to ensure that all records from the left-hand table are returned but you are still losing records, check the filtering conditions. In a left outer join where no matching record exists in the right-hand table, all report fields from the right-hand table will be treated as if they were null. If you have a filtering condition that uses a right-hand table field, be sure to use the Oracle function NVL to return a not null value or use the ISNULL Crystal function as needed in the selection formula to make sure that the row containing the null value is retained.

Smart Linking by Key Fails

Smart Linking by key fails in the release version of Crystal Reports 9. However, it has been fixed as of the February 2003 hot fix release.