| SQL Loader and Batch ID [message #582543] |
Fri, 19 April 2013 06:09  |
 |
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 #582595 is a reply to message #582548] |
Fri, 19 April 2013 19:23  |
 |
Barbara Boehmer
Messages: 9104
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 |
 |
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
