Oracle Cursor Loop in anonymous block giving PLS-00103 Error code-CodePudding

This SQL runs fine and it does everything I would like it to, except that I require it to output nothing but the result of the query. So, I have wrapped some code around it with the intent of doing so. See next block...

SELECT 
  'NO_PROBLEM' 
FROM 
  DUAL 
WHERE 
  NOT EXISTS (
    SELECT 
      UPPER(VALUE) 
    FROM 
      V$SYSTEM_PARAMETER 
    WHERE 
      UPPER(NAME)= 'O7_DICTIONARY_ACCESSIBILITY' 
      AND UPPER(VALUE) != 'FALSE'
  ) 
UNION ALL 
SELECT 
  'PROBLEM' 
FROM 
  DUAL 
WHERE 
  EXISTS (
    SELECT 
      UPPER(VALUE) 
    FROM 
      V$SYSTEM_PARAMETER 
    WHERE 
      UPPER(NAME)= 'O7_DICTIONARY_ACCESSIBILITY' 
      AND UPPER(VALUE) != 'FALSE'
  )

This is where I have added some wrapping

        DECLARE
          v_07_check VARCHAR2(10);
          
            CURSOR c_07_check IS

            SELECT 'NO_PROBLEM'
            FROM   dual
            WHERE  NOT EXISTS
                   (
                          SELECT upper(value)
                          FROM   v$system_parameter
                          WHERE  upper(name)='O7_DICTIONARY_ACCESSIBILITY'
                          AND    upper(value) != 'FALSE' )
            
UNION ALL

            SELECT 'PROBLEM'
            FROM   dual
            WHERE  EXISTS
                   (
                          SELECT upper(value)
                          FROM   v$system_parameter
                          WHERE  upper(name)='O7_DICTIONARY_ACCESSIBILITY'
                          AND    upper(value) != 'FALSE' ) 
    BEGIN OPEN c_07_check;
        
        LOOP
          FETCH c_07_check
          INTO  v_07_check;
          
          EXIT
        WHEN c_07_check%NOTFOUND;
          IF v_07_check = 'PROBLEM' THEN
            dbms_output.put_line('PROBLEM') )
        ELSIF v_07_check = 'NO_PROBLEM' THEN
          dbms_output.put_line('NO_PROBLEM')
        END IF;
        END LOOP;
        CLOSE C_07_CHECK;
        END;
        /

The error message outputted from the code is:

PLS-00103: Encountered the symbol "END" when expecting one of the following:

   := . ( % ;
The symbol ";" was substituted for "END" to continue.
ORA-06550: line 50, column 5:
PLS-00103: Encountered the symbol "CLOSE" 
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

The line it is complaining about is:

CLOSE c_07_check;

I have checked the documentation from oracle and I am not sure what is wrong here. I have tried moving the close statement to different points in the block but it does not resolve the issue. Can anyone make any suggestions as to where this is failing?

CodePudding user response：

Missing semicolons on 3 places (at the end of cursor declaration, behind dbms_output.put_line calls), superfluous closing bracket.

DECLARE
   v_07_check  VARCHAR2 (10);

   CURSOR c_07_check IS
      SELECT 'NO_PROBLEM'
        FROM DUAL
       WHERE NOT EXISTS
                (SELECT UPPER (VALUE)
                   FROM v$system_parameter
                  WHERE     UPPER (name) = 'O7_DICTIONARY_ACCESSIBILITY'
                        AND UPPER (VALUE) != 'FALSE')
      UNION ALL
      SELECT 'PROBLEM'
        FROM DUAL
       WHERE EXISTS
                (SELECT UPPER (VALUE)
                   FROM v$system_parameter
                  WHERE     UPPER (name) = 'O7_DICTIONARY_ACCESSIBILITY'
                        AND UPPER (VALUE) != 'FALSE');
BEGIN
   OPEN c_07_check;

   LOOP
      FETCH c_07_check INTO v_07_check;

      EXIT WHEN c_07_check%NOTFOUND;

      IF v_07_check = 'PROBLEM'
      THEN
         DBMS_OUTPUT.put_line ('PROBLEM');
      ELSIF v_07_check = 'NO_PROBLEM'
      THEN
         DBMS_OUTPUT.put_line ('NO_PROBLEM');
      END IF;
   END LOOP;

   CLOSE C_07_CHECK;
END;
/

Note that you could've shorten and simplify it if you used cursor FOR loop, e.g.

BEGIN
   FOR cur_r
      IN (SELECT 'NO_PROBLEM' v_07_check
            FROM DUAL
           WHERE NOT EXISTS
                    (SELECT UPPER (VALUE)
                       FROM v$system_parameter
                      WHERE     UPPER (name) = 'O7_DICTIONARY_ACCESSIBILITY'
                            AND UPPER (VALUE) != 'FALSE')
          UNION ALL
          SELECT 'PROBLEM'
            FROM DUAL
           WHERE EXISTS
                    (SELECT UPPER (VALUE)
                       FROM v$system_parameter
                      WHERE     UPPER (name) = 'O7_DICTIONARY_ACCESSIBILITY'
                            AND UPPER (VALUE) != 'FALSE'))
   LOOP
      DBMS_OUTPUT.put_line (cur_r.v_07_check);
   END LOOP;
END;

because - doing so - you don't have to declare cursor variable, open the cursor, fetch from it, take care about exiting the loop and close the cursor - Oracle does it for you.