Home » SQL & PL/SQL » SQL & PL/SQL » problem procedure (oracle 10g)
icon1.gif  problem procedure [message #316769] Mon, 28 April 2008 05:03 Go to next message
rolmau
Messages: 18
Registered: March 2008
Location: UK
Junior Member

Hi all
I am getting problem to run my pkg body
this is the code

CREATE OR REPLACE PACKAGE BODY pkg_roland IS
implementations Function and procedure
-----------------------------------------------------------------
PROCEDURE compute_discount(p_purchase_id IN NUMBER, p_discount_type_id IN NUMBER) AS v_result
NUMBER := 0;
v_percent NUMBER := 0;
v_message VARCHAR2(255);
-- Discount SELECT percent INTO v_percent FROM discount_type WHERE discount_type_id = p_discount_type_id;
BEGIN
IF v_percent IS NULL OR v_percent = 0 THEN no_discount_type_found; END IF;
-- Return Exception
no_data_found THEN v_message := 'No discount type found'; DBMS_OUTPUT.PUT_LINE(v_message);
WHEN no_discount_type_found THEN
v_message := 'No discount type found'; DBMS_OUTPUT.PUT_LINE(v_message); WHEN
no_record_of_purchase THEN v_message := 'No purchase record found'; DBMS_OUTPUT.PUT_LINE;
(v_message); END compute_discount;

error I am getting
PLS-00103: Encountered the symbol "AND" when expecting one of the
following:
:= . ( @ % ; not null range default character

Do they someone may help....

[Updated on: Mon, 28 April 2008 05:06]

Report message to a moderator

Re: problem procedure [message #316772 is a reply to message #316769] Mon, 28 April 2008 05:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
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 (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Do you expect us to be SQL syntax compiler?
Use SQL*Plus and copy and paste the session including line numbers.

Regards
Michel
Re: problem procedure [message #316778 is a reply to message #316772] Mon, 28 April 2008 05:24 Go to previous messageGo to next message
rolmau
Messages: 18
Registered: March 2008
Location: UK
Junior Member

sorry I can't cause
/*Notice: Formatted SQL is not the same as input*/
Re: problem procedure [message #316783 is a reply to message #316778] Mon, 28 April 2008 05:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Format it manually, use code tags and verify with prevew button.

Regards
Michel
Re: problem procedure [message #316798 is a reply to message #316783] Mon, 28 April 2008 06:44 Go to previous messageGo to next message
rolmau
Messages: 18
Registered: March 2008
Location: UK
Junior Member

sorry but still not working
SQL> CREATE OR REPLACE PACKAGE BODY pkg_roland
2 IS
3 PROCEDURE compute_discount(
4 p_purchase_id IN NUMBER
5 ,p_discount_type_id IN NUMBER
6 AS
7 v_result NUMBER := 0;
8 v_percent NUMBER := 0;
9 v_message VARCHAR2(200);
10 -- Discount SELECT percentage INTO v_percent FROM discount_type WHERE discount_type_id = p_disc
ount_type_id;
11 BEGIN
12 IF v_percent IS NULL
13 OR v_percent = 0
14 THEN
15 no_discount_type_found;
16 END IF;
17 -- Return
18 EXCEPTION
19 WHEN no_data_found
20 THEN
21 v_message := 'No discount type found';
22 dbms_output.put_line(v_message);
23 WHEN no_discount_type_found
24 THEN
25 v_message := 'No discount type found';
26 dbms_output.put_line(v_message);
27 WHEN no_record_of_purchase
28 THEN
29 v_message := 'No purchase record found';
30 dbms_output.put_line(v_message);
31 END compute_discount;
32 END pkg_roland;
33 /
Warning: Package Body created with compilation errors.
SQL> show error
Errors for PACKAGE BODY PKG_ROLAND:
LINE/COL ERROR
-------- -----------------------------------------------------------------
6/1 PLS-00103: Encountered the symbol "AS" when expecting one of the
following:
:= . ) , @ % default character
The symbol ")" was substituted for "AS" to continue.
Re: problem procedure [message #316799 is a reply to message #316783] Mon, 28 April 2008 06:48 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You didn't close brackets between lines 5 and 6.

"no_discount_type_found" is an exaception - you have to RAISE it (as it is now, it seems that you are calling a procedure with such a name).
Re: problem procedure [message #316811 is a reply to message #316769] Mon, 28 April 2008 07:36 Go to previous messageGo to next message
rolmau
Messages: 18
Registered: March 2008
Location: UK
Junior Member

by closing brackets getting those error now

5/1 PLS-00103: Encountered the symbol "P_DISCOUNT_TYPE_ID" when
expecting one of the following:
:= . ) , @ % default character
The symbol "," was substituted for "P_DISCOUNT_TYPE_ID" to
continue.
6/1 PLS-00103: Encountered the symbol "AS" when expecting one of the
following:
begin end function package pragma procedure subtype type use
<an identifier> <a double-quoted delimited-identifier> form
current cursor
LINE/COL ERROR
Re: problem procedure [message #316812 is a reply to message #316798] Mon, 28 April 2008 07:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
sorry but still not working

Carefully read the guide, it is explained how to do it.
Then there is a "Test" forum you can use to check your post.

Regards
Michel
Re: problem procedure [message #316825 is a reply to message #316811] Mon, 28 April 2008 08:31 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
rolmau wrote on Mon, 28 April 2008 14:36
by closing brackets getting those error now

You probably put the closing parenthesis on the wrong line.
Now, for your next post: Please follow the RULES.
Use [code] and [/code] around your code.
Previous Topic: LOOP Insert record cursor from one table to another
Next Topic: Staging table between 9i & 10gR2
Goto Forum:
  


Current Time: Sat Dec 03 06:21:24 CST 2016

Total time taken to generate the page: 0.19638 seconds