Home » RDBMS Server » Server Utilities » sql loader syntax error 350 (10.2.0.5)
sql loader syntax error 350 [message #554786] Thu, 17 May 2012 07:00 Go to next message
guddu_12
Messages: 219
Registered: April 2012
Location: UK
Senior Member
Hi Guys

I am trying to load data into multiple table using IN prdicate but it gives me error.

My data looks like below.

05/17/12,07:45:44,resn-j35-ctc116,"USR:ESM.CQueryTypeDlg::RunQuery().wft",0.110,P,schapptbook,24768,23940,848944703042,RBA4010975,10.174.107.107,0, "",0,"","",""

05/17/12,07:45:43,resn-j35-ctc107,"USR:ESM.CQueryTypeDlg::RunQuery().wft",0.516,P,schapptbook,2716,18352,178502215043,RBA38430,10.174.110.14,0, "",0,"","",""

control file looks like as below
 load data
        BADFILE '/backup/temp/rajesh/CERNER/BadFiles/FILENAME'
        append into table TEMP_CERNER_RESP_TIME_LND
        TRAILING NULLCOLS
        WHEN CLINICAL_TRANSACTION_ID = 'USR:ERM PMSEARCH ENCOUNTER RESULTS DISPLAY'
        (filler1 BOUNDFILLER TERMINATED BY ',' ,
        FILLER2 BOUNDFILLER TERMINATED BY ',' ,
        TRANSACTION_END_TIME
        TIMESTAMP OPTIONALLY ENCLOSED BY '"' "TO_TIMESTAMP(:filler1 || ' ' || :FILLER2||'.000','DD/MM/YYYY HH24:MI:SSXFF')",
        SERVER_ID    (19)            CHAR
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
        CLINICAL_TRANSACTION_ID  CHAR
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' ltrim(rtrim(:clinical_transaction_id,'"'),'"'),
        RESPONSE_TIME DECIMAL EXTERNAL
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
        STATUS          CHAR
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' Upper(:status),
        COLUMN 1        FILLER CHAR
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
        COLUMN 2        FILLER CHAR
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
        COLUMN 3        FILLER CHAR
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
        COLUMN 4        FILLER CHAR
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
        COLUMN 5        FILLER CHAR
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
        COLUMN 6        FILLER CHAR
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
        COLUMN 7        FILLER CHAR
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
        COLUMN 8        FILLER CHAR
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
        COLUMN 9        FILLER CHAR
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
        COLUMN 10       FILLER CHAR
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
        COLUMN 11       FILLER CHAR
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
        COLUMN 12       FILLER CHAR
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
        COLUMN 13       FILLER CHAR
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
        TRANSACTION_START_TIME DATE  "TO_TIMESTAMP(:filler1 || ' ' || :FILLER2 ||'.000','DD/MM/YYYY HH24:MI:SSXFF')  - NumToDsInterval(:RESPONSE_TIME,'SECOND')"
        LOCATION_ID     Substr(FILENAME,INSTR(FILE_NAME,'_',1,1)+1,INSTR(FILE_NAME,'_',1,2) - INSTR(FILE_NAME,'_',1,1) - 1),
        INSTALLATION_ID substr(FILENAME,1,INSTR(FILE_NAME,'_',1,1) -1),
        date_loaded SYSDATE            ,
         original_date_loaded SYSDATE
)



tables is as below


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
)

When i run the sql loader it comes out with the error as below.

SQL*Loader-350: Syntax error at line 5

Please help
Re: sql loader syntax error 350 [message #554798 is a reply to message #554786] Thu, 17 May 2012 08:41 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
You have many errors. When developing a data load, you should try to test just one field at a time, so that you can tell where the errors are. Once you get one field loaded, then add another field and re-test. Don't add any fields until the previous ones are working.

When you supply a test case, in addition to the data file, control file, and create table statement, it helps to have an example of what the results should look like, based on the sample data provided. It also helps if some of the data provided matches the WHEN clause, so that some of it is expected to load.

You WHEN clause needs to be immediately after your INTO TABLE clause, before anything else like TRAILING NULLCOLS.

Although you can supply individual termination characters and enclosure characters for each field, it is much easier to supply them once for the whole control file if they are all the same. Such delimiters and enclosures do not apply to calculated fields, such as transaction_end_time and transaction_start_time. You do not need to trim the quotes off of such enclosures either.

The fields in the control file must be in the order that they are encountered in the data file, not the order they are encountered in the table. All calculated fields, like transaction_end_time and transaction_start_time and fields that use constants, such as date_loaded and original_date_loaded must be after any fields in the data file.

When you use something like to_timestamp to convert a concatenated data type, then you should not try to also declare it as that data type prior to conversion.

I am guessing that when you use "server_id (19)" that you intended for the server_id column to start as position 19, but you left out the POSITION keyword and it is not needed anyhow, since it is comma-separated.

You are missing some quotations around things like "Upper(:status)".

Your location_id and installation_id are such a mess that I could not tell what you are trying to do. They seem to be based on filename, but I don't see a filename column in your control file or anything that looks like a file name in your data.

There are various other minor errors.

In the example below, I fixed a lot of the errors, so that most of the fields load. I changed the value for the WHEN clause, so that both rows would load, so that I would have something to test. You will need to do what you can with the other fields, working on the one at a time. If you get stuck, post an example of what the ending data should look like when selected from the table, so that we can tell what goes where.

-- test.dat:
05/17/12,07:45:44,resn-j35-ctc116,"USR:ESM.CQueryTypeDlg::RunQuery().wft",0.110,P,schapptbook,24768,23940,848944703042,RBA4010975,10.174.107.107,0, "",0,"","",""
05/17/12,07:45:43,resn-j35-ctc107,"USR:ESM.CQueryTypeDlg::RunQuery().wft",0.516,P,schapptbook,2716,18352,178502215043,RBA38430,10.174.110.14,0, "",0,"","",""


-- test.ctl:
LOAD DATA
APPEND
INTO TABLE temp_cerner_resp_time_lnd
WHEN clinical_transaction_id = 'USR:ESM.CQueryTypeDlg::RunQuery().wft'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(filler1 BOUNDFILLER,
 filler2 BOUNDFILLER,
 server_id,
 clinical_transaction_id,
 response_time,
 status 
  "UPPER (:status)",
 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') 
    - NumToDsInterval (:response_time, 'SECOND')",
 date_loaded SYSDATE,           
 original_date_loaded SYSDATE)


-- table:
SCOTT@orcl_11gR2> CREATE TABLE TEMP_CERNER_RESP_TIME_LND
  2  (
  3  INSTALLATION_ID VARCHAR2(50 BYTE) NULL,
  4  TRANSACTION_ID VARCHAR2(50 BYTE) NULL,
  5  SERVER_ID VARCHAR2(50 BYTE) NULL,
  6  CLINICAL_TRANSACTION_ID VARCHAR2(255 BYTE) NULL,
  7  RESPONSE_TIME NUMBER(10,3) NULL,
  8  TRANSACTION_START_TIME TIMESTAMP(9) NULL,
  9  TRANSACTION_END_TIME TIMESTAMP(9) NULL,
 10  LOCATION_ID VARCHAR2(50 BYTE) NULL,
 11  FILE_NAME VARCHAR2(100 BYTE) NULL,
 12  DATE_LOADED DATE NULL,
 13  USER_DETAIL_FLAG VARCHAR2(1 BYTE) NULL,
 14  STATUS VARCHAR2(1 BYTE) NULL,
 15  SLA_TRS_TYPE VARCHAR2(10 BYTE) NULL,
 16  COMPONENT_SHORT_DESC VARCHAR2(50 BYTE) NULL,
 17  LOCATION_SHORT_DESC VARCHAR2(50 BYTE) NULL,
 18  DATE_VALIDATED DATE NULL,
 19  ORIGINAL_DATE_LOADED DATE NULL
 20  )
 21  /

Table created.


-- load:
SCOTT@orcl_11gR2> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log DATA=test.dat


-- results:
SCOTT@orcl_11gR2> SELECT * FROM temp_cerner_resp_time_lnd
  2  /

INSTALLATION_ID
--------------------------------------------------
TRANSACTION_ID
--------------------------------------------------
SERVER_ID
--------------------------------------------------
CLINICAL_TRANSACTION_ID
--------------------------------------------------------------------------------
RESPONSE_TIME
-------------
TRANSACTION_START_TIME
---------------------------------------------------------------------------
TRANSACTION_END_TIME
---------------------------------------------------------------------------
LOCATION_ID
--------------------------------------------------
FILE_NAME
--------------------------------------------------------------------------------
DATE_LOAD U S SLA_TRS_TY COMPONENT_SHORT_DESC
--------- - - ---------- --------------------------------------------------
LOCATION_SHORT_DESC                                DATE_VALI ORIGINAL_
-------------------------------------------------- --------- ---------


resn-j35-ctc116
USR:ESM.CQueryTypeDlg::RunQuery().wft
          .11
17-MAY-12 07.45.43.890000000 AM
17-MAY-12 07.45.44.000000000 AM


17-MAY-12   P
                                                             17-MAY-12



resn-j35-ctc107
USR:ESM.CQueryTypeDlg::RunQuery().wft
         .516
17-MAY-12 07.45.42.484000000 AM
17-MAY-12 07.45.43.000000000 AM


17-MAY-12   P
                                                             17-MAY-12


2 rows selected.

Re: sql loader syntax error 350 [message #554814 is a reply to message #554798] Thu, 17 May 2012 10:52 Go to previous message
guddu_12
Messages: 219
Registered: April 2012
Location: UK
Senior Member
Hi Barbara,

I just read your post befor i have posted problem on the same data and same control file, i have resolved many of the error what you have resolved in your reply for this post. Your reply is superb , you have explained it very nicely that most of the concept is clear through your post.

I must say thank you.

Thanks

Rajesh
Previous Topic: sql loader control file help
Next Topic: sql loader to load in multiple table with IN predicate
Goto Forum:
  


Current Time: Wed Sep 23 00:28:18 CDT 2020