load data
        BADFILE '/backup/temp/rajesh/RIO/BadFiles/FILENAME'
        append into table ERS_RIO_SRC
        TRAILING NULLCOLS
        (
        INSTALLATION_ID          CHAR
        TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
        TRANSACTION_ID           CHAR
        TERMINATED BY "|" OPTIONALLY ENCLOSED 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 '"',
        TRANSACTION_START_TIME   TIMESTAMP
        TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"' "TO_TIMESTAMP(:TRANSACTION_START_TIME,'DD/MM/YYYY HH24:MI:SSXFF')",
        TRANSACTION_END_TIME     TIMESTAMP
        TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"' "TO_TIMESTAMP(:TRANSACTION_END_TIME,'DD/MM/YYYY HH24:MI:SSXFF')",
        LOCATION_ID              CHAR
        TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
        WAIT_TIME          DECIMAL EXTERNAL
        TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
        INTERNAL_TRANSACTION_ID  CHAR
        TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
        INTERNAL_TIME  DECIMAL EXTERNAL
        TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
        EXTERNAL_SERVICE_ID      CHAR
        TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
        EXTERNAL_SERVICE_TIME    DECIMAL EXTERNAL
        TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
        LOCAL_SERVICE_ID         CHAR
        TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
        LOCAL_SERVICE_TIME       DECIMAL EXTERNAL
        TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
        MESSAGE_GUID             CHAR
        TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
        RETURN_MESSAGE_GUID      CHAR
        TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
        TRS_SIZE     DECIMAL EXTERNAL
        TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"'  ,
        FILE_NAME    CONSTANT
        "FILENAME",
        DATE_LOADED              SYSDATE
        rec_cnt constant -------------------record count of file
        )

V5_RIO_5K7C|78967|172.16.43.153|RioLoginSrc.asp|0.375|01/08/2012 07:44:44.623|01/08/2012 07:44:45.000|V5_RIO_5K7C||||||||||
V5_RIO_5K7C|78968|172.16.43.150|RioLoginSrc.asp|0.187|01/08/2012 08:22:32.813|01/08/2012 08:22:33.000|V5_RIO_5K7C||||||||||
V5_RIO_5K7C|78969|172.16.43.147|RioLoginSrc.asp|60.296|01/08/2012 08:22:36.703|01/08/2012 08:23:37.000|V5_RIO_5K7C||||||||||
V5_RIO_5K7C|78970|172.16.43.141|RioLoginSrc.asp|30.265|01/08/2012 10:20:39.733|01/08/2012 10:21:10.000|V5_RIO_5K7C||||||||||
V5_RIO_5K7C|78971|172.16.43.152|RioLoginSrc.asp|0.312|01/08/2012 16:00:46.687|01/08/2012 16:00:47.000|V5_RIO_5K7C||||||||||

CREATE TABLE ERS_RIO_SRC
(
  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(256 BYTE)       NULL,
  RETURN_MESSAGE_GUID      VARCHAR2(256 BYTE)       NULL,
  FILE_NAME                VARCHAR2(100 BYTE)       NULL,
  DATE_LOADED              DATE                     NULL,
  TRS_SIZE                 NUMBER(14,3)             NULL
)
PARTITION BY RANGE (DATE_LOADED) 
(  
  PARTITION RIO_31123000 VALUES LESS THAN (MAXVALUE)
    NOLOGGING
    NOCOMPRESS
)
NOCOMPRESS 
NOCACHE
NOPARALLEL