Oracle Function to replace Oracle sequence [message #285351] |
Tue, 04 December 2007 05:12  |
ednms
Messages: 39 Registered: November 2007
|
Member |
|
|
Hi. I have problem with oracle sequence because once we create it, it will automatically commit all the transaction before.
I have to create new sequence because everytime i execute my program, the START_WITH in the sequence based on last_seq_no
example
ws_SqlStmt="CREATE SEQUENCE NPCS_NBNTSC_LTSN";
ws_SqlStmt+=" START WITH "+s1;
ws_SqlStmt+=" INCREMENT BY 1 MINVALUE 1 MAXVALUE 9999 NOCACHE CYCLE";
to get s1, I select last_seq_no from other table
everything run smoothly except if my program aborted, i cannot rollback if i already pass the create sequence part.
My question, is there any oracle function that can replace create sequence or act same like sequence(everytime we use it, the value always increase).
|
|
|
|
Re: Oracle Function to replace Oracle sequence [message #285367 is a reply to message #285354] |
Tue, 04 December 2007 05:34   |
ednms
Messages: 39 Registered: November 2007
|
Member |
|
|
No. I have to recreate everytime i execute my program because i only execute it once. After finish the process the sequence will not be used anymore so i think better to drop it instead of left it in my database. Besides, the s1 will change according to LAST_TXN_SEQ_NUM in other table.
BATCH_DAT POC MAC OCC_NUM LAST_TXN_SEQ_NUM
--------- ---- --- ---------- ----------------
11-JUL-07 3133 00 1 0
28-SEP-07 9999 91 1 4
03-JAN-07 1209 01 1 1594
if POC is 9999, so my sequence start with 4
if POC is 1209 , so my sequence should start with 1594
whenever the LAST_TXN_SEQ_NUM > 9999, the LAST_TXN_SEQ_NUM become 1 (recycle). That is why i use sequence but now i have the autocommit problem.
|
|
|
|
|
|
|
|
|
|
|
|
Re: Oracle Function to replace Oracle sequence [message #286207 is a reply to message #285351] |
Thu, 06 December 2007 21:13   |
ednms
Messages: 39 Registered: November 2007
|
Member |
|
|
Ok.
So now i will replace my oracle sequence with oracle function
CREATE OR REPLACE FUNCTION "XX_SEQ"
( pa_rownum IN NUMBER
, pa_last_seq_no IN NUMBER)
RETURN VARCHAR2
IS
X NUMBER := 1;
Y NUMBER := pa_rownum + pa_last_seq_no;
begin
select Decode(mod(Y,9999),0,9999,mod(Y,9999)) into X
from dual connect by level <1;
RETURN X;
end;
/
because i want to call the function in other sql query.
select
to_char(T1.TXN_DATE,'YYYYMMDD') - (ltrim(to_char(SUM(DECODE(MOD(XX_SEQ(rownum,9998), 9999),1,1,0)) OVER (ORDER BY rownum),'09')))
||ltrim(to_char(XX_SEQ(rownum,9998),'0999'))
from NPCS_INBND_TXN T1
where T1.NPCS_FILE_SEQ_NUM =305
Thanks a lot.
|
|
|
|
Re: Oracle Function to replace Oracle sequence [message #286229 is a reply to message #286216] |
Thu, 06 December 2007 23:50   |
ednms
Messages: 39 Registered: November 2007
|
Member |
|
|
i'm not using sequence anymore now.
my query need function because i want to pass last_seq_num (assuming my last_seq_num here = 9998) and the XX_SEQ will handle recycle value (max value = 9999 min value = 1)
whenever the cycle happen, i want to minus date with number of cycle.
select
to_char(T1.TXN_DATE,'YYYYMMDD') - SUM(DECODE(MOD(XX_SEQ(rownum,9998), 9999),1,1,0)) OVER (ORDER BY rownum)
||ltrim(to_char(XX_SEQ(rownum,9998),'0999'))
from NPCS_INBND_TXN T1
where T1.NPCS_FILE_SEQ_NUM =305
i thought 'a counter inside a package variable' same like oracle function that i create. Sorry if i'm wrong.
|
|
|
Re: Oracle Function to replace Oracle sequence [message #286244 is a reply to message #286229] |
Fri, 07 December 2007 00:27  |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Quote: | i thought 'a counter inside a package variable' same like oracle function that i create. Sorry if i'm wrong.
|
Yes you are wrong.
A counter inside a package does not require to access the database.
But as I don't know the final need, I can't say it is appropriate.
Regards
Michel
[Updated on: Fri, 07 December 2007 00:27] Report message to a moderator
|
|
|