Monday, December 30, 2013

Scenario 11: ORA-01502: index 'XYZ.UNIQUE_UK1' or partition of such index is in unusable state

Answer: This is error which is Related to Database table XYZ having Unique index UNIQUE_UK1. the Index was Dropped by somehow, so due to this we are getting this error.

Resolution: we have to Ask the DBA Guys to Recreate the indexes or we need to build the index manually. once indexes are built, we are good to restart the job.

***Note***
Generally indexes will be dropped if there are any Duplicate Records in the table.

Saturday, December 28, 2013

Scenario10: Incomplete Record at the end of file(Abinitio error)

This is an Abinitio Error. we usually get this kind of error when Delimiters were Present in between the field Values. Due to this Records will not be Ended with Proper Delimiters.

Example:

we have a flatfile which is having below delimiters:

Field Delimiter: "&&"
Record Delimiter: "\n"

We are Creating the Flatfile one of the file field value Contained "\n" in its Value. So Record will be Spillted into Another Record from that point(Where it contains "\n" new line Character). it will Treat "\n" as a Record Delimitter. Due to this One Complete record will be Splitted into Two Incomplete Records.

Example records::

100&&Rama&&156&&Good Boy
101&&Laxmi&&1560&&Bad Boy
102&&Suryanarayana
&&1760&&Good Boy
103&&JrNtr&&9999&&Perfect Actor

from the Above example Records we can see that record 102 was Splitted at Suryanarayana Baceuse of Newline Character("\n") at the end of Field. and again the next two fields are Treated as Another new record. both the Records are incomplete.

Resolution:

So we have to Remove the new line Character("\n") from the Value(Suryanarayana) and Re-create the Flatfile again. then data will be Loaded without Corrupted data. 

Friday, December 20, 2013

ORA-01422: exact fetch returns more than requested number of rows

Scenario:
ORA-01422: exact fetch returns more than requested number of rows

Solution:  This is an Error related to Cursor Variables. when we are Declaring Cursor variables and then we are trying to Insert Values INTO these variables from PL/SQL Select Statements. while Retrieving the Value from Select Statement at a time is more than one value then it will Throw the below error.

Example:

BEGIN
SELECT A.XYZ
INTO l_XYZ
FROM XYZ_TABLE_LOG A
WHERE
A.XYZ IN ((SELECT MAX(A.XYZ) FROM XYZ_TABLE_LOG A) where TEXT='SAMPLE')

Error:

ORA-01422: exact fetch returns more than requested number of rows

So here The Values Retrieving from the Select Statement Returning More than one value. so The Cursor Variable is Throwing an Error.

Select MAX(A.XYZ) from XYZ_TABLE_LOG A where TEXT='SAMPLE'
342
342

so here the max value is 342. but it returns Two Values, becuase it is selecting based on TEXT='SAMPLE' in where Clause. so Remove one Value from Table XYZ_TABLE_LOG.

Please investigate why we are getting 2 values As a MAX value. then Issue will be resolved.

Thanks,
Anilkumar.