IT Tutorial IT Tutorial | Oracle DBA | SQL Server, Goldengate, Exadata, Big Data, Data ScienceTutorial
I got ” ORA-24345: A Truncation or null fetch error occurred ” error in Oracle database.
ORA-24345: A Truncation or null fetch error occurred
Details of error are as follows.
ORA-24345: A Truncation or null fetch error occurred Cause: A truncation or a null fetch error Action: Please ensure that the buffer size is long enough to store the returned data
A Truncation or null fetch error occurred
This ORA-24345 error is related to the truncation or a null fetch error.
Please ensure that the buffer size is long enough to store the returned data.
In order to fix this behavior, the incorrect mapping should be identified. Here are the steps to follow:
- Run the application with the /s parameter in order to trace the SQL statements generated. For more information, refer to 475564.1 or 477897.1.
- Navigate to the view where the behavior occurs and identify the applet raising the error.
- Note the applet and business component name using the menu File > Help > About View.
If the view contains several applets, to confirm which one is concerned, place the focus in one applet and press CTRL+Q then ENTER to run a query, the error will be displayed on the applet having the behavior.
- Open the trace file generated, and note the SQL generated by the faulty applet. The SQL statement is the one appearing just above the message:
An error has occurred executing a Sql statement.
Please continue or ask your systems administrator to check your application configuration if the problem persists.
Please continue or ask your systems administrator to check your application configuration if the problem persists.
- Open Siebel Tools and display the business component noted in step 3.
- One by one, take the columns listed in the SQL found in step 4, and search for the field that is mapped to this column. Compare the type between this field and the corresponding column in the table to search for discrepancies.
If the field type is DTYPE_TEXT, then compare the definition of the underlying column in the repository (with Siebel Tools) to the actual definition of the column in the database. Some fields come from the base table, that is, the table that the business component is based on. Some fields may also come from a joined table. In both cases, the field definition will be in the definition of the business component by looking at the Column and Join properties. However, some tables may appear in the SQL statement that are neither the base table nor join (explicit or implicit).
If this business component contains multi-value links with “Use Primary Join” properties set to “TRUE” and “Primary Id Field” set to an existing field, then the destination business component of the multi-value link should also be inspected for such discrepancies. The reason behind this is that the SQL generated will include columns related to multi-value fields of multi-value links set like this.
- Fix the discrepancies found. Most of the time, the field type is incorrect and should be corrected. Note, however, that not all discrepancies found have to be changed, especially regarding the standard Siebel field/column.
For example, if the field is of type DTYPE_BOOL, mapped to a column Varchar greater than 1 character, but functionally speaking is a Boolean, then, it should not be changed. If the error occurs because of this field, it means that a data was loaded with a value different than “Y” or “N”, which is not valid because a Boolean field works only with these values. This could happen for example, when in another business component based on the same table, has a field with a type DTYPE_TEXT mapped on the same column, hence, allowing an end-user to enter values different than “Y” or “N”. This could also happen because of an EIM load that put incorrect values. This is the case for the column ROW_STATUS in table S_ACCNT_POSTN, S_OPTY_POSTN and S_POSTN_CON that are Varchar with 10 characters but used by Boolean fields [Account Row Status], [Opportunity Row Status] and [Contact Row Status] in the Position business component (this is the flag “New” for Account, Opportunity and Contact). In this scenario, these columns should never contain something else than “Y” or “N”. If it is not the case, then the data should be corrected with an EIM batch for example.
In summary, if functionally speaking the type of the field is correct, then the data is incorrect. Therefore focus on determining how this data was loaded (another field, Workflow, EIM, etc.). If not, then the type is not correct.
The SQL script provided in Attachment 476591.1:1 may be useful for finding discrepancies in the repository. This script should be run with SQL*Plus, connected as the Siebel table owner. It generates a spool file with mappings for the cases mentioned previously. This list may be used during steps 6 and 7 to narrow down the column(s) generating the error. Once again, as explained in above step 7, all the discrepancies listed should not be considered. However, this script does not take into account the difference between the logical and physical repository.
Other possible causes for error ORA-24345
For Siebel Versions that use an Oracle 11 RDBMS, there is a related Oracle RDBMS product defect and Siebel product defect that can also cause this error, ORA-24345 “A Truncation or null fetch error occurred”.
A test case that illustrates this product defect is the following:
In Tools, create a new flag column with DataType – Character, Length – 1, Default Value – Y/N in an existing table. Apply the created column on the physical db using ddldict/ddlimp and verify the length of the default values populated in the column created. The column gets created with length 1 but the values have 3 trailing spaces making the length of the string = 4.
The workaround is to change the session parameter for the Oracle Server DB as follows:
alter session set “_add_col_optim_enabled”=false ;
The above hidden parameter needs to be set up on the Oracle server to avoid the problem.
This is discussed in this document 1073581.1 – Siebel Merge or Delta Merge Fails With “Unable to connect to the database” And “ORA-24345: A Truncation or null fetch error occurred” In The MERGE Log
Also, for all Siebel versions supporting Oracle 10.2 databases, error message ORA-24345 can occur when using Oracle client version 10.2.0.2. Downgrading to 10.2.0.1 or upgrading to 10.2.0.3 fixes the problem.
Do you want to learn Oracle Database for Beginners, then read the following articles.
Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )
