Data transfer

[PiyuNewe]

Hi Gurus,

I have one Tab delimited text file which i wanted to transfer/migrate into ORACLE table. Can I do it without using DTS package or any Data transfer tool?

There are 6-7 columns in that file and same in the Oracle Table. I am reading the file using UTL_FILE package.

Any quick inputs appriciated.

-PiyuNewe

[Krzemo]

Why not use SQLLoader?

... or (if U have version 9i or more:rolleyes: ) U can do "CREATE EXTERNAL TABLE" which allows U to read external table the same way as U can read any data located in "normal" tables.


Best regards,
Krzemo.

[Krzemo]

some example:

CREATE TABLE TB_RAP_EXT
(
C01 VARCHAR2(128 BYTE),
C02 VARCHAR2(128 BYTE),
C03 VARCHAR2(128 BYTE),
C04 VARCHAR2(128 BYTE),
C05 VARCHAR2(128 BYTE),
C06 VARCHAR2(128 BYTE),
C07 VARCHAR2(128 BYTE),
C08 VARCHAR2(128 BYTE),
C09 VARCHAR2(128 BYTE)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY PR
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
LOAD WHEN ( c01!= BLANKS)
BADFILE PR:'bad_PR.log'
LOGFILE PR:'log_PR.log'
SKIP 1
FIELDS TERMINATED BY ';'
(
C01 POSITION(1) CHAR,
C02 CHAR,
C03 CHAR,
C04 CHAR,
C05 CHAR,
C06 CHAR,
C07 CHAR,
C08 CHAR,
C09 CHAR
) )
LOCATION (PR:'transfer.csv')
)
REJECT LIMIT 2000
LOGGING
NOCACHE
NOPARALLEL;


Best regards,
Krzemo.