Home » RDBMS Server » Server Utilities » Pound symbols in external table definition
icon5.gif  Pound symbols in external table definition [message #187604] Mon, 14 August 2006 11:56 Go to next message
thecentrecannothold
Messages: 3
Registered: August 2006
Location: Santa Cruz, CA
Junior Member
Hi,

From the Oracle documentation, I understand that pound signs are allowed in column definitions (though discouraged). I have no problem creating a table with column names that contain a pound sign. I can also create an external table with column names that contain a pound sign like this:

----------------------------------------------------------
CREATE TABLE x_test (
a1 VARCHAR(128),
a2 VARCHAR(256),
b1 VARCHAR(64),
b2 INTEGER,
c1 VARCHAR(40),
c1#actid VARCHAR(2048),
c1#bidpos VARCHAR(2048),
..
..
)
organization external
(
type oracle_loader
default directory x_aurora_test_dir
access parameters
(
records delimited by newline
badfile x_test_dir:'loadext'
logfile x_test_dir:'loadext'
characterset 'al32utf8'
skip 1
fields terminated by 0X'09'
missing field values are null
)
location ('data.txt')
)
reject limit unlimited;
----------------------------------------------------------

I can access the data in it fine. However, when I want to specify the input type in the field_list like this, it doesn't work:

----------------------------------------------------------
CREATE TABLE x_test (
a1 VARCHAR(128),
a2 VARCHAR(256),
b1 VARCHAR(64),
b2 INTEGER,
c1 VARCHAR(40),
c1#actid VARCHAR(2048),
c1#bidpos VARCHAR(2048),
..
..
)
organization external
(
type oracle_loader
default directory x_test_dir
access parameters
(
records delimited by newline
badfile x_test_dir:'loadext'
logfile x_test_dir:'loadext'
characterset 'al32utf8'
skip 1
fields terminated by 0X'09'
missing field values are null (
a1 VARCHAR(128),
a2 VARCHAR(256),
b1 VARCHAR(64),
b2 INTEGER,
c1 VARCHAR(40),
c1#actid VARCHAR(2048),
c1#bidpos VARCHAR(2048),
..
..
)
)
location ('data.txt')
)
reject limit unlimited;
----------------------------------------------------------

The table gets created, but I get an error:

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 "hash": expecting one of: "binary_double,
binary_float, comma, char, date, defaultif, decimal, double, float, integer, (,
nullif, oracle_date, oracle_number, position, raw, recnum, ), unsigned,
varrawc, varchar, varraw, varcharc, zoned"
KUP-01007: at line 13 column 20
ORA-06512: at "SYS.ORACLE_LOADER", line 19
ORA-06512: at line 1

I believe the error refers to the pound sign in the column name. I know I can just rename the columns, but the way this is being processed, that would be a big hassle. Does anyone have any ideas? I am using Oracle 10.1 on RHEL4. Thank you!
Re: Pound symbols in external table definition [message #188648 is a reply to message #187604] Mon, 21 August 2006 02:23 Go to previous message
aorehek
Messages: 52
Registered: August 2006
Member
Put the column names between double quotes while creating external table definition.

..,
..,
..,
"c1#actid" VARCHAR(2048),
"c1#bidpos" VARCHAR(2048),
..
..

Previous Topic: SQL optimizer
Next Topic: CONTROL FILE
Goto Forum:
  


Current Time: Fri Dec 09 06:15:40 CST 2016

Total time taken to generate the page: 0.07275 seconds