Home » RDBMS Server » Server Utilities » Pound symbols in external table definition
 Pound symbols in external table definition [message #187604] |
Mon, 14 August 2006 11:56  |
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!
|
|
|
|
|
|
Goto Forum:
Current Time: Wed Dec 25 23:29:12 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
