1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

External Table based on Network Drive

Discussion in 'SQL PL/SQL' started by adeel53, Oct 7, 2013.

  1. adeel53

    adeel53 Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Hi,

    I want to create external table based on network drive.
    Is this possible if yes please guide me how to do this.

    Regards
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Certainly it's possible but you need to make certain you have a fairly fast connection or performance will be slow. You simply need to 'map' the network location to an Oracle directory:

    Code (SQL):

     
    CREATE directory my_net_dir AS '[networked location here]';
     
     
    On Windows you can use the direct network path; on Linux and UNIX you will need to NFS mount the networked location to a local mount point.
     
  3. adeel53

    adeel53 Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Zargon,

    Thanks for your reply but i have tried this.
    Directory created but when i try to load this or try to use in external table it gives me an error.

    ORA-29913: error in executing ODCIEXTTABLEOPEN callout
    ORA-29400: data cartridge error
    KUP-04040: file IPDirectory.txt in FLAT_FILE_MOD_LOC not found

    Unfortunately after searching on Google i didn't get any solution, do you have any idea on that.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Not until you provide much more information on this issue. What operating system are you using? How are you referencing the network location? You need to provide ALL of the steps you are using before anyone can give you a usable solution.

    I have no problems using network drives as a source for creating external tables:

    Code (SQL):
    SQL> -- create source table
    SQL>
    SQL> CREATE TABLE admin_employees
      2                     (test_id           NUMBER(4),
      3                      test_name         VARCHAR2(25)
      4                     );
     
    TABLE created.
     
    SQL>
    SQL> INSERT INTO admin_employees
      2  VALUES (20, 'Test 3');
     
    1 ROW created.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> -- create the external table
    SQL> CREATE TABLE admin_ext_employees
      2                     (test_id           NUMBER(4),
      3                      test_name         VARCHAR2(25)
      4                     )
      5       ORGANIZATION EXTERNAL
      6       (
      7         TYPE ORACLE_LOADER
      8         DEFAULT DIRECTORY admin_dat_dir
      9         ACCESS PARAMETERS
     10         (
     11           records delimited BY newline
     12           badfile admin_bad_dir:'loader_tst%a_%p.bad'
     13           logfile admin_log_dir:'loader_tst%a_%p.log'
     14           FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
     15           missing FIELD VALUES are NULL
     16           ( test_id, test_name
     17           )
     18         )
     19         LOCATION ('loader_tst1.dat', 'loader_tst2.dat')
     20  --  LOCATION ('loader_tst1.dat')
     21       )
     22       PARALLEL
     23       REJECT LIMIT UNLIMITED;
     
    TABLE created.
     
    SQL>
    SQL> SELECT * FROM admin_ext_employees;
     
       TEST_ID TEST_NAME
    ---------- -------------------------
             1 Test 1
             2 Test 1
             3 Test 1
             4 Test 1
             5 Test 1
             6 Test 1
             7 Test 1
             8 Test 1
             9 Test 1
            10 Test 1
            11 Test 1
     
       TEST_ID TEST_NAME
    ---------- -------------------------
            12 Test 1
            13 Test 1
            14 Test 1
            15 Test 1
            16 Test 1
            17 Test 1
            18 Test 1
            19 Test 1
            21 Test 2
            22 Test 2
            23 Test 2
     
       TEST_ID TEST_NAME
    ---------- -------------------------
            24 Test 2
            25 Test 2
            26 Test 2
            27 Test 2
            28 Test 2
            29 Test 2
            30 Test 2
            31 Test 2
            32 Test 2
            33 Test 2
            34 Test 2
     
       TEST_ID TEST_NAME
    ---------- -------------------------
            35 Test 2
            36 Test 2
            37 Test 2
            38 Test 2
            39 Test 2
     
    38 ROWS selected.
     
    SQL>
    SQL> SELECT * FROM admin_employees;
     
       TEST_ID TEST_NAME
    ---------- -------------------------
            20 Test 3
     
    SQL>
    SQL> SET autotrace ON linesize 150
    SQL>
    SQL> MERGE INTO admin_employees a
      2          USING admin_ext_employees e ON (e.test_id = a.test_id)
      3  WHEN matched THEN
      4          UPDATE
      5          SET test_name = a.test_name
      6  WHEN NOT matched THEN
      7          INSERT
      8          (test_id, test_name)
      9          VALUES (e.test_id, e.test_name);
     
    38 ROWS merged.

    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 1420256803
    -----------------------------------------------------------------------------------------------------
    | Id  | Operation                     | Name                | ROWS  | Bytes | Cost (%CPU)| TIME     |
    -----------------------------------------------------------------------------------------------------
    |   0 | MERGE STATEMENT               |                     |  8168 |   542K|    33   (4)| 00:00:01 |
    |   1 |  MERGE                        | ADMIN_EMPLOYEES     |       |       |            |          |
    |   2 |   VIEW                        |                     |       |       |            |          |
    |*  3 |    HASH JOIN RIGHT OUTER      |                     |  8168 |   526K|    33   (4)| 00:00:01 |
    |   4 |     TABLE ACCESS FULL         | ADMIN_EMPLOYEES     |    39 |  1521 |     3   (0)| 00:00:01 |
    |   5 |     EXTERNAL TABLE ACCESS FULL| ADMIN_EXT_EMPLOYEES |  8168 |   215K|    29   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------------------------
    Predicate Information (IDENTIFIED BY operation id):
    ---------------------------------------------------
       3 - access("E"."TEST_ID"="A"."TEST_ID"(+))
    Note
    -----
       - dynamic sampling used FOR this statement (level=2)
       - automatic DOP: Computed Degree OF Parallelism IS 1 because OF parallel threshold

    Statistics
    ----------------------------------------------------------
            107  recursive calls
              5  db block gets
            765  consistent gets
              0  physical reads
              0  redo SIZE
            836  bytes sent via SQL*Net TO client
            976  bytes received via SQL*Net FROM client
              3  SQL*Net roundtrips TO/FROM client
              1  sorts (memory)
              0  sorts (disk)
             38  ROWS processed
     
    SQL>
    SQL> SELECT * FROM admin_employees
      2  ORDER BY 1;
     
       TEST_ID TEST_NAME
    ---------- -------------------------
             1 Test 1
             2 Test 1
             3 Test 1
             4 Test 1
             5 Test 1
             6 Test 1
             7 Test 1
             8 Test 1
             9 Test 1
            10 Test 1
            11 Test 1
     
       TEST_ID TEST_NAME
    ---------- -------------------------
            12 Test 1
            13 Test 1
            14 Test 1
            15 Test 1
            16 Test 1
            17 Test 1
            18 Test 1
            19 Test 1
            20 Test 3
            21 Test 2
            22 Test 2
     
       TEST_ID TEST_NAME
    ---------- -------------------------
            23 Test 2
            24 Test 2
            25 Test 2
            26 Test 2
            27 Test 2
            28 Test 2
            29 Test 2
            30 Test 2
            31 Test 2
            32 Test 2
            33 Test 2
     
       TEST_ID TEST_NAME
    ---------- -------------------------
            34 Test 2
            35 Test 2
            36 Test 2
            37 Test 2
            38 Test 2
            39 Test 2
     
    39 ROWS selected.

    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 2437798676
    --------------------------------------------------------------------------------------
    | Id  | Operation          | Name            | ROWS  | Bytes | Cost (%CPU)| TIME     |
    --------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |                 |    39 |  1053 |     4  (25)| 00:00:01 |
    |   1 |  SORT ORDER BY     |                 |    39 |  1053 |     4  (25)| 00:00:01 |
    |   2 |   TABLE ACCESS FULL| ADMIN_EMPLOYEES |    39 |  1053 |     3   (0)| 00:00:01 |
    --------------------------------------------------------------------------------------
    Note
    -----
       - dynamic sampling used FOR this statement (level=2)
       - automatic DOP: Computed Degree OF Parallelism IS 1 because OF parallel threshold

    Statistics
    ----------------------------------------------------------
              4  recursive calls
              0  db block gets
             15  consistent gets
              0  physical reads
              0  redo SIZE
           1340  bytes sent via SQL*Net TO client
            541  bytes received via SQL*Net FROM client
              4  SQL*Net roundtrips TO/FROM client
              1  sorts (memory)
              0  sorts (disk)
             39  ROWS processed
    SQL>
     
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I find I need to change my response: I, too, am receiving the ORA-29913 and ORA-29400 errors when using a network drive on Windows. I have no issues with using networked resources for external tables with Linux or UNIX, so it may be the way the network resources are mounted and shared in a Windows environment.

    I have no solution if you are using Windows.
     
  6. adeel53

    adeel53 Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Im using windows on client and oracle database server is on windows as well :(
    Step which i follow are simple

    =>Create directory Directory_Name as '\\drive\folder'
    =>CREATE TABLE "schema"."external_Table"
    ( "EXTENSION" NUMBER,
    "NAME" VARCHAR2(255),
    "HUB" VARCHAR2(255),
    "SITE" VARCHAR2(255),
    "DID" NUMBER,
    "DEPARTMENT" VARCHAR2(255),
    "CELL_NO" VARCHAR2(255),
    "EMAIL_ID" VARCHAR2(255)
    )
    ORGANIZATION EXTERNAL
    ( TYPE ORACLE_LOADER
    DEFAULT DIRECTORY "Directory_Name"
    ACCESS PARAMETERS
    ( RECORDS DELIMITED BY NEWLINE
    CHARACTERSET WE8MSWIN1252
    STRING SIZES ARE IN BYTES
    NOBADFILE
    NODISCARDFILE
    NOLOGFILE
    FIELDS
    TERMINATED BY ','
    NOTRIM
    (
    "EXTENSION" CHAR,
    "NAME" CHAR,
    "HUB" CHAR,
    "SITE" CHAR,
    "DID" CHAR,
    "DEPARTMENT" CHAR,
    "CELL_NO" CHAR,
    "EMAIL_ID" CHAR
    )
    )
    LOCATION
    ( "Directory_Name":'File_Name.txt'
    )
    )
    REJECT LIMIT UNLIMITED

    =>select * from external_table;

    ORA-29913: error in executing ODCIEXTTABLEOPEN callout
    ORA-29400: data cartridge error
    KUP-04040: file IPDirectory.txt in FLAT_FILE_MOD_LOC not found


    Any one could help me on this.....???
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Please see my second reply.
     
  8. adeel53

    adeel53 Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    David i know that you are also facing same problem on windows server but our server is on windows and i cant take decision to change this on Linux or UNIX :(

    So if any one {except David ;)} solve this please share his experience.