Skip to Main Content
  • Questions
  • Bulk insert with unique sequence number Country wise

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question.

Asked: June 29, 2017 - 9:38 am UTC

Last updated: June 30, 2017 - 4:34 am UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi Connor/Chris,

I have a stored procedure logic written which first insert data into table using BULK LIMIT, then for sequence number we are using ROWNUM to get the uniform sequence :

n_card_rowcount := 0;
LOOP
FETCH ref_cur BULK COLLECT INTO tb_stmt_data LIMIT 5000;
EXIT WHEN tb_stmt_data.COUNT = 0;
FORALL i IN 1 .. tb_stmt_data.COUNT
INSERT INTO tb_1
(emp_no, end_date,
..
..
status, created_on
)
VALUES
(
tb_stmt_data(i).emp_no, tb_stmt_data(i).end_date,
'AC', SYSDATE
);
n_card_rowcount := SQL%ROWCOUNT;

COMMIT;

END LOOP;

/*
For each combination of stmt_created_on, country_code, is_logger, we need to sequence number which will start with lets say 2017290600000001 .. 2017290600000099.. so on
*/
IF n_card_rowcount <> 0
THEN
UPDATE tb_1
SET sequence_no = TO_CHAR(d_stmt_date,'YYYYMMDD')||LPAD(ROWNUM, 8, 0)
WHERE stmt_created_on = TO_DATE(in_stmt_created_on,'MM/DD/YYYY')
AND country_code = in_country
--AND region = in_region
AND is_logger = 'N';

COMMIT;
END IF;

I thought of creating SEQUENCE, but since this procedure will be executed for multiple countires at the same time and for each country starting sequence from 2017290600000001 is not possible.
Is there any way we can create such sequnce in BULK INSERT itself?

and Connor said...

I'm going to take a guess at a fundamental problem here.

A sequence that looks like: "2017290600000001"

looks to me like a date and a number, mangled together to be a 'sequence'. They are the kind of things that always end up being trouble, because next thing you know, people are writing SQL queries for:

"Give my customers for today"

as

where seq between 2017072900000 and 2017072999999

or even worse, things like:

select to_char(substr(seq,1,8)) as customer_date

etc etc...ie, its no longer a sequence, it has morphed into several attributes cobbled into a single column.


If you want to a true sequence number...we've got just the thing for that. 'create sequence'

If you want to know the date you loaded the data, we've got that too. A *date* column.



Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library