PL SQL Code to Reset Sequence Every Day


I found two methods to reset a sequence daily.

First approach is using a function and a profile option.

Below function is used to reset the sequence every day. It is not advisable to use a sequence in this manner, but for a requirement I had to reset a sequence every day.

This method uses a profile option named ‘XX_SEQ_DATE’ to store current date. It has to be initialized, and the parameter p_user_id needs to be set from fnd_user value.

A sequence is created using:

CREATE SEQUENCE XX_SEQ
START WITH 1
MAXVALUE 99
MINVALUE 1
CYCLE
NOCACHE
NOORDER
/

 

FUNCTION get_seq_value (p_user_id IN NUMBER)

RETURN NUMBER
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
DECLARE
l_seq_cnt NUMBER;
l_seq_date DATE;
l_seq_value NUMBER;
l_day_diff NUMBER;
l_stat BOOLEAN;
busy_exception exception;
PRAGMA EXCEPTION_INIT (busy_exception, -54);
BEGIN
SELECT XX_SEQ.NEXTVAL INTO l_seq_value FROM DUAL;

fnd_profile.initialize (p_user_id);

l_seq_date := fnd_profile.VALUE (‘XX_SEQ_DATE’);

SELECT TRUNC (SYSDATE – l_seq_date) INTO l_day_diff FROM DUAL;

IF l_day_diff <> 0 OR l_day_diff IS NULL
THEN
EXECUTE IMMEDIATE ‘ALTER SEQUENCE XX_SEQ INCREMENT BY -‘ || (l_seq_value – 1);

SELECT XX_SEQ.NEXTVAL INTO l_seq_value FROM DUAL;

EXECUTE IMMEDIATE ‘ALTER SEQUENCE XX_SEQ INCREMENT BY 1’;

l_stat :=
fnd_profile.SAVE (‘XX_SEQ_DATE’,
TO_CHAR (SYSDATE),
‘SITE’);
COMMIT;
END IF;

RETURN l_seq_value;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_stat :=
fnd_profile.SAVE (‘XX_SEQ_DATE’,
TO_CHAR (SYSDATE),
‘SITE’);

COMMIT;
fnd_file.put_line (fnd_file.LOG,’Error in get_seq_value, no data found exception: ‘ || SQLERRM);
RETURN l_seq_value;
WHEN busy_exception
THEN
l_stat :=
fnd_profile.SAVE (‘XX_SEQ_DATE’,
TO_CHAR (SYSDATE),
‘SITE’);

COMMIT;
fnd_file.put_line (fnd_file.LOG,’Error in get_seq_value, busy exception: ‘ || SQLERRM);
RETURN -1;
WHEN OTHERS
THEN
l_stat :=
fnd_profile.SAVE (‘XX_SEQ_DATE’,
TO_CHAR (SYSDATE),
‘SITE’);

COMMIT;
fnd_file.put_line (fnd_file.LOG,’Error in get_seq_value, other exception: ‘ || SQLERRM);
RETURN -1;
END;
END get_seq_value;

 

 

Other approach is to use a stored procedure to drop and create the sequence and a daily scheduled job to run the procedure:

  • Create a stored procedure to reset your sequence:

 

CREATE OR REPLACE PROCEDURE XX_SEQ_RESET AS

BEGIN

EXECUTE IMMEDIATE ‘DROP SEQUENCE XX_SEQ’;

EXECUTE IMMEDIATE

‘CREATE SEQUENCE XX_SEQ ‘ ||

‘  MINVALUE 1 ‘ ||

‘  MAXVALUE 99 ‘ ||

‘  START WITH 1 ‘ ||

‘  INCREMENT BY 1 ‘ ||

‘ CYCLE ‘ ||

‘ NOCACHE ‘;

END;

 

 

  • Then create the job:

 

BEGIN

dbms_scheduler.create_job(

job_name        => ‘job$ XX_SEQ_RESET’,

job_type        => ‘STORED_PROCEDURE’,

job_action      => ‘ XX_SEQ_RESET’,

start_date      => TO_DATE(’01-01-09′, ‘DD-MM-RR’),

repeat_interval => ‘FREQ= DAILY;BYDATE=0101’,

enabled         => TRUE,

auto_drop       => FALSE,

comments        => ‘ XX_SEQ sequence daily reset job.’

);

END;

Source Knowledge : https://oraclestar.wordpress.com/2013/06/13/pl-sql-code-to-reset-sequence-every-day/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s