Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL Exception (Oracle 9i)
PL/SQL Exception [message #310952] Wed, 02 April 2008 23:35 Go to next message
me_arindam
Messages: 26
Registered: March 2008
Location: India
Junior Member
Hi,

I have a table employee.
I have the following code.

CREATE OR REPLACE package test_pck as
procedure test_p (n_id employee.pan_id%type);
end;

create or replace package body test_pck as
g_num number;
-------------------------------
function test_f(x employee.employee_id%type) return varchar2 is
b employee.emp_FIRST_NAME%type;
begin
dbms_output.put_line('inside test_f');
select emp_FIRST_NAME into b from employee where
employee_id = x;
dbms_output.put_line('the name is '||b);
return b;
exception
when others then
dbms_output.put_line('inside exception');
raise_application_error(-20999,employee_id is not there');
end;
------------------------------------------
procedure test_p (n_id employee.pan_id%type) is
a employee.employee_ID%type;
d varchar2(50);
begin
dbms_output.put_line('inside test_p');
select employee_id into a from employee where pan_id = n_id;
d:=test_f(a);
dbms_output.put_line('the emp_first_name is'||d);
exception
when others then
raise_application_error(-20998,'pan_id is not there');
end;
end test_pck;

When a is null(select employee_id into a from employee where pan_id = n_id) then it automatically oes into the exception.
But when b is null (select emp_FIRST_NAME into b from employee where employee_id = x;)it doesn't.

Could you please explain why it is happening.

Thanks,
Arindam


Re: PL/SQL Exception [message #310955 is a reply to message #310952] Wed, 02 April 2008 23:45 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
The forum title says"

"PL/SQL Experts
Advanced Oracle PL/SQL questions - stored procedures, functions, packages and triggers. Newbies should not post to this forum! "

The forum STICKY post say:

"Before you post anything in this forum be aware that this forum is meant for Expert PL/SQL problems.
This does NOT mean that experts only read this forum; 99% also read the Newbie-forum!

So, before posting here ask yourself the following:
- Do I consider myself an expert?
- Do I think the problem at hand is a difficult one?
- Is my problem strictly PL/SQL-related?

if you answer at least one of the above questions with "No" or "Don't know", find the appropriate forum here at OraFAQ.
If all three are answered "Yes", you are most welcome to post here!
[Updated on: Tue, 26 February 2008 23:38]"

Since you can not or will not read what has been plainly & clearly posted, I won't bother to answer your question since
you obviously don't understand what is presented to you.
Re: PL/SQL Exception [message #310972 is a reply to message #310952] Thu, 03 April 2008 00:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Another point: 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.
Always post your Oracle version (4 decimals).

Regards
Michel
Re: PL/SQL Exception [message #311084 is a reply to message #310952] Thu, 03 April 2008 06:54 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Please provide exactly what error are you getting.I don't see any problem with the code.
Re: PL/SQL Exception [message #311099 is a reply to message #311084] Thu, 03 April 2008 07:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You are able to see something! Wow, I'm impressed. ./fa/450/0/

Regards
Michel
Re: PL/SQL Exception [message #311111 is a reply to message #311099] Thu, 03 April 2008 07:39 Go to previous message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Study Exception Propagation Concept.

I think that will make you clear.

Cheers,
Rajat
Previous Topic: show sum
Next Topic: Is indexes are not used if the pl/sql tables are refered in the where clause?
Goto Forum:
  


Current Time: Sun Dec 11 08:24:12 CST 2016

Total time taken to generate the page: 0.04493 seconds