Home » RDBMS Server » Server Utilities » Error not a valid month in sql loader (10.2.0.5)
Error not a valid month in sql loader [message #554813] Thu, 17 May 2012 10:34 Go to next message
guddu_12
Messages: 219
Registered: April 2012
Location: UK
Senior Member
Hi

I am trying to load below data using sql loader.

05/17/12,07:45:39,resn-j35-ctc113,"USR:ESM.CQueryTypeDlg::RunQuery().wft",0.125,P,schapptbook,22712,25704,705355992045,RBA4010200,10.146.218.154,0, "",0,"","",""

table structure

CREATE TABLE TEMP_CERNER_RESP_TIME_LND
(
INSTALLATION_ID VARCHAR2(50 BYTE) NULL,
TRANSACTION_ID VARCHAR2(50 BYTE) NULL,
SERVER_ID VARCHAR2(50 BYTE) NULL,
CLINICAL_TRANSACTION_ID VARCHAR2(255 BYTE) NULL,
RESPONSE_TIME NUMBER(10,3) NULL,
TRANSACTION_START_TIME TIMESTAMP(9) NULL,
TRANSACTION_END_TIME TIMESTAMP(9) NULL,
LOCATION_ID VARCHAR2(50 BYTE) NULL,
FILE_NAME VARCHAR2(100 BYTE) NULL,
DATE_LOADED DATE NULL,
USER_DETAIL_FLAG VARCHAR2(1 BYTE) NULL,
STATUS VARCHAR2(1 BYTE) NULL,
SLA_TRS_TYPE VARCHAR2(10 BYTE) NULL,
COMPONENT_SHORT_DESC VARCHAR2(50 BYTE) NULL,
LOCATION_SHORT_DESC VARCHAR2(50 BYTE) NULL,
DATE_VALIDATED DATE NULL,
ORIGINAL_DATE_LOADED DATE NULL
)

Below function has been used to transfor data and callled in sql loader control file

CREATE OR REPLACE function return_domain( domain_name varchar2)
return varchar2
as

v_dmn varchar2(100);

begin


select Substr(domain_name,INSTR(domain_name,'_',1,1)+1,INSTR(domain_name,'_',1,2) - INSTR(domain_name,'_',1,1) - 1) into v_dmn from dual;

return v_dmn;

End return_domain;
/


sql loader control file is as below
load data
        BADFILE '/backup/temp/rajesh/CERNER/BadFiles/FILENAME'
        append into table TEMP_CERNER_RESP_TIME_LND
        WHEN CLINICAL_TRANSACTION_ID = 'USR:ERM PMSEARCH ENCOUNTER RESULTS DISPLAY'
        TRAILING NULLCOLS
        (filler1 BOUNDFILLER TERMINATED BY ',' ,
        FILLER2 BOUNDFILLER TERMINATED BY ',' ,
        TRANSACTION_END_TIME
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' "TO_TIMESTAMP(:filler1 || ' ' || :FILLER2 ,'MM/DD/YY HH24:MI:SS')",
        SERVER_ID                CHAR
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
        CLINICAL_TRANSACTION_ID  CHAR
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' ,
        RESPONSE_TIME DECIMAL EXTERNAL
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
        STATUS          CHAR
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' "UPPER(:status)",
        COLUMN1 FILLER CHAR
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
        COLUMN2 FILLER CHAR
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
        COLUMN3 FILLER CHAR
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
        COLUMN4 FILLER CHAR
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
        COLUMN5 FILLER CHAR
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
        COLUMN6 FILLER CHAR
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
        COLUMN7 FILLER CHAR
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
        COLUMN8 FILLER CHAR
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
        COLUMN9 FILLER CHAR
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
        COLUMN10        FILLER CHAR
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
        COLUMN11        FILLER CHAR
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
        COLUMN12        FILLER CHAR
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
        COLUMN13        FILLER CHAR
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
        FILE_NAME CONSTANT  "FILENAME",
        TRANSACTION_START_TIME   "TO_TIMESTAMP(:filler1 || ' ' || :FILLER2 ,'MM/DD/YY HH24:MI:SS')  - NumToDsInterval(:RESPONSE_TIME,'SECOND')",
        LOCATION_ID  "return_domain('FILENAME')",
        INSTALLATION_ID "substr('FILENAME',1,INSTR('FILENAME','_',1,1) -1)",
        date_loaded SYSDATE            ,
         original_date_loaded SYSDATE
)


function takes the parameter as 'DOMAIN50_LPAR5002_slainterval051712_rj35cmi102_08_45_00.csv '

FILENAME in control file will be replace by DOMAIN50_LPAR5002_slainterval051712_rj35cmi102_08_45_00.csv
when i run the the the loader i get the below error.

Record 1: Rejected - Error on table TEMP_CERNER_RESP_TIME_LND.
ORA-00604: error occurred at recursive SQL level 1
ORA-01843: not a valid month

Can any one help me on this
Re: Error not a valid month in sql loader [message #554816 is a reply to message #554813] Thu, 17 May 2012 11:06 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
I don't get that error when I run what you provided. However, I noticed in one of your previous posts that your date format did not match your data. You were using DD/MM/YYYY when you should have been using MM/DD/YYYY. I corrected it, but did not mention it. Without the correction, it would have raised the error that you are getting. You still have the transaction_end_time in the wrong place and that throws everything else off. You also have termination and enclosure characters for it, that cannot be used with a calculated field. I have provided corrected control file below. Also, once again you have provided only test data that will fail all WHEN clauses. Please read my detailed response to your previous post.

load data
        BADFILE '/backup/temp/rajesh/CERNER/BadFiles/FILENAME'
        append into table TEMP_CERNER_RESP_TIME_LND
        WHEN CLINICAL_TRANSACTION_ID = 'USR:ERM PMSEARCH ENCOUNTER RESULTS DISPLAY'
        TRAILING NULLCOLS
        (filler1 BOUNDFILLER TERMINATED BY ',' ,
        FILLER2 BOUNDFILLER TERMINATED BY ',' ,
        SERVER_ID                CHAR
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
        CLINICAL_TRANSACTION_ID  CHAR
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' ,
        RESPONSE_TIME DECIMAL EXTERNAL
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
        STATUS          CHAR
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' "UPPER(:status)",
        COLUMN1 FILLER CHAR
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
        COLUMN2 FILLER CHAR
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
        COLUMN3 FILLER CHAR
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
        COLUMN4 FILLER CHAR
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
        COLUMN5 FILLER CHAR
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
        COLUMN6 FILLER CHAR
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
        COLUMN7 FILLER CHAR
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
        COLUMN8 FILLER CHAR
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
        COLUMN9 FILLER CHAR
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
        COLUMN10        FILLER CHAR
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
        COLUMN11        FILLER CHAR
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
        COLUMN12        FILLER CHAR
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
        COLUMN13        FILLER CHAR
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
        FILE_NAME CONSTANT  "FILENAME",
        TRANSACTION_END_TIME "TO_TIMESTAMP(:filler1 || ' ' || :FILLER2 ,'MM/DD/YY HH24:MI:SS')",
        TRANSACTION_START_TIME   "TO_TIMESTAMP(:filler1 || ' ' || :FILLER2 ,'MM/DD/YY HH24:MI:SS')  - NumToDsInterval(:RESPONSE_TIME,'SECOND')",
        LOCATION_ID  "return_domain('FILENAME')",
        INSTALLATION_ID "substr('FILENAME',1,INSTR('FILENAME','_',1,1) -1)",
        date_loaded SYSDATE            ,
         original_date_loaded SYSDATE
)


Re: Error not a valid month in sql loader [message #554871 is a reply to message #554816] Fri, 18 May 2012 03:30 Go to previous messageGo to next message
guddu_12
Messages: 219
Registered: April 2012
Location: UK
Senior Member
HI Barbara,

I have changed the control files as you have instructed me. the control files looks like below.
load data
        BADFILE '/backup/temp/rajesh/CERNER/BadFiles/FILENAME'
        append into table TEMP_CERNER_RESP_TIME_LND
        WHEN CLINICAL_TRANSACTION_ID = 'USR:ERM PMSEARCH ENCOUNTER RESULTS DISPLAY'
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
        TRAILING NULLCOLS
        (filler1 BOUNDFILLER TERMINATED BY ',' ,
        FILLER2 BOUNDFILLER TERMINATED BY ',' ,
        SERVER_ID                CHAR,
        CLINICAL_TRANSACTION_ID  CHAR,
        RESPONSE_TIME DECIMAL EXTERNAL,
        STATUS          CHAR  "UPPER(:status)",
        COLUMN1 FILLER CHAR ,
        COLUMN2 FILLER CHAR ,
        COLUMN3 FILLER CHAR ,
        COLUMN4 FILLER CHAR ,
        COLUMN5 FILLER CHAR ,
        COLUMN6 FILLER CHAR ,
        COLUMN7 FILLER CHAR ,
        COLUMN8 FILLER CHAR ,
        COLUMN9 FILLER CHAR ,
        COLUMN10        FILLER CHAR ,
        COLUMN11        FILLER CHAR ,
        COLUMN12        FILLER CHAR ,
        FILE_NAME CONSTANT  "FILENAME",
        TRANSACTION_END_TIME "TO_TIMESTAMP(:filler1 || ' ' || :FILLER2 || '.000', 'MM/DD/YYYY HH24:MI:SSXFF')",
        TRANSACTION_START_TIME "TO_TIMESTAMP(:filler1 || ' ' || :FILLER2 || '.000', 'MM/DD/YYYY HH24:MI:SSXFF' ,'MM/DD/YY HH24:MI:SS')  - NumToDsInterval(:RESPONSE_TIME
,'SECOND')",
        LOCATION_ID  "return_domain('FILENAME')",
        INSTALLATION_ID "substr('FILENAME',1,INSTR('FILENAME','_',1,1) -1)",
        date_loaded SYSDATE            ,
         original_date_loaded SYSDATE
)
into table TEMP_CERNER_RESP_TIME_LND_1
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
        TRAILING NULLCOLS
        (filler1 BOUNDFILLER TERMINATED BY ',' ,
        FILLER2 BOUNDFILLER TERMINATED BY ',' ,
        SERVER_ID                CHAR,
        CLINICAL_TRANSACTION_ID  CHAR,
        RESPONSE_TIME DECIMAL EXTERNAL,
        STATUS          CHAR  "UPPER(:status)",
        COLUMN1 FILLER CHAR ,
        COLUMN2 FILLER CHAR ,
        COLUMN3 FILLER CHAR ,
        COLUMN4 FILLER CHAR ,
        COLUMN5 FILLER CHAR ,
        COLUMN6 FILLER CHAR ,
        COLUMN7 FILLER CHAR ,
        COLUMN8 FILLER CHAR ,
        COLUMN9 FILLER CHAR ,
        COLUMN10        FILLER CHAR ,
        COLUMN11        FILLER CHAR ,
        COLUMN12        FILLER CHAR ,
        FILE_NAME CONSTANT  "FILENAME",
        TRANSACTION_END_TIME "TO_TIMESTAMP(:filler1 || ' ' || :FILLER2 || '.000', 'MM/DD/YYYY HH24:MI:SSXFF')",
        TRANSACTION_START_TIME "TO_TIMESTAMP(:filler1 || ' ' || :FILLER2 || '.000', 'MM/DD/YYYY HH24:MI:SSXFF' ,'MM/DD/YY HH24:MI:SS')  - NumToDsInterval(:RESPONSE_TIME
,'SECOND')",
        LOCATION_ID  "return_domain('FILENAME')",
        INSTALLATION_ID "substr('FILENAME',1,INSTR('FILENAME','_',1,1) -1)",
        date_loaded SYSDATE            ,
         original_date_loaded SYSDATE
)


i have also added another table to route all the record which doesn't satisfy the condition.

When i run the loader it gives me different error

Record 685: Rejected - Error on table TEMP_CERNER_RESP_TIME_LND.
ORA-00604: error occurred at recursive SQL level 1
ORA-12702: invalid NLS parameter string used in SQL function.

do i need to add any patameter in the database. please advice.

Thanks for your all the help
Re: Error not a valid month in sql loader [message #554883 is a reply to message #554871] Fri, 18 May 2012 05:52 Go to previous messageGo to next message
guddu_12
Messages: 219
Registered: April 2012
Location: UK
Senior Member
It was a silly mistake i have resolved myself.

Many thanks

Rajesh
Re: Error not a valid month in sql loader [message #554905 is a reply to message #554883] Fri, 18 May 2012 12:41 Go to previous message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
I assume the problem was two date formats:

TRANSACTION_START_TIME "TO_TIMESTAMP(:filler1 || ' ' || :FILLER2 || '.000', 'MM/DD/YYYY HH24:MI:SSXFF' ,'MM/DD/YY HH24:MI:SS') - NumToDsInterval(:RESPONSE_TIME
,'SECOND')"

instead of one date format:

TRANSACTION_START_TIME "TO_TIMESTAMP(:filler1 || ' ' || :FILLER2 || '.000', 'MM/DD/YYYY HH24:MI:SSXFF') - NumToDsInterval(:RESPONSE_TIME,'SECOND')"



Previous Topic: call sequence in sql loader
Next Topic: tablespace management
Goto Forum:
  


Current Time: Fri Aug 07 07:53:37 CDT 2020