Home » SQL & PL/SQL » SQL & PL/SQL » Problem with procedure having exceptions (Oracle 9i)
Problem with procedure having exceptions [message #313116] Thu, 10 April 2008 15:17 Go to next message
vamsikgummadi
Messages: 62
Registered: April 2006
Location: United States, MI
Member
Hi Friends,
I have written a procedure as follows:


create or replace procedure vam_sal_eval
(p_sal emp.sal%type,
p_ename emp.ename%type,
p_empno emp.empno%type) is
greater exception;
lower exception;
begin
select empno,ename,sal into p_empno,p_ename,p_sal from emp where empno=p_empno;
if sal>avg(sal)
then raise greater;
elsif sal=avg(sal)
then dbms_output.put_line(p_ename|| 'earns' ||p_sal ||' which is equivalent to average);
else
raise lower;
end if;
exception
when greater then
raise_application_error(-20001,'the sal is more than average salary');
when lower then
raise_application_error(-20002,' the sal is less than average salary');
when no_data_found then
raise_application_error(-20003,'enter a valid empno');
when others then
dbms_output.put_line('error occured');
end;
/


The above syntax is returning the error as follows:

LINE/COL ERROR
-------- -----------------------------------------------------------------
18/33 PLS-00103: Encountered the symbol "THE" when expecting one of the
following:
. ( ) , * @ % & | = - + < / > at in is mod not rem => ..
<an exponent (**)> <> or != or ~= >= <= <> and or like as
between from using ||


I am stuck up with this problem. I will be extermely happy if you can help me in sorting out what went wrong with the syntax that I have give above.

Thanking you in anticipation,
Vamsi K Gummadi.
Re: Problem with procedure having exceptions [message #313117 is a reply to message #313116] Thu, 10 April 2008 15:25 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
To obtain assistance, read & follow posting guidelines as stated in URL above.

Besides you are missing a single quote mark.

[Updated on: Thu, 10 April 2008 15:34] by Moderator

Report message to a moderator

Re: Problem with procedure having exceptions [message #313119 is a reply to message #313116] Thu, 10 April 2008 15:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Missing '

Regards
Michel
Re: Problem with procedure having exceptions [message #313122 is a reply to message #313117] Thu, 10 April 2008 15:39 Go to previous messageGo to next message
vamsikgummadi
Messages: 62
Registered: April 2006
Location: United States, MI
Member
Hi Friends,
I am trying to make this problem much more clear. I am trying to sort out employees who have greater salary than average salary and lower salary than average salary from emp table using SQL*PLUS. So I have tried to write a procedure using the following syntax.

create or replace procedure vam_sal_eval
(p_sal emp.sal%type,
p_ename emp.ename%type,
p_empno emp.empno%type) is
greater exception;
lower exception;
begin
select empno,ename,sal into p_empno,p_ename,p_sal from emp where empno=p_empno;
if sal>avg(sal)
then raise greater;
elsif sal=avg(sal)
then dbms_output.put_line(p_ename|| 'earns' ||p_sal ||' which is equivalent to average);
else
raise lower;
end if;
exception
when greater then
raise_application_error(-20001,'the sal is more than average salary');
when lower then
raise_application_error(-20002,' the sal is less than average salary');
when no_data_found then
raise_application_error(-20003,'enter a valid empno');
when others then
dbms_output.put_line('error occured');
end;
/


The code when compiled gave me the following error.
Warning: Procedure created with compilation errors.

I tried to get the details of the error using the following query:

show errors;

The output of the query is:

Errors for PROCEDURE VAM_SAL_EVAL:

LINE/COL ERROR
-------- -----------------------------------------------------------------
18/33 PLS-00103: Encountered the symbol "THE" when expecting one of the
following:
. ( ) , * @ % & | = - + < / > at in is mod not rem => ..
<an exponent (**)> <> or != or ~= >= <= <> and or like as
between from using ||


I am unable to understand where my code went wrong. It would be helpful if anyone of you can guide me in fixing this problem.

Thanks,
Vamsi K Gummadi.
Re: Problem with procedure having exceptions [message #313124 is a reply to message #313116] Thu, 10 April 2008 15:41 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
You have been told TWICE that you have unmatched single quote marks.

I assume you know how to count to 2, but this remains unproven so far.

[Updated on: Thu, 10 April 2008 15:46] by Moderator

Report message to a moderator

Re: Problem with procedure having exceptions [message #313125 is a reply to message #313122] Thu, 10 April 2008 16:02 Go to previous messageGo to next message
ravi214u
Messages: 153
Registered: February 2008
Location: CANADA
Senior Member
Check this line of your code.

dbms_output.put_line(p_ename|| 'earns' ||p_sal ||' which is equivalent to average);
Re: Problem with procedure having exceptions [message #313128 is a reply to message #313124] Thu, 10 April 2008 16:11 Go to previous messageGo to next message
vamsikgummadi
Messages: 62
Registered: April 2006
Location: United States, MI
Member
Hi Anacedent,
In my previus mails some errors may have crept in due to overlook of some rules and regulations or due to more concentration on how to get the prolem solved.
I AM NOT A MACHINE TO BE ERROR FREE. I AM TRYING TO MAKE MY PROBLEM (CODE) ERROR FREE.

As suggested I have made some corrections in the code. Now the code is

1 create or replace procedure vam_sal_eval
2 (p_sal emp.sal%type,
3 p_ename emp.ename%type,
4 p_empno emp.empno%type) is
5 greater exception;
6 lower exception;
7 begin
8 select empno,ename,sal into p_empno,p_ename,p_sal from emp where empno=p_empno;
9 if sal>avg(sal)
10 then raise greater;
11 elsif sal=avg(sal)
12 then dbms_output.put_line(p_ename|| 'earns' ||p_sal ||' which is equivalent to average');
13 else
14 raise lower;
15 end if;
16 exception
17 when greater then
18 raise_application_error(-20001,'the sal is more than average salary');
19 when lower then
20 raise_application_error(-20002,' the sal is less than average salary');
21 when no_data_found then
22 raise_application_error(-20003,'enter a valid empno');
23 when others then
24 dbms_output.put_line('error occured');
25* end;
And still some more errors are being returned at the compile time.

Now the errors that i am being shown by using the SHOW ERRORS; query is:
Errors for PROCEDURE VAM_SAL_EVAL:

LINE/COL ERROR
-------- -----------------------------------------------------------------
8/29 PLS-00403: expression 'P_EMPNO' cannot be used as an INTO-target
of a SELECT/FETCH statement

8/37 PLS-00403: expression 'P_ENAME' cannot be used as an INTO-target
of a SELECT/FETCH statement

8/45 PLS-00403: expression 'P_SAL' cannot be used as an INTO-target of
a SELECT/FETCH statement

9/1 PL/SQL: Statement ignored
9/4 PLS-00201: identifier 'SAL' must be declared


I am unable to intepret the error. I have the following doubts:
1. Should I use p_sal in place of sal in line 9.
2. Can avg(sal) be used in line 9?

Please go through the entire code snippet once again and try to help me in solving this problem.

Thanks and regards,
Vamsi K Gummadi.
Re: Problem with procedure having exceptions [message #313129 is a reply to message #313125] Thu, 10 April 2008 16:13 Go to previous messageGo to next message
vamsikgummadi
Messages: 62
Registered: April 2006
Location: United States, MI
Member
Hi Ravi,
Thanks for the prompt response. Even then some errors are being shown. It would be very helpful if you can see my previous message and try to solve this problem.
Thanks,
Vamsi K Gummadi.
Re: Problem with procedure having exceptions [message #313143 is a reply to message #313129] Thu, 10 April 2008 18:55 Go to previous messageGo to next message
ravi214u
Messages: 153
Registered: February 2008
Location: CANADA
Senior Member
There is some problem in passing parameters and using them in into statement.
And there are some problem in declaring exceptions.

Please go through the manual and try to read these thing before implementing.

Thanks
Re: Problem with procedure having exceptions [message #313158 is a reply to message #313143] Thu, 10 April 2008 23:24 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
You are passing parameters in IN mode and you are using same variables to hold values return via the select statement.

Please read some pl/sql tutorials how to write a pl/sql program.

Don't try to guess???

Their are many tutorials available on orafag. Go through them??

To have answer on this forum you must have some basic knowledge that you are lacking right now??
Re: Problem with procedure having exceptions [message #313164 is a reply to message #313116] Thu, 10 April 2008 23:52 Go to previous messageGo to next message
bibsdash
Messages: 47
Registered: April 2008
Member
Can you try this to see if it works for you?

 create or replace procedure vam_sal_eval
 (p_sal IN OUT emp.sal%type,
 p_ename IN OUT emp.ename%type,
 p_empno IN OUT emp.empno%type) is
 greater exception;
 lower exception;
 begin
   begin
    select empno,ename,sal 
    into p_empno,p_ename,p_sal from emp 
    where empno=p_empno;
   exception
    when others then
     dbms_output.put_line('Error in select');
   end;
     if sal>avg(sal)
     then raise greater;
     elsif sal=avg(sal)
      then dbms_output.put_line(p_ename|| 'earns' ||p_sal ||'which is equivalent to average');
     else
     raise lower;
     end if;
 exception
 when greater then
 raise_application_error(-20001,'the sal is more than average salary');
 when lower then
 raise_application_error(-20002,' the sal is less than average salary');
 when no_data_found then
 raise_application_error(-20003,'enter a valid empno');
 when others then
 dbms_output.put_line('error occured');
 end;

[Updated on: Thu, 10 April 2008 23:55]

Report message to a moderator

Re: Problem with procedure having exceptions [message #313188 is a reply to message #313164] Fri, 11 April 2008 00:39 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
@bibsdash
Quote:
if sal>avg(sal)

you will get an error at this line 'SAL must be declared'.


regards,
Re: Problem with procedure having exceptions [message #313205 is a reply to message #313116] Fri, 11 April 2008 01:26 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> I am trying to sort out employees who have greater salary than average salary and lower salary than average salary from emp table using SQL*PLUS.

I do not see any SQL*Plus commands in your code, you use only PL/SQL procedure.
By the way, why do you not do it in pure SQL? A homework assignment? If so, use all the knowledge taken in the course using documentation, found eg. online on http://tahiti.oracle.com/ for consultation purpose (especially the syntax you are struggling with).

As pointed by dhananjay, you have to compute the average first. It may be separate query before, or you may use AVG function using the analytic part.

exception
 when greater then
  raise_application_error(-20001,'the sal is more than average salary');
 when lower then
  raise_application_error(-20002,' the sal is less than average salary');
 when no_data_found then
  raise_application_error(-20003,'enter a valid empno');
 when others then
  dbms_output.put_line('error occured');
end;
Very interesting. You re-raise some user invoked exceptions within the "no data found" one, but apparently ignoring all other exceptions (the WHEN OTHERS clause). Remove it if you do not want to have problems in future (I also doubt the examiner will like it).
Re: Problem with procedure having exceptions [message #313385 is a reply to message #313116] Fri, 11 April 2008 10:12 Go to previous messageGo to next message
vamsikgummadi
Messages: 62
Registered: April 2006
Location: United States, MI
Member
Hi,
I am still working out with all your suggestions. I am trying to learn converting Oracle anonymous procedure to Oracle Stored Procedure.
Code for ananymous procedure is :
DECLARE
SALARY EMP.SAL%TYPE;
NAME EMP.ENAME%TYPE;
NO NUMBER:=&NO;
GREATER EXCEPTION; --User defined exception
LESSER EXCEPTION; --User defined exception
BEGIN
SELECT ENAME,SAL INTO NAME,SALARY FROM EMP WHERE EMPNO=NO;
IF SALARY>2000 THEN
RAISE GREATER; --User defined exception raised explicitely.
ELSE
RAISE LESSER; --User defined exception raised explicitely.
END IF;
EXCEPTION
WHEN GREATER THEN
RAISE_APPLICATION_ERROR(-20001,'YOUR SALARY IS MORE THAN 2000');
--User defined exception handled here.
WHEN LESSER THEN
RAISE_APPLICATION_ERROR(-20002,'YOUR SALARY IS LESS THAN 2000');
--User defined exception handled here.
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20003,'PLEASE ENTER A VALID EMPNO');
END;

I have found this code in
http://bytes.com/forum/thread748326.html

and I am trying to make use of all parmeters which are declared in the declarative section.

I am working out for the solution as you people suggested. Will get back once I get the solution.

Thanks and regards,
Vamsi K Gummadi.

Re: Problem with procedure having exceptions [message #313398 is a reply to message #313385] Fri, 11 April 2008 12:27 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
Please format your posts correctly. No BOLD, and use code tags. You have been told numerous times already. Read the sticky at the top of the forum to learn what is expected for a proper post.
Re: Problem with procedure having exceptions [message #313399 is a reply to message #313398] Fri, 11 April 2008 12:35 Go to previous messageGo to next message
vamsikgummadi
Messages: 62
Registered: April 2006
Location: United States, MI
Member
Thanks I have seen the how to format the posts just now. Sorry for using bold letters for the code.
Re: Problem with procedure having exceptions [message #313811 is a reply to message #313116] Mon, 14 April 2008 12:45 Go to previous messageGo to next message
vamsikgummadi
Messages: 62
Registered: April 2006
Location: United States, MI
Member
Hi Friends,
I think now I have learnt how to post into the forum.With your suggestions I have modified my code as follows.
create or replace procedure vam_sal_eval
 (p_sal OUT emp.sal%type, 
  p_empno IN emp.empno%type) is
 greater exception;
 lower exception;
 begin
    select sal 
    into p_sal from emp 
    where empno=p_empno;
     if p_sal>= 2000
     then raise greater;     
     else
     raise lower;
     end if;
 exception
 when greater then
dbms_output.put_line('the sal is more than than or equal to average salary');
 --raise_application_error(-20001,'the sal is more than or equal to average salary');
 when lower then
dbms_output.put_line('the sal is less than the avg sal');
 --raise_application_error(-20002,' the sal is less than average salary');
 when no_data_found then
 raise_application_error(-20003,'enter a valid empno');
 when others then
 dbms_output.put_line('error occured');
 end;
/


The PROCEDURE is sucessfully created.

Now I am using this procedure from an anonymous block as follows:
 declare
 v_sal emp.sal%type;
 begin
 vam_sal_eval(v_sal,7369);--assuming I have a employee with empno 7369
 dbms_output.put_line('sal'|| v_sal);
 end;


When I am using
raise_application_error(-20001,'the sal is more than or equal to average salary');

or
raise_application_error(-20002,' the sal is less than average salary');

I am getting an Oracle error like
ERROR at line 1:
ORA-20002:  the sal is less than average salary
ORA-06512: at "SCOTT.VAM_SAL_EVAL", line 19
ORA-06512: at line 4


When using
dbms_output_put_line 
I am not getting errors but when using
raise_application_error
I am getting the errors. Why is it so happening? Am I using exceptions in the wrong way? Please try to help me with your valuable suggestions and explanations on what went wrong or where I have messed up the things?
Thanks and regards,
Vamsi K Gummadi.

[mod-edit] changed quote tags to code so people can read it.

[Updated on: Mon, 14 April 2008 13:33] by Moderator

Report message to a moderator

Re: Problem with procedure having exceptions [message #313814 is a reply to message #313116] Mon, 14 April 2008 12:58 Go to previous messageGo to next message
vamsikgummadi
Messages: 62
Registered: April 2006
Location: United States, MI
Member
Hi,
Sorry for using QUOTE instead of CODE in my previous post. Will post correctly from my next message.
Thanks,
Vamsi K Gummadi.
Re: Problem with procedure having exceptions [message #313817 is a reply to message #313116] Mon, 14 April 2008 13:39 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> Sorry for using QUOTE instead of CODE in my previous post. Will post correctly from my next message.

You could "Edit" it (the button should be visible, at least when it was last message in the thread). Now it seems that moderator corrected it.

> Why is it so happening?

Because exception handling is implemented this way, as stated in the documentation. I already provided you links in this thread.
PL/SQL User's Guide and Reference
Chapter 13 PL/SQL Language Elements
Blocks
Exceptions
Quote:
EXCEPTION

This keyword signals the start of the exception-handling part of a PL/SQL block. When an exception is raised, normal execution of the block stops and control transfers to the appropriate exception handler. After the exception handler completes, execution proceeds with the statement following the block.

If there is no exception handler for the raised exception in the current block, control passes to the enclosing block. This process repeats until an exception handler is found or there are no more enclosing blocks. If PL/SQL can find no exception handler for the exception, execution stops and an unhandled exception error is returned to the host environment. For more information, see Chapter 7.

Next time, please, consult the documentation yourself.
Re: Problem with procedure having exceptions [message #313828 is a reply to message #313817] Mon, 14 April 2008 14:52 Go to previous messageGo to next message
vamsikgummadi
Messages: 62
Registered: April 2006
Location: United States, MI
Member
Hi Flyboy,
Thank you for the suggestions given. Before posting my earlier message I have refered to http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_packages.htm#sthref925 . But I am unable to understand what went wrong with in the entire process. I tried to raise the exception in the stored procedure and even the procedure is cmpiled successfully but when I am trying to use it in an anonymous block I am getting an error. Perhaps you can explain me much better. So, I request you and all our friends to try to explain me the point I may be missing or over looking at. It will also be helpul to me if you can add comments (what I am supposed to do and what I am currently doing?) to the CODE I have previously posted.
Thanks,
Vamsi K Gummadi.
Re: Problem with procedure having exceptions [message #313831 is a reply to message #313828] Mon, 14 April 2008 15:18 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8633
Registered: November 2002
Location: California, USA
Senior Member
What you are referring to as an error is what you are supposed to get:

ERROR at line 1:
ORA-20002: the sal is less than average salary
ORA-06512: at "SCOTT.VAM_SAL_EVAL", line 19
ORA-06512: at line 4

It is raising the exception that you toldl it to:

ORA-20002: the sal is less than average salary

and telling you that the exception was raised from line 19 of the vam_sal_eval procedure in the scott shcema:

ORA-06512: at "SCOTT.VAM_SAL_EVAL", line 19

which was called form line 4 of your anonymous pl/sql block:

ORA-06512: at line 4

This is the excpected behavior when you use raise_application_error. This is what it is supposed to do. There is nothing to fix there.



Re: Problem with procedure having exceptions [message #313833 is a reply to message #313831] Mon, 14 April 2008 15:46 Go to previous messageGo to next message
vamsikgummadi
Messages: 62
Registered: April 2006
Location: United States, MI
Member
Hi Barbara Boehmer,
Thank you for the detailed explanation. Now I am able to interpret the output. Now I got the point. Thanks a lot.
Regards,
Vamsi K Gummadi
Re: Problem with procedure having exceptions [message #313834 is a reply to message #313116] Mon, 14 April 2008 15:46 Go to previous message
vamsikgummadi
Messages: 62
Registered: April 2006
Location: United States, MI
Member
Hi Friends thanks a lot for your suggestions and explanations.
Previous Topic: NLS Settings
Next Topic: Impact of UNION ALL of nearly 10 SQLs on performance ?
Goto Forum:
  


Current Time: Tue Dec 06 14:25:18 CST 2016

Total time taken to generate the page: 0.06232 seconds