Load mutliple files using multiple load options in sql loader

Learnt something new in Oracle, its all about handling mutliple load options for loading into multiple table using sql-loader.

Here is a quick demo of what i have.

rajesh@ORA10GR2> select * from t1;

no rows selected

rajesh@ORA10GR2> select * from t2;

         X          Y
---------- ----------
         2          1

1 row selected.

rajesh@ORA10GR2>

D:\app\179818\product\11.2.0\dbhome_1\BIN>sqlldr rajesh/oracle@iradsnvl control=d:\ctl.txt log=d:\log.txt data=d:\data.txt

SQL*Loader: Release 11.2.0.3.0 - Production on Tue Oct 22 13:58:52 2013

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

Commit point reached - logical record count 4

D:\app\179818\product\11.2.0\dbhome_1\BIN>exit

rajesh@ORA10GR2>
rajesh@ORA10GR2>
rajesh@ORA10GR2> select * from t1;

         X          Y
---------- ----------
         1          1
         1          2
         1          3

3 rows selected.

rajesh@ORA10GR2> select * from t2;

         X          Y
---------- ----------
         2          4

1 row selected.

rajesh@ORA10GR2>

now the data got loaded into T1 but got updated in T2.

Here is what i have in Control file ( Table T1 in TRUNCATE option and T2 in REPLACE option)

D:\>type ctl.txt
LOAD DATA
INTO TABLE T1
TRUNCATE
WHEN X='1'
FIELDS TERMINATED BY ','  TRAILING NULLCOLS
(
        X ,
        Y
)
INTO TABLE T2
REPLACE
WHEN X='2'
FIELDS TERMINATED BY ','  TRAILING NULLCOLS
(
        X position(1) ,
        Y
)

and data file contents are here.

D:\>type data.txt
1,1
1,2
1,3
2,4

Subprogram Inlining and plsql optimization in 11g

One of the most important optimization that happened to Oracle 11g plsql compiler is Sub-program in-lining. more about sub-program in-lining are available in docs.  In short its a new plsql enhancement in compiler to replace all sub-program invocation with a copy of invoked sub-program ( given that both invoking and invoked subprogram are in same plsql unit ). Which can happen by default if plsql_optimize_level is set to 2 or 3

with plsql_optimize_level=2 you must specify each subprogram to be inlined with INLINE pragma
with plsql_optimize_level=3 the plsql optimizer seeks opportunities to inline subprograms.

Consider a simple routine which validates a the input string is purely numerical or not.

rajesh@ORA11GR2> create or replace procedure p
  2  as
  3     function is_number(x in varchar2)
  4     return varchar2 as
  5             y number ;
  6     begin
  7             y := to_number(x);
  8                     return 'Y';
  9             exception
 10                     when others then
 11                     return 'N' ;
 12     end;
 13  begin
 14     dbms_output.put_line (' Results for 1A is = '|| is_number('1A') );
 15     dbms_output.put_line (' Results for 12 is = '|| is_number('12') );
 16  end;
 17  /

Procedure created.

rajesh@ORA11GR2>
rajesh@ORA11GR2> exec p ;
 Results for 1A is = N
 Results for 12 is = Y

PL/SQL procedure successfully completed.

rajesh@ORA11GR2>

When plsql_optimize_level set to 3 the execution changes like this.

rajesh@ORA11GR2> alter procedure p compile plsql_optimize_level=3;

Procedure altered.

rajesh@ORA11GR2> exec p ;
 Results for 1A is = Y
 Results for 12 is = Y

PL/SQL procedure successfully completed.

rajesh@ORA11GR2>

so in short what happened during plsql_optimize_level=3 is

a) to_number is pure and deterministic
b) the variable y in code is assigned but never used, so its dead. Its a dead code.
c) therefore the above code can be reduced to:

create or replace procedure p
as
    function is_number(x in varchar2)
    return varchar2 as
    begin
        return 'Y';
    end;
begin
    dbms_output.put_line (' Results for 1A is = '|| is_number('1A') );
    dbms_output.put_line (' Results for 12 is = '|| is_number('12') );
end;   
/


the way to code the "is_datatype" function - knowing that to_date/to_number are pure deterministic functions - is to include that non-inlinable function call

rajesh@ORA11GR2> create or replace procedure p
  2  as
  3     function is_number(x in varchar2)
  4     return varchar2
  5     as
  6             y number;
  7     begin
  8             y := to_number(x);
  9             return 'Y';
 10             exception
 11                     when others then
 12                             return 'N' ;
 13     end;
 14  begin
 15     pragma inline(is_number,'NO');
 16     dbms_output.put_line (' Results for 1A is = '|| is_number('1A') );
 17     dbms_output.put_line (' Results for 12 is = '|| is_number('12') );
 18  end;
 19  /

Procedure created.

rajesh@ORA11GR2>
rajesh@ORA11GR2> alter procedure p compile plsql_optimize_level=2;

Procedure altered.

rajesh@ORA11GR2> exec p;
 Results for 1A is = N
 Results for 12 is = Y

PL/SQL procedure successfully completed.

rajesh@ORA11GR2> alter procedure p compile plsql_optimize_level=3;

Procedure altered.

rajesh@ORA11GR2>
rajesh@ORA11GR2> exec p ;
 Results for 1A is = N
 Results for 12 is = Y

PL/SQL procedure successfully completed.

rajesh@ORA11GR2>

Another valid approach would be like this

rajesh@ORA11GR2> create or replace procedure p
  2  as
  3     function is_number(x in varchar2)
  4     return varchar2
  5     as
  6             y number;
  7     begin
  8             y := to_number(x);
  9             if y is not null then
 10                     return 'Y';
 11             else
 12                     return 'N';
 13             end if;
 14             exception
 15                     when others then
 16                     return 'N' ;
 17     end;
 18  begin
 19     dbms_output.put_line (' Results for 1A is = '|| is_number('1A') );
 20     dbms_output.put_line (' Results for 12 is = '|| is_number('12') );
 21  end;
 22  /

Procedure created.

rajesh@ORA11GR2>
rajesh@ORA11GR2> alter procedure p compile plsql_optimize_level=2;

Procedure altered.

rajesh@ORA11GR2> exec p;
 Results for 1A is = N
 Results for 12 is = Y

PL/SQL procedure successfully completed.

rajesh@ORA11GR2> alter procedure p compile plsql_optimize_level=3;

Procedure altered.

rajesh@ORA11GR2> exec p;
 Results for 1A is = N
 Results for 12 is = Y

PL/SQL procedure successfully completed.

rajesh@ORA11GR2>