9 replies to this topic

[Neil Hunter]

Well i'm busy testing the use of Oracle generated sequence numbers instead of having a file with a counter of the previous transation number. Now has anybody used this method of generating a sequence number ?

I've done it by doing an insert into a global temp table, and then reading the global temp table before adding to my transaction file. But for some odd reason, i'm not getting a value coming across

[Dan Shannon]

Have done, but if you're using it as primary key with a trigger to set the value, then don't expect it to work in paging screens. There's no way to retrieve the value actually written to the table from inside PRO-IV, because the write of the column value happens in the DB.

You *can* do it using a file def to read DUAL - a la 'SELECT SEQUENCE_NAME.NEXTVAL FROM DUAL' - run it inside a global update or some such and you get a value, then set the key before attempting to add the record. That does, or used to, work.

Cheers

Dan

[Bill Loven]

Yes it does work. We use Oracle sequences exclusively. Oracle handles multiple users accessing the same table at the same time very well.

Here is the ProIv Code the we use.

File Definition ORASEQ
AK USER_NAME 30
N ORA_SEQUENCE 10

SQL
SELECT USER, YOUR_KEY_SEQUENCE.NEXTVAL FROM DUAL
ENDSQL


AFTER READ LOGIC

#KEY_VALUE = ORA_SEQUENCE
$KEY = PIC(#KEY,'9999999999')

This works like a charm.

We are also on 10G

Bill

HTH

[Richard Bassett]

We actually do this ourselves in C because we were replacing a proprietary mechanism used by ProIV and C that was based on shared memory rather than files.

However, I think it's possible to do this in ProIV a little more simply than Dan and Bill are suggesting.

Suppose you have an Oracle Sequence called MYSEQ.

Create a filedef named say MYSEQ.
Make the alternate filename DUAL.
Add one field, of type NK, named say SEQNUM
Make the field length 20, external type NUMBER, storage 20.0
Make the alternate field name MYSEQ.NEXTVAL

Now you should be able to use this filedef in L mode to get the next sequence number into the numeric SEQNUM, provided you include the following logic in the before-read or default logic as appropriate:

SQL
SELECT FROM MYSEQ
ENDSQL

That's it (I think)
I didn't test it

HTH. Richard

[Neil Hunter]

Thanks for the reply guys

Richard, you are my new role model

Works like a dream, and is very very simple

Edited by Neil Hunter, 18 April 2005 - 07:07 AM.

[Neil Hunter]

Now everything is working fine except I have a slight problem. Working with a multi division and multi transaction environment and being on 4.6 has posed a problem.

Sequence numbers need to run in sequence per division, I can't share it across the whole company. And with 4.6 I cant use SQL DYNAMIC.

I'm sure I'm missing something really stupid here

[Richard Bassett]

Guest is me BTW.
Shouldn't start on the keyboard before the coffee..

[Neil Hunter]

Uh Oh.

I'm not sure you're missing anything at all Neil.

As far as I can see, you'd have to have a separate Oracle Sequence per division. Which is pretty messy if divisions can be added by users/administrators of the app.

I'm not sure SQL DYNAMIC would help you as it would seem you need to vary the selected 'scalar expression' and my understanding is SQL DYNAMIC is intended for arbitrary variation of the WHERE clause only (I'm on 4.6 too mostly).

You might be able to do something with SYS-SQL(), maybe in a global logic that generates the SQL needed to select the number from the correct sequence and inserts it into some "table". Then after calling that GL (say in the BR logic?) read the "table" in ProIV in the usual way. I guess you'd have to key the table on process-ids or use temporary tables or something. Have to say I don't like the sound of this much.

You might be able to do something better with PL/SQL procedures. The problem there is returning data to ProIV is not officially supported (I think - although I believe some people use it). Maybe you could use triggers although it's often a bigger change to the application than one thinks.

I can't see a neat solution anywhere here, maybe someone else can provide a missing link

Incidentally, are you still using CO/DIV codes on your filedefs then? I didn't think that was supported.


PS. I'm sure you know, but Oracle Sequences are not intended to support allocation of numbers in "strict" sequence without "gaps". If a transaction rolls backs, any sequence numbers it acquired are lost for ever. Also, when a database instance goes down you normally lose a few sequence numbers that were "cached in the allocation mechanism". Further, if you run Real Application Clusters (RAC) then numbers are not necessarily allocated in ascending sequence across the multiple database instances in the cluster (you can actually force that but it damages performance and rather negates the point of having a cluster).

Ouch, I guess that goes down in flames then.

No, not using CO/DIV as part of the key. So that wont be a problem

Yeah I know, thats why i'm testing different ways of doing a transaction. Whats looking the best answer at the moment is to get the transaction number at the very last point of the transaction, and then update the key field. We use temp files before creating any new transaction, so key structure would be known. I've also created my sequence as a non cached sequence, so there should be less chance of sequence numbers going into thin air. Luckily were running a single database on a single machine

[Richard Bassett]

I've also created my sequence as a non cached sequence, so there should be less chance of sequence numbers going into thin air

Don't want to labour this point because you understand it perfectly well, but perhaps for the sake of future readers..

Some numbers allocated from an Oracle Sequence will always be lost "into thin air" because of transaction rollback. It is literally just a question of chance. Even if you don't do explicit rollbacks they will occur from time to time because of runtime "errors" such as deadlock resolution.

If you want to guarantee use of contiguous numbers (often something auditors are hot on) then you have to allocate the numbers from a table in the "traditional" ProIV way so that the allocation of numbers is rolled back with the transaction in which it occurs.

And yes, as you rightly suggest, the locking issues usually mean you need to defer the allocation of numbers and hence the insertion of new rows that use those numbers until as late in the database transaction as possible.