Rejects - capturning DBMS.CODE=ORA-01401

[phyllis]

I have a separate reject link for three data files I'm converting to ASCII and inserting into Oracle tables. The reject file contains rows that were rejected due to several reasons, including the following:

ORA-01401: inserted value too large for column
DBMS.CODE=ORA-01401

ORA-01438: value larger than specified precision allows for this column
DBMS.CODE=ORA-01438

I need to capture the reject reason into a variable (code or description) so that I can write with every row rejected, making it easier for someone looking at the reject file to determine the reject reason. Also, it would be WONDERFUL to know exactly which column caused the error (i.e. phone#, name). How can this be done?

In looking at previous postings to this forum, I found where someone was trying to use a the LinkVariable -> DBMSCODE and using the
following derivation in a transformer stage:
Field(DSGetLogEntry(DSJ.ME,DSGetNewestLogId (DSJ.ME,DSJ.LOGANY)-1) ,":",4,5)

but I didn't see where anyone responded that this actually worked.

[kcbland]

In order to parse the text message being return, you have to look in the job log. This is extremely inefficient. While you can get the DBMSCODE, translating it to the offending column is nearly impossible without inspecting the log message, and even then you may not be able to extract the column name.

There really is no inherent solution for your problem, other then putting a derivation on every column to inspect its values for adherance to the target column definition (datatype, length, scale, nullability). This can be exhaustive on your job designs, but the only way to absolutely get 100% correct data. I personally don't recommend doing this, and instead built an engine to enforce data quality characteristics using a metadata driven external process.

[phyllis]

Unless I'm missing something in the log, the values being written to each column appear, but it is not evident which column(s) actually caused the error (e.g. value too large). For example, this is the text in the log:

Jobname..TransformFiletoAscii: The value of the row is:
ADDR_ID = 14049209
DATE = 1984-11-08
E_CODE = 48000
M_NUM = 14790
ADDR_TYPE = O
NAME = Briarwood
ADDR_1 =
ADDR_2 =
CITY = Chesapeake
STATE = WA
ZIP_CD = 02332
C_CD = 01
AREA_CD = 0
PHONE_NUM = 0

Jobname..TransformFiletoAscii: ORA-01401: inserted value too large for column

I can't tell, quickly, from the log which column caused the error without looking at every value. So there's no way to write it to the reject file other than, as you suggested, evaluate each one, or am I missing something in the log?

[phyllis]

I forgot to mention this is a conversion job (running one time) and the log info is to provide the reason for certain rows not being converted. The goal is to export the reject file to a spreadsheet to be filed, and it would be most helpful to include the reject reason in the spreadsheet, whether it be the DMBS.CODE=ORACA-01401 or a description (insert value too large for column).

[ray.wurlod]

See here for one technique.
In terms of determining which columns are too large, unfortunately you have to do this yourself. For example, on the output link that handles rejects, you could test each of the likely columns against its known width, for example

Code: Select all

(CITY <> Trim(Fmt(CITY,"L#10"))) 

if CITY were a Char(10) column.

[phyllis]

Thank you so much - I was able to write linkname.DBMSCODE to the reject file.

[ketfos]

Hi
You can write the records that failed (e.g. Oracle Errors) to a file or table by setting the Reject link
Put the following statement in the Derivation for the Reject link.
DSGetLogEntry(DSJ.ME, DSGetNewestLogId (DSJ.ME, DSJ.LOGWARNING)) [20]

I tried it works.
I am trying to figure out the way to write actual value of the column which resulted in the error code.

Hope this helps.

Ketfos