Home » RDBMS Server » Server Utilities » SQL Loader and Batch ID (Oracle 11 G)
SQL Loader and Batch ID [message #582543] Fri, 19 April 2013 06:09 Go to next message
cacofonix
Messages: 1
Registered: April 2013
Junior Member
Hi All,

In our application, we are allowing user to upload data using excel sheet in UI.

We are using PHP script in UI and using SQL Loader to load data from excel sheet to temp_table.

The temp_table has a primary key.

Here my question is , Is there any way to put some batch id for every upload in that table in automatic way ?

so that we can easily extract the data by using batch id

we are using Oracle 11g.




For example

First time i am uploading 5 data.
second time i am uploading 3 data
so next and subsequent time batch id should be increasing automatically.

p_id P_name batch_id
~~~~~~ ~~~~~~~~~ ~~~~~~
1 Prod1 1
2 Prod2 1
3 Prod3 1
4 Prod4 1
5 Prod5 1
6 Prod6 2
7 Prod7 2
8 Prod8 2
Re: SQL Loader and Batch ID [message #582548 is a reply to message #582543] Fri, 19 April 2013 06:31 Go to previous messageGo to next message
cookiemonster
Messages: 10573
Registered: September 2008
Location: Rainy Manchester
Senior Member
Use a sequence
Re: SQL Loader and Batch ID [message #582595 is a reply to message #582548] Fri, 19 April 2013 19:23 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7860
Registered: November 2002
Location: California, USA
Senior Member
Please see the example below. Prior to each data load, it loads the next value of the sequence into a separate table, then selects that value during the data load. Note that a SQL*Loader expression that uses select must be enclosed within parentheses within the double quotes.

SCOTT@orcl_11gR2> host type test1.dat
1 Prod1
2 Prod2
3 Prod3
4 Prod4
5 Prod5

SCOTT@orcl_11gR2> host type test2.dat
6 Prod6
7 Prod7
8 Prod8

SCOTT@orcl_11gR2> host type batch.ctl
options(load=1)
load data
replace
into table batch_tab
(batch_id expression "test_seq.nextval")

SCOTT@orcl_11gR2> host type data.ctl
load data
append
into table temp_table
fields terminated by whitespace
trailing nullcols
(p_id,
p_name,
batch_id expression "(select batch_id from batch_tab)")

SCOTT@orcl_11gR2> create table temp_table
  2    (p_id      number primary key,
  3     p_name    varchar2(6),
  4     batch_id  number)
  5  /

Table created.

SCOTT@orcl_11gR2> create sequence test_seq
  2  /

Sequence created.

SCOTT@orcl_11gR2> create table batch_tab
  2    (batch_id  number)
  3  /

Table created.

SCOTT@orcl_11gR2> -- first load:
SCOTT@orcl_11gR2> host sqlldr scott/tiger control=batch.ctl log=batch1.log

SQL*Loader: Release 11.2.0.1.0 - Production on Fri Apr 19 17:16:33 2013

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

Commit point reached - logical record count 1

SCOTT@orcl_11gR2> host sqlldr scott/tiger control=data.ctl data=test1.dat log=test1.log

SQL*Loader: Release 11.2.0.1.0 - Production on Fri Apr 19 17:16:33 2013

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

Commit point reached - logical record count 5

SCOTT@orcl_11gR2> select * from batch_tab
  2  /

  BATCH_ID
----------
         1

1 row selected.

SCOTT@orcl_11gR2> select * from temp_table
  2  /

      P_ID P_NAME   BATCH_ID
---------- ------ ----------
         1 Prod1           1
         2 Prod2           1
         3 Prod3           1
         4 Prod4           1
         5 Prod5           1

5 rows selected.

SCOTT@orcl_11gR2> -- second load:
SCOTT@orcl_11gR2> host sqlldr scott/tiger control=batch.ctl log=batch2.log

SQL*Loader: Release 11.2.0.1.0 - Production on Fri Apr 19 17:16:33 2013

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

Commit point reached - logical record count 1

SCOTT@orcl_11gR2> host sqlldr scott/tiger control=data.ctl data=test2.dat log=test2.log

SQL*Loader: Release 11.2.0.1.0 - Production on Fri Apr 19 17:16:33 2013

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

Commit point reached - logical record count 3

SCOTT@orcl_11gR2> select * from batch_tab
  2  /

  BATCH_ID
----------
         2

1 row selected.

SCOTT@orcl_11gR2> select * from temp_table
  2  /

      P_ID P_NAME   BATCH_ID
---------- ------ ----------
         1 Prod1           1
         2 Prod2           1
         3 Prod3           1
         4 Prod4           1
         5 Prod5           1
         6 Prod6           2
         7 Prod7           2
         8 Prod8           2

8 rows selected.

Re: SQL Loader and Batch ID [message #582605 is a reply to message #582595] Sat, 20 April 2013 00:59 Go to previous message
Michel Cadot
Messages: 57617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can do it with a function:
SQL> create table temp_table
  2    (p_id      number primary key,
  3     p_name    varchar2(6),
  4     batch_id  number)
  5  /

Table created.

SQL> create sequence test_seq
  2  /

Sequence created.

SQL> create or replace function get_batch_id return integer is
  2    b integer; -- Only for pre-11g version
  3    x exception;
  4    -- ORA-08002: sequence %s.CURRVAL is not yet defined in this session
  5    pragma exception_init (x, -8002);
  6  begin
  7    -- Note: in 11g you can directly use "return test_seq.currval;"
  8    select test_seq.currval into b from dual;
  9    return b;
 10  exception
 11    when x then 
 12       -- Note: in 11g you can directly use "return test_seq.nextval;"
 13      select test_seq.nextval into b from dual;
 14      return b;
 15  end;
 16  /

Function created.

SQL> host type test1.dat
1 Prod1
2 Prod2
3 Prod3
4 Prod4
5 Prod5
SQL> host type test2.dat
6 Prod6
7 Prod7
8 Prod8
SQL> host type data.ctl
load data
append
into table temp_table
fields terminated by whitespace
trailing nullcols
(p_id,
p_name,
batch_id "get_batch_id()")
SQL> -- first load:
SQL> host sqlldr michel/michel control=data.ctl data=test1.dat log=test1.log

SQL*Loader: Release 10.2.0.4.0 - Production on Sam. Avr. 20 07:58:39 2013

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

Commit point reached - logical record count 4
Commit point reached - logical record count 5

SQL> -- second load:
SQL> host sqlldr michel/michel control=data.ctl data=test2.dat log=test2.log

SQL*Loader: Release 10.2.0.4.0 - Production on Sam. Avr. 20 07:58:39 2013

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

Commit point reached - logical record count 2
Commit point reached - logical record count 3

SQL> select * from temp_table;
      P_ID P_NAME   BATCH_ID
---------- ------ ----------
         1 Prod1           1
         2 Prod2           1
         3 Prod3           1
         4 Prod4           1
         5 Prod5           1
         6 Prod6           2
         7 Prod7           2
         8 Prod8           2

8 rows selected.

Regards
Michel

[Edit: added comment line 4 in function]

[Updated on: Sat, 20 April 2013 01:03]

Report message to a moderator

Previous Topic: Doubts in data restore from one schema to another
Next Topic: import on dblink
Goto Forum:
  


Current Time: Mon Apr 21 11:23:06 CDT 2014

Total time taken to generate the page: 0.06876 seconds