3 Replies Latest reply: Dec 18, 2013 7:36 AM by EdStevens RSS

    Controller file for SQL Loader

    799706

      Guys,

       

      My data is coming in flat from some third party in the below format

       

      1,Stewart,"Current Address: Street-A, Flat-507,  London, UK

      Permanent Address: Street-B, Flat-201, London, UK"

       

      2,Patrick,"Current Address: Street-A, Flat-507,  Bangalore, India

      Permanent Address: Street-B, Flat-201, Delhi, India"

       

      I want to load the data in a custom table by using a SQL loader program which will use a controller program. This program needs to populate the table in below manner

       

      SEQ                                EMPLOYEE                                           ADDRESS INFORMATION

      1                                     Stewart                                                  Current Address: Street-A, Flat-507,  London, UK

                                                                                                          Permanent Address: Street-B, Flat-201, London, UK

      2                                     Patrick                                                  Current Address: Street-A, Flat-507,  Bangalore, India

                                                                                                          Permanent Address: Street-B, Flat-201, Delhi, India

       

      Could you guys please help me in writing the controller file to read the data from file and populate the table as described above. Thanks.

       

      -Sunil

        • 1. Re: Controller file for SQL Loader
          L-MachineGun

          Create external table and code sql query to populate your table.

           

          • 2. Re: Controller file for SQL Loader
            Barbara Boehmer

            It is a little hard to tell exactly what your data file looks like and what you want your results to be, due to this forum mangling things a bit and adding extra lines and such.  In general you can either use CONTINUEIF or CONCATENATE.  I have demonstrated both below.  When using CONTINUEIF, it assumes each additional line begins with the word "Permanent".  When using CONCATENATE, it assumes each two lines constitutes one record.  I have also used REPLACE to add a line feed in front of "Permanent", after it is removed during continuation or concatenation.

             

             

            SCOTT@orcl12c> host type test.dat

            1,Stewart,"Current Address: Street-A, Flat-507,  London, UK

            Permanent Address: Street-B, Flat-201, London, UK"

            2,Patrick,"Current Address: Street-A, Flat-507,  Bangalore, India

            Permanent Address: Street-B, Flat-201, Delhi, India"

             

            SCOTT@orcl12c> host type test.ctl

            load data

            infile test.dat

            continueif next preserve (1:9) = 'Permanent'

            into table test_tab

            fields terminated by ','

            optionally enclosed by '"'

            trailing nullcols

            (seq, employee,

            address_information "replace (:address_information, 'Permanent', CHR(10) || 'Permanent')")

             

            SCOTT@orcl12c> host type test2.ctl

            load data

            infile test.dat

            concatenate 2

            into table test_tab

            fields terminated by ','

            optionally enclosed by '"'

            trailing nullcols

            (seq, employee,

            address_information "replace (:address_information, 'Permanent', CHR(10) || 'Permanent')")

             

            SCOTT@orcl12c> create table test_tab

              2    (seq                    number,

              3     employee               varchar2(8),

              4     address_information    varchar2(200))

              5  /

             

            Table created.

             

            SCOTT@orcl12c> host sqlldr scott/tiger control=test.ctl log=test.log

             

            SQL*Loader: Release 12.1.0.1.0 - Production on Mon Dec 16 13:11:40 2013

             

            Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

             

            Path used:      Conventional

            Commit point reached - logical record count 1

            Commit point reached - logical record count 2

             

            Table TEST_TAB:

              2 Rows successfully loaded.

             

            Check the log file:

              test.log

            for more information about the load.

             

            SCOTT@orcl12c> column address_information format a60

            SCOTT@orcl12c> select * from test_tab

              2  /

             

                   SEQ EMPLOYEE ADDRESS_INFORMATION

            ---------- -------- ------------------------------------------------------------

                     1 Stewart  Current Address: Street-A, Flat-507,  London, UK

                                Permanent Address: Street-B, Flat-201, London, UK

             

                     2 Patrick  Current Address: Street-A, Flat-507,  Bangalore, India

                                Permanent Address: Street-B, Flat-201, Delhi, India

             

             

            2 rows selected.

             

            SCOTT@orcl12c> truncate table test_tab

              2  /

             

            Table truncated.

             

            SCOTT@orcl12c> host sqlldr scott/tiger control=test2.ctl log=test2.log

             

            SQL*Loader: Release 12.1.0.1.0 - Production on Mon Dec 16 13:11:40 2013

             

            Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

             

            Path used:      Conventional

            Commit point reached - logical record count 2

             

            Table TEST_TAB:

              2 Rows successfully loaded.

             

            Check the log file:

              test2.log

            for more information about the load.

             

            SCOTT@orcl12c> select * from test_tab

              2  /

             

                   SEQ EMPLOYEE ADDRESS_INFORMATION

            ---------- -------- ------------------------------------------------------------

                     1 Stewart  Current Address: Street-A, Flat-507,  London, UK

                                Permanent Address: Street-B, Flat-201, London, UK

             

                     2 Patrick  Current Address: Street-A, Flat-507,  Bangalore, India

                                Permanent Address: Street-B, Flat-201, Delhi, India

             

             

            2 rows selected.

            • 3. Re: Controller file for SQL Loader
              EdStevens

              799706 wrote:

               

              Guys,

               

              My data is coming in flat from some third party in the below format

               

              1,Stewart,"Current Address: Street-A, Flat-507,  London, UK

              Permanent Address: Street-B, Flat-201, London, UK"

               

              2,Patrick,"Current Address: Street-A, Flat-507,  Bangalore, India

              Permanent Address: Street-B, Flat-201, Delhi, India"

               

              I want to load the data in a custom table by using a SQL loader program which will use a controller program. This program needs to populate the table in below manner

               

              SEQ                                EMPLOYEE                                           ADDRESS INFORMATION

              1                                     Stewart                                                  Current Address: Street-A, Flat-507,  London, UK

                                                                                                                  Permanent Address: Street-B, Flat-201, London, UK

              2                                     Patrick                                                  Current Address: Street-A, Flat-507,  Bangalore, India

                                                                                                                  Permanent Address: Street-B, Flat-201, Delhi, India

               

              Could you guys please help me in writing the controller file to read the data from file and populate the table as described above. Thanks.

               

              -Sunil

              As mentioned, its rather hard to decipher some things, given the way the lousy editor in this forum can reformat things.  That said, it appears that you want to store all of that address information (the current/permanent tag, the street address, the city and the country --- for both the current and the permanent addresses)  all in one column.  That is a lousy design.  Address type indicator, street, city, country should all be broken out into their own columns, and with multiple possible addresses, they should be broken out into their own child table.  If you don't fix this apparent design flaw, you will be back here in short order wanting to know how to parse out this column which has become nothing more than a garbage dump.