OraFAQ Forum: Server Utilities » Problem related to bulk loader file

Home » RDBMS Server » Server Utilities » Problem related to bulk loader file

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

Fri, 09 December 2005 10:44

endow
Messages: 2
Registered: December 2005

Junior Member

I am trying to load a Bulk loader file which is using a column specification like this:

LOAD DATA
INFILE *
INTO TABLE load_delimited_data
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
( data1 integer external,
data2 char,
data3 expression "N"
)
BEGINDATA
11111,AAAAAAAAAA
22222,"A,B,C,D,"

But the sql*loader is throwing error in the 3rd column.
Need some help on that, specially about the term expression.

Database - 10.2.0.
Platform - Linux RHEL Release 4

Report message to a moderator

Fri, 09 December 2005 12:22

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

I don't know what you want, but if you want N in every row then use the keyword CONSTANT.

Report message to a moderator

Sat, 10 December 2005 03:13

endow
Messages: 2
Registered: December 2005

Junior Member

Thanks Joy for the prompt response..

What I do not understand is the role of the keyword expression here.

let me tell you that I have a script originaly written for 10g release 1 in HP 264 platform, which will laod data with SQL* Loader.

there is a column L_VALUE CHAR(2) where it will insert a constant data say 'N' for all the rows.

the script goes on like this:-

LOAD DATA
INFILE *
APPEND
INTO TABLE ot_st_tb
FIELDS
TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
name char,
n_value integer external,
l_value expression "N"
)

BEGINDATA
"VLOG_CLUSTER",1001
"XP_DEAMON",1045
"FLX_NO_FILE",2001

This is working. But the same file while I am trying to run in 10.2.0.1 in Linux RHEL 4 it shows error in log file:-

Record 1: rejected - error on table OT_ST_TB, column L_VALUE
ORA-00984: column not allowed here

Record 2: rejected - error on table OT_ST_TB, column L_VALUE
ORA-00984: column not allowed here

Record 3: rejected - error on table OT_ST_TB, column L_VALUE
ORA-00984: column not allowed here.

-------------------------------------------------------------

The same script when I tried using l_value expression "'N'", it throws error :-
SQL*Loader-297: Invalid syntax or bind varriable in SQL string for column L_VALUE
ORA-01756: quoted string not properly terminated.

--------------------------------------------------------------

It is running properly when I use l_value expression "to_char('N')".

Plese see if you can put any light to it.

Thanks

Anirban

Report message to a moderator

Previous Topic:	SQL*Loader
Next Topic:	find out versions of import/export utilities

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Jan 13 10:34:00 CST 2025