0


In Oracle SQL Developer, I'm declaring an INT variable, iterating over it in a WHILE loop, printing whether the variable's value is positive or negative, and incrementing the value in the loop like so:

SET DEFINE OFF -- required to prevent the "Enter Substitution Variable" dialog
DECLARE myNumber INT := 0; -- looks like Ada, or Algol 68 assignment...
SET myNumber := 1; -- ...why is SET required here?
BEGIN
  WHILE myNumber < 10 LOOP
    IF REMAINDER(myNumber, 2) = 0 THEN -- no remainder operator in PL/SQL?
      -- string concatenation is done with ||
      DBMS_OUTPUT.PUT_LINE('even number:  ' || myNumber || '.\n');
    ELSE
      DBMS_OUTPUT.PUT_LINE('odd number:  ' || myNumber || '.\n');
    END IF;

    myNumber := myNumber + 1; -- SET is not allowed here?
  END LOOP;
END;


...but it's not working and is throwing a compilation error on the variable's declaration:

Error report -
ORA-06550: line 2, column 5:
PLS-00488: 'MYNUMBER' must be a type
ORA-06550: line 2, column 5:
PL/SQL: Item ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:


I found reference to the error PLS-00488 here, but it's really not clear what the problem is...

7
  • shouldn't INT be INTEGER Commented Jul 19, 2014 at 3:27
  • @Satya -- INT is also valid, see docs.oracle.com/cd/B10500_01/appdev.920/a96624/… . I tried INTEGER instead, and I get the same compilation error though. Commented Jul 19, 2014 at 3:30
  • Did you try to put the variable declaration below the DECLARE statement? Commented Jul 19, 2014 at 3:39
  • @Abend -- do you mean the variable assignment? I tried replacing the declaration line with DECLARE myNumber INT; (removing the assignment from this line), but the same error is occurring though. Commented Jul 19, 2014 at 3:43
  • I was talking about writing the DECLARE statement in a line, and myNumber INT := 0; just below, not in the same line. Commented Jul 19, 2014 at 3:45

1 Answer 1

1

In PL/SQL, you don't use the SET operator for assignments. You very, very rarely use it to do things like setting the transaction isolation level in a PL/SQL block. If you're just learning PL/SQL, you can probably safely ignore the existence of SET for a few years.

You can't assign a value to a local variable in the DECLARE section of your block other than as part of the declaration of the variable. If you want myNumber to be initialized to 1, you'd need to specify 1 rather than 0 in the initialization section. If you want to set the value of the variable after initialization, you'd need to do that in the body of the block not in the declarations.

You also almost certainly don't want "\n" in your dbms_output.put_line calls. dbms_output.put_line implicitly includes the new line character. And "\n" is not interpreted as a new line, it's simply interpreted as two characters "\" and "n" so it prints the literal "\n" at the end of every line.

If you want to start from 0, you probably want something like

SQL> ed
Wrote file afiedt.buf

  1  DECLARE
  2    myNumber INT := 0; -- looks like Ada, or Algol 68 assignment...
  3  BEGIN
  4    WHILE myNumber < 10 LOOP
  5      IF REMAINDER(myNumber, 2) = 0 THEN -- no remainder operator in PL/SQL?
  6        -- string concatenation is done with ||
  7        DBMS_OUTPUT.PUT_LINE('even number:  ' || myNumber || '.');
  8      ELSE
  9        DBMS_OUTPUT.PUT_LINE('odd number:  ' || myNumber || '.');
 10      END IF;
 11      myNumber := myNumber + 1; -- SET is not allowed here?
 12    END LOOP;
 13* END;
SQL> /
even number:  0.
odd number:  1.
even number:  2.
odd number:  3.
even number:  4.
odd number:  5.
even number:  6.
odd number:  7.
even number:  8.
odd number:  9.

PL/SQL procedure successfully completed.

If you want to assign a value of 1 before you start your loop, you'd use the := assignment operator in the body of your block

SQL> ed
Wrote file afiedt.buf

  1  DECLARE
  2    myNumber INT := 0; -- looks like Ada, or Algol 68 assignment...
  3  BEGIN
  4    myNumber := 1;
  5    WHILE myNumber < 10 LOOP
  6      IF REMAINDER(myNumber, 2) = 0 THEN -- no remainder operator in PL/SQL?
  7        -- string concatenation is done with ||
  8        DBMS_OUTPUT.PUT_LINE('even number:  ' || myNumber || '.');
  9      ELSE
 10        DBMS_OUTPUT.PUT_LINE('odd number:  ' || myNumber || '.');
 11      END IF;
 12      myNumber := myNumber + 1; -- SET is not allowed here?
 13    END LOOP;
 14* END;
SQL> /
odd number:  1.
even number:  2.
odd number:  3.
even number:  4.
odd number:  5.
even number:  6.
odd number:  7.
even number:  8.
odd number:  9.

PL/SQL procedure successfully completed.
Sign up to request clarification or add additional context in comments.

2 Comments

A few questions -- when you refer to the "DECLARE section of the block", does this mean that DECLARE can encapsulate multiple statements? Also, where do you see the output in SQL Developer? All I am seeing is "anonymous block completed" in the script output window.
@IanCampbell - You can declare many variables, types, and even local procedures and functions between the DECLARE and the BEGIN. In SQL*Plus, you type "set serveroutput on size unlimited" to see output. In SQL Developer, you'd need to ensure that the DBMS Output window is visible (View | DBMS Output) and then enable output using the green plus sign in that window.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.