out of touch with non pros
A reader, August 23, 2001 - 1:50 pm UTC
Sorry Tom, way out of line, there. I think you've been hanging around with too many professionals.
Every, non-technical end user I have ever come across has had a requirement (demand really) to have all invoice numbers in exact sequence order, (mostly old time accounting issues) and won't/can't be swayed. I've dealt with it the serially "silly" way for years. Insert the sequence, commit, get the number back then open the invoice app. Whether rollback or not, increment is secure. whether invounce is populated or not, order is stable.
August 23, 2001 - 3:02 pm UTC
but isn't that a gap? You have a non-populated invoice that might never, ever be used (after you commit, the app crashes, power goes out). There goes the gap free-ness.
Other then the tongue in cheek comment about quitting (which was driven primarily by their comment " I didnot find any alternate logic/method to get all numbers in
sequence,I WILL LOSE MY JOB." I don't really see anything "out of line". That comment alone would be enough for me to start packing my bags and looking elsewhere.
Search for
gap free
on my search. I've been beating this drum for a while...
Really it is silly Tom
nana, August 23, 2001 - 2:33 pm UTC
Mubeen,
Can't you explain it to your manager,about this type of scenarios.
Tom i'm really sorry for you.
Allocate the number only on invoice creation
Andrew, August 23, 2001 - 4:21 pm UTC
I've used the "silly" method but overcome the serialization issue by only allocating the number when the invoice is actually created in the database. The downside is that you can't display it until the invoice has been created. One reason for gaps when using sequence number generators is when CACHED values are lost at shutdown etc. Alter the sequence to NOCACHE to help with that - at the expense of speed.
August 23, 2001 - 4:47 pm UTC
Other reasons for gaps are rollbacks (errors in entry), power failures, software failures and so on.
If there cannot be gaps, you cannot use a sequence. They will definitely produce a gap.
re: out of touch with non pros
Lester Burnham, August 23, 2001 - 5:12 pm UTC
I just wonder what would happen if these "managers" (or whatever non-technical people are involved) had to void a check in their personal checkbook. Would they open a new account so their statement doesn't have a gap in it when it arrives?
Two parts to this
Shrek, August 23, 2001 - 5:17 pm UTC
1) There is no guarantee that gaps won't be created using sequences, alternative approach, although one that sucks, designed for people that suck was bad_way_to_do_this
2) As for packing your bags and quitting: If you are really really sharp (like Tom) and also widely recognized as sharp then you have plenty of cool options and you can literally decide what you want to say before you quit. If your capabilities are limited, then you would have limited options and quitting in style would be RECKLESS!
But in any case Mubeen, I urge you to look for another position, because even if you solve this situation there will be other such situations sooner or later.
BTW, Tom I don't believe you were out of line!
if you're really stuck with this....
Connor, August 23, 2001 - 7:29 pm UTC
A recent suggestion I read somewhere (apologies to who it was 'cos I can't remember), was to have an autonomous transaction get the max+1, insert this into an "audit" table, and have a trigger on the target table remove the audit row. In this way, after rollback/etc, the audit + target table should contain every sequence number....
... Still an awful non-scalable way of doing things.
My apologies, not out of touch
J, August 24, 2001 - 11:18 am UTC
Sorry, meant no disrespect here. Out of line was a little much. I was just trying to point out that regardless to what is logical, there are some who don't care about it, they want order. Yes, there would be a blank invoice, but no gap. I didn't mean to imply a sequence Ie somenum_nextval, meant more the max+1 thought.
No, I wouldn't get a new checking account, Lester, but I would have the voided check in my hand, no?
At the end of the day, when theyre tracking the money, it would be 1,2,3,4......
No?
Find out the real problem
Jim Kennedy, August 24, 2001 - 11:54 am UTC
I think we need to find out the real problem. By that I mean the boss or customer has some business need (we hope) or requirement. Granted it really isn't a very useful method to meet some requirement to threaten the guy with his job.
I would ask what the business need or requirement is. Perhaps Accounting when they do audits is looking for "missing" invoices - think paper here. Perhaps Accounting is concerned that a paper invoice will be kited or show up unexpectedly. I think with a better understanding of the business need you can better come up with a solution that meets the need. Often bosses specify the solution and not what it is they are trying to accomplish. Hopefully, you have a boss that will take the time to explain the business need so you can help him or her accomplish the goal without undue solution constraints.
A reader, August 25, 2001 - 2:00 am UTC
thanks !!!
A reader, August 27, 2001 - 10:17 am UTC
Thank you all guys for participating in this discussion and thanks a lot to Tom ! As for as changing of job is concerned that I will do at an appropriate time !!!
cheers
mubeen
pls help with this similar question
Godwin, May 13, 2002 - 2:56 pm UTC
Please help me with this problem given to me by my boss.
''I need to create sequence/primary key which will pick the first 4 characters of a column called dept and count the number of records in that same dept to generate the p.k. the sequence must restart at every new dept. eg.
select dept,grade from staffrecord
insert into mytable
values (dept,grade,seq)
Thus the records in mytable should look like
Zoology Senior Clerk ZOOL001
Zoology Junior Clerk ZOOL002
Mathematics Lecturer MATH001
Mathematics T.A MATH002
............ ..... ......
in that order.
Will plsql rather be okay?
Thanks
May 13, 2002 - 3:34 pm UTC
use an autonomus transaction
Jim Kennedy, May 13, 2002 - 4:33 pm UTC
Conner from UK probably read about using an autonomous transaction in Jonathan Lewis's book "Practical Oracle 8i Building Efficient Databases" - a nice compliment to Tom's book. (Chapter 20 pages 450 to 453) Buy the books (Tom's and Jonathan's). Basically, an audit table is constructed and so you can track all the sequence numbers and "not lose" any. So you could tell if someone "discarded an invoice".(who did it when and to what invoice.) That should satisfy most auditors.
OK, am I missing something here?
Colin Davies, May 13, 2002 - 4:39 pm UTC
What about the ORDER and NOCACHE parameters? I thought that the purpose of ORDER was to maintain sequence.
May 13, 2002 - 7:00 pm UTC
It takes ONE rollback after a select seq.nextval to create a gap. That is all.
the sequence (by default) is nicely ordered but it will still have gaps -- 100% sure of that.
How can we move sequences from one schema to another dynamically?
A Reader, August 08, 2002 - 12:58 pm UTC
Hi, Tom:
We are moving a lot of objects from one schema to another, including sequences. Is there a way to move them dynamically without human intervine?
When I do
create sequence schema2.seq1 start with (select last_value schema1.seq1from schema1.seq1) increment by one nocache;
I get the following error:
ERROR at line 1:
ORA-01722: invalid number
I tried some other ways to do it, they all fail. What I can do now is query first and then create them using the last_value as the starting value of new creation. Is there a better way to do it dynamically?
Thank you in advance.
August 08, 2002 - 1:12 pm UTC
begin
for x in ( select schema1.seq1.nextval nv from dual )
loop
execute immediate 'create sequence schema2.seq1 start with ' || x.nv ||
' increment by one nocache';
end loop;
end;
/
will do that -- use that instead of just the CREATE.
SEQUENCE AND DYNAMIC SQL
sonali, October 13, 2004 - 3:43 pm UTC
I am trying to write a script that will generate sequences for me from schema with max(pk_id)+1..
SET SERVEROUTPUT ON;
DECLARE NV NUMBER;
begin
for x in ( select DISTINCT user_CONS_columns.COLUMN_NAME CNAME, user_CONS_columns.TABLE_NAME TNAME
from user_CONS_columns, user_constraints
WHERE user_CONS_columns.TABLE_NAME =user_constraints.TABLE_NAME
AND user_CONS_columns.CONSTRAINT_NAME =user_constraints.CONSTRAINT_NAME
and user_constraints.CONSTRAINT_TYPE ='P' )
loop
EXECUTE IMMEDIATE 'Select nvl(max('||X.CNAME||'),0)+1 from'|| X.TNAME INTO nv ;
DBMS_OUTPUT.PUT_LINE ('NV'||nv);
end loop;
end;
/
I keep getting error
DECLARE NV NUMBER;
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
ORA-06512: at line 10
My aim is to generate the script like this..
create sequence tablename_seq start with nvl(max(pkcol_ID),0)+1;
If my select works inside the loop, I was hoping to convert it to create sequence script.
Can you help ?
Thanks
October 13, 2004 - 4:19 pm UTC
you have to actually select out the value.
your create sequence is "not a sql statement recognized by oracle"
you might want to create a "create_sequence" procedure
create procedure create_sequence( p_seq in varchar2, p_tab in varchar2, p_cname in varchar2)
as
l_val number;
begin
execute immediate 'select nvl(max('||p_cname||'),0)+1 from ' || p_tab
into l_val;
execute immediate 'create sequence ' || p_seq || ' start with ' || l_val;
end;
/
and your script would generate
exec create_sequenct( 'S', 'T', 'PK' );
instead...
thanks will rock..
Sonali, October 13, 2004 - 4:59 pm UTC
Didn't think of this option !!
Thanks
Data query sequencially
Debasish, April 11, 2006 - 8:22 am UTC
I have a table with the follwing data
NO
-------
1
2
4
6
3
4
5
7
5
6
the output shoud be
1
2
3
4
5
5
6
allways give data when it is serially but if break then skip that data
April 11, 2006 - 4:07 pm UTC
er? did not understand the outputs given the inputs.
where did 6, 6, 7, the other 4 and such go?
unable to create the seqence using execute immediate
Sriram, May 14, 2006 - 4:40 am UTC
I wish to create the sequence using execute immediate.
my code is like this...
-----------------------
Create Pakcage mypack
Is
Procedure create_sequence(pseq_text in varchar2);
End mypack;
create package body my pack
is
Procedure create_sequence(pseq_text in varchar2)
is
begin
Execute Immediate pseq_text;
end create_sequence;
end mypack;
---------------
Note: dba_user is an user having DBA privilage.
SQL> conn dba_user/dba_user
Connected.
SQL> Declare
2 Lvtext VarChar2(500) := 'Create Sequence SEQSIFACC_RECNR Increment By 1 Start With 1 MaxValue 9999999999 MinValue 1 ';
3 Begin
4 mypack.create_sequence(Lvtext);
5 End;
6 /
ORA-01031: insufficient privileges
Sql>
--------------------------------------------------
Hope I can find some help!
May 14, 2006 - 9:02 am UTC
Thanks Tom
Sriram, May 14, 2006 - 11:53 pm UTC
Thanks you very much for the quick help!
We need to have a can do attitude
Michael Friedman, May 15, 2006 - 12:24 am UTC
Guys,
Let's face it - sometimes there are regulatory or other reasons why gaps are not allowed.
In such cases we need to find solutions to the problem - that's our job.
In the checkbook example, a voided check is not a gap. It's there marked void.
In the same way, we have all sorts of ways to solve this kind of problem.
For example, precreate numbered objects marked "not yet used". As soon as someone starts to use one mark it "voided" until a valid object gets saved.
You lose a certain small amount of concurrency - the allocation has to be single threaded - but not nearly as much as you suggest.
May 15, 2006 - 6:45 am UTC
There are not, I want someone to point me to this regulation - and then explain how it worked in the days of old - please.
In the checkbook example, when you spill coffee, lose a check - what then? destroyed, gone. And I don't know about you - but do you keep your voided checks or do you write VOID on them simply TO DESTROY THEM AND MAKE THEM NOT USEFUL. That is what I do.
You lose all concurrency, not a "small amount".
I have yet to see this "need" be anything more than "some guy said we have to do this" - nothing more.
After the fact, to close out books - sure, in "real time" - nope. It is an entirely artificial request.