Solved

PL/SQL Cursor and output issue

Posted on 2004-04-20
10
3,135 Views
Last Modified: 2007-12-19
Here is my working program so far.

declare
   
v_value  number(8,2) := 0;
v_totalvalue  number(8,2) := 0;
v_itemid  ITEM.ITEM_ID%TYPE;  
v_itemdesc  ITEM.ITEM_DESC%TYPE;
v_itemsize  INVENTORY.ITEM_SIZE%TYPE;
v_color  INVENTORY.COLOR%TYPE;
v_price  INVENTORY.PRICE%TYPE;
v_qoh  INVENTORY.QOH%TYPE;

cursor itemid_cursor is
  select ITEM_ID, ITEM_DESC, ITEM_SIZE, COLOR, PRICE, QOH
  from ITEM natural join INVENTORY
  where ITEM_ID = v_itemid;

begin
     
select ITEM_ID
  into v_itemid
  from Phase2_Q1_Input
  where (ITEM_ID = 559);
   
dbms_output.put_line ('Item ID: ' || v_itemid);

open itemid_cursor;
     
loop
     
fetch itemid_cursor into v_itemid, v_itemdesc, v_itemsize, v_color, v_price, v_qoh;
v_value := v_price*v_qoh;
exit when itemid_cursor%NOTFOUND;

v_totalvalue := v_totalvalue + v_value ;
   
dbms_output.put_line (v_itemdesc || '  ' || v_itemsize || '   ' || v_color || '    ' ||

to_char(v_price, '$999.99') || '    ' || v_qoh || '    ' || to_char(V_Value, '$99,999.99'));
 
end loop;
   
dbms_output.put_line ('Total Value: ' || to_char(v_totalvalue, '$99,999.99'));  
     
end;
----------------------------------------------------------------------------------------------

Two things I am looking for are:

Phase2_Q1_Input has 4 different Item Id's loaded in the table.  I need to read each one of the item id's and output them like the single output I get from the above code.  Here it is:
-------------------------------------------------------------
Item ID: 559
Men's Expedition Parka  S   Spruce     $199.95    114     $22,794.30
Men's Expedition Parka  M   Spruce     $199.95    17      $3,399.15
Men's Expedition Parka  L   Spruce     $209.95    0           $.00
Men's Expedition Parka  XL   Spruce     $209.95    12      $2,519.40
Total Value:  $28,712.85
---------------------------------------------------------------

So the output should have 4 different Item Id's listed.

The second part of the problem is that 1 of the 4 Item Id's is invalid(not found in the original table) and the program needs to recognize this and output a statement that that Item Id is not found.

0
Comment
Question by:dcs001
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 1

Expert Comment

by:ora-dba
ID: 10870270
Hello,
please try the following code:
declare
   
v_value  number(8,2) := 0;
v_totalvalue  number(8,2) := 0;
v_itemid  ITEM.ITEM_ID%TYPE;  
v_itemdesc  ITEM.ITEM_DESC%TYPE;
v_itemsize  INVENTORY.ITEM_SIZE%TYPE;
v_color  INVENTORY.COLOR%TYPE;
v_price  INVENTORY.PRICE%TYPE;
v_qoh  INVENTORY.QOH%TYPE;

cursor Phase2 is
  select ITEM_ID
  from Phase2_Q1_Input;
 
cursor itemid_cursor(itemid ITEM.ITEM_ID%TYPE) is
  select ITEM_ID, ITEM_DESC, ITEM_SIZE, COLOR, PRICE, QOH
  from ITEM natural join INVENTORY
  where ITEM_ID = itemid;

begin

for c in Plase2 loop
   dbms_output.put_line ('Item ID: ' || c.itemid);
   open itemid_cursor(c.itemid);
   loop
     fetch itemid_cursor(c.itemid) into v_itemid, v_itemdesc, v_itemsize, v_color, v_price, v_qoh;
     v_value := v_price*v_qoh;
     exit when itemid_cursor%NOTFOUND;

     if itemid_cursor%ROWCOUNT = 0 then
        dbms_output.put_line('Invalid item id');
     else

         v_totalvalue := v_totalvalue + v_value ;
         dbms_output.put_line (v_itemdesc || '  ' || v_itemsize || '   ' || v_color || '    ' ||
         to_char(v_price, '$999.99') || '    ' || v_qoh || '    ' || to_char(V_Value, '$99,999.99'));
     end if;
   end loop;
   close itemid_cursor;
   dbms_output.put_line ('Total Value: ' || to_char(v_totalvalue, '$99,999.99'));  
end loop;    
end;
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 10870823
@dcs001:

your code has done the first part, pulling the four items of one Item ID, right?
the second part is very easy:

just do this at the end of the cursor loop:
-----------------------------------------------------------------------------------------------------------------
loop
   
fetch itemid_cursor into v_itemid, v_itemdesc, v_itemsize, v_color, v_price, v_qoh;
v_value := v_price*v_qoh;
exit when itemid_cursor%NOTFOUND;

v_totalvalue := v_totalvalue + v_value ;
   
dbms_output.put_line (v_itemdesc || '  ' || v_itemsize || '   ' || v_color || '    ' ||

to_char(v_price, '$999.99') || '    ' || v_qoh || '    ' || to_char(V_Value, '$99,999.99'));

end loop;
   
dbms_output.put_line ('Total Value: ' || to_char(v_totalvalue, '$99,999.99'));  
   
end;

---------------------------------------------------------------------------------------------------------------

--Add these three lines



if (itemid_cursor%ROWCOUNT <4) then
dbms_output.print_line ('this item id '||  v_itemid || ' has less than 4 items');

close itemid_cursor;




0
 
LVL 23

Expert Comment

by:seazodiac
ID: 10870828
sorry it should be four lines:

if (itemid_cursor%ROWCOUNT <4) then
dbms_output.print_line ('this item id '||  v_itemid || ' has less than 4 items');
end if;
close itemid_cursor;

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:dcs001
ID: 10871394
I get this error from ora-dba response:

ERROR at line 27:
ORA-06550: line 27, column 25:
PLS-00103: Encountered the symbol "(" when expecting one of the following:
. into bulk

Seazodiac, your response did not output all of the item id's from the Phase2_Q1_Input table, only the first one.  There are 4 item id's in this table, one of them is invalid.
0
 

Author Comment

by:dcs001
ID: 10872061
seazodiac said, "your code has done the first part, pulling the four items of one Item ID, right?"

I am actually trying to pull four ITEM ID's from an existing table with many Item ID's.
0
 

Author Comment

by:dcs001
ID: 10872297
Output should look like this:
----------------------------------
Item ID: 559
Men's Expedition Parka  S   Spruce     $199.95    114     $22,794.30
Men's Expedition Parka  M   Spruce     $199.95    17      $3,399.15
Men's Expedition Parka  L   Spruce     $209.95    0           $.00
Men's Expedition Parka  XL   Spruce     $209.95    12      $2,519.40
Total Value:  $28,712.85

Item ID: 894
Women's Hiking Shorts  S   Khaki      $29.95    150      $4,492.50
Women's Hiking Shorts  M   Khaki      $29.95    147      $4,402.65
Women's Hiking Shorts  L   Khaki      $29.95    0           $.00
Women's Hiking Shorts  S   Navy      $29.95    139      $4,163.05
Women's Hiking Shorts  M   Navy      $29.95    137      $4,103.15
Women's Hiking Shorts  L   Navy      $29.95    115      $3,444.25
Total Value:  $20,605.60

Item ID: 897
Women's Fleece Pullover  S   Eggplant      $59.95    135      $8,093.25
Women's Fleece Pullover  M   Eggplant      $59.95    168     $10,071.60
Women's Fleece Pullover  L   Eggplant      $59.95    187     $11,210.65
Women's Fleece Pullover  S   Royal      $59.95    0           $.00
Women's Fleece Pullover  M   Royal      $59.95    124      $7,433.80
Women's Fleece Pullover  L   Royal      $59.95    112      $6,714.40
Total Value:  $43,523.70

Item ID: 111 is not found in the database
----------------------------------------------------

This is where i'm having trouble.  it works for one item id when defined this way:
select ITEM_ID
  into v_itemid
  from Phase2_Q1_Input;
  where (ITEM_ID = 894);

I need to get them all at the same time, and produce an error message when an item number is not found.
0
 
LVL 1

Expert Comment

by:ora-dba
ID: 10872438
I'm sorry the following code should not give you an error:
declare
   
v_value  number(8,2) := 0;
v_totalvalue  number(8,2) := 0;
v_itemid  ITEM.ITEM_ID%TYPE;  
v_itemdesc  ITEM.ITEM_DESC%TYPE;
v_itemsize  INVENTORY.ITEM_SIZE%TYPE;
v_color  INVENTORY.COLOR%TYPE;
v_price  INVENTORY.PRICE%TYPE;
v_qoh  INVENTORY.QOH%TYPE;

cursor Phase2 is
  select ITEM_ID
  from Phase2_Q1_Input;
 
cursor itemid_cursor(itemid ITEM.ITEM_ID%TYPE) is
  select ITEM_ID, ITEM_DESC, ITEM_SIZE, COLOR, PRICE, QOH
  from ITEM natural join INVENTORY
  where ITEM_ID = itemid;

begin

for c in Plase2 loop
   dbms_output.put_line ('Item ID: ' || c.itemid);
   open itemid_cursor(c.itemid);
   loop
     fetch itemid_cursor into v_itemid, v_itemdesc, v_itemsize, v_color, v_price, v_qoh;
     v_value := v_price*v_qoh;
     exit when itemid_cursor%NOTFOUND;

     if itemid_cursor%ROWCOUNT = 0 then
        dbms_output.put_line('Invalid item id');
     else

         v_totalvalue := v_totalvalue + v_value ;
         dbms_output.put_line (v_itemdesc || '  ' || v_itemsize || '   ' || v_color || '    ' ||
         to_char(v_price, '$999.99') || '    ' || v_qoh || '    ' || to_char(V_Value, '$99,999.99'));
     end if;
   end loop;
   close itemid_cursor;
   dbms_output.put_line ('Total Value: ' || to_char(v_totalvalue, '$99,999.99'));  
end loop;    
end;
0
 

Author Comment

by:dcs001
ID: 10873410
I get this erro with your code.

ERROR at line 20:
ORA-06550: line 20, column 19:
PL/SQL: ORA-00904: "ITEMID": invalid identifier
ORA-06550: line 18, column 3:
PL/SQL: SQL Statement ignored
ORA-06550: line 25, column 43:
PLS-00302: component 'ITEMID' must be declared
ORA-06550: line 25, column 4:
PL/SQL: Statement ignored
ORA-06550: line 26, column 25:
PLS-00302: component 'ITEMID' must be declared
ORA-06550: line 26, column 4:
PL/SQL: SQL Statement ignored
0
 
LVL 8

Expert Comment

by:annamalai77
ID: 10874921
hi

the below method should solve ur problem.
i assume that ur itemid is varchar field. in case it is number
then change according and in the dbms line
use to_char(i.itemid).

declare
v_value  number(8,2) := 0;
v_totalvalue  number(8,2) := 0;
v_itemid  ITEM.ITEM_ID%TYPE;  
v_itemdesc  ITEM.ITEM_DESC%TYPE;
v_itemsize  INVENTORY.ITEM_SIZE%TYPE;
v_color  INVENTORY.COLOR%TYPE;
v_price  INVENTORY.PRICE%TYPE;
v_qoh  INVENTORY.QOH%TYPE;

cursor c1 is
select ITEM_ID
  from Phase2_Q1_Input
order by item_id;
   
cursor itemid_cursor (v_itemid in number) is
  select ITEM_ID, ITEM_DESC, ITEM_SIZE, COLOR, PRICE, QOH
  from ITEM natural join INVENTORY
  where ITEM_ID = v_itemid;

begin
for i in c1
loop
  dbms_output.put_line ('Item ID: ' || i.itemid);
  for j in  itemid_cursor(i.itemid)
  loop
     v_itemid := j.itemid;
     v_itemdesc := j.item_desc;
     v_itemsize := j.item_size;
     v_color := j.color;
     v_price := j.price;
     v_qoh   := j.qoh;

     v_value := v_price*v_qoh;

     v_totalvalue := v_totalvalue + v_value ;
   
     dbms_output.put_line (v_itemdesc || '  ' || v_itemsize   || '   ' || v_color || '    ' || to_char(v_price, '$999.99') || '    ' || v_qoh || '    ' || to_char(V_Value, '$99,999.99'));
  end loop;
   
dbms_output.put_line ('Total Value: ' || to_char(v_totalvalue, '$99,999.99'));  

v_value := 0;
v_totalvalue := 0;
end loop;
end;
/

regards
annamalai
0
 
LVL 1

Accepted Solution

by:
ora-dba earned 250 total points
ID: 10876594
I'm sorry again but I don't have your table structure. Please try this:

declare
   
v_value  number(8,2) := 0;
v_totalvalue  number(8,2) := 0;
v_itemid  ITEM.ITEM_ID%TYPE;  
v_itemdesc  ITEM.ITEM_DESC%TYPE;
v_itemsize  INVENTORY.ITEM_SIZE%TYPE;
v_color  INVENTORY.COLOR%TYPE;
v_price  INVENTORY.PRICE%TYPE;
v_qoh  INVENTORY.QOH%TYPE;

cursor Phase2 is
  select ITEM_ID
  from Phase2_Q1_Input;
 
cursor itemid_cursor(itemid ITEM.ITEM_ID%TYPE) is
  select ITEM_ID, ITEM_DESC, ITEM_SIZE, COLOR, PRICE, QOH
  from ITEM natural join INVENTORY
  where ITEM_ID = itemid;

begin

for c in Plase2 loop
   dbms_output.put_line ('Item ID: ' || c.item_id);
   open itemid_cursor(c.item_id);
   loop
     fetch itemid_cursor into v_itemid, v_itemdesc, v_itemsize, v_color, v_price, v_qoh;
     v_value := v_price*v_qoh;
     exit when itemid_cursor%NOTFOUND;

     if itemid_cursor%ROWCOUNT = 0 then
        dbms_output.put_line('Invalid item id');
     else

         v_totalvalue := v_totalvalue + v_value ;
         dbms_output.put_line (v_itemdesc || '  ' || v_itemsize || '   ' || v_color || '    ' ||
         to_char(v_price, '$999.99') || '    ' || v_qoh || '    ' || to_char(V_Value, '$99,999.99'));
     end if;
   end loop;
   close itemid_cursor;
   dbms_output.put_line ('Total Value: ' || to_char(v_totalvalue, '$99,999.99'));  
end loop;    
end;
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  â€¦
Via a live example, show how to take different types of Oracle backups using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question