Home » SQL & PL/SQL » SQL & PL/SQL » Exceptions
Exceptions [message #306688] Sat, 15 March 2008 14:41 Go to next message
mandys
Messages: 14
Registered: February 2008
Location: Pittsburgh
Junior Member
Hi,

I have written a pl/sql script.It checks the ind table if the customer exits if found it displays the name if not found it will check the org table if found it displays the details .But if the customer does not exist in both these tables it does not raise the exception.I would like to know what am I missing here.
DECLARE
lc_id txp.id%type:='2000000000';
lc_lname VARCHAR2(15);
lc_mname VARCHAR2(15);
lc_fname VARCHAR2(15);
lc_org_name VARCHAR2(75);

BEGIN
--Get the names from the Ind table if it is a individual.
BEGIN
SELECT lname,fname,mname
INTO lc_lname,lc_fname,lc_mname
FROM ind
WHERE tid=lc_id ;
IF(SQL%FOUND) THEN
dbms_output.put_line(lc_lname||' '||lc_mname||' '||lc_fname||':CUSTOMER IS A INDIVIDUAL');
END IF;
EXCEPTION
WHEN no_data_found THEN
lc_lname:=NULL;
lc_fname:=NULL;
lc_mname:=NULL;
END;
BEGIN
--Get the names from the org table if customer is a business
SELECT nm1
INTO lc_org_name
FROM org
WHERE tid=lc_id ;
IF(SQL%FOUND)THEN
dbms_output.put_line(lc_org_name||':CUSTOMER IS A BUSINESS');
END IF;
EXCEPTION
WHEN no_data_found THEN
lc_org_name:=NULL;
lc_id :=NULL;
END;
EXCEPTION
WHEN no_data_found THEN
lc_id:=NULL;
dbms_output.put_line('NO DATA FOUND');
WHEN OTHERS THEN
dbms_output.put_line(SUBSTR(SQLERRM,1,100));
END;
/

Thanks,
Mandy
Re: Exceptions [message #306689 is a reply to message #306688] Sat, 15 March 2008 15:44 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>I would like to know what am I missing here.
You are missing following the directions as stated in the URL below.
http://www.orafaq.com/forum/t/88153/0/

We (TINW) don't know what you expected & don't know what you saw.

You're On Your Own (YOYO); unless or until you provide more/better information.
Re: Exceptions [message #306690 is a reply to message #306688] Sat, 15 March 2008 15:51 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
1. When you post code, enclose it in code tags:
[code] your code here [/code] as it mentions in the forum guide (which you have already been asked to read by another member in a previous post)

2. Think about your code.
You effectively have
BEGIN
  BEGIN
    something that might raise an error;
  EXCEPTION
    handle that error;
  END;
  BEGIN
    something that might raise another error;
  EXCEPTION
    handle that other error;
  END;
EXCEPTION
  handle and error that can never occur because you have 
no code in the outer procedure and all other errors are 
handles by the nested blocks.
END;

Bit of a dodgy table structure, 2 tables holding info about a single entity. Have a look at unioning the two lists of customer id values together and do a single search on that resultset.

[Edit: Sorry Ana, cut your grass there]

[Updated on: Sat, 15 March 2008 15:59] by Moderator

Report message to a moderator

Re: Exceptions [message #306769 is a reply to message #306690] Sun, 16 March 2008 11:52 Go to previous message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
posts regarding grass cutting and beverages were moved to the community hangout:

http://www.orafaq.com/forum/t/99047/43710/
Previous Topic: How to use dll commands using database triggers
Next Topic: How can I deal with this exception"buffer flow" when I use cursor.
Goto Forum:
  


Current Time: Sun Dec 04 16:33:40 CST 2016

Total time taken to generate the page: 0.07471 seconds