Home » SQL & PL/SQL » SQL & PL/SQL » Oracle Function to replace Oracle sequence
Oracle Function to replace Oracle sequence [message #285351] Tue, 04 December 2007 05:12 Go to next message
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 #285354 is a reply to message #285351] Tue, 04 December 2007 05:15 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
If you would use the sequence dedicated for this process, it would automatically continue where you left it.
No need to recreate (bad idea even)
Re: Oracle Function to replace Oracle sequence [message #285367 is a reply to message #285354] Tue, 04 December 2007 05:34 Go to previous messageGo to next message
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 #285371 is a reply to message #285367] Tue, 04 December 2007 05:40 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Every DDL-statement would automatically COMMIT outstanding transactions. There is nothing you can do about THAT.

I don't see any reason why you can't "leave" the sequence in the database. If you need to be sure your program/process is the only one using it, I suggest to create a dedicated Oracle-user (with a password only you know if needed) and grant the necessary privileges to that user.

On the other hand: if you can not have gaps in your numbering, then using a sequence isn't the best choice.

I don't know if your program/process can run simultaneously, but just using MAX(value)+1 each time isn't such a bad idea after all Wink
Re: Oracle Function to replace Oracle sequence [message #285377 is a reply to message #285371] Tue, 04 December 2007 05:46 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Maybe you don't need a sequence at all. Depends on what you are doing with the value.

If it's pure SQL, you could do a construct with ROWNUM + the start value.

If it's PL/SQL just use a counter variable.
Re: Oracle Function to replace Oracle sequence [message #285378 is a reply to message #285351] Tue, 04 December 2007 05:47 Go to previous messageGo to next message
ednms
Messages: 39
Registered: November 2007
Member
Actually i use the sequence during bulk insert.
If my insert return 5 rows
and if POC is 1209 , so my sequence start with 1594

so my sequence result is 1595,1596,1597,1598,1599.
How to use MAX(value)+1. The 'value' refer to?

I also use sequence to handle recycle.

[Updated on: Tue, 04 December 2007 05:52]

Report message to a moderator

Re: Oracle Function to replace Oracle sequence [message #285379 is a reply to message #285351] Tue, 04 December 2007 05:52 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

ThomasG
Quote:

you could do a construct with ROWNUM + the start value.


This may be a good idea, for bulk isert. Am I right?

Kiran.

[Updated on: Tue, 04 December 2007 05:53]

Report message to a moderator

Re: Oracle Function to replace Oracle sequence [message #285982 is a reply to message #285379] Thu, 06 December 2007 04:19 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Yes, if you do an SQL select which you want to insert ( in a single statement or with bulk insert ), then ROWNUM + start_value would be a solution that doesn't need a sequence.
Re: Oracle Function to replace Oracle sequence [message #286098 is a reply to message #285982] Thu, 06 December 2007 08:11 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
... and would only allow for a single execution at a time.
Re: Oracle Function to replace Oracle sequence [message #286106 is a reply to message #286098] Thu, 06 December 2007 08:23 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
That's true, I figured the reason OP re-created the sequence every time the program was run was because he wanted the sequence numbers to be private to the session, not globally incremented.

Re: Oracle Function to replace Oracle sequence [message #286109 is a reply to message #286106] Thu, 06 December 2007 08:31 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
I know. I didn't really mean to criticize your post; it was meant as a more general warning against using this kind of functionality.
Re: Oracle Function to replace Oracle sequence [message #286115 is a reply to message #286106] Thu, 06 December 2007 08:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
If this is the purpose, the simplest way is not to use a sequence but a counter inside a package variable.

Regards
Michel
Re: Oracle Function to replace Oracle sequence [message #286207 is a reply to message #285351] Thu, 06 December 2007 21:13 Go to previous messageGo to next message
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 #286216 is a reply to message #285351] Thu, 06 December 2007 23:05 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Actually what are you trying to achieve using your query ?
Looks like you are doing something like 'A' - 'B' Shocked

why ignoring Michel's suggestion ?

Thumbs Up
Rajuvan

[Updated on: Thu, 06 December 2007 23:06]

Report message to a moderator

Re: Oracle Function to replace Oracle sequence [message #286229 is a reply to message #286216] Thu, 06 December 2007 23:50 Go to previous messageGo to next message
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 Go to previous message
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

Previous Topic: join the result of two different queries
Next Topic: Correct calender expression ?
Goto Forum:
  


Current Time: Mon Feb 10 01:48:50 CST 2025