Start Free Trial

asked on

How do I fix errors from creating an external table

I created an external table

create table aux1
(col2 char (5),
col2 char (20))
organization external
(type oracle_loader
default directory aux_imp
access parameters
(fields terminated by "|" optionally enclosed by '|' records delimited by newline
(col2 char (5),
col2 char (20))
)
location (aux1.txt)
);

It successfully creates the table. However, when I try to do a select count I get the following errors:

ORA-29113: error executing OPCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error: found " fields ": expecting one of : " and, column, (, ltrim, lrtrim, ldrtrim, missing notrim, rtrim, reject ....
KUP-01007: at line 2 column 1

I have no experience with creating external tables and I have had a difficult time finding the right documentation. Could someone please help me resolve this issue? I am trying to load 1800 records from a flat file into an Oracle database

Alex [***Alex140181***]

this seems to be a syntax error.
this sample is from http://www.dba-oracle.com/t_sql_loader_sqlldr_external_tables.htm:

CREATE TABLE SYS_SQLLDR_X_EXT_DEPT 
(
  DEPTNO NUMBER(2),
  DNAME CHAR(14),
  LOC CHAR(13)
)
ORGANIZATION external 
(
  TYPE oracle_loader
  DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
  ACCESS PARAMETERS 
  (
    RECORDS DELIMITED BY NEWLINE
    BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000:ulcase1.bad'
    SKIP 20
    FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM 
    (
      DEPTNO CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      DNAME CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      LOC CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
    )
  )
  location 
  (
    'ulcase1.ctl'
  )
)REJECT LIMIT UNLIMITED

Open in new window

slightwv (䄆 Netminder)

Try single quotes around the file name?

location ('aux1.txt')

I don't think it's a data issue but just in case, can you post some data? Then I can recreate what you have and provide a working solution.

ASKER

ok I will try both suggestions

ASKER

I change my create statement to the following format:

ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY aux.imp
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
BADFILE 'aux.bad'
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
(
col1 CHAR(5)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
col2 CHAR(20)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',

)
)
location
(
'aux1.txt'
)
)REJECT LIMIT UNLIMITED

Now I get the following errors:

sp2-0734: unknown command beginning "Organizati..." - rest of line ignored.
sp2-0042: unknown command "location" - rest of line ignored.
'aux1.txt
error at line 2:
ORA-00928: missing select keyword

SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

you're right it was the blank spaces

ASKER

now when i try to access the aux1 i get the message table or object does not exist when i query user objects I see the table object aux1

ASKER

i get the same error message that it doesn't exist when i type describe aux1

slightwv (䄆 Netminder)

I have to ask: Did you create the table in a different schema than the one you are connected to?

ASKER

no I created the table as the schema I am currently logged into
I don't know if this has any meaning but I created the user to be identified at the OS level
ie create user ops$dev identified externally;

this user however doesn't exist at the OS level. but it did give the user a password
alter user ops$dev identified by'<pw>';
would that cause a problem?

i was able to create other tables in that schema and I can query those tables

ASKER

at the OS level I am logged in as oracle

slightwv (䄆 Netminder)

How are you connecting to the database?

slightwv (䄆 Netminder)

For grins log in as SYSTEM or SYS and post the results of:

select owner, object_type from dba_objects where object_name='AUX1';

ASKER

connect / as sysdba
sql> connect ops$dev
password:
connected

slightwv (䄆 Netminder)

How about the results from the query above?

>>connect / as sysdba

Why do you connect as SYS then as ops$dev?

My gut feeling is you created the table as SYS not ops@dev.

ASKER

select owner, object_type from dba_objects where object_name='AUX1';

no rows selected

ASKER

sql> connect ops$dev
password:
connected
sql>show user
ops$dev
sql>select * from cat;

TABLE_NAME TABLE_TYPE
users TABLE
audit_data TABLE
sq_aud_data SEQUENCE
aux1 TABLE

ASKER

when I type the following as ops$dev

select table_name, type_owner from user_external_tables;

TABLE_NAME TYP
----------------- ----
aux1 SYS

when I log in as SYS and type

SQL>select table_name, type_owner from user_external_tables;
no rows selected

SQL>desc aux1
object or view does not exist

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

yes that is exactly what I did

ASKER

i removed the quotes and now i can query the table thanks

ASKER

I saw on asktom example of creating an external table he had his columns and table name enclosed in quotes and his output showed he created the table and was able to query so I did the same thing

create table 'aux1' etc

thanks everyone

ASKER

Thank you so much excellent

slightwv (䄆 Netminder)

Using single quotes around object names isn't valid syntax.

Using double quotes around objects forces case sensitivity and is a bad thing to do in Oracle.

Not sure exactly what you did but as long as you are happy with it, who are we to judge.