?
Solved

How do I fix errors from creating an external table

Posted on 2013-10-24
24
Medium Priority
?
733 Views
Last Modified: 2013-10-25
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
0
Comment
Question by:sikyala
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 15
  • 8
24 Comments
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39598305
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

0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39598312
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.
0
 

Author Comment

by:sikyala
ID: 39598326
ok I will try both suggestions
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

Author Comment

by:sikyala
ID: 39600290
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
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 2000 total points
ID: 39600297
Check for blank lines if using sqlplus.  By default, sqlplus doesn't like them.

If not, please post the entire create statement you have.
0
 

Author Comment

by:sikyala
ID: 39600424
you're right it was the blank spaces
0
 

Author Comment

by:sikyala
ID: 39600429
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
0
 

Author Comment

by:sikyala
ID: 39600433
i get the same error message that it doesn't exist when i type describe aux1
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39600447
I have to ask:  Did you create the table in a different schema than the one you are connected to?
0
 

Author Comment

by:sikyala
ID: 39600497
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
0
 

Author Comment

by:sikyala
ID: 39600500
at the OS level I am logged in as oracle
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39600506
How are you connecting to the database?
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39600511
For grins log in as SYSTEM or SYS and post the results of:

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

Author Comment

by:sikyala
ID: 39600646
connect / as sysdba
sql> connect ops$dev
password:
connected
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39600660
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.
0
 

Author Comment

by:sikyala
ID: 39600673
select owner, object_type from dba_objects where object_name='AUX1';
 
no rows selected
0
 

Author Comment

by:sikyala
ID: 39600717
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
0
 

Author Comment

by:sikyala
ID: 39600780
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
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 39600840
re: http:#a39600673

Did you create the external table with the name 'AUX1'?  If not, replace AUX1 with the table name.

If you still get no rows returned, then you didn't actually create the table.
0
 

Author Comment

by:sikyala
ID: 39601111
yes that is exactly what I did
0
 

Author Comment

by:sikyala
ID: 39601112
i removed the quotes and now i can query the table thanks
0
 

Author Comment

by:sikyala
ID: 39601120
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
0
 

Author Closing Comment

by:sikyala
ID: 39601127
Thank you so much excellent
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39601128
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.
0

Featured Post

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
The purpose of this article is to demonstrate how we can use conditional statements using Python.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses
Course of the Month10 days, 3 hours left to enroll

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question