The fact that you could raise an ugly error when you query an external table always bothered me. I looked at Java stored procedures as the best solution initially. That was overkill. This afternoon, while writing about them for the new PL/SQL Workboook, it became clear. The fix is really easy.
If you know little to nothing about external tables, you can go read this earlier post. Likewise, if you don’t know about objects and object collection, you can refer to this post. Having provided you with the context, here’s an example that eliminates errors when querying an external table without an external file.
- Create an external file, like this
character table.
CREATE TABLE CHARACTER
( character_id NUMBER
, first_name VARCHAR2(20)
, last_name VARCHAR2(20))
ORGANIZATION EXTERNAL
( TYPE oracle_loader
DEFAULT DIRECTORY download
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
BADFILE 'DOWNLOAD':'character.bad'
DISCARDFILE 'DOWNLOAD':'character.dis'
LOGFILE 'DOWNLOAD':'character.log'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY "'"
MISSING FIELD VALUES ARE NULL )
LOCATION ('character.csv'))
REJECT LIMIT UNLIMITED; |
CREATE TABLE character
( character_id NUMBER
, first_name VARCHAR2(20)
, last_name VARCHAR2(20))
ORGANIZATION EXTERNAL
( TYPE oracle_loader
DEFAULT DIRECTORY download
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
BADFILE 'DOWNLOAD':'character.bad'
DISCARDFILE 'DOWNLOAD':'character.dis'
LOGFILE 'DOWNLOAD':'character.log'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY "'"
MISSING FIELD VALUES ARE NULL )
LOCATION ('character.csv'))
REJECT LIMIT UNLIMITED;
- Create a user-defined object type that mirrors your external table defintion, like this:
CREATE OR REPLACE TYPE character_obj IS OBJECT
( character_id NUMBER
, first_name VARCHAR2(20)
, last_name VARCHAR2(20));
/ |
CREATE OR REPLACE TYPE character_obj IS OBJECT
( character_id NUMBER
, first_name VARCHAR2(20)
, last_name VARCHAR2(20));
/
- Create a user-defined collection of your object type, like
CREATE OR REPLACE TYPE character_obj_table IS TABLE OF character_obj;
/ |
CREATE OR REPLACE TYPE character_obj_table IS TABLE OF character_obj;
/
- Create a function that returns the user-defined collection of your object type, like
CREATE OR REPLACE FUNCTION character_source
RETURN character_obj_table IS
c NUMBER;
collection CHARACTER_OBJ_TABLE := character_obj_table();
BEGIN
FOR i IN (SELECT * FROM CHARACTER) LOOP
collection.EXTEND;
collection(c) := character_obj( i.character_id
, i.first_name
, i.last_name);
c := c + 1;
END LOOP;
RETURN collection;
EXCEPTION
WHEN OTHERS THEN
RETURN collection;
END;
/ |
CREATE OR REPLACE FUNCTION character_source
RETURN character_obj_table IS
c NUMBER;
collection CHARACTER_OBJ_TABLE := character_obj_table();
BEGIN
FOR i IN (SELECT * FROM character) LOOP
collection.EXTEND;
collection(c) := character_obj( i.character_id
, i.first_name
, i.last_name);
c := c + 1;
END LOOP;
RETURN collection;
EXCEPTION
WHEN OTHERS THEN
RETURN collection;
END;
/
- Query the function not the table, which returns no rows found when the file doesn’t physically exist, or the file contains no data. Lastly, the function returns the data when it is there.
SELECT * FROM TABLE(character_source); |
SELECT * FROM TABLE(character_source);
Hope this helps those using external tables to avoid the typical error stack:
SELECT * FROM CHARACTER
*
ERROR at line 1:
ORA-29913: error IN executing ODCIEXTTABLEOPEN callout
ORA-29400: DATA cartridge error
KUP-04040: file CHARACTER.csv IN CHARACTER NOT found |
select * from character
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file character.csv in CHARACTER not found
I also wrote this older post about confirming it in the database catalog. If you any follow-up suggestions, please let me know.
Subscribe via RSS