This discussion is archived
14 Replies Latest reply: Sep 20, 2007 5:58 AM by 472273 RSS

select statement in sql loader

472273 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    Please post your code, with sample data
  • 4. Re: select statement in sql loader
    121256 Newbie
    Currently Being Moderated
    (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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    My try was also with conventional path
    It's strange. Look at loader log.
  • 11. Re: select statement in sql loader
    jeneesh Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    >    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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