Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Solaris: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
I'm trying to run a mass insert. Not allowed to use sql loader...stupid, I know, but I was told it has to be run manually as inserts.
So some of these files have 50K+ lines of inserts in them.
And they are failing.
Is there a better way to put a wrapper around the whole block of inserts so that I can get it to return the primary key value of what is being inserted so I can look at the culprit line and find the fix?
Example...
Insert into FS_NRIS_NVUM.NRN_BASIC_DEMOGRAPHICS
(BASIC_DEMOGRAPHICS_CN, ADMIN_CN_FK, PERSON1_AGE, PERSON1_SEX, PERSON2_AGE,
PERSON2_SEX, NUMBER_UNDER16, HISPANIC, RACE_NATIVAM, RACE_PACISLAND,
RACE_ASIAN, RACE_BLACK, RACE_WHITE, VPDUNIT_ID, LAST_UPDATE)
Values
('E244FADBDA5F3A50E0440003BA0F14C2', 'E244FAD7AA6A3A50E0440003BA0F14C2', '20-29', 'F', '20-29',
'M', 0, 'N', 'N', 'N',
'N', 'N', 'Y', '0804', TO_TIMESTAMP_TZ('07/24/2013 12:08:21.000000 PM -06:00','MM/DD/YYYY fmHH12fm:MI:SS.FF AM TZH:TZM'));
BASIC_DEMOGRAPHICS_CN is the primary key. Have the script return some thing like:
Insert into FS_NRIS_NVUM.NRN_BASIC_DEMOGRAPHICS
*
ERROR at line 1:
ORA-02290: check constraint (FS_NRIS_NVUM.NRN_BASIC_DEMOGRAPHICS_CK02) violated
CN= (Problematic CN goes here)
The error and the insert line aren't a 1 for 1 in this instance, so it isn't that easy.
Basically is there a way to trap the value being passed to the insert, into that error message.
Thanks.