SQL & PL/SQL

Hi Experts,

I have 2 tables:

First table - I_STG_X - is the main table where the data is stored permanently.

Second table - X1 - is actually a GTT where data is constructed properly and then inserted into I_STG_X.

This is done using :

insert into i_stg_x 
select nvl( select max(file_no) from i_stg_x, 0 ) + id from x1 ;

But, the highlighted part to generate new file_no is prone to error, if the code is running in parallel in different sessions.

The "id" column in table X1 is generated using DENSE_RANK based on some logic. But, ultimately the data looks like as shown below in DDL.

Is the anyway I can use Sequences to achieve this ?

---[ DDLs ]---



create table i_stg_x
as
select 1 file_no,1 seq_no,'aaaa_1' content FROM dual union all  
select 1 file_no,2 seq_no,'aaaa_2' content FROM dual union all  
select 1 file_no,3 seq_no,'aaaa_3' content FROM dual union all  
select 1 file_no,4 seq_no,'aaaa_4' content FROM dual union all  
select 1 file_no,5 seq_no,'aaaa_5' content FROM dual union all  
select 1 file_no,6 seq_no,'aaaa_6' content FROM dual union all  
--
select 2 file_no,1 seq_no,'bbbb_1' content FROM dual union all  
select 2 file_no,2 seq_no,'bbbb_2' content FROM dual union all  
select 2 file_no,3 seq_no,'bbbb_3' content FROM dual union all  
select 2 file_no,4 seq_no,'bbbb_4' content FROM dual union all  
select 2 file_no,5 seq_no,'bbbb_5' content FROM dual union all  
select 2 file_no,6 seq_no,'bbbb_6' content FROM dual union all  
select 2 file_no,7 seq_no,'bbbb_7' content FROM dual union all  
--
select 3 file_no,1 seq_no,'cccc_1' content FROM dual union all  
select 3 file_no,2 seq_no,'cccc_2' content FROM dual union all  
select 3 file_no,3 seq_no,'cccc_3' content FROM dual union all  
select 3 file_no,4 seq_no,'cccc_4' content FROM dual 
;


CREATE TABLE x1
AS
SELECT 1 id, 1 sl_no, 'aaaaa1' content FROM dual UNION ALL
SELECT 1 id, 2 sl_no, 'aaaaa2' content FROM dual UNION ALL 
SELECT 1 id, 3 sl_no, 'aaaaa3' content FROM dual UNION ALL
SELECT 1 id, 4 sl_no, 'aaaaa4' content FROM dual UNION ALL 
SELECT 2 id, 1 sl_no, 'bb1'    content FROM dual UNION ALL 
SELECT 2 id, 2 sl_no, 'bb2'    content FROM dual UNION ALL 
SELECT 2 id, 3 sl_no, 'bb3'    content FROM dual;  

Finally, the data in I_STG_X should be this -

Please let me know if you need any further inputs.

Thanks and Regards,

-Ranit

(on Oracle 11.2.0.4.0)