PL/SQL Error With Nested IF statements

Question

Apex 4.2

I have the following pl/sql code block below but whenever I run it in APex, I keep getting the error:

ORA-06550: line 35, column 4: PLS-00103: Encountered the symbol ";" when expecting one of the following: if

DECLARE
   show_changes BOOLEAN;
   l_exists INTEGER;

BEGIN

Select count(*) into l_exists
From dba_role_privs
where grantee = upper(:APP_USER) and
(granted_role = 'SURVEY_JOB_SUID' or granted_role = 'SURVEY_SUID');

IF l_exists = 0 THEN            /* unauthorized */
   IF :P32_SURVEY_JOB_REQUEST_ID IS NOT NULL  AND (:P32_SUBMISSION_DATE IS NULL     AND :P32_LOCK_DATE IS NULL) THEN   
           show_changes := TRUE;
   IF :P32_SURVEY_JOB_REQUEST_ID IS NOT NULL AND (:P32_SUBMISSION_DATE IS NOT NULL AND :P32_LOCK_DATE IS NULL) THEN
      show_changes := TRUE;
   IF :P32_SURVEY_JOB_REQUEST_ID IS NOT NULL AND (:P32_SUBMISSION_DATE IS NOT NULL     AND :P32_LOCK_DATE IS NOT NULL) THEN
          show_changes := FALSE;    
END IF;
ELSE                          /* authorized */  
   IF :P32_SURVEY_JOB_REQUEST_ID IS NOT NULL  AND (:P32_SUBMISSION_DATE IS NULL AND :P32_LOCK_DATE IS NULL) THEN   
           show_changes := TRUE;
   IF :P32_SURVEY_JOB_REQUEST_ID IS NOT NULL AND (:P32_SUBMISSION_DATE IS NOT NULL AND :P32_LOCK_DATE IS NULL) THEN
      show_changes := TRUE;
   IF :P32_SURVEY_JOB_REQUEST_ID IS NOT NULL AND (:P32_SUBMISSION_DATE IS NOT NULL AND :P32_LOCK_DATE IS NOT NULL) THEN
      show_changes := TRUE;

END IF;
Return show_changes;
END;

I'm not sure what the problem is. Any help would be greatly appreciated. Thanks in advance.

You have the wrong syntax of IF-conditional selection statement. Please have a look to PL/SQL Control Statements. — user272735
– user272735, Commented Aug 23, 2013 at 18:36

Justin Cave · Accepted Answer · 2013-08-23 18:31:10Z

An IF always has to be paired with an END IF and the ELSE comes before the END IF. If you want to have three separate IF statements in each of two branches in your outer IF, you'd want something like

IF l_exists = 0
THEN
  IF :P32_SURVEY_JOB_REQUEST_ID IS NOT NULL  AND 
    (:P32_SUBMISSION_DATE IS NULL     AND :P32_LOCK_DATE IS NULL) 
  THEN   
           show_changes := TRUE;
  END IF;
  IF :P32_SURVEY_JOB_REQUEST_ID IS NOT NULL AND 
     (:P32_SUBMISSION_DATE IS NOT NULL AND :P32_LOCK_DATE IS NULL) 
  THEN
      show_changes := TRUE;
  END IF;
  IF :P32_SURVEY_JOB_REQUEST_ID IS NOT NULL AND 
     (:P32_SUBMISSION_DATE IS NOT NULL     AND :P32_LOCK_DATE IS NOT NULL) 
  THEN
          show_changes := FALSE;    
  END IF;
ELSE
  <<repeat the pattern>>
END IF;

My guess, however, is that you really don't want to have three separate IF statements in each branch. My guess is that you want a single IF statement with an ELSIF

IF l_exists = 0
THEN
  IF :P32_SURVEY_JOB_REQUEST_ID IS NOT NULL AND 
    (:P32_SUBMISSION_DATE IS NULL     AND :P32_LOCK_DATE IS NULL) 
  THEN   
           show_changes := TRUE;
  ELSIF :P32_SURVEY_JOB_REQUEST_ID IS NOT NULL AND 
       (:P32_SUBMISSION_DATE IS NOT NULL AND :P32_LOCK_DATE IS NULL) 
  THEN
      show_changes := TRUE;
  ELSIF :P32_SURVEY_JOB_REQUEST_ID IS NOT NULL AND 
       (:P32_SUBMISSION_DATE IS NOT NULL     AND :P32_LOCK_DATE IS NOT NULL) 
  THEN
          show_changes := FALSE;    
  END IF;
ELSE
  <<repeat the pattern>>
END IF;

Collectives™ on Stack Overflow

PL/SQL Error With Nested IF statements

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related