Hello all,
I have a question about External tables. I have a CSV file like this:
$ more example.csv
1,foo,10
2,bar,20
When I create an external table like this one:
DROP TABLE example;
CREATE TABLE example
(
id number,
name varchar2(100),
cnt number
)
ORGANIZATION EXTERNAL (
DEFAULT DIRECTORY EXT_DIR
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ','
(
id,
name,
cnt
)
)
LOCATION('example.csv')
)
REJECT LIMIT UNLIMITED;
/
I can select from it. No worries it works.
Now, if I edit my CSV file and add a line like this one:
$ more example.csv
1,foo,10
2,bar,20
3,new,\N
Since the \N is not a decimal it won't work. But, in my file, it's a way to mark "empty" values. So, I wanted to add NULLIF condition in my table. like this
DROP TABLE example;
CREATE TABLE example
(
id number,
name varchar2(100),
cnt number
)
ORGANIZATION EXTERNAL (
DEFAULT DIRECTORY EXT_DIR
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ','
(
id,
name,
cnt NULLIF cnt = '\N'
)
)
LOCATION('example.csv')
)
REJECT LIMIT UNLIMITED;
When I try to add the NULLIF condition, I get the following error:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01006: error signalled during parse of access parameters
KUP-00562: unknown escape sequence
29913. 00000 - "error in executing %s callout"
I tried to escape and did NULLIF cnt = '\\N' and I get the same error.
Is it possible to use NULLIF and escape a character like this?
Regards,