0

In the history table, I wanted to add all values of column3 IF they have the same column2 value which is 12345. And if the sum of that equates to 0, then their column1 would be change to 16977.

Using this code:

    UPDATE history
    SET column1 = 16977
    WHERE 
       (SELECT SUM(column3)FROM history WHERE column2 = 12345) = 0 
       AND column2 = 12345;

I got the following error:

Error report -

SQL Error: ORA-08002: sequence SEQLOG.CURRVAL is not yet defined in this session ORA-06512: at "MCM_2017.GETUSERID", line 8 ORA-06512: at "MCM_2017.GET_MVT", line 103 ORA-04088: error during execution of trigger 'MCM_2017.GET_MVT' 08002. 00000 - "sequence %s.CURRVAL is not yet defined in this session" *Cause: sequence CURRVAL has been selected before sequence NEXTVAL *Action: select NEXTVAL from the sequence before selecting CURRVAL

I tried using another code using IF statement:

DECLARE
total: NUMBER;

 BEGIN
  SELECT SUM(column3) INTO total FROM history WHERE column2 = 12345;

  IF total = 0 THEN
    UPDATE history
    SET column1 = 16977
    WHERE column2 = 12345;
  END IF;
 END;

But I ended up with another error:

Error report - Missing IN or OUT parameter at index:: 1

Please help me on this one, I'm really stuck

Toby Allen
  • 10,997
  • 11
  • 73
  • 124
R. Smith
  • 25
  • 1
  • 5
  • 4
    Your problem is not actually caused by the query. There is a trigger defined for that table, called `MCM_2017.GET_MVT`, that is being invoked and is also doing something incorrect with the sequence `SEQLOG`. You should debug that instead – gpeche Mar 28 '18 at 10:06
  • You have an ON UPDATE trigger defined against the HISTORY table. That trigger is attempting do 'something' that involves a sequence and is generating an error. Find the trigger code and take account of it in your update. If you can't then you may need to consider disabling the trigger prior to the update and then enabling it afterwards. – BriteSponge Mar 28 '18 at 10:07
  • 1
    Once you have read other comments, Read this to understand/resolve your problem : https://stackoverflow.com/questions/809247/oracle-sequence-mysequence-currval-is-not-yet-defined-in-this-session – Kaushik Nayak Mar 28 '18 at 10:09
  • 1
    This is wrong `total: NUMBER;` If you want to assign something use `total NUMBER :=0;` If you remove `:` from your anonymous block then it dhould not throw the issue and your block will work. – XING Mar 28 '18 at 10:11
  • I've updated my query: select SEQLOG.nextval from dual;  UPDATE history SET column1 = 16977 WHERE (SELECT SUM(column3)FROM history WHERE column2 = 12345) = 0 AND column2 = 12345; It does the job but it produces an unkown command error at the first line. – R. Smith Mar 28 '18 at 10:32

0 Answers0