8 Replies Latest reply: May 2, 2013 3:00 PM by reddy1986 RSS

    Help with external tables

    reddy1986
      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
          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
            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
              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
                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
                  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
                    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
                      >
                      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
                        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