Solved

PL/SQL Cursor and output issue

Posted on 2004-04-20
10
3,129 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
Comment Utility
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
Comment Utility
@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
Comment Utility
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
 

Author Comment

by:dcs001
Comment Utility
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
Comment Utility
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
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Oracle PL/SQL syntax 4 49
Oracle - Stored Procedure Privilge access 3 22
dates - loop 12 38
SQL Retrieve Values 4 37
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…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
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
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

763 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

Need Help in Real-Time?

Connect with top rated Experts

5 Experts available now in Live!

Get 1:1 Help Now