Home » SQL & PL/SQL » SQL & PL/SQL » Ext Table
Ext Table [message #439319] Fri, 15 January 2010 11:00 Go to next message
bella13
Messages: 90
Registered: July 2005
Member
HI- I am able to cerate an external table- but when I read out of it , its giving me an error.

I tried various options in access parameters. But got the same error. I am reading out of a csv file which has only 2 columns.


the direcorty does have grants and the ext table gets created. its only when i read- there is this error and the data is not loaded because of that.

create table ext_tabl(
S_ID Number,
S_desc Number
)
organization external
(type oracle_loader
default directory FR_data_DIR
ACCESS PARAMETERS (S_ID Number,
S_desc Number)
location ('S6.csv')
)
reject limit unlimited

S6.csv

S_ID S_Desc
30 1
73 2
65 3

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 "identifier": expecting one of: "column, debug, exit, fields, records"
KUP-01008: the bad identifier was: S_ID
KUP-01007: at line 1 column 1
ORA-06512: at "SYS.ORACLE_LOADER", line 19
Re: Ext Table [message #439322 is a reply to message #439319] Fri, 15 January 2010 11:13 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL*Plus and copy and paste your session from table creation to error.

Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: Ext Table [message #439325 is a reply to message #439319] Fri, 15 January 2010 11:20 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If what you posted is what you executed, your ACCESS PARAMETERS clause does not contain field delimiters and so on and fields list are not enclosed between parenthesis.

Regards
Michel
Re: Ext Table [message #439327 is a reply to message #439322] Fri, 15 January 2010 11:26 Go to previous messageGo to next message
bella13
Messages: 90
Registered: July 2005
Member
Oracle 10.2.0.4

SQL> create table ext17(
  2    S_ID    Number,
  3    S_Desc    Number
  4   )
  5   organization external
  6   (type oracle_loader
  7   default directory G_DATA_DIR
  8   access parameters
  9   ( RECORDS DELIMITED BY NEWLINE
 10              LOAD WHEN (S_ID != BLANKS)
 11              READSIZE 10000
 12              badfile     G_DATA_DIR
:'/tmp/wid/load/first_rate/data/S16.csv'
 13              DISCARDFILE G_DATA_DIR
:'/tmp/wid/load/first_rate/data/S16.dis'
 14              LOGFILE     G_DATA_DIR
:'/tmp/wid/load/first_rate/data/S16.LOG'
 15              FIELDS TERMINATED BY '',''
 16              LRTRIM
 17              missing field values are null
 18   )
 19  location ('S16.csv')
 20   )
 21   reject limit unlimited;

Table created.

SQL> select * from ext17 ;
select * from ext17
*
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 "comma": expecting one of: "column, enclosed,
exit, (, ltrim, lrtrim, ldrtrim, missing, notrim, optionally, rtrim, reject"
KUP-01007: at line 7 column 36
ORA-06512: at "SYS.ORACLE_LOADER", line 19


[Edit MC: Add code tags]
  • Attachment: S16.csv
    (Size: 0.07KB, Downloaded 162 times)

[Updated on: Fri, 15 January 2010 12:32] by Moderator

Report message to a moderator

Re: Ext Table [message #439330 is a reply to message #439327] Fri, 15 January 2010 12:14 Go to previous messageGo to next message
Littlefoot
Messages: 20827
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
In order to use a directory (an Oracle object, line 7 in your last message), you first have to create it. I suppose that you did that, didn't you? But, do you know that it points to a directory on the database server (not on your PC)? It also means that the file you are using as an external table has to be in that particular directory on the database server. Is that the problem, perhaps?

Here's an example; check whether you can use it.
SQL> connect sys/pwd as sysdba;
Connected.

SQL> create or replace directory ext_dir as 'c:\temp';

Directory created.

SQL> grant read, write on directory ext_dir to scott;

Grant succeeded.

SQL> connect scott/tiger
Connected.

SQL>  create table ext17
  2   (s_id number,
  3    s_desc number
  4   )
  5   organization external
  6     ( type oracle_loader
  7       default directory ext_dir
  8       access parameters
  9         ( records delimited by newline
 10           fields terminated by ","
 11           missing field values are null
 12         )
 13       location ('s16.txt')
 14     )
 15   reject limit unlimited;

Table created.

SQL> select * from ext17;

      S_ID     S_DESC
---------- ----------
        30          1
        73          2
        65          3
        34          4
       134          5
        92          6
       113          7
        81          8
        99          9

9 rows selected.

SQL>

[Updated on: Fri, 15 January 2010 12:28]

Report message to a moderator

Re: Ext Table [message #439333 is a reply to message #439327] Fri, 15 January 2010 12:35 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With code tags it is easier to find the error:
FIELDS TERMINATED BY '',''

Not twice single quotes, but once double quotes or once single quotes.

Next time use code tags, read the link I provided.

Regards
Michel

[Updated on: Fri, 15 January 2010 12:35]

Report message to a moderator

Re: Ext Table [message #439334 is a reply to message #439330] Fri, 15 January 2010 12:39 Go to previous messageGo to next message
bella13
Messages: 90
Registered: July 2005
Member
Littlefoot:
thank you. It help me understand and I could do this successfully,
Re: Ext Table [message #439337 is a reply to message #439334] Fri, 15 January 2010 12:59 Go to previous message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Hopefully Littlefoot correctly format her post otherwise you didn't see the difference with your code and will still wondering why it does not work.

Regards
Michel
Previous Topic: Ledger summery
Next Topic: truncate and insert
Goto Forum:
  


Current Time: Tue Sep 27 14:31:52 CDT 2016

Total time taken to generate the page: 0.21282 seconds