Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Can you create EXTERNAL table for a file in network drive.

user12240205Jun 12 2012 — edited Jun 12 2012
We create a external table like this:
SQL> ed
Wrote file afiedt.buf

  1  Create table EXT_SUBSCRIPTION_ext2 (f1   VARCHAR2(20),
  2                                                        f2      VARCHAR2(30)
  3                                                        )
  4                      organization external
  5                      (
  6                          TYPE ORACLE_LOADER
  7                          DEFAULT DIRECTORY WEBUTIL_EXTERNAL_DIR
  8                          ACCESS PARAMETERS
  9                      (
 10                          records delimited by newline
 11                          fields TERMINATED BY ','
 12                      )
 13                          LOCATION('abcdefghi.txt')
 14                      )
 15                      parallel
 16*                     REJECT LIMIT UNLIMITED
 17  /

Table created.
The directory is mapped to a directory in another machine in the network. i.e. not the DB machine.
SQL> L
  1* SELECT * FROM dba_directories WHERE DIRECTORY_NAME = 'WEBUTIL_EXTERNAL_DIR'
SQL> /

OWNER                          DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ ------------------------------ --------------------------------------
SYS                            WEBUTIL_EXTERNAL_DIR           \\192.168.10.53\Users\webutiltest\Files
When we run it we get this error:
SQL> SELECT * FROM  EXT_SUBSCRIPTION_ext2 ;
SELECT * FROM  EXT_SUBSCRIPTION_ext2
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
error opening file \\192.168.10.53\Users\webutiltest\Files\EXT_SUBSCRIPTION_EXT2_3348_916.log
If the directory the file is in is in the machine where the DB is, this works fine.

So, my question is, is it a MUST that the file an external table is mapped to, reside in the DB machine itself??
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 10 2012
Added on Jun 12 2012
3 comments
1,102 views