You Asked

Hi team,

I have a txt file which has data delimited by "TAB SPACE" and i am trying to load the data using the oet table.Here is the script below which i used to load the data . Unfortunately the data is not loading . Can any one say how to load the tab space data using oracle external table.

Script:

CREATE TABLE car_oet_laod
(
car_id VARCHAR2(500 BYTE),
car_nm VARCHAR2(500 BYTE),
car_num VARCHAR2(500 BYTE)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY LOGGING
ACCESS PARAMETERS 
( records delimited by newline skip 1
logfile EXT:'CAR.txt.log'
fields terminated by '\t' OPTIONALLY ENCLOSED BY '"'
)
LOCATION (EXT:'CAR.txt')
)
REJECT LIMIT UNLIMITED
NOPARALLEL
NOMONITORING;

Thank you.

and Connor said...

I think it must be content in your file, because your DDL looks fine. eg

Here is my file - one tab after col1 and col2

TAB_DELIM_FILE

And here is your DDL

SQL> CREATE TABLE car_oet_laod
  2  (
  3  car_id VARCHAR2(50 BYTE),
  4  car_nm VARCHAR2(50 BYTE),
  5  car_num VARCHAR2(50 BYTE)
  6  )
  7  ORGANIZATION EXTERNAL
  8  ( TYPE ORACLE_LOADER
  9  DEFAULT DIRECTORY TEMP
 10  ACCESS PARAMETERS
 11  ( records delimited by newline skip 1
 12  logfile TEMP:'CAR.txt.log'
 13  fields terminated by '\t' OPTIONALLY ENCLOSED BY '"'
 14  )
 15  LOCATION (TEMP:'cars.txt')
 16  )
 17  REJECT LIMIT UNLIMITED
 18  NOPARALLEL
 19  NOMONITORING;

Table created.

SQL>
SQL> select * from car_oet_laod;

CAR_ID                                             CAR_NM                                             CAR_NUM
-------------------------------------------------- -------------------------------------------------- ----------------
Column1b                                           Column2b                                           Column3b
Column1c                                           Column2c                                           Column3c
Column1d                                           Column2d                                           Column3d

So double check your data.

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

JimS, November 13, 2017 - 5:13 am UTC

Hello Santosh

You mentioned the values in your file are separated by "TAB SPACE" but you only have the TAB character '\t' in your access parameters. If you change that to '\t ' (add a space before closing quote. It should work.

Quick example follows, of course you could also look to trim the space from the TAB.

I used vi to create a 2 row file with 3 values per row
each delimited by TAB and a SPACE then used cat command to check.

[oracle@localhost tmp]$ pwd
/tmp
[oracle@localhost tmp]$ cat -T test.txt
val1^I val2^I val3
val4^I val5^I val6

JIMS@ORCL> create table ext_table_load
2 (columna varchar2(50 byte),
3 columnb varchar2(50 byte),
4 columnc varchar2(50 byte)
5 )
6 organization external
7 (type oracle_loader
8 default directory ext_temp
9 access parameters
10 (records delimited by newline skip 1
11 nologfile
12 nobadfile
13 fields terminated by '\t ' optionally enclosed by '"'
14 )
15 location (ext_temp:'test.txt')
16 )
17 reject limit unlimited
18 noparallel
19 nomonitoring
20 /

Table created.

JIMS@ORCL> select * from ext_table_load;

COLUMNA
--------------------------------------------------
COLUMNB
--------------------------------------------------
COLUMNC
--------------------------------------------------
val4
val5
val6

As expected on the 2nd row shows as you have a skip 1 in your sample which I expect was to skip a header row?

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

More to Explore

Design

oracle external table issue

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

More to Explore

Design