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 Go to next message](/forum/theme/orafaq/images/down.png) |
![](//www.gravatar.com/avatar/e1807e23242de2805a657f4ad9ae6654?s=64&d=mm&r=g) |
guddu_12
Messages: 227 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](/forum/images/custom_avatars/43710.gif) |
Barbara Boehmer
Messages: 9104 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](//www.gravatar.com/avatar/e1807e23242de2805a657f4ad9ae6654?s=64&d=mm&r=g) |
guddu_12
Messages: 227 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 #554905 is a reply to message #554883] |
Fri, 18 May 2012 12:41 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](/forum/images/custom_avatars/43710.gif) |
Barbara Boehmer
Messages: 9104 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')"
|
|
|
Goto Forum:
Current Time: Fri Feb 07 01:30:56 CST 2025
|