Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Zahir.

Asked: December 09, 2008 - 12:45 pm UTC

Last updated: December 07, 2011 - 12:53 pm UTC

Version: 10.2.0.1.0

Viewed 10K+ times! This question is

You Asked

Tom .
I am running into a problem with External Tables. Please advise.

I have created the following statement for the external table . 


  DROP TABLE tab_ext ;
  CREATE TABLE tab_ext
   (  col1       INTEGER,
  col2           VARCHAR2(1),
  col3        VARCHAR2(40),
  col4      INTEGER,
  col5          INTEGER,
  col6          INTEGER,
  col7        INTEGER,
  col8          INTEGER,
  col9          INTEGER,
  col10          INTEGER,
  col11          INTEGER,
  col12         INTEGER null ,
   col13         INTEGER  null
   ) 
   ORGANIZATION EXTERNAL 
    ( TYPE ORACLE_LOADER
      DEFAULT DIRECTORY DP_LOAD_DIR
      ACCESS PARAMETERS
      ( RECORDS DELIMITED BY NEWLINE CHARACTERSET we8mswin1252      
    BADFILE DP_LOAD_DIR:'tab_ext.bad_xt'
    LOGFILE 'tab_ext.log_xt'
    FIELDS TERMINATED BY "," LDRTRIM 
    REJECT ROWS WITH ALL NULL FIELDS    
          )
      LOCATION
       ( 'tab_ext.out'
       )
    )
   REJECT LIMIT UNLIMITED;


This is the contents of tab_ext.out 

  9780061095948,R,9780061095948_R,20081207,,,,,-3    ,-3    , 8    ,,
   9780061095948,R,9780061095948_R,20081214,, 48    , 48    ,,-5    ,-5    , 51    ,,


When I issue the SELECT statement . I see the following the entries in the log file .


Field Definitions for table TAB_EXT
Record format DELIMITED BY NEWLINE
Data in file has same endianness as the platform
Reject rows with all null fields

Fields in Data Source:

COL1 CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
COL2 CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
COL3 CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
COL4 CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
COL5 CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
COL6 CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
COL7 CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
COL8 CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
COL9 CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
COL10 CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
COL11 CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
COL12 CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
COL13 CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
KUP-04021: field formatting error for field COL13
KUP-04023: field start is after end of record
KUP-04101: record 1 rejected in file E:\br5\dp1\tout\tab_ext.out
KUP-04021: field formatting error for field COL13
KUP-04023: field start is after end of record
KUP-04101: record 2 rejected in file E:\br5\dp1\tout\tab_ext.out



But , If I add a "additional " comma to the end of the record in the datafile like shown below , I see no issue in the data retrival . 

9780061095948,R,9780061095948_R,20081207,,,,,-3    ,-3    , 8    ,,,
9780061095948,R,9780061095948_R,20081214,, 48    , 48    ,,-5    ,-5    , 51    ,,,



This is in 10g Release 2 on Windows 64 bit itanimum . It seems to be excepting additional comma to the end . Please advise.

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 64-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production



and Tom said...

when you had less inputs in the file than columns in the table - it didn't know what to do.

You would usually:

LOGFILE 'tab_ext.log_xt'
    FIELDS TERMINATED BY "," LDRTRIM
   <b> MISSING FIELD VALUES ARE NULL
   </b> REJECT ROWS WITH ALL NULL FIELDS
          )
      LOCATION


use that clause.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks, this solved my problem, too.

Rick Randall, July 22, 2010 - 3:59 pm UTC

I hit this problem, found your fix by Yahoo web search

Zahir, November 28, 2011 - 11:04 am UTC

Tom -

I am trying to use preprocessor functionality in populating external tables . I am getting to kick off the batch script.


Here is the batch script.


d:\snb\ext>type uz.bat
unzip  -o d:\snb\ext\t.zip -d d:\snb\ext\



Here is the table definition . 

SQL>   drop TABLE tab_pre_ext purge;

Table dropped.

SQL>   CREATE TABLE tab_pre_ext
  2     (  emp_id      INTEGER,
  3    emp_name           VARCHAR2(30),
  4    emp_type        VARCHAR2(50)
  5     )
  6     ORGANIZATION EXTERNAL
  7      ( TYPE ORACLE_LOADER
  8        DEFAULT DIRECTORY SNBDAT
  9        ACCESS PARAMETERS
 10        (
 11        debug = 3
 12        RECORDS DELIMITED BY NEWLINE
 13        preprocessor SNBDAT:'uz.bat'
 14        FIELDS TERMINATED BY "," LDRTRIM
 15        MISSING FIELD VALUES ARE NULL
 16        REJECT ROWS WITH ALL NULL FIELDS
 17            )
 18        LOCATION
 19         ( 't.out'
 20         )
 21      )
 22     REJECT LIMIT UNLIMITED;

Table created.



SQL> Select count(*) from tab_pre_ext;
Select count(*) from tab_pre_ext
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file t.out in SNBDAT not found


When I tried to retrieve the records from the external table , I am getting the following error. 
It seems the batch file did not get executed. 



SQL> ho dir t.*
 Volume in drive D is Data
 Volume Serial Number is 9835-AC6F

 Directory of d:\snb\ext

11/28/2011  11:23 AM           759,420 t.zip
               1 File(s)        759,420 bytes
               0 Dir(s)   8,840,630,272 bytes free



But , when I execute the batch file from DOS prompt , it does work. 


d:\snb\ext>uz

d:\snb\ext>unzip  -o d:\snb\ext\t.zip -d d:\snb\ext\
Archive:  d:/ssnb/ext/t.zip
  inflating: d:/ssnb/ext/t.out
  
  
I have given read , write , execute on this directory to this user.   

SQL> select DIRECTORY_NAME, DIRECTORY_PATH  , owner from dba_directories where  DIRECTORY_NAME='SNBDAT';

DIRECTORY_NAME
------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
OWNER
------------------------------
SNBDAT
d:\snb\ext
SYS


I am on 11g Release 2 on Windows 64 bit.   
  
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production  



Please advise.

Tom Kyte
November 29, 2011 - 7:51 am UTC

does the file t.out exist?

t.out is a filename that would be passed in as a parameter to your unzip script. Your unzip script should be referencing this parameter to figure out what file to actually unzip.


it would seem to me that you would want to have t.zip as the location, and reference a parameter that tells unzip what file to unzip - and use parameters to unzip to make sure it writes to STDOUT, not to a file in the file system.



A reader, December 06, 2011 - 12:12 pm UTC

I am trying to create an external table from a network share on Windows.
First , I tried with UNC . It does not work .

After reading DOC ID 290703.1 , I changed the directory to mapped network . Still , it doesn't work.
Is any other setup required ?.

Thanks .


Select * from TAB2_EXT
Error report:
SQL Error: ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file tab.out in LIVE2DIR not found
29913. 00000 - "error in executing %s callout"
*Cause: The execution of the specified callout caused an error.
*Action: Examine the error messages take appropriate action.


SQL> Select * from product_component_version;

PRODUCT                                                                          VERSION                                                                  STATUS
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------
NLSRTL                                                                           11.2.0.3.0                                                               Production
Oracle Database 11g Enterprise Edition                                           11.2.0.3.0                                                               64bit Production
PL/SQL                                                                           11.2.0.3.0                                                               Production
TNS for 64-bit Windows:                                                          11.2.0.3.0                                                               Production


C:\>net use
New connections will be remembered.
Status       Local     Remote                    Network
-------------------------------------------------------------------------------
OK           Y:        \\198.22.996.52\FRA        Microsoft Windows Network


SQL> select directory_name , directory_path from dba_directories;

DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ ---------------------------------------
LIVE2DIR                       Y:\
LIVE1DIR                       \\198.22.996.52\FRA\

C:\>type y:\tab.out
1234567890123  Record 1
2345678901234  Record 2



DROP TABLE TAB1_EXT; 
CREATE TABLE TAB1_EXT
  (
    COL1 CHAR(15),
    COL2 VARCHAR2(4000)
  )
  ORGANIZATION EXTERNAL
  (
    TYPE ORACLE_LOADER 
    DEFAULT DIRECTORY LIVE1DIR 
    ACCESS PARAMETERS 
    ( RECORDS DELIMITED BY NEWLINE 
    CHARACTERSET US7ASCII 
    BADFILE TESTDIR:'TAB_EXT.bad_txt' 
    LOGFILE TESTDIR:'TAB_EXT.log_txt' 
    FIELDS LDRTRIM REJECT ROWS
    WITH ALL NULL FIELDS 
    (COL1 POSITION(1:15), 
     COL2 POSITION(16:4015) 
     ) 
  ) LOCATION ( 'tab.out' )
  )
  REJECT LIMIT UNLIMITED ;
  
  
DROP TABLE TAB2_EXT; 
CREATE TABLE TAB2_EXT
  (
    COL1 CHAR(15),
    COL2 VARCHAR2(4000)
  )
  ORGANIZATION EXTERNAL
  (
    TYPE ORACLE_LOADER 
    DEFAULT DIRECTORY LIVE2DIR 
    ACCESS PARAMETERS 
    ( RECORDS DELIMITED BY NEWLINE 
    CHARACTERSET US7ASCII 
    BADFILE TESTDIR:'TAB_EXT.bad_txt' 
    LOGFILE TESTDIR:'TAB_EXT.log_txt' 
    FIELDS LDRTRIM REJECT ROWS
    WITH ALL NULL FIELDS 
    (COL1 POSITION(1:15), 
     COL2 POSITION(16:4015) 
     ) 
  ) LOCATION ( 'tab.out' )
  )
  REJECT LIMIT UNLIMITED ;

Tom Kyte
December 07, 2011 - 12:53 pm UTC

can the account the database server is running as see that - remember, windows isn't easy like unix - just because YOU can see something does not mean the less privileged other user (the oracle software account) can see it.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.