Re: How do I change the interpreted decimal character in an external table file

From: Doug <dougburns_at_yahoo.com>
Date: 31 Aug 2005 15:00:52 -0700
Message-ID: <1125525652.125093.26180@g43g2000cwa.googlegroups.com>

Hi Jaap,

Sorry for the sluggish reply - busy day at work.

For the following data file :-

1|111.111,0
2|456.123,5

I thought you might be able to do something like this, which is valid sqlldr syntax :-

LOAD DATA
INFILE 'test.txt'
TRUNCATE
INTO TABLE doug_test
FIELDS TERMINATED BY "|"

	(pk,
	test_value CHAR "TO_NUMBER(:test_value, '999,990.90',
			'NLS_NUMERIC_CHARACTERS = '',.''')"

)

However, it's not valid external table syntax as far as I can tell. The following works fine until I add the TO_NUMBER string :-

SQL> CREATE TABLE doug_test
  2 (
  3 "PK" NUMBER,
  4 "TEST_VALUE" VARCHAR(255)
  5 )
  6 ORGANIZATION external
  7 (
  8 TYPE oracle_loader
  9 DEFAULT DIRECTORY test_dir
 10 ACCESS PARAMETERS
 11 (

 12      RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8MSWIN1252
 13      READSIZE 1048576
 14      FIELDS TERMINATED BY "|" LDRTRIM
 15      REJECT ROWS WITH ALL NULL FIELDS
 16      (
 17        "PK" CHAR(255)
 18          TERMINATED BY "|",
 19        "TEST_VALUE" CHAR(255)
 20          TERMINATED BY "|"

 21 "TO_NUMBER(:test_value, '999,990.90', 'NLS_NUMERIC_CHARACTERS = '',.''')"
 22 )
 23 )
 24 location
 25 (
 26 'test.txt'
 27 )
 28 )REJECT LIMIT UNLIMITED; Table created.

SQL> select * from doug_test;
select * from doug_test
*
ERROR at line 1:

ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "double-quoted-string": expecting one

of:

"comma, date_format, defaultif, enclosed, ltrim, lrtrim, ldrtrim,
notrim,
nullif, optionally, ), rtrim"
KUP-01007: at line 10 column 3

ORA-06512: at "SYS.ORACLE_LOADER", line 19 ORA-06512: at line 1

Still thinking about it, though!

Cheers,

--
Doug Burns - Oracle DBA

dougburns_at_yahoo.com

http://doug.burns.tripod.com
http://oracledoug.blogspot.com

Received on Wed Aug 31 2005 - 17:00:52 CDT