ORA-29829: implementation type does not exist

[systemsfsu]

I am trying to use External Tables with Oracle 10g in a unix box. I have created a corresponding directory...all the settings seem to be fine but I keep getting this error for some reason.


Here dummy1 is the external table that I have created which needs to contain all the contents from my file...
--------------------------
SQL> select * from dummy1;
select * from dummy1
*
ERROR at line 1:
ORA-29829: implementation type does not exist
------------------------

Any help would be appreciated.

Thanks
-Sai

 

[avjoshi]

This error means type specified by the using clause doesn't exist. Can you share your DDL statement with us?

Anand

 

[systemsfsu]

This is the statement for creating the external table.
......................
create table dummy1(
"SRCPHNUM" number(10),
"SRCNAME" varchar2(20),
"SRCLOCATION" varchar2(20),
"DESTNPHNUM" number(10),
"DESTNAME" varchar2(20),
"DESTLOCATION"varchar2(20),
"CALLTIME" number(3),
"COMMENTS" varchar2(20)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER DEFAULT DIRECTORY External_Tables1
ACCESS PARAMETERS(
RECORDS DELIMITED BY NEWLINE
BADFILE 'sat10_scores.bad'
LOGFILE'sat10_scores.log'
FIELDS terminated by ','
MISSING FIELD VALUES ARE NULL
)
LOCATION('Most_Critical.txt')
)
REJECT LIMIT UNLIMITED;
........................

-Sai

 

[SantaMufasa]

Sai,

Something is "fishy" with your implementation. I just ran all of your code with the following completely successful results:

create directory external_tables1 as 'c:\dhunt\sqldba\';

Directory created.

create table dummy1(
"SRCPHNUM" number(10),
"SRCNAME" varchar2(20),
"SRCLOCATION" varchar2(20),
"DESTNPHNUM" number(10),
"DESTNAME" varchar2(20),
"DESTLOCATION"varchar2(20),
"CALLTIME" number(3),
"COMMENTS" varchar2(20)
)
ORGANIZATION EXTERNAL
(
        TYPE ORACLE_LOADER DEFAULT DIRECTORY External_Tables1
        ACCESS PARAMETERS(
        RECORDS DELIMITED BY NEWLINE
        BADFILE 'sat10_scores.bad'
        LOGFILE'sat10_scores.log'
        FIELDS terminated by ','
        MISSING FIELD VALUES ARE NULL
        )
LOCATION('Most_Critical.txt')
)
REJECT LIMIT UNLIMITED;

Table created.

select * from dummy1;

SRCPHNUM SRCNAME  SRCLOCATION  DESTNPHNUM DESTNAME  DESTLOCATION    CALLTIME COMMENTS
-------- -------- ------------ ---------- --------- ------------- ---------- ---------
       1 SRCNAME1 SRCLOCATION1          1 DESTNAME1 DESTLOCATION1          1 COMMENTS1
       2 SRCNAME2 SRCLOCATION2          2 DESTNAME2 DESTLOCATION2          2 COMMENTS2
**************************************************************************************

So, my next step would be to attempt a SQL*Loader session to confirm that your Oracle 10g properly executes SQL*Loader (since "external tables" uses the SQL*Loader internals).

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[avjoshi]

Concur with SantaMufasa. The syntax is correct.

Anand

 

[systemsfsu]

Mufasa,

It seems to work out sucessfully in a Windows box for me too but screws up with the UNIX implementation.

The result in the UNIX m/c is

SQL> select * from dummy1;
select * from dummy1
*
ERROR at line 1:
ORA-29829: implementation type does not exist


Thanks
-Sai

 

[SantaMufasa]

Actually, my Unix test produced equally successful results...Back to the drawing board.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[systemsfsu]

Mufasa,

Thats interesting..I tried doing it all over again but it gives me the same error.

ORA-29829: implementation type does not exist

-Sai

 

[SantaMufasa]

Sai,

At this point, where we have confirmed the proof-of-concept with your exact code, on the same version of Oracle, on the same operating system, I believe that your next viable method of isolating the problem is to log a service request (SR) via Oracle's Metalink (technical support) [metalink.oracle.com] or call them (in the U.S. at) 800-223-1711.

Oracle Technical Support currently provides very quick responses to technical questions provided you have a Customer Service Identifier (CSI).

If you pursue this route, please post your findings/resolution here.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[systemsfsu]

Sure..will do so and will get back to you..Thanks for the information.

-Sai

 

[systemsfsu]

Mufasa,

Looks like they support only people with licenses and I am using a downloaded version.So there was nothing I could get out of it!

-Sai

 

[karluk]

The type you are using in your external table is "ORACLE_LOADER" and the error message appears to be saying that this type doesn't exist. But "ORACLE_LOADER" is a type which should exist in the SYS schema with execute granted to public by virtue of running the appropriate catalog scripts at database creation.

That suggests that the catalog script to create "ORACLE_LOADER" was never executed. To test this, please run the following query and report back the results.

select owner, object_name, object_type from all_objects where object_name='ORACLE_LOADER';

 

[systemsfsu]

Karluk,

This is the result..looks like the loader is missing..wat do I do now?

-------------
SQL> select owner, object_name, object_type from all_objects where object_name='ORACLE_LOADER';
select owner, object_name, object_type from all_objects where object_name='ORACLE_LOADER'
*
ERROR at line 1:
ORA-00942: table or view does not exist

-Sai

 

[karluk]

Your problems are a lot more serious than just a missing type. The error from your query indicates that the catalog view, "all_objects", doesn't exist. Since this is one of the more fundamental catalog views, I infer that your entire database is suspect, and needs to be completely rebuilt.

 

[systemsfsu]

Thanks Karluk,

Guess thats my only option..lemme see what can be done..Thanks for your help!

-Sai

 

[systemsfsu]

Karluk,

I tried and started from the scratch but ended up with the same error.

Any other thoughts?

-Sai

 

[karluk]

If you just repeated the same database creation steps you did before, it's not surprising that you just recreated the same problem. Basically you need to resolve the issue of why your Oracle id can "see" neither the ORACLE_LOADER type nor the ALL_OBJECTS view. You are most likely not running the appropriate catalog scripts or maybe they are generating some errors which you didn't notice.

 

[SantaMufasa]

Sai,

Specifically, once you successfully "CREATE DATABASE...", you must also run the commands to create the data dictionary. From the SQL*Plus prompt, issue these commands:

connect / as sysdba
@$ORACLE_HOME/rdbms/admin/catalog
@$ORACLE_HOME/rdbms/admin/catproc
@$ORACLE_HOME/rdbms/admin/catblock.sql
@$ORACLE_HOME/rdbms/admin/catexp.sql
@$ORACLE_HOME/rdbms/admin/utlxplan.sql
@$ORACLE_HOME/rdbms/admin/utltkprf.sql
@$ORACLE_HOME/sqlplus/admin/plustrce
connect system/<password>
@$ORACLE_HOME/sqlplus/admin/pupbld

Let us know your results following your creating the data dictionary.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[systemsfsu]

Karluk,

No I did not follow the same steps..infact this time I customized the installation and carefully reviewed each step.

-Sai

 

[systemsfsu]

Mufasa,

I will try to follow your steps and will get back to you.

-Sai