Home » RDBMS Server » Server Utilities » count of data in sql loader (oracle 11gr2 solaris 10)
count of data in sql loader [message #562672] Fri, 03 August 2012 05:37 Go to next message
guddu_12
Messages: 219
Registered: April 2012
Location: UK
Senior Member
Hi ,

I want to populate totale number of record in the file. Usually i get 10000 records per file and i load them using sql loader.
I want to also insert the number of records in file while loading the data in table.

How can i achive it.

structure of control file is

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
        )



data
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||||||||||


table structure
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
Re: count of data in sql loader [message #562677 is a reply to message #562672] Fri, 03 August 2012 06:16 Go to previous messageGo to next message
Michel Cadot
Messages: 67369
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Bad design. Fix it.

Regards
Michel
Re: count of data in sql loader [message #562681 is a reply to message #562677] Fri, 03 August 2012 06:28 Go to previous messageGo to next message
guddu_12
Messages: 219
Registered: April 2012
Location: UK
Senior Member
Sir, I have not designed it but i have to do it, is there any way in loader which can count the record in file and load the data
Re: count of data in sql loader [message #562684 is a reply to message #562681] Fri, 03 August 2012 06:41 Go to previous messageGo to next message
Michel Cadot
Messages: 67369
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Afaik, you can't do it with SQL*Loader.
Now, how will you do something you can't do?
I bet you will change the requirements, so change the design in the same time.

Regards
Michel
Re: count of data in sql loader [message #562695 is a reply to message #562672] Fri, 03 August 2012 08:22 Go to previous message
joy_division
Messages: 4949
Registered: February 2005
Location: East Coast USA
Senior Member
First of all, you have no column called REC_CNT.
Next, what is the purpose of loading the total number of record in every record? you load 10000 records and all records would have the number 10000 loaded into the REC_CNT column., if you even had that column in the table. What possible use would this be?
Previous Topic: IMPORT(Impdp) multiple dump files
Next Topic: strange issue in sql loader
Goto Forum:
  


Current Time: Thu Sep 24 04:16:40 CDT 2020