Wednesday, October 13, 2010

Oracle External Table Examples

If you have some temporary data to import to the database you can use an external table instead.

Lets amuse that you have the following file.

myfile.txt
1
2
3
....

1) You must create a directory and grant read writes to your user.

create or replace directory my_dir as '/home/oracle';

grant read on directory my dir to my_user;

2) Put your file there and


create table mytable_load
(
a int
)
organization external
(
type oracle_loader default directory my_dir
access parameters (RECORDS DELIMITED BY NEWLINE FIELDS)
location ('myfile.txt')
);


... and you have your table.

This is an example of adding a window csv file created by Excel , having Headings and the Windows new line as record terminator:

create table mytable_load
(
a int,
b varchar2(100 char),
c date
)
organization external
(
type oracle_loader default directory my_dir
access parameters (RECORDS DELIMITED BY '\r\n' skip 1 FIELDS TERMINATED BY ';')
location ('myfile.csv')
);

Thursday, October 7, 2010

ORA-600 [17018] And ORA-21522

In your alert log you find out : You have

ORA-00600: internal error code, arguments: [17018], [0], [], [], [], [], [], []

In the trace file you find out that :

Error on rollback: ORA-22303: type "SYS"."AQ$_RECIPIENTS" not found
ORA-21522: attempted to use an invalid connection in OCI (object mode only)
*** 2010-10-05 07:23:34.517
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [17018], [0], [], [], [], [], [], []

The call stack has one of the following:

kglrls kxsReleaseLookupLoc kxsUnlock kksCloseCursor opicca
or
kglrls kxsReleaseParentLoc kxsFreeXsc kksCloseCursor opicca

The cause of this problem has been identified and verified in unpublished Bug 5444620, which is a duplicate of unpublished Bug 5675310.

There is no known workaround for this bug which is fixed in 11.1
According to development, it is not feasible to fix this issue in the 10.2