MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

External Table Query Fix

with 6 comments

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.

  1. 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;
  1. 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));
/
  1. Create a user-defined collection of your object type, like
CREATE OR REPLACE TYPE character_obj_table IS TABLE OF character_obj;
/
  1. 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;
/
  1. 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);

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

I also wrote this older post about confirming it in the database catalog. If you any follow-up suggestions, please let me know.

Written by maclochlainn

July 11th, 2009 at 7:42 pm

Posted in Objects,Oracle,pl/sql,sql

6 Responses to 'External Table Query Fix'

Subscribe to comments with RSS or TrackBack to 'External Table Query Fix'.

  1. Just wondering – what happens if the name of the location file varies. Is there any way of creating an external table if you don’t know the name of the file?
    for example: LOCATION (‘*.csv’)
    Best Regards
    Joel

    js

    21 Jul 09 at 6:42 am

  2. No, you can’t create a generic (*.csv) file. However, here’s what some folks do:

    1. Create an external table that has two columns, the first column holds the import name and the second the number of rows.

    2. Create a 20 – 50 column table of strings for any type of import.

    3. Write a function that reads and converts the string data to a reference cursor or associative array structure.

    4. Call the function in their import and then purge the external data.

    If need more help, use the contact page.

    maclochlainn

    21 Jul 09 at 9:30 am

  3. I’m trying to create an external table for a short-term need. I’ve been able to do this in development databases with little problem. However, now that I’m up to the Production server, I’m hitting a snag. My Production databases is RAC. I moved the .csv file to node 1. Is that the problem? I can get the table created, but when I go to view the results, I get the following errors in TOAD:

    15:03:55 Error: ORA-29913: error IN executing ODCIEXTTABLEOPEN callout
    ORA-29400: DATA cartridge error
    KUP-04063: unable TO OPEN log file code_ext1.log
     OS error No such file OR directory
    ORA-06512: at "SYS.ORACLE_LOADER", line 19
    ORA-06512: at line 1

    The OS is Linux Redhat AS 4. Perms on the directory at the OS level are now 777 and owned by the database admin account. The directory creation was done in Grid and gives RW perms to the main schema owner. I’ve tried creating the external table as the main schema and as sys with the same results. I’m wondering if this isn’t a case where I’m a bit hampered because it’s a RAC system. Maybe the other nodes wouldn’t be able to see the table since it’s on node 1’s local storage?

    Any suggestions?

    manjunath
    Database Administrator
    VA Health Administration Center
    banglore

    manjunath

    7 Sep 10 at 5:06 am

  4. I’ve never tested it on a RAC system but it seems likely that could impede it. I’ll try to check whether there’s a published limit on the use of external tables.

    I didn’t find anything but external tables are shared resources across all nodes. That means you’ve two options. You can create DB_LINK that resolves to the remote node, or you can use a clustered file system. If you use the DB_LINK solution, you’ll also need a synonym on that node.

    I hope this helps. Unfortunately, I start a new term this week and don’t have the time to test it for you. I’ll put it on my list and try to get to it.

    maclochlainn

    7 Sep 10 at 8:59 am

  5. i have simple & stupid solution on this,
    i just create func, to check whether ext table have data or not.

    then before i select data from ext table,
    i exec this func first, so that i wont hit any error if no data in ext table/flat file not exist

    CREATE OR REPLACE
      FUNCTION usf_check
      RETURN NVARCHAR2 AS
      retVal NVARCHAR2(50) := 'FALSE';
      v_count NUMBER :=0;
    BEGIN
     
      SELECT COUNT(*) INTO v_count FROM YOUR_TABLE;
     
    EXCEPTION WHEN OTHERS THEN
      retVal :=FALSE’;
     
      IF(v_count > 0) THEN
        retVal :=TRUE’;
      END IF;
     
      RETURN retVal;
    END;
    /

    Edward

    19 Feb 14 at 2:51 am

  6. Edward, there are many solutions. I’ve created an entire framework.

    maclochlainn

    19 Feb 14 at 10:01 pm

Leave a Reply