Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL STORED PROCEDURE output problem (Trying to display a stored procedures output to the console)
PL/SQL STORED PROCEDURE output problem [message #427730] Sat, 24 October 2009 10:11 Go to next message
jtaylorok
Messages: 4
Registered: October 2009
Location: USA
Junior Member

I'm trying to write my first stored procedure. I want the procedure to select a number of books orders and customer info from the joined tables and output the total with a coupon of 12.5% applied to the order results.


CREATE OR REPLACE PROCEDURE Discount
AS
CURSOR c_group_discount
IS
SELECT sum(books.cost),customers.firstname,customers.lastname
FROM orders, orderitems,customers, books
WHERE
orders.order# = orderitems.order#
AND
orders.customer# = customers.customer#
AND orderitems.isbn = books.isbn
GROUP BY
customers.firstname, customers.lastname;

BEGIN
FOR r_group_discount IN c_group_discount
LOOP
FETCH
c_group_discount.FIRSTNAME,c_group_discount.LASTNAME,sum(c_group_discount.cost) as SPENT, sum(c_group_discount.cost * 12.5) as DISCOUNT
INTO r_group_discount;
EXIT WHEN c_group_discount.FIRSTNAME%NOTFOUND;
DBMS_OUTPUT.PUTLINE(r_group_discount.DISCOUNT); //<--- does not like this line but I need to output the results of the calculation through each loop iteration
END LOOP;

CLOSE c_group_discount;
END;
Re: PL/SQL STORED PROCEDURE output problem [message #427733 is a reply to message #427730] Sat, 24 October 2009 10:27 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
>//<--- does not like this line
Who does not like this line & why.
Post actual results & explain in detail what you think is wrong.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

Re: PL/SQL STORED PROCEDURE output problem [message #427734 is a reply to message #427733] Sat, 24 October 2009 10:43 Go to previous messageGo to next message
jtaylorok
Messages: 4
Registered: October 2009
Location: USA
Junior Member

This is my error:



ERROR at line 20: PLS-00103: Encountered the symbol ";" when expecting one of the following:

. ( * @ % & = - + < / > at in is mod remainder not rem then
<> or != or ~= >= <= <> and or like LIKE2_
LIKE4_ LIKEC_ between || multiset member SUBMULTISET_

1. CREATE OR REPLACE PROCEDURE Discount
2. AS
3. CURSOR c_group_discount
4. IS


I don't really have an example of multiple lines being manipulated in a cursor and copied over( moved from the c_group_discount to r_group_discount while appying discount to the field) inside the loop.
What I want it to do is print the name of the customer, each items original cost, and then apply the discount and then print them row by row.

I guess oracle is seeing a ; when what I want is for it to do this operation. I don't know why.
Re: PL/SQL STORED PROCEDURE output problem [message #427735 is a reply to message #427734] Sat, 24 October 2009 10:48 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Use SQL*Plus and copy and paste your session.

Regards
Michel
Re: PL/SQL STORED PROCEDURE output problem [message #427736 is a reply to message #427734] Sat, 24 October 2009 10:52 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
below is what I get when trying the posted code

  1  CREATE OR REPLACE PROCEDURE Discount
  2  AS
  3  CURSOR c_group_discount
  4  IS
  5  SELECT sum(books.cost),customers.firstname,customers.lastname
  6  FROM orders, orderitems,customers, books
  7  WHERE
  8  orders.order# = orderitems.order#
  9  AND
 10  orders.customer# = customers.customer#
 11  AND orderitems.isbn = books.isbn
 12  GROUP BY
 13  customers.firstname, customers.lastname;
 14  BEGIN
 15  FOR r_group_discount IN c_group_discount
 16  LOOP
 17  FETCH
 18  c_group_discount.FIRSTNAME,c_group_discount.LASTNAME,sum(c_group_discount.cost) as SPENT, sum(c_group_discount.cost * 12.5) as DISCOUNT
 19  INTO r_group_discount;
 20  EXIT WHEN c_group_discount.FIRSTNAME%NOTFOUND;
 21  DBMS_OUTPUT.PUTLINE(r_group_discount.DISCOUNT); //<--- does not like this line but I need to output the results of the calculation through each loop iteration
 22  END LOOP;
 23  CLOSE c_group_discount;
 24* END;
 25  /

Warning: Procedure created with compilation errors.

SQL> show error
Errors for PROCEDURE DISCOUNT:

LINE/COL ERROR
-------- -----------------------------------------------------------------
18/27	 PLS-00103: Encountered the symbol "," when expecting one of the
	 following:
	 . into bulk

18/81	 PLS-00103: Encountered the symbol "AS" when expecting one of the
	 following:
	 ) , * & | = - + < / > at in is mod remainder not rem => ..
	 <an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
	 LIKE4_ LIKEC_ between || multiset member SUBMULTISET_

20/1	 PLS-00103: Encountered the symbol "EXIT"

LINE/COL ERROR
-------- -----------------------------------------------------------------
20/46	 PLS-00103: Encountered the symbol ";" when expecting one of the
	 following:
	 . ( * @ % & = - + < / > at in is mod remainder not rem then
	 <an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
	 LIKE4_ LIKEC_ between || multiset member SUBMULTISET_

21/49	 PLS-00103: Encountered the symbol "/" when expecting one of the
	 following:
	 begin case declare end exit for goto if loop mod null pragma
	 raise return select update while with <an identifier>
	 <a double-quoted delimited-identifier> <a bind variable> <<

LINE/COL ERROR
-------- -----------------------------------------------------------------
	 close current delete fetch lock insert open rollback
	 savepoint set sql execute commit forall merge pipe




PL/SQL Reference Manual can be found at http://tahiti.oracle.com

many find coding examples can be found at http://asktom.oracle.com

First you need to find & fix syntax errors
Re: PL/SQL STORED PROCEDURE output problem [message #427767 is a reply to message #427730] Sun, 25 October 2009 04:34 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You use a cursor, which defines what fields to select and in what order to return them.
This means that the FETCH should not name the columns, only the fields/record to fetch the columns (as listed in the cursor) into.
So, a typical example would look like this:
create or replace procedure my_test
as
  cursor c_emp
  is
    select last_name
    ,      count(*)  total
    from   emp
    group  by last_name
  ; 
  r_emp c_emp%rowtype;
begin
  open c_emp;
  fetch c_emp into r_emp;
  dbms_output.put_line('Number of people with last_name = '
                      ||r_emp.last_name
                      ||' is: '
                      ||r_emp.total);
  close c_emp;
end;


(Note: untested code)
Re: PL/SQL STORED PROCEDURE output problem [message #427893 is a reply to message #427730] Mon, 26 October 2009 05:46 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
//<--- does ...


// is NOT a comment. Use either /* ... */ or --

HTH
Re: PL/SQL STORED PROCEDURE output problem [message #427910 is a reply to message #427893] Mon, 26 October 2009 06:30 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This part is OBVIOUSLY not part of the code but added for the post.

Regards
Michel
Re: PL/SQL STORED PROCEDURE output problem [message #427926 is a reply to message #427730] Mon, 26 October 2009 07:44 Go to previous messageGo to next message
joy_division
Messages: 4615
Registered: February 2005
Location: East Coast USA
Senior Member
jtaylorok wrote on Sat, 24 October 2009 11:11

DBMS_OUTPUT.PUTLINE(r_group_discount.DISCOUNT); //<--- does not


It's put_line, not putline.

But...

Since this is a stored procedure, output has no meaning. It runs on the server. No one is going to see any output from it.

Sure, you will see output from it if you run it in a SQL*Plus session yourself, but that's nor the purpose of a stored procedure; it's for an application to execute and no one will ever see output from it.

[Updated on: Mon, 26 October 2009 07:48]

Report message to a moderator

Re: PL/SQL STORED PROCEDURE output problem [message #427934 is a reply to message #427910] Mon, 26 October 2009 08:17 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
21/49	 PLS-00103: Encountered the symbol "/" when expecting one of the
	 following:
	 begin case declare end exit for goto if loop mod null pragma
	 raise return select update while with <an identifier>
	 <a double-quoted delimited-identifier> <a bind variable> <<


I don't think so
Re: PL/SQL STORED PROCEDURE output problem [message #427936 is a reply to message #427934] Mon, 26 October 2009 08:25 Go to previous message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
michael_bialik wrote on Mon, 26 October 2009 13:17
21/49	 PLS-00103: Encountered the symbol "/" when expecting one of the
	 following:
	 begin case declare end exit for goto if loop mod null pragma
	 raise return select update while with <an identifier>
	 <a double-quoted delimited-identifier> <a bind variable> <<


I don't think so


That was from blackswan running not the OP
Previous Topic: Queue monitoring in Oracle Enterprise manager
Next Topic: Reflection (merged 3)
Goto Forum:
  


Current Time: Sun Sep 25 16:18:02 CDT 2016

Total time taken to generate the page: 0.14629 seconds