Home » SQL & PL/SQL » SQL & PL/SQL » Problem with Sequence-Creation via Trigger (Oracle 12.1 on an ODA (Oracle Database Appliance))
Problem with Sequence-Creation via Trigger [message #681138] Fri, 19 June 2020 08:17 Go to next message
YannVDC
Messages: 3
Registered: June 2020
Junior Member
Hello!

We do have a software installed, that uses Oracle as a database. I have to say, that not every table has it's own continouos ID, there is a global ID.

For a few months now we do have the problem, that this ID surpasses the value of a sequence. How can I explain, we do a lot of data export from this software, and for every export it creates logging-entries in a table. Normally, the sequence should increase itself automatically, but that failes. I suppose due to the high amount of exports. The manufacturer couldn't help us until now. We only got a work-around, to check the maximum ID in that export-table and to drop and re-create the sequence. That is not very practical for us, as we only get the error when already a lot of exports have failed.

We then got a trigger from the company, to log the id's. I now tried to extend this trigger, that when the id get's over, it should automatically drop and re-create the sequence. I know, it's not a real solution, but it would help us.

create or replace TRIGGER check_evo BEFORE INSERT OR UPDATE OF EMO_ID ON EDI_VALUELIST_OUT REFERENCING OLD AS old NEW AS new FOR EACH ROW 
declare
  PRAGMA AUTONOMOUS_TRANSACTION;
  id number;
begin
  select last_number 
    into id
    from user_sequences
   where sequence_name ='SEQ_16316';
  if id > :new.evo_id then
    INSERT INTO evo_log (when, value, last_value) VALUES (systimestamp, :new.evo_id, id);
    COMMIT;  -- allowed only in autonomous triggers
  else
    EXECUTE IMMEDIATE 'DROP SEQUENCE  "BELVIS01"."SEQ_16316"';
    EXECUTE IMMEDIATE 'CREATE SEQUENCE  "BELVIS01"."SEQ_16316"  MINVALUE 0 MAXVALUE 2147483647 INCREMENT BY 1 START WITH ' || TO_CHAR(id+1000) || ' CACHE 1000 NOORDER  NOCYCLE';
    INSERT INTO seq_reload (when, sequence, oldid, newid) VALUES (systimestamp, 'SEQ_16316', id, :new.evo_id+1000);
    COMMIT;  -- allowed only in autonomous triggers
  end if;
end;
What I added is the else-Part in the if-sequence. On our Test-System it works perfectly and I was so proud of myself (I'm a PL/SQL-Beginner), but when I deploy it on the Production-System it failes. The dropping of the sequence works, but the re-creation failes and so the INSERT, what leads to a defective system.

I am no DBADMIN and not a PL/SQL-Developer, so I am limited in knowledge. I just tried something, because the manufacturer doesn't do anything.

My problem is now, how can I find out, why it doesn't work on both systems?

Has anyone some advice?

Best regards
Yann
Re: Problem with Sequence-Creation via Trigger [message #681139 is a reply to message #681138] Fri, 19 June 2020 08:59 Go to previous messageGo to next message
John Watson
Messages: 8348
Registered: January 2010
Location: Global Village
Senior Member
You have messed up your START WITH data type:
orclz> create sequence s2 start with '1000';
create sequence s2 start with '1000'
                              *
ERROR at line 1:
ORA-01722: invalid number


orclz> create sequence s2 start with 1000;

Sequence created.

orclz>
Re: Problem with Sequence-Creation via Trigger [message #681140 is a reply to message #681138] Fri, 19 June 2020 09:03 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It'd help if you'd tell what "fails" mean. Any Oracle error?

Note that instead of recreating the sequence (which means you have to re-grant the privileges) you could just altering it to jump to the next wanted value, something like:

DECLARE
...
  newid PLS_INTEGER;
BEGIN
  ...
  ELSE
    EXECUTE IMMEDIATE 'ALTER SEQUENCE "BELVIS01"."SEQ_16316"  INCREMENT BY '||TO_CHAR(:new.evo_id-id+1);
    newid := "BELVIS01"."SEQ_16316".NEXTVAL;
    EXECUTE IMMEDIATE 'ALTER SEQUENCE "BELVIS01"."SEQ_16316"  INCREMENT BY 1';
    INSERT INTO seq_reload (when, sequence, oldid, newid) VALUES (systimestamp, 'SEQ_16316', id, newid);
  END IF;
END;
Re: Problem with Sequence-Creation via Trigger [message #681141 is a reply to message #681140] Fri, 19 June 2020 10:01 Go to previous messageGo to next message
YannVDC
Messages: 3
Registered: June 2020
Junior Member
@John: I know that it looks strange, but as the identical trigger works on one system and not on the other, how can this be the problem in that case?

@Michel: Unfortunately I don't have access to logs or I don't know how I can access them. It's just that when the id is equal or smaller than new.evo_id, only the first EXECUTE IMMEDIATE with the "DROP SEQUENCE" is executed, the others not. So when this situation happens, I can't export anything (the error I see in the software does just say that, no detailed error message), and when I check on the database, the Sequence has gone.

I tried to alter the sequence instead of re-creating it, with the same result. It seems to me, that the trigger hasn't the rights to alter or create a sequence on the production system, but on the test system it works.


How can I log/check a trigger in detail or debugging it while it works? As test and prod system don't behave the same it is difficult for me to test it, as I cannot play to much on prod of course.
Re: Problem with Sequence-Creation via Trigger [message #681143 is a reply to message #681141] Fri, 19 June 2020 10:33 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
the trigger hasn't the rights to alter or create a sequence on the production system, but on the test system it works.

The owner of the trigger must have the ALTER or CREATE ANY SEQUENCE privilege if it is not the owner of the sequence.

Quote:
How can I log/check a trigger in detail or debugging it while it works?

You can do it with a log table and procedure, something like:
create table log_table (dt timestamp, msg varchar2(1000));
create or replace procedure log (p_val varchar2) is
  pragma autonomous_transaction;
begin
  insert into log_table values (systimestamp, substr(p_val, 1, 1000));
  commit;
end;
/
and modifying the code like:
DECLARE
...
  newid PLS_INTEGER;
BEGIN
  ...
  ELSE
    EXECUTE IMMEDIATE 'ALTER SEQUENCE "BELVIS01"."SEQ_16316"  INCREMENT BY '||TO_CHAR(:new.evo_id-id+1);
    newid := "BELVIS01"."SEQ_16316".NEXTVAL;
    EXECUTE IMMEDIATE 'ALTER SEQUENCE "BELVIS01"."SEQ_16316"  INCREMENT BY 1';
    log('Sequence SEQ_16316 modified; old: '||id||', new: ||newid);
    INSERT INTO seq_reload (when, sequence, oldid, newid) VALUES (systimestamp, 'SEQ_16316', id, newid);
  END IF;
EXCEPTION
  WHEN OTHERS THEN 
   log('Sequence '||sequence||': '||errmsg);
   RAISE;
END;

[Updated on: Fri, 19 June 2020 10:34]

Report message to a moderator

Re: Problem with Sequence-Creation via Trigger [message #681144 is a reply to message #681141] Fri, 19 June 2020 10:39 Go to previous messageGo to next message
John Watson
Messages: 8348
Registered: January 2010
Location: Global Village
Senior Member
Quote:
@John: I know that it looks strange, but as the identical trigger works on one system and not on the other, how can this be the problem in that case?
Just get rid of that TO_CHAR. It is wrong, and will cause the statement to fail. How do you know that it is succeeding on the other system? Perhaps what you are seeing is another failure: that the sequence is never actually dropped.
Re: Problem with Sequence-Creation via Trigger [message #681166 is a reply to message #681144] Mon, 22 June 2020 06:44 Go to previous messageGo to next message
Bill B
Messages: 1969
Registered: December 2004
Senior Member
you actually have more then 99,999,999,999,999,999,999,999,999,999,999,999,999 items loaded in a six month period. Just wow. The number column can be up to 38 significant digits. How is that possible?
Re: Problem with Sequence-Creation via Trigger [message #681167 is a reply to message #681144] Mon, 22 June 2020 07:05 Go to previous messageGo to next message
YannVDC
Messages: 3
Registered: June 2020
Junior Member
John Watson wrote on Fri, 19 June 2020 10:39
Quote:
@John: I know that it looks strange, but as the identical trigger works on one system and not on the other, how can this be the problem in that case?
Just get rid of that TO_CHAR. It is wrong, and will cause the statement to fail. How do you know that it is succeeding on the other system? Perhaps what you are seeing is another failure: that the sequence is never actually dropped.
As you can see, I do a INSERT in the table seq_reload, as third step in the else-part (after drop and re-create of the sequence), and on the test-system (even with the '' around the number) I can see the entry, and when I check the value of the sequence, it corresponds to the value I have in my reload-table.
Re: Problem with Sequence-Creation via Trigger [message #681169 is a reply to message #681167] Mon, 22 June 2020 07:16 Go to previous messageGo to next message
John Watson
Messages: 8348
Registered: January 2010
Location: Global Village
Senior Member
The trouble is that what you are describing (that your code works in one DB but not in another) is so improbable. It might help if you could show some actual evidence. For example, the CREATED date from DBA_OBJECTS. And, please, correct the bug I have pointed out: it will mean your code will never run, anywhere.
Re: Problem with Sequence-Creation via Trigger [message #681175 is a reply to message #681167] Mon, 22 June 2020 09:55 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And a feedback on what I posted should also be welcome.

Re: Problem with Sequence-Creation via Trigger [message #681242 is a reply to message #681175] Mon, 29 June 2020 06:33 Go to previous message
Bill B
Messages: 1969
Registered: December 2004
Senior Member
I suspect there is no need to to rebuild the sequence, you just need to increase the cache and also looking at last_number will NOT return the last sequence used.

Do the following

select cache from user_sequence where sequence_name ='SEQ_16316';

Since you are using this sequence for multiple tables you need to check the csche. If it is under 200 then I would issue the following

ALTER SEQUENCE CUSTOM.SEQ_16316 CACHE 200;

The cache value is how many sequence numbers it grabs at once and holds in memory. If the cache is zero then it needs to access the database every time it's used which will really slow down you application.
Previous Topic: ORA-00904: : invalid identifier
Next Topic: PLS-00801: internal error [*** ASSERT at file pdz2.c, line 4788; Illegal access (Text)
Goto Forum:
  


Current Time: Mon Sep 28 20:38:51 CDT 2020