OraFAQ Forum: Server Utilities

Home » RDBMS Server » Server Utilities » sql loader error (Release 10.2.0.5.0 sun solaris )

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

sql loader error [message #555978]

Wed, 30 May 2012 06:27

guddu_12
Messages: 227
Registered: April 2012
Location: UK

Senior Member

HI Gurus,

I am trying to load data from file to oracle and getting error, though the error is silly but i couln't find it in my scripts.

Table structure

CREATE TABLE TEMP_PACS_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(3)             NULL,
  TRANSACTION_END_TIME     TIMESTAMP(3)             NULL,
  LOCATION_ID              VARCHAR2(50 BYTE)        NULL,
  WAIT_TIME                NUMBER(10,3)             NULL,
  INTERNAL_TRANSACTION_ID  VARCHAR2(50 BYTE)        NULL,
  INTERNAL_TIME            NUMBER(10,3)             NULL,
  EXTERNAL_SERVICE_ID      VARCHAR2(50 BYTE)        NULL,
  EXTERNAL_SERVICE_TIME    NUMBER(10,3)             NULL,
  LOCAL_SERVICE_ID         VARCHAR2(50 BYTE)        NULL,
  LOCAL_SERVICE_TIME       NUMBER(10,3)             NULL,
  MESSAGE_GUID             VARCHAR2(50 BYTE)        NULL,
  RETURN_MESSAGE_GUID      VARCHAR2(50 BYTE)        NULL,
  FILE_NAME                VARCHAR2(100 BYTE)       NULL,
  DATE_LOADED              DATE                     NULL,
  TRS_SIZE                 NUMBER(14,3)             NULL,
  USER_DETAIL_FLAG         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
)

control file

 load data
        BADFILE '/backup/temp/rajesh/PACS/BadFiles/FILENAME'
        append into table TEMP_PACS_RESP_TIME_LND
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
        TRAILING NULLCOLS
        (
        INSTALLATION_ID          CHAR,
        TRANSACTION_ID           CHAR,
        SERVER_ID                CHAR,
        CLINICAL_TRANSACTION_ID  CHAR,
        RESPONSE_TIME DECIMAL EXTERNAL,
        TRANSACTION_START_TIME   TIMESTAMP
        "TO_TIMESTAMP(:TRANSACTION_START_TIME,'DD/MM/YYYY HH24:MI:SSXFF')",
        TRANSACTION_END_TIME     TIMESTAMP
        "TO_TIMESTAMP(:TRANSACTION_END_TIME,'DD/MM/YYYY HH24:MI:SSXFF')",
        LOCATION_ID              CHAR,
        WAIT_TIME          DECIMAL EXTERNAL,
        INTERNAL_TRANSACTION_ID  CHAR,
        INTERNAL_TIME  DECIMAL EXTERNAL,
        EXTERNAL_SERVICE_ID      CHAR,
        EXTERNAL_SERVICE_TIME    DECIMAL EXTERNAL,
        LOCAL_SERVICE_ID         CHAR,
        LOCAL_SERVICE_TIME       DECIMAL EXTERNAL,
        MESSAGE_GUID             CHAR,
        RETURN_MESSAGE_GUID      CHAR,
        TRS_SIZE     DECIMAL EXTERNAL,
        FILE_NAME    CONSTANT
        "FILENAME", -- this will be replace by 'PACS_WEB_Q36-RNH_20120530103802.log'
        DATE_LOADED              SYSDATE  ,
        ORIGINAL_DATE_LOADED     SYSDATE
        )

Data
PACS_WEB_Q36-RNH|BPM001_30052012103803_PACS_CRT_NEWHAM|NUNT_Newham_Plaistow_PACS_CRT|PACS_CRT_NEWHAM|0.007|30/05/2012 10:38:03.000|30/05/2012 10:38:03.007|PACS_WEB_Q36-RNH|0|||||||||

when running i am getting below error.

Record 1: Rejected - Error on table TEMP_PACS_RESP_TIME_LND, column TRANSACTION_START_TIME.
ORA-00907: missing right parenthesis

Record 2: Rejected - Error on table TEMP_PACS_RESP_TIME_LND, column TRANSACTION_START_TIME.
ORA-00907: missing right parenthesis

Table TEMP_PACS_RESP_TIME_LND:
0 Rows successfully loaded.
2 Rows not loaded due to data errors.

Can you please help me

Report message to a moderator

Re: sql loader error [message #555999 is a reply to message #555978]

Wed, 30 May 2012 08:50

Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA

Senior Member

You are mixing syntax options. You need to use one or the other of the below syntaxes.

TRANSACTION_START_TIME TIMESTAMP 'DD/MM/YYYY HH24:MI:SSXFF'

TRANSACTION_START_TIME "TO_TIMESTAMP(:TRANSACTION_START_TIME,'DD/MM/YYYY HH24:MI:SSXFF')"

TRANSACTION_START_TIME CHAR "TO_TIMESTAMP(:TRANSACTION_START_TIME,'DD/MM/YYYY HH24:MI:SSXFF')"

The same applies to the other timestamp column, transaction_end_time.

You also need to make the installation_id column in your table big enough (182 BYTE) to hold the data that you are trying to load.

Report message to a moderator

Re: sql loader error [message #556027 is a reply to message #555978]

Wed, 30 May 2012 12:01

John Watson
Messages: 8966
Registered: January 2010
Location: Global Village

Senior Member

Furthermore, your control file says
FIELDS TERMINATED BY ','
which does not match the delimiters in your data file.

Report message to a moderator

Re: sql loader error [message #556035 is a reply to message #556027]

Wed, 30 May 2012 12:28

Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA

Senior Member

John Watson wrote on Wed, 30 May 2012 10:01

Furthermore, your control file says
FIELDS TERMINATED BY ','
which does not match the delimiters in your data file.

I missed that. That explains why, when I tested it, it looked like the first column needed to bigger, because it tried to load the whole row in the first column.

Report message to a moderator

Previous Topic:	export/dump with expdp for non-privileged users
Next Topic:	Export Schema Error

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Mar 03 16:28:04 CST 2025