Solved

PL/SQL Cursor and output issue

Posted on 2004-04-20
10
3,147 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Veeam gives away 10 full conference passes

Veeam is a VMworld 2017 US & Europe Platinum Sponsor. Enter the raffle to get the full conference pass. Pass includes the admission to all general and breakout sessions, VMware Hands-On Labs, Solutions Exchange, exclusive giveaways and the great VMworld Customer Appreciation Part

 

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

Veeam gives away 10 full conference passes

Veeam is a VMworld 2017 US & Europe Platinum Sponsor. Enter the raffle to get the full conference pass. Pass includes the admission to all general and breakout sessions, VMware Hands-On Labs, Solutions Exchange, exclusive giveaways and the great VMworld Customer Appreciation Part

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that useā€¦
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

635 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