Home » SQL & PL/SQL » SQL & PL/SQL » Problem with loop (yes, I searched the forum) (Oracle 10g)
Problem with loop (yes, I searched the forum) [message #448159] Fri, 19 March 2010 20:33 Go to next message
dbmeany
Messages: 11
Registered: January 2010
Junior Member
This should be fairly direct but I continue to get compile errors attempting a While Loop. Should I being using a For Loop?

 
DECLARE
v_item VARCHAR2(20) := &v_item;
v_cost  vm_inventory.cost%TYPE;
v_expense CHAR(1) := '$';
loop_count Binary_Integer := 1000;
BEGIN
SELECT item, cost
INTO v_item, v_cost
FROM vm_inventory
WHERE item = v_item;
WHILE (v_cost/1000) <= v_cost LOOP 
v_expense := loop_count;
END LOOP;
dbms_output.put_line('Results: ' || loop_count);
END;
 
--If entered 'Tools', Results: $$$$...(16 $ signs)


Here is a sample table

CREATE TABLE vm_inventory
(cost NUMBER(5),
item VARCHAR2(20));
 
INSERT INTO vm_inventory VALUES
(12000, 'Auto Parts');
 
INSERT INTO vm_inventory VALUES
(10000, 'Electronics');
 
INSERT INTO vm_inventory VALUES
(16000, 'Tools');
 
INSERT INTO vm_inventory VALUES
(25000, 'Lawn and Garden');
 
Re: Problem with loop (yes, I searched the forum) [message #448161 is a reply to message #448159] Fri, 19 March 2010 20:44 Go to previous messageGo to next message
BlackSwan
Messages: 24906
Registered: January 2009
Senior Member
 1  DECLARE
  2  v_item VARCHAR2(20) := &v_item;
  3  v_cost  vm_inventory.cost%TYPE;
  4  v_expense CHAR(1) := '$';
  5  loop_count Binary_Integer := 1000;
  6  BEGIN
  7  SELECT item, cost
  8  INTO v_item, v_cost
  9  FROM vm_inventory
 10  WHERE item = v_item;
 11  WHILE (v_cost/1000) <= v_cost LOOP
 12  v_expense := loop_count;
 13  END LOOP;
 14  dbms_output.put_line('Results: ' || loop_count);
 15* END;
SQL> /
Enter value for v_item: 'Auto Parts' 
old   2: v_item VARCHAR2(20) := &v_item;
new   2: v_item VARCHAR2(20) := 'Auto Parts';
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 12


You have a RUN TIME error, because the LOOP never terminates
Re: Problem with loop (yes, I searched the forum) [message #448175 is a reply to message #448159] Sat, 20 March 2010 02:02 Go to previous messageGo to next message
Michel Cadot
Messages: 63805
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
WHILE (v_cost/1000) <= v_cost LOOP 
v_expense := loop_count;
END LOOP;

Quote:
(yes, I searched the forum)

Before searching in forum maybe you should look at and think about what you write.
How could v_cost/1000 be greater than v_cost?

Regards
Michel
Re: Problem with loop (yes, I searched the forum) [message #448195 is a reply to message #448175] Sat, 20 March 2010 07:06 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Michel Cadot wrote on Sat, 20 March 2010 02:02

How could v_cost/1000 be greater than v_cost?

It could if v_cost < 0, but in practice you are probably correct.
Add to that the fact that v_cost's value is not changed in the loop and you got yourself an infinite loop.
Re: Problem with loop (yes, I searched the forum) [message #448205 is a reply to message #448195] Sat, 20 March 2010 10:12 Go to previous messageGo to next message
Michel Cadot
Messages: 63805
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
It could if v_cost < 0

He he, I was waiting for this question... I never saw a negative cost but who knows in the earlier 90's Japan banks offer negative interest to boost economy.

Regards
Michel
Re: Problem with loop (yes, I searched the forum) [message #448226 is a reply to message #448159] Mon, 22 March 2010 04:19 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Notwithstanding the various problems that people have pointed out, your actual error happens because v_expense is a CHAR(1), and you are assigning a 4 digit number to it.
Previous Topic: Query for non nulls in a row
Next Topic: Function that multiple the number when there is more than 3 decimal point (mered by CM)
Goto Forum:
  


Current Time: Tue Sep 27 02:26:34 CDT 2016

Total time taken to generate the page: 0.08995 seconds