OraFAQ Forum: SQL & PL/SQL » problem procedure

Home » SQL & PL/SQL » SQL & PL/SQL » problem procedure (oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

problem procedure [message #316769]

Mon, 28 April 2008 05:03

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

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: problem procedure [message #316778 is a reply to message #316772]

Mon, 28 April 2008 05:24

rolmau
Messages: 18
Registered: March 2008
Location: UK

Junior Member

sorry I can't cause
/*Notice: Formatted SQL is not the same as input*/

Report message to a moderator

Re: problem procedure [message #316783 is a reply to message #316778]

Mon, 28 April 2008 05:37

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Format it manually, use code tags and verify with prevew button.

Regards
Michel

Report message to a moderator

Re: problem procedure [message #316798 is a reply to message #316783]

Mon, 28 April 2008 06:44

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.

Report message to a moderator

Re: problem procedure [message #316799 is a reply to message #316783]

Mon, 28 April 2008 06:48

Littlefoot
Messages: 21823
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).

Report message to a moderator

Re: problem procedure [message #316811 is a reply to message #316769]

Mon, 28 April 2008 07:36

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

Report message to a moderator

Re: problem procedure [message #316812 is a reply to message #316798]

Mon, 28 April 2008 07:37

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: problem procedure [message #316825 is a reply to message #316811]

Mon, 28 April 2008 08:31

Frank
Messages: 7901
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.

Report message to a moderator

Previous Topic:	LOOP Insert record cursor from one table to another
Next Topic:	Staging table between 9i & 10gR2

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Dec 06 17:38:53 CST 2024