7 Replies Latest reply on Jun 23, 2008 4:52 PM by 577396

    ORA-04071

    577396
      Hi Guru's

      When i am trying to execute below SQL it giving me an ORA error.
      ORA-04071: missing BEFORE, AFTER or INSTEAD OF keyword

      Can anyone please let me know if I am overlooking any syntax error.

      create or replace trigger tr_subject
      2 BEFORE insert on SUBJECT
      3 for each row
      4 begin
      5 select SQ_SUBJECT.nextval into :new.SUBJECT_ID from dual;
      6 end;

      Rgds

      user574393 - HAVE EDITED WITH THE EXACT SQL
        • 1. Re: ORA-04071
          546595
          you are missing select from dual
          here is the example you can replcae with your table name and seq name;
          SQL> CREATE OR REPLACE TRIGGER tr_subject
            2    BEFORE INSERT
            3    ON emp
            4    FOR EACH ROW
            5  BEGIN
            6    SELECT SEQ_THREAD.NEXTVAL
            7      INTO :NEW.empno
            8     FROM DUAL;
            9  END;
          10  /

          Trigger created.
          not sure why are you creating trigger, you can use SQ_SUBJECT.nextval in insert statement also.
          • 2. Re: ORA-04071
            Sven W.
            looks ok, but I would try this version
            create or replace trigger trg_bri_students
            before insert on students
            for each row
            begin
               if :new.SUBJECT_ID is null then
                  select SQ_SUBJECT.nextval into :new.SUBJECT_ID from dual;
               end if;
            end;
            /
            Are you sure that you use the correct table and ids? It is strange that a table "students" has a primary key "subject_id".
            • 3. Re: ORA-04071
              577396
              what is the prominence of '/' ???

              in the pl/sql

              Thnks

              user574393
              • 4. Re: ORA-04071
                577396
                Hi

                Below is the exact SQL , i hv noticed later that I haven't given '/' ,
                so would like to know its importance in a sql statement.

                create or replace trigger tr_subject
                2 BEFORE insert on SUBJECT
                3 for each row
                4 begin
                5 select SQ_SUBJECT.nextval into :new.SUBJECT_ID from dual;
                6 end;


                Rgds
                • 5. Re: ORA-04071
                  547625
                  / indicates Oracle that the statements are over and now it should execute them. For an un-named code block you have to tell Oracle when it should start the execution. '/' is meant for exactly the same purpose.
                  Hope it helps.
                  • 6. Re: ORA-04071
                    Boneist
                    / indicates Oracle that the statements are over and
                    now it should execute them. For an un-named code
                    block you have to tell Oracle when it should start
                    the execution. '/' is meant for exactly the same
                    purpose.
                    Hope it helps.
                    That's not strictly true - the / is a SQL*Plus command, not an Oracle command. When you're keying in pl/sql code into SQL*Plus, it leaves you in the editor until you give either a . (quit the editor without running the code) or / (quit the editor and run the code)

                    If you're not using SQL*Plus to run your code, then it may be that the / is unnecessary - for example, I know that Toad does not require an additional /.

                    However, the bulk of scripts will still be run through SQL*Plus, so it's good practice to make them SQL*Plus ready. If you don't, be prepared to spend a while working out why your release script that ran so fine on Toad is now stuck on a line number in SQL*Plus...

                    To my knowledge, most other Oracle client tools will accept the extra / without complaining.
                    • 7. Re: ORA-04071
                      577396
                      thank you gurus!!