14 Replies Latest reply: Sep 20, 2007 7:58 AM by 472273 RSS

    select statement in sql loader

    472273
      Hi,
      My excel sheet contains the following colomns
      dest_desc,route.
      Whili loading this data i need to load dest_ids instead of dest_desc which are stored in mast dest table .

      load data
      infile 'route_dest.csv'
           append      into table mast_routedest
      fields terminated by "," optionally enclosed by '"'     
      TRAILING NULLCOLS
      (dest_id , -->select dest_id from mast_dest where dest_desc=dest_desc( from excel colomn)).
      route
      )

      Is it possible to spesify queries while loading using sql loader?
      any idea pls
      cheers
      RRK
        • 1. Re: select statement in sql loader
          jeneesh
          Use db functions..Like..
          SQL> create or replace function fn1(p_id number)
            2  return varchar2 is
            3   name emp.ename%type;
            4  begin
            5   select ename
            6   into name
            7   from emp
            8   where empno = p_id;
            9   return name;
          10  end;
          11  /

          Function created.

          SQL> create table emp1(empno number,ename varchar2(50));

          Table created.
          <br>
          <br>
          CTRL3.txt :
          <br>
          <br>
          load data
          infile *
          into table emp1 truncate
          (empno char terminated by '\n',
          ename expression "fn1(:empno)")
          begindata
          7876
          7900
          7902
          <br>
          <br>

          C:\>sqlldr scott/tiger control=ctrl3.txt

          SQL*Loader: Release 10.2.0.1.0 - Production on Tue Sep 18 14:09:16 2007

          Copyright (c) 1982, 2005, Oracle.  All rights reserved.

          Commit point reached - logical record count 3

          <br>
          <br>
          SQL> select * from emp1;

               EMPNO ENAME
          ---------- -----------------
                7876 ADAMS
                7900 JAMES
                7902 FORD                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
          • 2. Re: select statement in sql loader
            472273
            hi jeneesh,
            I am getting the following error due to your solution
            SQL*Loader-291: Invalid bind variable :dest_desc in SQL string for column DEST.
            whats wrong?
            • 3. Re: select statement in sql loader
              jeneesh
              Please post your code, with sample data
              • 4. Re: select statement in sql loader
                121256
                (dest_id , -->select dest_id from mast_dest where dest_desc=dest_desc( from excel colomn)).
                (dest_id "select dest_id from mast_dest where dest_desc = :dest_id)",
                • 5. Re: select statement in sql loader
                  jeneesh
                  load data
                  infile *
                  into table emp1 truncate
                  (empno char ,
                  ename "select ename from emp where empno = :empno"
                  )
                  begindata
                  7876
                  7900
                  7902

                  This is not working for me..What is wrong here?
                  Could you point to the documentation, please?

                  Message was edited by:
                          jeneesh
                  Log:
                  Record 1: Rejected - Error on table EMP1, column ENAME.
                  ORA-00936: missing expression                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
                  • 6. Re: select statement in sql loader
                    121256
                    This is not working for me..What is wrong here?
                    I forgot a parenthis in scalar subquery expression, you did both ones :)
                    • 7. Re: select statement in sql loader
                      jeneesh
                      Works, but..
                      <br>
                      load data
                      infile *
                      into table emp1 truncate
                      (empno char terminated by ',',
                      ename expression "(select ename from emp where empno = :empno)"
                      )
                      begindata
                      7876,x
                      7900,z
                      7902,y
                      <br>
                      <br>
                      SQL> select * from emp1;

                           EMPNO ENAME
                      ---------- -------------------
                            7876 ADAMS
                            7900 ADAMS
                            7902 ADAMS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
                      • 8. Re: select statement in sql loader
                        121256
                        Works, but..
                        It works fine using conventional path at 9.2.0.8 and 10.2.0.3.
                        • 9. Re: select statement in sql loader
                          jeneesh
                          My try was also with conventional path
                          SQL> select banner from v$version;

                          BANNER
                          ----------------------------------------------------------------
                          Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
                          PL/SQL Release 10.2.0.1.0 - Production
                          CORE    10.2.0.1.0      Production
                          TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
                          NLSRTL Version 10.2.0.1.0 - Production                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
                          • 10. Re: select statement in sql loader
                            121256
                            My try was also with conventional path
                            It's strange. Look at loader log.
                            • 11. Re: select statement in sql loader
                              jeneesh
                              SQL*Loader: Release 10.2.0.1.0 - Production on Wed Sep 19 14:33:04 2007

                              Copyright (c) 1982, 2005, Oracle.  All rights reserved.

                              Control File:   ctrl3.txt
                              Data File:      ctrl3.txt
                                Bad File:     ctrl3.bad
                                Discard File:  none specified

                              (Allow all discards)

                              Number to load: ALL
                              Number to skip: 0
                              Errors allowed: 50
                              Bind array:     64 rows, maximum of 256000 bytes
                              Continuation:    none specified
                              Path used:      Conventional

                              Table EMP1, loaded from every logical record.
                              Insert option in effect for this table: TRUNCATE

                                 Column Name                  Position   Len  Term Encl Datatype
                              ------------------------------ ---------- ----- ---- ---- ---------------------
                              EMPNO                                 1:4     4           CHARACTER           
                              ENAME                                                     EXPRESSION
                                  SQL string for column : "(select ename from emp where empno = :empno)"


                              Table EMP1:
                                5 Rows successfully loaded.
                                0 Rows not loaded due to data errors.
                                0 Rows not loaded because all WHEN clauses were failed.
                                0 Rows not loaded because all fields were null.


                              Space allocated for bind array:                    384 bytes(64 rows)
                              Read   buffer bytes: 1048576

                              Total logical records skipped:          0
                              Total logical records read:             5
                              Total logical records rejected:         0
                              Total logical records discarded:        0

                              Run began on Wed Sep 19 14:33:04 2007
                              Run ended on Wed Sep 19 14:33:05 2007

                              Elapsed time was:     00:00:00.21
                              CPU time was:         00:00:00.04                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
                              • 12. Re: select statement in sql loader
                                121256
                                >    Column Name                  Position   Len  Term Encl Datatype
                                
                                ------------------------------ ---------- ----- ---- ---- ---------------------
                                EMPNO 1:4 4 CHARACTER

                                5 Rows successfully loaded.
                                This doesn't correspond to your control file above.
                                • 13. Re: select statement in sql loader
                                  jeneesh
                                  Really sorry Elic.
                                  I was in a hurry..
                                  C:\>type ctrl3.txt
                                  load data
                                  infile *
                                  into table emp1 truncate
                                  (empno char terminated by ',',
                                  ename expression "(select ename from emp where empno = :empno)"
                                  )
                                  begindata
                                  7876,x
                                  7900,z
                                  7902,y

                                  C:\>del ctrl3.log
                                  Could Not Find C:\ctrl3.log

                                  C:\>sqlldr scott/tiger control=ctrl3.txt log=ctrl3.log

                                  SQL*Loader: Release 10.2.0.1.0 - Production on Wed Sep 19 16:06:54 2007

                                  Copyright (c) 1982, 2005, Oracle.  All rights reserved.

                                  Commit point reached - logical record count 3

                                  C:\>sqlplus

                                  SQL*Plus: Release 10.1.0.4.2 - Production on Wed Sep 19 16:08:00 2007

                                  Copyright (c) 1982, 2005, Oracle.  All rights reserved.

                                  Enter user-name: scott@test
                                  Enter password:

                                  Connected to:
                                  Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
                                  With the Partitioning, OLAP and Data Mining options

                                  SQL> select * from emp1;

                                       EMPNO ENAME
                                  ---------- --------------------------------------------------
                                        7876 ADAMS
                                        7900 ADAMS
                                        7902 ADAMS

                                  SQL> exit
                                  Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Pr
                                  oduction
                                  With the Partitioning, OLAP and Data Mining options

                                  C:\>type ctrl3.log

                                  SQL*Loader: Release 10.2.0.1.0 - Production on Wed Sep 19 16:06:54 2007

                                  Copyright (c) 1982, 2005, Oracle.  All rights reserved.

                                  Control File:   ctrl3.txt
                                  Data File:      ctrl3.txt
                                    Bad File:     ctrl3.bad
                                    Discard File:  none specified

                                  (Allow all discards)

                                  Number to load: ALL
                                  Number to skip: 0
                                  Errors allowed: 50
                                  Bind array:     64 rows, maximum of 256000 bytes
                                  Continuation:    none specified
                                  Path used:      Conventional

                                  Table EMP1, loaded from every logical record.
                                  Insert option in effect for this table: TRUNCATE

                                     Column Name                  Position   Len  Term Encl Datatype
                                  ------------------------------ ---------- ----- ---- ---- ---------------------
                                  EMPNO                               FIRST     *   ,       CHARACTER
                                  ENAME                                                     EXPRESSION
                                      SQL string for column : "(select ename from emp where empno = :empno)"


                                  Table EMP1:
                                    3 Rows successfully loaded.
                                    0 Rows not loaded due to data errors.
                                    0 Rows not loaded because all WHEN clauses were failed.
                                    0 Rows not loaded because all fields were null.


                                  Space allocated for bind array:                  16512 bytes(64 rows)
                                  Read   buffer bytes: 1048576

                                  Total logical records skipped:          0
                                  Total logical records read:             3
                                  Total logical records rejected:         0
                                  Total logical records discarded:        0

                                  Run began on Wed Sep 19 16:06:54 2007
                                  Run ended on Wed Sep 19 16:06:56 2007

                                  Elapsed time was:     00:00:02.03
                                  CPU time was:         00:00:00.07                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
                                  • 14. Re: select statement in sql loader
                                    472273
                                    Hi jeneesh,
                                    Did you notice that the same the same name is being inserted for all the emplee numbers.That means the select statement is considering only the first employee number(:empno) in the data file and getting the name of that employee every time.

                                    cheers
                                    RRK