Oracle DB trigger compiles with warnings. No error available

Question

This is my first db trigger. It compiles with warnings and therefore doesn't work. I've re-read the Oracle docs and searched online but can't work out where I'm going wrong. Any help with my trigger below would gratefully received.

    CREATE OR REPLACE TRIGGER oa_mhd_update AFTER
      INSERT ON men_mhd FOR EACH row WHEN (new.mhd_tktc LIKE 'OA_A_%'
        OR new.mhd_tktc LIKE 'OA_T_%'
        OR new.mhd_tktc LIKE 'OA_M_%') 
      DECLARE seq_var NVARCHAR2 (20);
      BEGIN
        SELECT (MAX (seq) + 1) into seq_var FROM oa_mhd_data;
        INSERT
        INTO oa_mhd_data
          (
            mhd_code,
            seq,
            mhd_mst1,
            mhd_mst2,
            mhd_cred,
            mhd_cret,
            mhd_tsks,
            mhd_msgs,
            mhd_tktc,
            mhd_tref,
            mhd_actn,
            mhd_eref,
            mhd_subj,
            mhd_udf1,
            mhd_udf2,
            mhd_udf3,
            mhd_udf4,
            mhd_udf5,
            mhd_udf6,
            mhd_udf7,
            mhd_udf8,
            mhd_udf9,
            mhd_udfa,
            mhd_udfb,
            mhd_udfc,
            mhd_udfd,
            mhd_udfe,
            mhd_udff,
            mhd_udfg,
            mhd_udfh,
            mhd_udfi,
            mhd_udfj,
            mhd_udfk,
            mhd_updd,
            mhd_begd,
            mhd_begt,
            mhd_endd,
            mhd_endt,
            mhd_mrcc,
            mhd_mhdc,
            mhd_mscc,
            mhd_pprc,
            mhd_ppss,
            mhd_inst
          )
          VALUES
          (
            :new.mhd_code
            seq_var,
            :new.mhd_mst1,
            :new.mhd_mst2,
            :new.mhd_cred,
            :new.mhd_cret,
            :new.mhd_tsks,
            :new.mhd_msgs,
            :new.mhd_tktc,
            :new.mhd_tref,
            :new.mhd_actn,
            :new.mhd_eref,
            :new.mhd_subj,
            :new.mhd_udf1,
            :new.mhd_udf2,
            :new.mhd_udf3,
            :new.mhd_udf4,
            :new.mhd_udf5,
            :new.mhd_udf6,
            :new.mhd_udf7,
            :new.mhd_udf8,
            :new.mhd_udf9,
            :new.mhd_udfa,
            :new.mhd_udfb,
            :new.mhd_udfc,
            :new.mhd_udfd,
            :new.mhd_udfe,
            :new.mhd_udff,
            :new.mhd_udfg,
            :new.mhd_udfh,
            :new.mhd_udfi,
            :new.mhd_udfj,
            :new.mhd_udfk,
            :new.mhd_updd,
            :new.mhd_begd,
            :new.mhd_begt,
            :new.mhd_endd,
            :new.mhd_endt,
            :new.mhd_mrcc,
            :new.mhd_mhdc,
            :new.mhd_mscc,
            :new.mhd_pprc,
            :new.mhd_ppss,
            :new.mhd_inst
          )
      END;
    /

This is the only feedback available: TRIGGER oa_mhd_update compiled Warning: execution completed with warning Nothing is available using show error, or in error_all — user2181935
– user2181935, Commented Jul 29, 2013 at 9:15

Alex Poole · Accepted Answer · 2013-07-29 09:45:18Z

1

You're missing a comma between the first two elements of the values clause, and a semi-colon at the end of the insert statement:

      VALUES
      (
        :new.mhd_code
        seq_var,
        :new.mhd_mst1,
        ...
        :new.mhd_ppss,
        :new.mhd_inst
      )

... should be:

      VALUES
      (
        :new.mhd_code,
        seq_var,
        :new.mhd_mst1,
        ...
        :new.mhd_ppss,
        :new.mhd_inst
      );

Odd that you can't see the error though.

Incidentally, the max(seq) + 1 from ... pattern isn't reliable in a multi-user environment. It would be more normal (and safer) to use a proper sequence to generate that value.

edited Jul 29, 2013 at 9:45

answered Jul 29, 2013 at 9:29

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user2181935 Over a year ago

Thanks Alex. I corrected the code, but provides the same result.

Alex Poole Over a year ago

@user2181935 - you're missing a semi-colon too, I've updated the answer. Figuring out why you can't see the errors should probably be the priority though. Are you creating this is a different schema? Though querying all_errors should still work in that case. Which client are you using - I'm wondering if it might be a buggy old version of SQL Developer. And if you do alter trigger oa_mhd_update compile; then can you see the error report? Can you see it if you run this in a different client (e.g. SQL*Plus)?

user2181935 Over a year ago

Thanks Alex. I've corrected syntax, the trigger worked and resolved the error viewing issue too. There was a different owner than I had expected in all_errors.

Harshit · Accepted Answer · 2013-07-29 09:51:09Z

1

Hi there are two syntactical errors First please add a comma between two values you are inserting

 VALUES
      (
        :new.mhd_,
        seq_var,
        :new.mhd_mst1,...

and second please add a semi colon at he end of insert statement

 ...
 :new.mhd_pprc,
        :new.mhd_ppss,
        :new.mhd_inst
      );

Hope this will solve your problem

answered Jul 29, 2013 at 9:51

Harshit

5601 gold badge5 silver badges15 bronze badges

Collectives™ on Stack Overflow

Oracle DB trigger compiles with warnings. No error available

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related