This discussion is archived
8 Replies Latest reply: May 2, 2013 1:00 PM by reddy1986 RSS

Help with external tables

reddy1986 Newbie
Currently Being Moderated
Hi,

I am creating an external table with multiple source files. All the source files have a header as their first line.
I have to skip the header and load the files.
select * from v$version;

BANNER                                                          
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production                          
CORE     10.2.0.4.0     Production                                      
TNS for Linux: Version 10.2.0.4.0 - Production                  
NLSRTL Version 10.2.0.4.0 - Production  
CREATE TABLE TEST_EXT_TAB
  (coulumn's list)
ORGANIZATION EXTERNAL
  (  TYPE ORACLE_LOADER
     DEFAULT DIRECTORY test_dir
     ACCESS PARAMETERS 
       ( RECORDS DELIMITED BY NEWLINE
         SKIP 1 FIELDS TERMINATED BY ',' 
           ( coulumn's list)
       )
   location ('file1','file2',......)
)
My issue:
When i use SKIP 1 -- it's skipping first line of the first file.

What i want:
To skip first line in all source files.

All the file types are csv.

Thanks in advance..

Cheers
Kiran
  • 1. Re: Help with external tables
    Solomon Yakobson Guru
    Currently Being Moderated
    SKIP doesn't work that way. It tells how many leading records to skip. Whay ou can do is:
    CREATE TABLE TEST_EXT_TAB_PART1
      (coulumn's list)
    ORGANIZATION EXTERNAL
      (  TYPE ORACLE_LOADER
         DEFAULT DIRECTORY test_dir
         ACCESS PARAMETERS 
           ( RECORDS DELIMITED BY NEWLINE
             SKIP 1 FIELDS TERMINATED BY ',' 
               ( coulumn's list)
           )
       location ('file1')
    )
    /
    CREATE TABLE TEST_EXT_TAB_PART2
      (coulumn's list)
    ORGANIZATION EXTERNAL
      (  TYPE ORACLE_LOADER
         DEFAULT DIRECTORY test_dir
         ACCESS PARAMETERS 
           ( RECORDS DELIMITED BY NEWLINE
             SKIP 1 FIELDS TERMINATED BY ',' 
               ( coulumn's list)
           )
       location ('file2')
    )
    /
    .
    .
    .
    CREATE OR REPLACE
      VIEW TEST_EXT_TAB_VW
        AS
          SELECT  *
            FROM  TEST_EXT_TAB_PART1
          UNION ALL
          SELECT  *
            FROM  TEST_EXT_TAB_PART2
    .
    .
    .
    /
    Now, when you select from the view it will skip 1 record of each file.

    SY.
  • 2. Re: Help with external tables
    Tubby Guru
    Currently Being Moderated
    An alternative to what Solomon posted would be to have a single definition for the external table and "switch" the file(s).

    http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:37593123416931

    Cheers,
  • 3. Re: Help with external tables
    reddy1986 Newbie
    Currently Being Moderated
    Thanks for the quick reply,

    The problem is
    1. I have no idea how many files i will be receiving from my source system, it varies on daily basis (avg 20 files).
    2. I will end up creating too many tables with this approach (Although ET will only create an entry in data dictionary) .
    3. I can achieve this in a shell script with out creating toomany tables by removing header lines with basic unix commands
    or i can ignore the header records in BAD file.

    All i wanted to know is, if there is any option available that serves my prupose.

    And again thanks for the advise, i'll keep that as an option untill i find a better soln.

    Cheers
    Kiran
  • 4. Re: Help with external tables
    reddy1986 Newbie
    Currently Being Moderated
    This would have been an option if i can work/select data from the files indivdually and move on.
    But, here all the files combined are a single data set comming from multiple sources.
  • 5. Re: Help with external tables
    Christine Schnittker Explorer
    Currently Being Moderated
    If your headers are distinctively recognizable, you could maybe try to exclude them with a LOAD WHEN.
    But if you don't know how many files you get, you'll have a problem with the
     
    location ('file1','file2',......) 
    approach anyway.
    //Tine

    Edited by: Christine Schnittker on 25.04.2013 21:52
  • 6. Re: Help with external tables
    Etbin Guru
    Currently Being Moderated
    But if you don't know how many files you get, you'll have a problem ...
    http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:439619916584 might make it easier

    Regards

    Etbin
  • 7. Re: Help with external tables
    rp0428 Guru
    Currently Being Moderated
    >
    This would have been an option if i can work/select data from the files indivdually and move on.
    But, here all the files combined are a single data set comming from multiple sources.
    >
    That doesn't make any sense based on what you posted so far.
    >
    I am creating an external table with multiple source files.
    . . .
    location ('file1','file2',......)
    . . .
    What i want:
    To skip first line in all source files.
    . . .
    1. I have no idea how many files i will be receiving from my source system, it varies on daily basis (avg 20 files).
    >
    ALL of those statements say that you are dealing with multiple, individual files; not a 'single data set'.

    We can't offer meaningful suggestions if you don't tell us what your requirement really is.

    Also, an external table is just a definition. The important part is what you haven't yet told us: what process is actually SELECTING from the external table?

    Post information about the entire context that the external table is part of.
  • 8. Re: Help with external tables
    reddy1986 Newbie
    Currently Being Moderated
    You're commenting on a response to a suggested solution by tubby.

    tubby said
    single definition for the external table and "switch" the file(s).
    my reply
    This would have been an option if i can work/select data from the files indivdually and move on.
    But, here all the files combined are a single data set comming from multiple sources. 
    We can't offer meaningful suggestions if you don't tell us what your requirement really is.
    My requirement was very clear of what i am able to achieve and what i need
    My issue:
    When i use SKIP 1 -- it's skipping first line of the first file.
    
    What i want:
    To skip first line in all source files.
     The important part is what you haven't yet told us: what process is actually SELECTING from the external table?
    I didn't get what your asking for..

    As a work-a-round i am clipping off header while i copy the file and then selecting the data through ET.

    Thanks for the response

    Cheers
    Kiran

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points