RE: Access Append Query to update Sequence Generator in Oracle

That's a HUGE bit of info .. thank you. I actually thought it commited after
every insertion. I'll post that as soon as I can, but I've got a few
pressing things immediately ... thank you so much for your help. Jim

"Jerry Whittle" wrote:

Oracle does not expect a commit after every record change. You could insert a
thousand records into a table and roll them all back out by not committing.
Also committing should not make any difference to the sequence or trigger.

If possible, post the script for both the trigger and sequence.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"Jim" wrote:

That's what I thought too, but this is the whole reason for the post ... it
did sequence just fine ... the new numbers are IN the table. What happened
was that the Sequence Generator in Oracle remained at the last number PRIOR
to the insert query, so, even though the new larger numbers went in, Oracle
still thinks the next number is the next number BEFORE the insert. See my
dilemma? FYI, Access could read Oracles next number to start the sequence.
It then sequenced just fine as inserted. The only issue is after the insert
was complete, Oracle didnt' recognize the NEW last number.

I read something that said Access doesn't "commit" till the end of the
insert query. Could this be it? Is Oracle EXPECTING to insert, then commit,
record by record?? If so, how can I include this in my Access query?

"Jerry Whittle" wrote:

The trigger "should" automatically grab the next sequence number as you
insert the records. It's probably fired during the BEFORE INSERT event on the
table. I don't think that ODBC will mess it up; however, it's always worth
testing first.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"Jim" wrote:

Thanks Jerry. I wrote the query in Design View of Access, rather than VBA
directly. It is a multiuser app, so of course that point is well taken. I
was under the assumption that Access would be reading the available largest
Unique ID on the fly (as records are appending). Sounds like that's not the
case. There is a trigger in Oracle as you guessed, but I don't know how to
"trigger" this from the action query. I have a datasource representing the
records to insert and I append this to the actual dB.

The code you offer below, does indeed look like the Sequence information in
Oracle. How would include this in my query? OR are you even saying this.
I'm a novice ... your help is greatly appreciated. Jim

"Jerry Whittle" wrote:

For the most part an Oracle sequence is run by a trigger on the table. So you
should normally let Oracle get the next unique ID as the records are appended.

If for some reason you do need to do this, there are problems.

If this is a multi-user app, you don't know if someone else is inserting a
record at the same time. That could cause a problem or two.

As far as I know, to update a sequence to a different number manually, you
need to drop the sequence then recreate it with something like below. I'm not
sure that you can do this sort of DDL over an ODBC connection.

DROP SEQUENCE COINS.AMCRTE_ID;

CREATE SEQUENCE COINS.AMCRTE_ID
START WITH 105527
MAXVALUE 999999999999999999999999999
MINVALUE 1
NOCYCLE
NOCACHE
NOORDER;

DROP PUBLIC SYNONYM AMCRTE_ID;

CREATE PUBLIC SYNONYM AMCRTE_ID FOR COINS.AMCRTE_ID;
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"Jim" wrote:

I wrote an append query that successfully gets new Unique ID's, loads them as
the append query runs, and appends to the Oracle tables just as expected
(OBDC connection). However, the Sequence Generator in Oracle IS NOT updated
to recognize the newly inserted records.

What do I do to make this happen? The current result is any new additions
after the action query "think" the sequence begins at the number just prior
to the inserted records (as if they weren't successfully inserted) ... but
they were. Help!

.

Relevant Pages

  • RE: Access Append Query to update Sequence Generator in Oracle
    ... Jerry Whittle, Microsoft Access MVP ... to be a Trigger for TASSIGNINTERVAL ... Also committing should not make any difference to the sequence or trigger. ... was that the Sequence Generator in Oracle remained at the last number PRIOR ...
    (microsoft.public.access.queries)
  • Re: Why Oracle dont have AUTO_INCREMENT as in MySQL
    ... me implementing cursor loops again. ... One example is Oracle, which uses a special data object called a SEQUENCE to get around the bottleneck. ... loops and array processing on one hand and sequences and autoincrementing on the other does not hold up in the lab. ...
    (comp.databases.oracle.misc)
  • RE: Access Append Query to update Sequence Generator in Oracle
    ... Oracle does not expect a commit after every record change. ... Also committing should not make any difference to the sequence or trigger. ... to the insert query, so, even though the new larger numbers went in, Oracle ...
    (microsoft.public.access.queries)
  • O Sean Pitman, Redux
    ... Each line in the sequence above represents a generation. ... > We have shown that the number of possible mutations under the extended ... > insertion spots) what are the odd that it will get inserted right at ... The evolution of absolutely ANY ...
    (talk.origins)
  • Re: Please help with no-gap autoincrement field
    ... Since MySQL has this feature, some of the code depends on it. ... I am new to ORACLE and based on my limited knowledge, ... autoincrement field with no gaps? ... as the source of the key rather than a sequence. ...
    (comp.databases.oracle.misc)