Oracle Trigger error 'Statement Ignored' and PLS-00405:sub-query not allowed in this context

Question

I am getting the following two errors in the screenshot while trying to create the trigger. Can somebody please tell me what am I doing wrong while passing values in the variables user_audit and user_evdnse using SELECT INTO?

create or replace TRIGGER TRG_CHK_HRCQA_CASE_ACTIONS
AFTER INSERT ON CUSTOMERS
FOR EACH ROW
DECLARE

 user_audit VARCHAR(30); /* The user that is displayed in audit */
 user_evdnse VARCHAR(30); /* The user that took action in evidence tab */
 LV_ERRORCODE VARCHAR2(1000);
 
BEGIN
    
  /* pass the username into the variables */
    
    SELECT OWNER_ID into user_audit from kdd_review_owner  where OWNER_SEQ_ID = :NEW.ACTION_BY_ID;
    
    SELECT OWNER_ID into user_evdnse from kdd_review_owner  where OWNER_SEQ_ID = :NEW.ACTION_BY_ID;

  /* If the logged in user is QA or HRCO */
    
    IF (:NEW.ACTION_BY_ID in (
                              (select ENTITY_ID from table(f_get_arg_table(F_GET_QAUSR(NEW.CASE_INTRL_ID,user_audit,'AUDIT'))))
                              UNION
                              (select ENTITY_ID from table(f_get_arg_table(F_GET_QAUSR(NEW.CASE_INTRL_ID,user_evdnse,'EVDNSE'))))
                             )
        ) THEN
    
  /* then insert record in the SC_HRCQA_CASE_ACTIONS table with IS_HRCO_QA flag as Y  */
        
        Insert into SC_HRCQA_CASE_ACTIONS (ACTION_SEQ_ID,ACTION_BY_ID,ACTION_TS,STATUS_CD,CASE_INTRL_ID,ACTION_ID,NEW_CASE_OWNR_ASSGN_ID,CASE_DUE_TS,PREV_CASE_OWNR_ASSGN_ID,IS_HRCO_QA) 
        values (:NEW.ACTION_SEQ_ID, :NEW.ACTION_BY_ID, :NEW.ACTION_TS, :NEW.STATUS_CD, :NEW.CASE_INTRL_ID, :NEW.ACTION_ID, :NEW.NEW_CASE_OWNR_ASSGN_ID, :NEW.CASE_DUE_TS, :NEW.PREV_CASE_OWNR_ASSGN_ID,'Y');
        
        ELSE
     
  /* else the logged in user is NOT HRCO/QA hence insert record in the SC_HRCQA_CASE_ACTIONS table with IS_HRCO_QA flag as N  */   
  
        Insert into SC_HRCQA_CASE_ACTIONS (ACTION_SEQ_ID,ACTION_BY_ID,ACTION_TS,STATUS_CD,CASE_INTRL_ID,ACTION_ID,NEW_CASE_OWNR_ASSGN_ID,CASE_DUE_TS,PREV_CASE_OWNR_ASSGN_ID,IS_HRCO_QA) 
        values (:NEW.ACTION_SEQ_ID, :NEW.ACTION_BY_ID, :NEW.ACTION_TS, :NEW.STATUS_CD, :NEW.CASE_INTRL_ID, :NEW.ACTION_ID, :NEW.NEW_CASE_OWNR_ASSGN_ID, :NEW.CASE_DUE_TS, :NEW.PREV_CASE_OWNR_ASSGN_ID,'N');
        
    END IF;

EXCEPTION
    WHEN OTHERS THEN LV_ERRORCODE := SQLCODE;
        INSERT INTO KDD_LOGS_MSGS (LOG_DT, LOG_INFO_TX, REMARK_TX)
        VALUES (SYSDATE,'ErrorCode - ' || LV_ERRORCODE,'TRG_CHK_HRCQA_CASE_ACTIONS');
END;

score 2 · Answer 1 · answered Jul 14 '22 at 16:14

This syntax:

...
    IF (:NEW.ACTION_BY_ID in (
                              (select ENTITY_ID from table(f_get_arg_table(F_GET_QAUSR(NEW.CASE_INTRL_ID,user_audit,'AUDIT'))))
                              UNION
                              (select ENTITY_ID from table(f_get_arg_table(F_GET_QAUSR(NEW.CASE_INTRL_ID,user_evdnse,'EVDNSE'))))
                             )
        ) THEN
...

is not valid.

Similar example on sample EMP table:

declare
  l_var VARCHAR2(1) := 'KING';
begin
  IF l_var IN (SELECT ename FROM EMP) THEN
    dbms_output.put_line('emp exists');
  ELSE
    dbms_output.put_line('no emp found');
  END IF;
end;
/

Error report -
ORA-06550: line 4, column 15:
PLS-00405: subquery not allowed in this context
ORA-06550: line 4, column 3:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Here is a possible solution:

declare
  l_var VARCHAR2(1) := 'KING';
  l_dummy PLS_INTEGER;
begin
  BEGIN
    SELECT 1 FROM DUAL INTO l_dummy WHERE exists (SELECT ename FROM EMP WHERE ename = l_var);
    dbms_output.put_line('emp exists');
  EXCEPTION WHEN NO_DATA_FOUND THEN
    dbms_output.put_line('no emp found');
  END;
end;
/

Thank you so much for pointing out the invalid syntax. I was able to figure out the solution after adding two more variables and using simple `SELECT INTO` statements. I will post my changes shortly as an answer. — Reeya Oberoi, Jul 14 '22 at 18:12

score 0 · Answer 2 · answered Jul 14 '22 at 18:17

The problem with my code was in the following section where I had used sub-query which is invalid syntax and the second issue was the missing colon : beside NEW.CASE_INTRL_ID.

IF (:NEW.ACTION_BY_ID in (
                          (select ENTITY_ID from table(f_get_arg_table(F_GET_QAUSR(NEW.CASE_INTRL_ID,user_audit,'AUDIT'))))
                          UNION
                          (select ENTITY_ID from table(f_get_arg_table(F_GET_QAUSR(NEW.CASE_INTRL_ID,user_evdnse,'EVDNSE'))))
                         )
    ) THEN

I had to declare two more variables in my trigger to pass the ENTITY_ID therein and eventually use those two variables in my IF. Here is the final change -

create or replace TRIGGER TRG_CHK_HRCQA_CASE_ACTIONS
AFTER INSERT ON KDD_CASE_ACTIONS
FOR EACH ROW
DECLARE

 user_audit kdd_review_owner.OWNER_ID%TYPE; /* The user that is displayed in audit */
 user_evdnse kdd_review_owner.OWNER_ID%TYPE; /* The user that took action in evidence tab */
 v_audit_user VARCHAR(30);  /* The HRCO/QA user from audit tab */
 v_evdnse_user VARCHAR(30); /* The HRCO/QA user from evidence tab */
 LV_ERRORCODE VARCHAR2(1000);

BEGIN

  /* pass the username into the variables */

    SELECT kro.OWNER_ID into user_audit from kdd_review_owner kro where kro.OWNER_SEQ_ID = :NEW.ACTION_BY_ID;

    SELECT kro.OWNER_ID into user_evdnse from kdd_review_owner kro where kro.OWNER_SEQ_ID = :NEW.ACTION_BY_ID;

    select ENTITY_ID into v_audit_user from table(f_get_arg_table(F_GET_QAUSR(:NEW.CASE_INTRL_ID,user_audit,'AUDIT')));

    select ENTITY_ID into v_evdnse_user from table(f_get_arg_table(F_GET_QAUSR(:NEW.CASE_INTRL_ID,user_evdnse,'EVDNSE')));
  /* If the logged in user is QA or HRCO */

    IF (:NEW.ACTION_BY_ID in (v_audit_user,v_evdnse_user)) 

    THEN

I would wrap each of these statements in a *begin exception when no data found / too many rows end* statement or if any of the selects returns 0 or >1 rows the generic exception will be raised. Note that the exception currently is not re-raised so if the exception is thrown, a user will not see that. It will only be in the logs and the logic in the trigger will not be executed. This might not be the behaviour you want... — Koen Lostrie, Jul 15 '22 at 07:38
Please answer your own question or it keeps showing up as "answers: 0" and others will read it to see if they can answer. https://stackoverflow.com/help/self-answer — Koen Lostrie, Jul 15 '22 at 07:40

Oracle Trigger error 'Statement Ignored' and PLS-00405:sub-query not allowed in this context

2 Answers2