Error retrieving next record in Siebel

I have seen multiple instances and variations of the following error:

Error retrieving next record from the database.(SBL-DBC-00104)

You will get this error when you navigate to one or more specific views. You will hardly find any associated debugging information in the logs, which makes it a little harder to solve.

The debugging becomes a lot easier to know what you’re looking for, and that is the purpose of this post.

The first and foremost thing you do is to find iny other errors in the log file. Go to the end of the log file, and find for “Error " (without quotes). There are a few typical errors that eventually lead to the above error.

1. ORA-24345: A Truncation or null fetch error occurred #

As the code suggests, this is an error thrown by the database. This happens when there is a discrepancy between the type/length at the BC field level and column of the table.

For example: Contact.First Name is of type “Text” with a length of 50 characters. S_CONTACT.FST_NAME is the underlying column that is a varchar of length 100 characters.

Siebel ignores the length specified against a Text field . You can ignore that default behaviour by defining a field user property:

Name: Text Length Override
Value: 50

This raises a conflict between business layer, and the definition in data layer. When you have any existing data that is going beyond 50 characters in FST_NAME, that leads to “truncation or null fetch error”.

There can happen to other data types as well -

1. Field of DTYPE_ID containing values of length greater than 15 characters
2. Field of type DTYPE_BOOL having more than one character in the database
3. Field of type DTYPE_PHONE having values more than 40 characters in length
4. Column of type “Numeric” having more than 16 digits (15 digits when data has a decimal point)

The solution is quite simple. You have to decide whether the business rule of restricting text length is a priority over modification of existing data. Thereon, you simply remove the restriction, or truncate/modify the outlier data.

There is another symptom this problem. You can spool the query from Siebel client, and directly execute that retrieved query against the database without any issues. But only Siebel client seemed to be the problem.

Oracle Support has provided a comprehensive prescription to determine all instances of differences between the logical and physical repository. You can use that very to find out columns relevant to your specific issue. Be aware that this can have other “errors” that you can safely ignore. Download SQL from Oracle support site  [needs Support access].

2. Truncation or null fetch error occurred due to buffer issues. #

When you encounter the truncation or null fetch error, one of the immediate steps should be to spool the SQL, And run it in SQLPlus/any DB client. If the SQL fails to run and gives the same error, try increasing the buffer length in the database.

3. Terminating queries during execution. #

Many Siebel implementations (especially older ones) would have implemented a mechanism at the database level To terminate queries running longer than a specified time.

While the normal behaviour is for Siebel to show an error message and allow the user to move on to next steps, abrupt very termination may also lead to the “retrieving next record” error, and a Siebel application crash.

Tune the database query, or eliminate the source of the query altogether.

4. “Error retrieving next record” while processing workflows during Siebel upgrade. #

If you receive the error during upgrep process, double check your parameters against the recommended values. You have to set the following two parameters in tools.cfg file as per the upgrade guide:

MaxCursorSize: -1
PrefetchSize: -1

5. ORA-24338: statement handle not executed. #

This error typically means you’re doing what you’re not supposed to do while customising Siebel. Check any of the events in the BC/applet for customisation with respect to creation/modification of records other than the current record.

For example, trying to create a record in a child BC without setting the proper context can lead to errors.

5. Discrepancies in Oracle Client version. #

If you have made any changes to the Oracle client and the server, or on the client, you can check whether the error goes away when you roll back the changes. I had seen this long back during the Oracle 10g times, but hey, desperate times need desperate measures.

In general, this is the recommended approach to resolve the issue -

1. Check if the problem goes away by rolling back any recent changes (data or configuration)
2. If the error happens for direct query execution at the database, remove columns  one by one to isolate the cause
3. In the error is observed only through Siebel application, try to remove the list column/control until the causes identified
4. Disable runtime events, scripting and any workflows being triggered through user properties, and recheck for the error