Home » SQL & PL/SQL » SQL & PL/SQL » procedure probleam
procedure probleam [message #415827] Wed, 29 July 2009 08:16 Go to next message
vodlink
Messages: 24
Registered: July 2009
Junior Member
hello i have to create a procedure that will output an itemised bill for any given order#. for example the output should look like this

6 portions of steak and chips at 14 pounds : 84 pounds
4 portions of pasta bake at 6 pounds :24 pounds
3 portions of stuffed peppers at 11.50 pounds: 34.5 pounds

total food cost for order 000001:142.50 pounds

13 units of water at 1 pound: 13 pounds
13 units of coffee at 1.70 pounds: 22.1 pounds
13 units of beer at 2.30 pounds: 29.90 pounds

total drink cost for order 000001: 65 pounds

total cost for order 000001: 207.50 pounds

The example above is not actully the result for order# 000001


heres is the database information
create table Customer_order( order# NUMBER primary key , 
date_order    DATE,
  date_required DATE,
  address       VARCHAR2(30)
  );

CREATE  TABLE Food_order (
  food_order# VARCHAR2(7) primary key,
  order#      number references Customer_order (order#) ,
  dish#       VARCHAR2(5) references Dish(Dish#),
  n_portions  NUMBER);

CREATE  TABLE Drink_order (
  drink_order# VARCHAR2(5)primary key,
  order#       number references Customer_order (order#),
  drink#       VARCHAR2(6)references drink (drink#),
  n_units      NUMBER);

CREATE  TABLE Dish (
  dish#      VARCHAR2(5) primary key,
  dish_name  CHAR(15),
  vegetarian CHAR(3),
  price      NUMBER);

CREATE  TABLE Drink (
  drink#     VARCHAR2(6) primary key,
  drink_name CHAR(6),
  drink_type CHAR(9),
  price      NUMBER);

Insert into customer_order values ('00001', '03-Apr-09', '07-apr-09','St. Andrew St'); 
Insert into customer_order values ('00002', '05-Apr-09', '01-May-09', 'St. Andrew St');
Insert into customer_order values ('00003', '12-Apr-09', '27-Apr-09', 'Union St');
Insert into customer_order values ('00004', '12-Apr-09', '17-Apr-09', 'St. Andrew St');

Insert into Dish values ('D0001', 'Pasta bake',      'yes', '6.00'); 
Insert into Dish values ('D0002', 'Fish pie',        'no',  '9.00');  
Insert into Dish values ('D0003', 'Steak and chips', 'no',  '14.00');   
Insert into Dish values ('D0004', 'Stuffed peppers', 'yes', '11.50');   
Insert into Dish values ('D0005', 'Ham and rice'   , 'no',  '7.25');  
Insert into Dish values ('D0006', 'Lamb curry'     , 'no',  '8.50'); 

Insert into Drink values ('DR0001', 'Water',  'soft',      '1.0');
Insert into Drink values ('DR0002', 'Coffee', 'hot',       '1.70');
Insert into Drink values ('DR0003', 'Wine'  , 'alcoholic', '3.00'); 
Insert into Drink values ('DR0004', 'Beer'  , 'alcoholic', '2.30');  
Insert into Drink values ('DR0005', 'Tea'   , 'hot'     ,  '1.50');   

Insert into food_order values ('F000001', '000001', 'D0003', '6');
Insert into food_order values ('F000002', '000001', 'D0001', '4');
Insert into food_order values ('F000003', '000001', 'D0004', '3');
Insert into food_order values ('F000004', '000002', 'D0001', '10');
Insert into food_order values ('F000005', '000002', 'D0002', '10');
Insert into food_order values ('F000006', '000003', 'D0002', '35');
Insert into food_order values ('F000007', '000004', 'D0002', '23');

Insert into drink_order values ('D000001', '000001', 'DR0001', '13');
Insert into drink_order values ('D000002', '000001', 'DR0002', '13');
Insert into drink_order values ('D000003', '000001', 'DR0004', '13');
Insert into drink_order values ('D000004', '000002', 'DROOO1', '20');
Insert into drink_order values ('D000005', '000002', 'DR0003', '20');
Insert into drink_order values ('D000006', '000002', 'DR0004', '15');
Insert into drink_order values ('D000007', '000003', 'DR0002', '35');
Insert into drink_order values ('D000008', '000004', 'DR0001', '23'); 
Insert into drink_order values ('D000009', '000004', 'DR0003', '15');
Insert into drink_order values ('D0000010', '000004', 'DR0004', '15');


Any help welcome.
Re: procedure probleam [message #415830 is a reply to message #415827] Wed, 29 July 2009 08:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://forums.oracle.com/forums/thread.jspa?messageID=3656174

Quote:
Show us your best try and expain what problems you got.


In addition, why '00001' for a number?

Regards
Michel

[Updated on: Wed, 29 July 2009 08:27]

Report message to a moderator

Re: procedure probleam [message #415831 is a reply to message #415827] Wed, 29 July 2009 08:23 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Why do you want to "write a procedure" for everything that is normally done by a report or a spooled SQL query?
Re: procedure probleam [message #415838 is a reply to message #415831] Wed, 29 July 2009 09:06 Go to previous messageGo to next message
vodlink
Messages: 24
Registered: July 2009
Junior Member
ThomasG wrote on Wed, 29 July 2009 14:23
Why do you want to "write a procedure" for everything that is normally done by a report or a spooled SQL query?


for this it just has to be written as a procedure and all i got so far is
create or replace procedure item_bill
(pr_order# in out customer_order.order#
n.n_portions out food_order. n_portions
di.fname     out dish.dish_name 
di.price     out dish.dish_price
dro. n_units out drink_order.n_units
dr.dname     out drink.drink_name
dr.price     out drink.price);
is
begin
select n_portions, dish_name,di.price,n_units, drink_name,dr.price  
from food_order, dish di, drink_order, drink dr
where
pr_order# = n_portions
and



even if i complted the code i dont think it would work but this is my best so far.
Re: procedure probleam [message #415839 is a reply to message #415838] Wed, 29 July 2009 09:10 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:
for this it just has to be written as a procedure


Why? To what medium does the "output" finally happen?

Re: procedure probleam [message #415840 is a reply to message #415839] Wed, 29 July 2009 09:14 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Or, to clarify, I know at least half a dozen different methods to "output" something from a procedure, so you need to specify where the "output" finally has to end up.
Re: procedure probleam [message #415841 is a reply to message #415839] Wed, 29 July 2009 09:16 Go to previous messageGo to next message
vodlink
Messages: 24
Registered: July 2009
Junior Member
ThomasG wrote on Wed, 29 July 2009 15:10
Quote:
for this it just has to be written as a procedure


Why? To what medium does the "output" finally happen?



The reason it has be done as a procedure is because this is what has been asked of me and not as SQL query.
the final output has be soming like this shows as a message

6 portions of steak and chips at 14 pounds : 84 pounds
4 portions of pasta bake at 6 pounds :24 pounds
3 portions of stuffed peppers at 11.50 pounds: 34.5 pounds

total food cost for order 000001:142.50 pounds

13 units of water at 1 pound: 13 pounds
13 units of coffee at 1.70 pounds: 22.1 pounds
13 units of beer at 2.30 pounds: 29.90 pounds

total drink cost for order 000001: 65 pounds

total cost for order 000001: 207.50 pounds

[Updated on: Wed, 29 July 2009 09:18]

Report message to a moderator

Re: procedure probleam [message #415846 is a reply to message #415841] Wed, 29 July 2009 09:23 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Well, you can't "show a message" from a procedure, since it runs on the server.

Any "output" method would depend on the method you need to DO the output with.
Re: procedure probleam [message #415850 is a reply to message #415827] Wed, 29 July 2009 09:28 Go to previous messageGo to next message
vodlink
Messages: 24
Registered: July 2009
Junior Member
if it helps am working in sql developer.
when i mean output i mean it should appear in the results or script output area of sql developer. just like my function does.
Re: procedure probleam [message #415858 is a reply to message #415850] Wed, 29 July 2009 09:52 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
That doesn't work for any real-live scenario, so it's homework.

In that case have a look at DBMS_OUTPUT and Loop statements.
Re: procedure probleam [message #415871 is a reply to message #415858] Wed, 29 July 2009 10:30 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Your datamodel is suboptimal as well:
- Do not use the CHAR datatype, use VARCHAR2
- It does not make sense to make the PK for the order tables strings, make them numbers, like the main parent table
- You create references to the Drink and Food tables before you actually create these
- Don't use special characters (like #) in column names, especially if the columns do not contain numbers.
- why the n_ prefix for the number of portions/drinks?
Re: procedure probleam [message #415878 is a reply to message #415827] Wed, 29 July 2009 10:48 Go to previous messageGo to next message
vodlink
Messages: 24
Registered: July 2009
Junior Member
what if i had soming like this as part of the procedure
create or replace procedure Item_bill
DECLARE
sSQLstr VARCHAR2(1000);
type refcur is ref cursor;
rcur refcur;
stmt1 VARCHAR2(300);
BEGIN

sSQLstr := 'select t.n_portions||'' portions of ''||p.dish_name||'' at '' ||p. price|| '' pounds :''||
t.n_portions*p. price
from Customer_order s,food_order t, Dish p
where s.order#=t.order#
and t.dish#=p.dish#';


OPEN rcur FOR sSQLstr;
LOOP
FETCH rcur
INTO stmt1;
EXIT WHEN rcur%NOTFOUND;
dbms_output.put_line(stmt1);


END LOOP;
END;


when i run it in the script output area i get this
Error starting at line 1 in command:
create or replace Item_bill
DECLARE
sSQLstr VARCHAR2(1000)
Error at Command Line:1 Column:18
Error report:
SQL Error: ORA-00922: missing or invalid option
00922. 00000 -  "missing or invalid option"
*Cause:    
*Action:

Error starting at line 4 in command:
type refcur is ref cursor;
Error report:
Unknown Command

Error starting at line 5 in command:
rcur refcur;
Error report:
Unknown Command

Error starting at line 6 in command:
stmt1 VARCHAR2(300);
Error report:
Unknown Command

Error starting at line 7 in command:
BEGIN

sSQLstr := 'select t.n_portions||'' portions of ''||p.dish_name||'' at '' ||p. price|| '' pounds :''||
t.n_portions*p. price
from Customer_order s,food_order t, Dish p
where s.order#=t.order#
and t.dish#=p.dish#';


OPEN rcur FOR sSQLstr;
LOOP
FETCH rcur
INTO stmt1;
EXIT WHEN rcur%NOTFOUND;
dbms_output.put_line(stmt1);


END LOOP;
END;
Error report:
ORA-06550: line 3, column 1:
PLS-00201: identifier 'SSQLSTR' must be declared
ORA-06550: line 3, column 1:
PL/SQL: Statement ignored
ORA-06550: line 10, column 6:
PLS-00201: identifier 'RCUR' must be declared
ORA-06550: line 10, column 1:
PL/SQL: Statement ignored
ORA-06550: line 12, column 7:
PLS-00201: identifier 'RCUR' must be declared
ORA-06550: line 12, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 14, column 11:
PLS-00201: identifier 'RCUR' must be declared
ORA-06550: line 14, column 1:
PL/SQL: Statement ignored
ORA-06550: line 15, column 22:
PLS-00201: identifier 'STMT1' must be declared
ORA-06550: line 15, column 1:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:
Re: procedure probleam [message #415880 is a reply to message #415827] Wed, 29 July 2009 11:06 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
your quotes are wrong for sSQLstr.
Re: procedure probleam [message #415884 is a reply to message #415880] Wed, 29 July 2009 11:17 Go to previous messageGo to next message
vodlink
Messages: 24
Registered: July 2009
Junior Member
cookiemonster wrote on Wed, 29 July 2009 17:06
your quotes are wrong for sSQLstr.

What should the quotes be then?
Re: procedure probleam [message #415885 is a reply to message #415880] Wed, 29 July 2009 11:22 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
UGHHHHH!! Stay AWAY from dynamic SQL. If you are using dynamic SQL, you are doing it wrong 99.9999999% of the time. And it is completely impossible to debug.

Just use a simple standard implicit cursor loop instead.

SQL> set serverout on
SQL> BEGIN
  2   FOR l IN (
  3      SELECT ROWNUM rn FROM dual CONNECT BY level < 10
  4   ) LOOP
  5
  6      Dbms_Output.put_line('This is row ' || l.rn);
  7
  8   END LOOP;
  9  END;
 10  /
This is row 1
This is row 2
This is row 3
This is row 4
This is row 5
This is row 6
This is row 7
This is row 8
This is row 9

PL/SQL procedure successfully completed.

SQL>


Re: procedure probleam [message #415887 is a reply to message #415827] Wed, 29 July 2009 11:30 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
On second look the quotes are right - it's the ref cursor declaration that it's complaining about.

You also appear to be running it in an unusual way, stuff like this:
Error starting at line 4 in command:
type refcur is ref cursor;
Error report:
Unknown Command


is not standard output.

But I agree with ThomasG - you've got absolutely no need for dynamic sql or ref cursors here - so don't use them.
Re: procedure probleam [message #415890 is a reply to message #415827] Wed, 29 July 2009 11:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your test case is not valid, please correct it.
Tables are not in the correct order.
Fields or data length are not compatible.
Reference constraint is not satisfied.

Regards
Michel
Re: procedure probleam [message #416036 is a reply to message #415827] Thu, 30 July 2009 04:30 Go to previous messageGo to next message
vodlink
Messages: 24
Registered: July 2009
Junior Member
how about this for a procedure.
create or replace procedure Item_bill
is
sSQLstr VARCHAR2(1000);
type refcur is ref cursor;
rcur refcur;
stmt1 VARCHAR2(300);
BEGIN 
sSQLstr := 'select t.n_portions||'' portions of ''||p.dish_name||'' at '' ||p. price|| '' pounds :''||
t.n_portions*p. price
from Customer_order s,food_order t, Dish p
where s.order#=t.order#
and t.dish#=p.dish#'; 
OPEN rcur FOR sSQLstr;
LOOP
FETCH rcur
INTO stmt1;
EXIT WHEN rcur%NOTFOUND;
dbms_output.put_line(stmt1);
END LOOP;
END;

now all i get is
 Warning: execution completed with warning
procedure Item_bill Compiled. 

Re: procedure probleam [message #416039 is a reply to message #415827] Thu, 30 July 2009 04:33 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Use sqlplus to create the procedure.
Re: procedure probleam [message #416040 is a reply to message #415827] Thu, 30 July 2009 04:35 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
And get rid of the dynamic SQL you don't need it.
Re: procedure probleam [message #416064 is a reply to message #416040] Thu, 30 July 2009 05:41 Go to previous messageGo to next message
vodlink
Messages: 24
Registered: July 2009
Junior Member
i got it sort of working i dont get
 Warning: execution completed with warning
procedure Item_bill Compiled. 

now i just get
procedure Item_bill Compiled 


but it still dont give any output.

[Updated on: Thu, 30 July 2009 05:42]

Report message to a moderator

Re: procedure probleam [message #416067 is a reply to message #416064] Thu, 30 July 2009 06:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry you didn't make the effort to fix your test case, I could help you.

Regards
Michel
Re: procedure probleam [message #416074 is a reply to message #416067] Thu, 30 July 2009 06:15 Go to previous messageGo to next message
vodlink
Messages: 24
Registered: July 2009
Junior Member
Michel Cadot wrote on Thu, 30 July 2009 12:00
Sorry you didn't make the effort to fix your test case, I could help you.

Regards
Michel


i fixed it it works now thank you anyway.
Re: procedure probleam [message #416100 is a reply to message #415827] Thu, 30 July 2009 08:35 Go to previous messageGo to next message
vodlink
Messages: 24
Registered: July 2009
Junior Member
I tryed this
create or replace procedure Item_bill
is
   sSQLstr VARCHAR2(1000);
   type refcur is ref cursor;
   rcur refcur;
   stmt1 VARCHAR2(300);
BEGIN 
   for rec in (select t.n_portions||'' portions of ''||p.dish_name||'' at '' ||p. price|| '' pounds :''||
                  t.n_portions*p.price  stmt
                  from Customer_order s,food_order t, Dish p
                  where s.order#=t.order#
                  and t.dish#=p.dish#)
   loop
      dbms_output.put_line(rec.stmt);   
   END LOOP;
END;




now i get
Warning: execution completed with warning
procedure Item_bill Compiled.
Re: procedure probleam [message #416101 is a reply to message #416074] Thu, 30 July 2009 08:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
vodlink wrote on Thu, 30 July 2009 13:15
Michel Cadot wrote on Thu, 30 July 2009 12:00
Sorry you didn't make the effort to fix your test case, I could help you.

Regards
Michel


i fixed it it works now thank you anyway.

Good it works for you and sorry you didn't post it for us.

Regards
Michel

Re: procedure probleam [message #416102 is a reply to message #416100] Thu, 30 July 2009 08:42 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
vodlink wrote on Thu, 30 July 2009 14:35
I tryed this
create or replace procedure Item_bill
is
   sSQLstr VARCHAR2(1000);
   type refcur is ref cursor;
   rcur refcur;
   stmt1 VARCHAR2(300);
BEGIN 
   for rec in (select t.n_portions||'' portions of ''||p.dish_name||'' at '' ||p. price|| '' pounds :''||
                  t.n_portions*p.price  stmt
                  from Customer_order s,food_order t, Dish p
                  where s.order#=t.order#
                  and t.dish#=p.dish#)
   loop
      dbms_output.put_line(rec.stmt);   
   END LOOP;
END;




now i get
Warning: execution completed with warning
procedure Item_bill Compiled.


You've got more quotes than you need.
And will you please use sqlplus so we can get understandable messages.
It'll make all our lives easier if you do.
Re: procedure probleam [message #416104 is a reply to message #416101] Thu, 30 July 2009 08:44 Go to previous messageGo to next message
vodlink
Messages: 24
Registered: July 2009
Junior Member
Michel Cadot wrote on Thu, 30 July 2009 14:38
vodlink wrote on Thu, 30 July 2009 13:15
Michel Cadot wrote on Thu, 30 July 2009 12:00
Sorry you didn't make the effort to fix your test case, I could help you.

Regards
Michel


i fixed it it works now thank you anyway.

Good it works for you and sorry you didn't post it for us.

Regards
Michel



heres the code that worked
create or replace procedure Item_bill
is
sSQLstr VARCHAR2(1000);
type refcur is ref cursor;
rcur refcur;
stmt1 VARCHAR2(300);
BEGIN 
sSQLstr := 'select t.n_portions||'' portions of ''||p.dish_name||'' at '' ||p. price|| '' pounds :''||
t.n_portions*p. price
from Customer_order s,food_order t, Dish p
where s.order#=t.order#
and t.dish#=p.dish#'; 
OPEN rcur FOR sSQLstr;
LOOP
FETCH rcur
INTO stmt1;
EXIT WHEN rcur%NOTFOUND;
dbms_output.put_line(stmt1);
END LOOP;
END;

begin
item_bill();
end;
Re: procedure probleam [message #416107 is a reply to message #416104] Thu, 30 July 2009 08:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I meant the test case (see your first post above).

Regards
Michel
Re: procedure probleam [message #416121 is a reply to message #416107] Thu, 30 July 2009 09:40 Go to previous messageGo to next message
vodlink
Messages: 24
Registered: July 2009
Junior Member
Michel Cadot wrote on Thu, 30 July 2009 14:57
I meant the test case (see your first post above).

Regards
Michel


create table Customer_order( order# NUMBER primary key , 
date_order    DATE,
  date_required DATE,
  address       VARCHAR2(30)
  );
CREATE  TABLE Dish (
  dish#      VARCHAR2(5) primary key,
  dish_name  CHAR(15),
  vegetarian CHAR(3),
  price      NUMBER);

 CREATE  TABLE Drink (
  drink#     VARCHAR2(6) primary key,
  drink_name CHAR(6),
  drink_type CHAR(9),
  price      NUMBER);
  
CREATE  TABLE Food_order (
  food_order# VARCHAR2(7) primary key,
  order#      number references Customer_order (order#) ,
  dish#       VARCHAR2(5) references Dish(Dish#),
  n_portions  NUMBER);

CREATE  TABLE Drink_order (
  drink_order# VARCHAR2(5)primary key,
  order#       number references Customer_order (order#),
  drink#       VARCHAR2(6)references drink (drink#),
  n_units      NUMBER);


-- data insert
Insert into customer_order values ('00001', '03-Apr-09', '07-apr-09','St. Andrew St'); 
Insert into customer_order values ('00002', '05-Apr-09', '01-May-09', 'St. Andrew St');
Insert into customer_order values ('00003', '12-Apr-09', '27-Apr-09', 'Union St');
Insert into customer_order values ('00004', '12-Apr-09', '17-Apr-09', 'St. Andrew St');

Insert into Dish values ('D0001', 'Pasta bake',      'yes', '6.00'); 
Insert into Dish values ('D0002', 'Fish pie',        'no',  '9.00');  
Insert into Dish values ('D0003', 'Steak and chips', 'no',  '14.00');   
Insert into Dish values ('D0004', 'Stuffed peppers', 'yes', '11.50');   
Insert into Dish values ('D0005', 'Ham and rice'   , 'no',  '7.25');  
Insert into Dish values ('D0006', 'Lamb curry'     , 'no',  '8.50'); 

Insert into Drink values ('DR0001', 'Water',  'soft',      '1.0');
Insert into Drink values ('DR0002', 'Coffee', 'hot',       '1.70');
Insert into Drink values ('DR0003', 'Wine'  , 'alcoholic', '3.00'); 
Insert into Drink values ('DR0004', 'Beer'  , 'alcoholic', '2.30');  
Insert into Drink values ('DR0005', 'Tea'   , 'hot'     ,  '1.50');   

Insert into food_order values ('F000001', '000001', 'D0003', '6');
Insert into food_order values ('F000002', '000001', 'D0001', '4');
Insert into food_order values ('F000003', '000001', 'D0004', '3');
Insert into food_order values ('F000004', '000002', 'D0001', '10');
Insert into food_order values ('F000005', '000002', 'D0002', '10');
Insert into food_order values ('F000006', '000003', 'D0002', '35');
Insert into food_order values ('F000007', '000004', 'D0002', '23');

Insert into drink_order values ('D000001', '000001', 'DR0001', '13');
Insert into drink_order values ('D000002', '000001', 'DR0002', '13');
Insert into drink_order values ('D000003', '000001', 'DR0004', '13');
Insert into drink_order values ('D000004', '000002', 'DROOO1', '20');
Insert into drink_order values ('D000005', '000002', 'DR0003', '20');
Insert into drink_order values ('D000006', '000002', 'DR0004', '15');
Insert into drink_order values ('D000007', '000003', 'DR0002', '35');
Insert into drink_order values ('D000008', '000004', 'DR0001', '23'); 
Insert into drink_order values ('D000009', '000004', 'DR0003', '15');
Insert into drink_order values ('D0000010', '000004', 'DR0004', '15');
Re: procedure probleam [message #416122 is a reply to message #415827] Thu, 30 July 2009 09:47 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
You blatently haven't tested that.

drink_order# is defined as varchar2(5).
The shortest value you've supplied for it is 7 characters.

Also dates need a to_date - it might work for you but I get:
Connected to:
Oracle Database 10g Release 10.2.0.2.0 - 64bit Production

SQL> create table Customer_order( order# NUMBER primary key , 
  2  date_order    DATE,
  3    date_required DATE,
  4    address       VARCHAR2(30)
  5    );

Table created.

SQL> 
SQL> Insert into customer_order values ('00001', '03-Apr-09', '07-apr-09','St. Andrew St'); 
Insert into customer_order values ('00001', '03-Apr-09', '07-apr-09','St. Andrew St')
                                            *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected


SQL> Insert into customer_order values ('00002', '05-Apr-09', '01-May-09', 'St. Andrew St');
Insert into customer_order values ('00002', '05-Apr-09', '01-May-09', 'St. Andrew St')
                                            *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected


SQL> Insert into customer_order values ('00003', '12-Apr-09', '27-Apr-09', 'Union St');
Insert into customer_order values ('00003', '12-Apr-09', '27-Apr-09', 'Union St')
                                            *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected


SQL> Insert into customer_order values ('00004', '12-Apr-09', '17-Apr-09', 'St. Andrew St');
Insert into customer_order values ('00004', '12-Apr-09', '17-Apr-09', 'St. Andrew St')
                                            *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
Re: procedure probleam [message #416131 is a reply to message #416121] Thu, 30 July 2009 12:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Even if you use an appropriate default date format, you still have the same errors than before.
2 among them:
SQL> Insert into drink_order values ('D000001', '000001', 'DR0001', '13');
Insert into drink_order values ('D000001', '000001', 'DR0001', '13')
                                *
ERROR at line 1:
ORA-12899: value too large for column "MICHEL"."DRINK_ORDER"."DRINK_ORDER#" (actual: 7, maximum: 5)

SQL> Insert into drink_order values ('D000004', '000002', 'DROOO1', '20');
Insert into drink_order values ('D000004', '000002', 'DROOO1', '20')
*
ERROR at line 1:
ORA-02291: integrity constraint (MICHEL.SYS_C006306) violated - parent key not found

Is this so hard to copy and paste what you post in a SQL*Plus window to check if it works?

In addition, fill number fields with numbers not strings.

Regards
Michel

[Updated on: Thu, 30 July 2009 12:35]

Report message to a moderator

Re: procedure probleam [message #416132 is a reply to message #416121] Thu, 30 July 2009 13:11 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
After fixing your test case (at least the most important errors):
drop TABLE Drink_order ;
drop TABLE Food_order ;
drop TABLE Drink ;
drop TABLE Dish ;
drop table Customer_order ;

create table Customer_order ( order# NUMBER primary key , 
date_order    DATE,
  date_required DATE,
  address       VARCHAR2(30)
  );
CREATE  TABLE Dish (
  dish#      VARCHAR2(5) primary key,
  dish_name  CHAR(15),
  vegetarian CHAR(3),
  price      NUMBER);

 CREATE  TABLE Drink (
  drink#     VARCHAR2(6) primary key,
  drink_name CHAR(6),
  drink_type CHAR(9),
  price      NUMBER);
  
CREATE  TABLE Food_order (
  food_order# VARCHAR2(7) primary key,
  order#      number references Customer_order (order#) ,
  dish#       VARCHAR2(5) references Dish(Dish#),
  n_portions  NUMBER);

CREATE  TABLE Drink_order (
  drink_order# VARCHAR2(7)primary key,
  order#       number references Customer_order (order#),
  drink#       VARCHAR2(6)references drink (drink#),
  n_units      NUMBER);


-- data insert
Insert into customer_order values ('00001', '03-Apr-09', '07-apr-09','St. Andrew St'); 
Insert into customer_order values ('00002', '05-Apr-09', '01-May-09', 'St. Andrew St');
Insert into customer_order values ('00003', '12-Apr-09', '27-Apr-09', 'Union St');
Insert into customer_order values ('00004', '12-Apr-09', '17-Apr-09', 'St. Andrew St');

Insert into Dish values ('D0001', 'Pasta bake',      'yes', '6.00'); 
Insert into Dish values ('D0002', 'Fish pie',        'no',  '9.00');  
Insert into Dish values ('D0003', 'Steak and chips', 'no',  '14.00');   
Insert into Dish values ('D0004', 'Stuffed peppers', 'yes', '11.50');   
Insert into Dish values ('D0005', 'Ham and rice'   , 'no',  '7.25');  
Insert into Dish values ('D0006', 'Lamb curry'     , 'no',  '8.50'); 

Insert into Drink values ('DR0001', 'Water',  'soft',      '1.0');
Insert into Drink values ('DR0002', 'Coffee', 'hot',       '1.70');
Insert into Drink values ('DR0003', 'Wine'  , 'alcoholic', '3.00'); 
Insert into Drink values ('DR0004', 'Beer'  , 'alcoholic', '2.30');  
Insert into Drink values ('DR0005', 'Tea'   , 'hot'     ,  '1.50');   

Insert into food_order values ('F000001', '000001', 'D0003', '6');
Insert into food_order values ('F000002', '000001', 'D0001', '4');
Insert into food_order values ('F000003', '000001', 'D0004', '3');
Insert into food_order values ('F000004', '000002', 'D0001', '10');
Insert into food_order values ('F000005', '000002', 'D0002', '10');
Insert into food_order values ('F000006', '000003', 'D0002', '35');
Insert into food_order values ('F000007', '000004', 'D0002', '23');

Insert into drink_order values ('D000001', '000001', 'DR0001', '13');
Insert into drink_order values ('D000002', '000001', 'DR0002', '13');
Insert into drink_order values ('D000003', '000001', 'DR0004', '13');
Insert into drink_order values ('D000004', '000002', 'DR0001', '20');
Insert into drink_order values ('D000005', '000002', 'DR0003', '20');
Insert into drink_order values ('D000006', '000002', 'DR0004', '15');
Insert into drink_order values ('D000007', '000003', 'DR0002', '35');
Insert into drink_order values ('D000008', '000004', 'DR0001', '23'); 
Insert into drink_order values ('D000009', '000004', 'DR0003', '15');
Insert into drink_order values ('D000010', '000004', 'DR0004', '15');
commit;

You get the result in SQL with:
SQL> def order=1
SQL> with
  2    data as (
  3      select fo.order#, 'food' food_type, fo.n_portions, di.dish_name food_name, di.price
  4      from food_order fo, dish di
  5      where fo.order# = &order
  6        and di.dish# = fo.dish#
  7      union all
  8      select do.order#, 'drink', do.n_units, dr.drink_name, dr.price
  9      from drink_order do, drink dr
 10      where do.order# = &order
 11        and dr.drink# = do.drink#
 12    )
 13  select decode(grouping_id(order#, food_type, food_name),
 14                0, n_portions||' '||decode(food_type,'drink','units','portions')||' of '||
 15                     food_name||': '||n_portions*price||' pounds',
 16                1, '
 17  Total '||food_type||' for order'||to_char(&order,'00000')||': '||sum(n_portions*price)||' pounds
 18  '||chr(10),
 19                3, 'Total cost for order'||to_char(&order,'00000')||': '||
 20                      sum(n_portions*price)||' pounds') 
 21           res
 22  from data
 23  group by rollup(order#, food_type, (food_name, n_portions, price))
 24  order by food_type desc nulls last, grouping_id(order#, food_type, food_name)
 25  /
RES
---------------------------------------------------------------------------------------------------------------------
4 portions of Pasta bake     : 24 pounds
6 portions of Steak and chips: 84 pounds
3 portions of Stuffed peppers: 34.5 pounds

Total food for order 00001: 142.5 pounds

13 units of Beer  : 29.9 pounds
13 units of Water : 13 pounds
13 units of Coffee: 22.1 pounds

Total drink for order 00001: 65 pounds

Total cost for order 00001: 207.5 pounds

Regards
Michel
Previous Topic: Record Fetch from Multiple Range Partition takes much time
Next Topic: ORA-06502: PL/SQL: numeric or value error while using CLOB
Goto Forum:
  


Current Time: Sat Dec 10 12:32:26 CST 2016

Total time taken to generate the page: 0.07872 seconds