Home » SQL & PL/SQL » SQL & PL/SQL » syntax loop error
syntax loop error [message #216560] Mon, 29 January 2007 07:16 Go to next message
emadbsb
Messages: 334
Registered: May 2005
Location: egypt
Senior Member

Hii all

i have an error in the next code i think it is in the syntax of the loop
DECLARE
I VARCHAR2(10) := '0000'
BEGIN
WHILE I <= '0185' LOOP
INSERT INTO CUS_SER VALUES (I);
I := I +1;
END LOOP;
END;


Thanks for everyone helped and helping me
Re: syntax loop error [message #216561 is a reply to message #216560] Mon, 29 January 2007 07:23 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Actually, you miss semi-colon at the end of the variable declaration. After you fix it, the code will execute, but not the way you wanted it to - it will insert only '0000' into a table and exit the loop.

To insert all 186 records, you'll have to work with numbers:
DECLARE
  I NUMBER := 0;
BEGIN
  WHILE I <= 185 LOOP
    INSERT INTO CUS_SER VALUES (LPAD(I, 4, '0'));
    I := I +1;
  END LOOP;
END;
Re: syntax loop error [message #216562 is a reply to message #216561] Mon, 29 January 2007 07:37 Go to previous messageGo to next message
emadbsb
Messages: 334
Registered: May 2005
Location: egypt
Senior Member

Thanks for your reply
can you another version of that code

DECLARE
J CHAR(4) := '0000';
BEGIN
FOR  I IN 1..10 LOOP
INSERT INTO CUS_SER VALUES (J);
J := J +1;
END LOOP;
END;



after executing that code the table now contain
number of

0000
1
2
3
4
5
6
7
8
9
10


i want the inserted will be like
0000
0001
0002
0003
0004
0005
0006
0007
0008
0009
0010


alter the argument "code" is of type "varchar2"

can i do this ??
Re: syntax loop error [message #216565 is a reply to message #216562] Mon, 29 January 2007 07:45 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Look into the command LPAD

Doh, beaten out by Littlefoot again.

And then, the Littlefoot message disappeared.

[Updated on: Mon, 29 January 2007 07:46]

Report message to a moderator

Re: syntax loop error [message #216568 is a reply to message #216565] Mon, 29 January 2007 07:55 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
LOL, now you see me, and now you don't Smile

LPAD won't do much good here because he declared J as CHAR so char/number arithmetic fails. Therefore, it needs to be

(LPAD(TO_NUMBER(j), 4, '0'))

Sorry for being inconsistent, Joy! I just wanted to double-check it (because not once I got on my fingers for not testing what I've suggested as a solution to the problem).

[Updated on: Mon, 29 January 2007 07:57]

Report message to a moderator

Re: syntax loop error [message #216616 is a reply to message #216562] Mon, 29 January 2007 13:33 Go to previous message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
Why on Earth would you use a CHAR anyway? Not only is CHAR essentially evil, but it makes no sense to declare a variable of any sort of character string and then do arithmetic on it. Use INTEGER, NUMBER, PLS_INTEGER etc as needed.
Previous Topic: Retain the value from the prev. row / returning clause (merged)
Next Topic: Listing tables and their contents (merged)
Goto Forum:
  


Current Time: Tue Dec 06 12:19:52 CST 2016

Total time taken to generate the page: 0.10202 seconds