| Delimited by '\' in oracle external table [message #614607] |
Fri, 23 May 2014 12:40  |
 |
arvindvegad
Messages: 5
Registered: May 2014
Location: USA
|
Junior Member |
|
|
Hi - I have data file delimited by '\'
test.txt file has
a1\b1\c1\d1\e1
a2\b2\c2\d2\e2
create table test
(col1 varchar2(10),
col2 varchar2(10),
col3 varchar2(10))
ORGANIZATION external
(TYPE oracle_loader
DEFAULT DIRECTORY DIR_NAME
ACCESS PARAMETERS
(FIELDS TERMINATED BY "\"
(col1,
col2,
col3)
location ('test.txt'))
Trying to run select * from test - does not work - Can you help me how to define text file with field terminated by '\' in external table syntax.
|
|
|
|
| Re: Delimited by '\' in oracle external table [message #614609 is a reply to message #614607] |
Fri, 23 May 2014 12:54  |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
"does not work" is not a valid Oracle error message.
SQL> l
1 create table test
2 (col1 varchar2(10),
3 col2 varchar2(10),
4 col3 varchar2(10))
5 ORGANIZATION external
6 (TYPE oracle_loader
7 DEFAULT DIRECTORY DIR_NAME
8 ACCESS PARAMETERS
9 (FIELDS TERMINATED BY "\"
10 (col1,
11 col2,
12 col3)
13* location ('test.txt'))
SQL> /
location ('test.txt'))
*
ERROR at line 13:
ORA-30648: missing LOCATION keyword
|
|
|
|
| Re: Delimited by '\' in oracle external table [message #614612 is a reply to message #614609] |
Fri, 23 May 2014 13:20 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
I am assuming you are saying the error is when you select and NOT on the creation of the table, although I ran your create table script and it is invalid syntax.
Backslash is hex character 5C. did you try that in the definition?
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Delimited by '\' in oracle external table [message #614618 is a reply to message #614614] |
Fri, 23 May 2014 14:54 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
arvindvegad wrote on Fri, 23 May 2014 15:25Yes. the error is when i run select * from test; - how can we use \ as delimited in file. Can u suggest in the table syntax ?
Backslash is escape character. Use its ASCII code X'5C' instead, as others suggested. Also, your table has 3 columns and file has 5 fields:
SQL> create table test(
2 col1 varchar2(10),
3 col2 varchar2(10),
4 col3 varchar2(10)
5 )
6 ORGANIZATION external
7 (
8 TYPE oracle_loader
9 DEFAULT DIRECTORY TEMP
10 ACCESS PARAMETERS
11 (
12 FIELDS TERMINATED BY X'5c'
13 (
14 col1,
15 col2,
16 col3,
17 col4,
18 col5
19 )
20 )
21 location ('test.txt')
22 )
23 /
Table created.
SQL> select *
2 from test
3 /
COL1 COL2 COL3
---------- ---------- ----------
a1 b1 c1
a2 b2 c2
SQL>
SY.
[Updated on: Fri, 23 May 2014 14:55] Report message to a moderator |
|
|
|
|
|
| Re: Delimited by '\' in oracle external table [message #614621 is a reply to message #614620] |
Fri, 23 May 2014 15:57 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
arvindvegad wrote on Fri, 23 May 2014 16:33one more twist - The data file may have different no.
So how many columns do you want? Assuming you want all and max number of fields is 7, define 7 columns:
SQL> create table test(
2 col1 varchar2(10),
3 col2 varchar2(10),
4 col3 varchar2(10),
5 col4 varchar2(10),
6 col5 varchar2(10),
7 col6 varchar2(10),
8 col7 varchar2(10)
9 )
10 ORGANIZATION external
11 (
12 TYPE oracle_loader
13 DEFAULT DIRECTORY TEMP
14 ACCESS PARAMETERS
15 (
16 FIELDS TERMINATED BY X'5c'
17 )
18 location ('test.txt')
19 )
20 /
Table created.
SQL> select *
2 from test
3 /
COL1 COL2 COL3 COL4 COL5 COL6 COL7
---------- ---------- ---------- ---------- ---------- ---------- ----------
a1 b1 c1 d1 e1
a2 b2 c2 d2 e2
a3 b3 c3
a4 b4 c4 d4 e4 f4 g4
SQL>
SY.
[Updated on: Fri, 23 May 2014 15:59] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Delimited by '\' in oracle external table [message #614646 is a reply to message #614642] |
Sun, 25 May 2014 02:54 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
arvindvegad wrote on Sun, 25 May 2014 11:18
I tried with above systax ( 7 column ) with test file as
a1\b1\c1\d1\e1\f1\g1
a1\b1\c1\d1\e1
row one have seven column while row 2 has 5 - It does not work. how you achieve this ?
If you see SY's demo in above post, he took maximum number of fields which totals to 7 columns. And for the 4 rows inserted, it has values for 5,5,3 and 7 columns respectively. So what different are you trying to achieve? Please post it and show us.
|
|
|
|
| Re: Delimited by '\' in oracle external table [message #614650 is a reply to message #614646] |
Sun, 25 May 2014 06:53 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Lalit Kumar B wrote on Sun, 25 May 2014 03:54And for the 4 rows inserted, it has values for 5,5,3 and 7 columns respectively.
Absolutely not. Table can't have different number of columns in different rows. All rows in my example have 7 columns. External tables have default of MISSING FIELD VALUES ARE NULL. So in this case external table automatically assigns NULLs to missing fields when loading records with less than 7 fields. As a result columns corresponding to missing fields get NULL values.
SY.
|
|
|
|
| Re: Delimited by '\' in oracle external table [message #614653 is a reply to message #614650] |
Sun, 25 May 2014 07:28 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
I am absolutely correct by saying there are 5,5,3 and 7 values in each row respectively. I said 5,5,3,7 are values for columns, and NULL is not a value, its nothing. So values out of 7 columns in each row are 5,5,3 and 7 respectively.
The other fields which doesn't have values are cinsidered NULL.
|
|
|
|
|
|
|
|
|
|
| Re: Delimited by '\' in oracle external table [message #614670 is a reply to message #614663] |
Sun, 25 May 2014 12:02 |
 |
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
I do not speak about English, I can't give any lesson in English to anyone given my poor level in this mattter, I speak about relational theory. NULL means we currently do not know the value or this attribute/field/column is meaningless for this item/element/row which is far from nothing.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
But can't be a value itself.