0

So for example, I have tried inserting 9 columns in the oracle sql table.

Table ACCOUNTREQUEST: 7 Rows successfully loaded. 2 Rows not loaded due to data errors.

I want to create a echo.txt or .log that counts the error and the error name. I have this as my control data

    LOAD DATA
INFILE 'csv_accountrequest.csv'
BADFILE 'bad_accountrequest.bad'
APPEND INTO TABLE AccountRequest
FIELDS TERMINATED BY "," TRAILING NULLCOLS

(requestid "request_id_interval.nextval", branch, account_type, title, firstname, lastname, Birthday Date "dd/mm/yyyy", workphone, homephone, address, state, zip, email,status)

and have this as my csv

1,manila,SAVINGS,MR,Alpha,Bravo,01/02/2000,21313,213,kor,north,3232,MyEmail@sample.com,Entered
2,japan,CURRENT,MR,Charlie,Delta,02/03/2001,21313,213,kor,north,3232,MyEmail@sample.com,Approved
3,indo,CURRENT,MS,Echo,Foxtrot,03/04/2002,21313,213,kor,north,3232,MyEmail@sample.com,Entered
4,MY,SAVINGS,MR,Golf,Hotel,04/05/2003,21313,213,kor,north,3232,MyEmail@sample.com,Entered
5,SG,CURRENT,MRS,India,Juliet,05/06/2004,21313,213,kor,north,3232,MyEmail@sample.com,Approved
6,china,CURRENT,MS,Kilo,Lima,06/07/2005,21313,213,kor,north,3232,MyEmail@sample.com,Entered
7,turkey,CURRENT,MR,Mike,November,07/08/2006,21313,213,kor,north,3232,MyEmail@sample.com,Entered
8,chile,CURRENT,MRsr,Mike,November,07/08/2006,21313,213,kor,north,3232,MyEmail@sample.com,rejected
9,poland,CURRENTs,MR,Mike,November,07/08/2006,21313,213,kor,north,3232,MyEmail@sample.com,Entered

via SQLLDR,

And this as my error.text

Record 8: Rejected - Error on table ACCOUNTREQUEST.
ORA-02290: check constraint (HR.CHK_TITLE) violated

Record 9: Rejected - Error on table ACCOUNTREQUEST.
ORA-02290: check constraint (HR.CHK_ACCOUNTS) violated


Table ACCOUNTREQUEST:
  7 Rows successfully loaded.
  2 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.

i want to use a shell scrip that counts the rows that is successfully loaded

and not loaded and why (like what was said in record 8 and 9)

PS. The data didnt insert because of constraint which only allows (MR,MRS,MS) for title and (SAVINGS,CURRENT) for account type

I can already count the rejected via shell script

grep -wc "rejected" txtsample.txt 

but cant echo it to a error.log and i also need the reason for error which is the record 8 and 9

2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.