Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

PL/SQL Cursor and output issue

Posted on 2004-04-20
10
Medium Priority
?
3,156 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

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 750 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to take different types of Oracle backups using RMAN.

670 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