Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Learn more about Collectives

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Learn more about Teams 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');
      DBMS_OUTPUT.PUT_LINE('odd number:  ' || myNumber || '.\n');
    END IF;
    myNumber := myNumber + 1; -- SET is not allowed here?
  END LOOP;
...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...

                @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.
– Ian Campbell
                Jul 19, 2014 at 3:30
                @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.
– Ian Campbell
                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.
– Abend
                Jul 19, 2014 at 3:45
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.
                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.
– Ian Campbell
                Jul 19, 2014 at 4:44
                @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.
– Justin Cave
                Jul 19, 2014 at 4:47
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.