Oracle - Is there any way I can get around SELECT INTO on PL/SQL block

Question

I am trying to create a trigger, which automatically updates a student's application state when the application status row in the application table changes. I have been browsing the web for a little over an hour or so now and despite finding a potential work around using EXECUTE IMMEDIATE I cannot achieve my desired result (EXECUTE IMMEDIATE was causing an unbound variable error).

Trigger code

CREATE OR REPLACE TRIGGER trg_applications
BEFORE INSERT OR UPDATE ON applications FOR EACH ROW
BEGIN 
  IF UPDATING THEN
  /* If the status is ACCEPTED, then approve the students application */
    SELECT CASE 
             WHEN get_status(:NEW.status_id) = 
                    LOWER('Applicant Accepted Offer')
             THEN student_accept_offer( :NEW.student_id )
       END
  FROM status; 
  END IF;
END;

The get status method returns a VARCHAR2 to check whether the new status matches the condition, if so I want to update the student_approved row using the autonomous_transaction below.

student_accept_offer code

CREATE OR REPLACE FUNCTION student_accept_offer( this_stu_id NUMBER )
  RETURN VARCHAR2 IS 
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  UPDATE students 
 SET students.student_on_placement = 1 
   WHERE students.student_id = this_stu_id;
  COMMIT;
  RETURN 'Student has approved application';
END student_accept_offer;

This function works as intended when I test it outside of my trigger, however when it is embedded in the trigger an PLS-00428 error gets thrown. Could anyone point me in the right direction as to how can I work around this, to allow me to have this function fire automatically on an update if the status matches.

Thanks for your time

EDIT - Tables I am referencing enter image description here

please show the schemas of the two tables referenced and the exact error message. — OldProgrammer, Nov 29 '13 at 16:31
@Alex:In trigger ,you are selecting ,but where you are going to store the value "student has approved application" and very important part always remember ,thumb rule of oracle `select is not supposed to change the state of database` and you are trying to achieve this in the trigger code — Gaurav Soni, Nov 29 '13 at 16:34

score 1 · Accepted Answer · answered Nov 29 '13 at 16:40

1

Changing your code slightly to remove the SELECT statement (as it seems unnecessary) then does this work?

CREATE OR REPLACE TRIGGER trg_applications
BEFORE INSERT OR UPDATE ON applications FOR EACH ROW
BEGIN 
    IF UPDATING THEN
      /* If the status is ACCEPTED, then approve the students application */
      IF get_status(:NEW.status_id) = 'applicant accepted offer' THEN
        student_accept_offer( :NEW.student_id );
      END IF;
    END IF;
END;

answered Nov 29 '13 at 16:40

MT0

143,790
11
59
117

Thank-you for highlighting this for me, it sparked an idea in my head and now I feel extremely silly! I created a function instead of a procedure so it was causing me to run a SELECT statement before student_accept_offer call, have fixed it now by changing to a procedure. – Halfpint Nov 29 '13 at 16:53

Oracle - Is there any way I can get around SELECT INTO on PL/SQL block

1 Answers1