Results 1 to 4 of 4

Thread: external table

  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: external table

    So I almost have the following EXTERNAL TABLE def'n working, thanks to help from the forum, here:
    Code:
    CREATE TABLE PTS.NL2301_MILLER
    (
      DCBS_POSITION         CHAR(7 BYTE),
      NAME                  CHAR(50 BYTE),
      TYPE                  CHAR(3 BYTE),
      BEGINNING_BALANCE     NUMBER(7,2),
      ADJUSTMENT            NUMBER(7,2),
      AUTO_ACCRUAL          NUMBER(7,2),
      ENTERED_ACCRUAL       NUMBER(7,2),
      LEAVE_PAID            NUMBER(7,2),
      LEAVE_TAKEN           NUMBER(7,2),
      LOST_LEAVE            NUMBER(7,2),
      ENDING_BALANCE        NUMBER(7,2),
      XLR_FROM_CLR_ACCOUNT  NUMBER(7,2)
    )
    ORGANIZATION EXTERNAL
      (  TYPE ORACLE_LOADER
         DEFAULT DIRECTORY PTS_DATA_DIR
         ACCESS PARAMETERS 
           ( records delimited by newline
       fields terminated by 0X'09' optionally enclosed by '"'
       missing field values are null
       (
      DCBS_POSITION,
      NAME,
      TYPE,
      BEGINNING_BALANCE,
      ADJUSTMENT,
      AUTO_ACCRUAL,
      ENTERED_ACCRUAL,
      LEAVE_PAID,
      LEAVE_TAKEN,
      LOST_LEAVE,
      ENDING_BALANCE,
      XLR_FROM_CLR_ACCOUNT
       )
      )
         LOCATION (PTS_DATA_DIR:'Aug_2006.txt')
      )
    REJECT LIMIT UNLIMITED;
    The overall business process works like this: the data is prepared for input to the external table by saving it out of Excel into a tab-delimited txt file.

    Unfortunately, the data doesn't appear in a SELECT against the external table, due to missing tabs after the last field on each row. I'm not sure how to fix this, since I have the clause "records delimited by newline" in the def'n.

    So the data looks like
    Code:
    0230515 <tab> "JONES, NANCY E" <tab> CT <tab> 12.50 <tab> <tab> <tab> <tab> <tab> <tab> <tab> -6.50 <tab> 6.00 <newline>
    when Oracle wants this format with the final <tab> after the 12th field
    Code:
    0230515 <tab> "JONES, NANCY E" <tab> CT <tab> 12.50 <tab> <tab> <tab> <tab> <tab> <tab> <tab> -6.50 <tab> 6.00 <tab> <newline>
    We only save out from Excel since I'm under the impression that the file needs to be in a different format. That's no problem, as long as we can create a format that works.

    Any ideas? Thanks,
    -Chuck

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    Try adding this clause:
    Code:
    TRAILING NULLCOLS

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    TRAILING NULLCOLS turned out to be invalid syntax. What I did find out is that if you avoid that last delimiter, a NUMBER datatype as the last field in the table def'n is what causes the problem. When I changed the last datatype to be VARCHAR2(x) - using the same datafile - then the data loads, but the "newline" character gets included in the field when it's displayed.

    Since I have to write an insert statement to pull this data into a table, I am hoping that I can deal with a varchar2(x) converted to a number, trimming off that newline character.

    -cf

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    Is there a way to get the Chr(13) out of the last field in the file, when it's loaded into the external table? I even tried the example in the Oracle docs (http://download-west.oracle.com/docs...739/tables.htm - and see below), and it also loaded the newline character. I can certainly trim it off, but I also wanted to make sure that I understood external table def'ns as well.

    Code:
    EXAMPLE: Creating an External Table and Loading Data
    
    The file empxt1.dat contains the following sample data:
    
    360,Jane,Janus,ST_CLERK,121,17-MAY-2001,3000,0,50,jjanus
    361,Mark,Jasper,SA_REP,145,17-MAY-2001,8000,.1,80,mjasper
    362,Brenda,Starr,AD_ASST,200,17-MAY-2001,5500,0,10,bstarr
    363,Alex,Alda,AC_MGR,145,17-MAY-2001,9000,.15,80,aalda
    
    The file empxt2.dat contains the following sample data:
    
    401,Jesse,Cromwell,HR_REP,203,17-MAY-2001,7000,0,40,jcromwel
    402,Abby,Applegate,IT_PROG,103,17-MAY-2001,9000,.2,60,aapplega
    403,Carol,Cousins,AD_VP,100,17-MAY-2001,27000,.3,90,ccousins
    404,John,Richardson,AC_ACCOUNT,205,17-MAY-2001,5000,0,110,jrichard
    
    The following hypothetical SQL statements create an external table in the hr schema named admin_ext_employees and load its data into the hr.employees table.
    
    CONNECT  /  AS SYSDBA;
    -- Set up directories and grant access to hr 
    CREATE OR REPLACE DIRECTORY admin_dat_dir
        AS '/flatfiles/data'; 
    CREATE OR REPLACE DIRECTORY admin_log_dir 
        AS '/flatfiles/log'; 
    CREATE OR REPLACE DIRECTORY admin_bad_dir 
        AS '/flatfiles/bad'; 
    GRANT READ ON DIRECTORY admin_dat_dir TO hr; 
    GRANT WRITE ON DIRECTORY admin_log_dir TO hr; 
    GRANT WRITE ON DIRECTORY admin_bad_dir TO hr;
    -- hr connects 
    CONNECT hr/hr
    -- create the external table
    CREATE TABLE admin_ext_employees
                       (employee_id       NUMBER(4), 
                        first_name        VARCHAR2(20),
                        last_name         VARCHAR2(25), 
                        job_id            VARCHAR2(10),
                        manager_id        NUMBER(4),
                        hire_date         DATE,
                        salary            NUMBER(8,2),
                        commission_pct    NUMBER(2,2),
                        department_id     NUMBER(4),
                        email             VARCHAR2(25) 
                       ) 
         ORGANIZATION EXTERNAL 
         ( 
           TYPE ORACLE_LOADER 
           DEFAULT DIRECTORY admin_dat_dir 
           ACCESS PARAMETERS 
           ( 
             records delimited by newline 
             badfile admin_bad_dir:'empxt%a_%p.bad' 
             logfile admin_log_dir:'empxt%a_%p.log' 
             fields terminated by ',' 
             missing field values are null 
             ( employee_id, first_name, last_name, job_id, manager_id, 
               hire_date char date_format date mask "dd-mon-yyyy", 
               salary, commission_pct, department_id, email 
             ) 
           ) 
           LOCATION ('empxt1.dat', 'empxt2.dat') 
         ) 
         PARALLEL 
         REJECT LIMIT UNLIMITED;
    -cf

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •