Solved

Handling PL/SQL Errors

Posted on 2011-02-17
9
425 Views
Last Modified: 2012-05-11
I want to use unique messages in my block if no data found,
I have a below query which is false but when ever it is false then I should get a message like:

"Something to display"


DECLARE
  DA DATE;
  NO_DATE_FOUND EXCEPTION ;
BEGIN
  SELECT SYSDATE INTO DA FROM DUAL WHERE 1=2;
  IF DA IS NULL THEN
    RAISE NO_DATE_FOUND;
  END IF;
EXCEPTION
WHEN NO_DATE_FOUND THEN
  DBMS_OUTPUT.PUT_LINE('Something to display');
END;

Open in new window

0
Comment
Question by:pinkuray
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 2
9 Comments
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 34923915
so is it not displaying "something to display" here ?

What is the issue with your code ?
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 34923918
try this :

DECLARE
  DA DATE;
  NO_DATE_FOUND EXCEPTION ;
BEGIN
  SELECT SYSDATE INTO DA FROM DUAL WHERE 1=2;
  IF DA IS NULL THEN
    RAISE NO_DATE_FOUND;
  END IF;
EXCEPTION
WHEN NO_DATE_FOUND THEN
  DBMS_OUTPUT.PUT_LINE('Something to display');
when no_data_found then
  DBMS_OUTPUT.PUT_LINE('Update here to display what you want');
END;
/
0
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 500 total points
ID: 34923937
you were not getting that because NO_DATA_FOUND exception will get rasied and not your exception.

if you want to make your exception to raise when instead of NO_DATA_FOUND then you can another version as given below ( we use exception_init to tell pl/sql to display your message instead of no data found when it does not find any records for the select statement ) :

DECLARE
  DA DATE;
  NO_DATE_FOUND EXCEPTION ;
  pragma exception_init(no_date_found, 100 );
BEGIN
  SELECT SYSDATE INTO DA FROM DUAL WHERE 1=2;
  IF DA IS NULL THEN
    RAISE NO_DATE_FOUND;
  END IF;
EXCEPTION
WHEN NO_DATE_FOUND THEN
  DBMS_OUTPUT.PUT_LINE('Something to display');
END;
/
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 34923938
if you try the code given in update : 34923937, i am getting the output which you wanted :

Something to display
0
 
LVL 4

Author Comment

by:pinkuray
ID: 34923973
Hello nav_kum_v:
Your 2nd comment i.e. in 34923937 works fine for me but just for a clarification:

why it is then required declaring a NO_DATE_FOUND EXCEPTION ;

Rather then I can also use it like below:

DECLARE
  DA DATE;
BEGIN
  SELECT SYSDATE INTO DA FROM DUAL WHERE 1=2;
EXCEPTION
WHEN NO_DATA_FOUND THEN
  DBMS_OUTPUT.PUT_LINE('Something to display');
END;


The example was only for no data found what if I want to add many more condition like in my block i have 2 insert statement which need to be passed as like a parameter and then I want to check if the parameter should only be in caps now what i need to do here.


0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 34924055
<<<<<< why it is then required declaring a NO_DATE_FOUND EXCEPTION ;

Rather then I can also use it like below:

DECLARE
  DA DATE;
BEGIN
  SELECT SYSDATE INTO DA FROM DUAL WHERE 1=2;
EXCEPTION
WHEN NO_DATA_FOUND THEN
  DBMS_OUTPUT.PUT_LINE('Something to display');
END;

>>>>>>>>>>>> ---> please find my comments for this below :

NO_DATA_FOUND is generic and will get raised for any select statement which does not return data.  NO_DATE_FOUND is your own exception and in this case does not suit here becasue it does not give any data in the select itself.

0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 34924059
to make meaningful use of user defined exceptions : ( the below code is taken from
http://psoug.org/reference/exception_handling.html )

CREATE OR REPLACE FUNCTION is_ssn (string_in VARCHAR2)
RETURN VARCHAR2 IS
 -- validating ###-##-#### format
 delim VARCHAR2(1);
 part1 NUMBER(3,0);
 part2 NUMBER(2,0);
 part3 NUMBER(4,0);

 too_long  EXCEPTION;
 too_short EXCEPTION;
 delimiter EXCEPTION;
BEGIN
  IF LENGTH(string_in) > 11 THEN
    RAISE too_long;
  ELSIF LENGTH(string_in) < 11 THEN
    RAISE too_short;
  END IF;

  part1 := TO_NUMBER(SUBSTR(string_in,1,3),'999');

  delim := SUBSTR(string_in,4,1);
  IF delim <> '-' THEN
    RAISE delimiter;
  END IF;

  part2 := TO_NUMBER(SUBSTR(string_in,5,2),'99');

  delim := SUBSTR(string_in,7,1);
  IF delim <> '-' THEN
    RAISE delimiter;
  END IF;

  part3 := TO_NUMBER(SUBSTR(string_in,8,4),'9999');

  RETURN 'TRUE';
EXCEPTION
  WHEN too_long THEN
    dbms_output.put_line('More Than 11 Characters');
    RETURN 'FALSE';
  WHEN too_short THEN
    dbms_output.put_line('Less Than 11 Characters');
    RETURN 'FALSE';
  WHEN delimiter THEN
    dbms_output.put_line('Incorrect Delimiter');
    RETURN 'FALSE';
  WHEN OTHERS THEN
    dbms_output.put_line('Some Other Issue');
    RETURN 'FALSE';
END is_ssn;
/

set serveroutput on

SELECT is_ssn('123-45-6789') FROM dual;

SELECT is_ssn('123-45-67890') FROM dual;

SELECT is_ssn('123-45-678') FROM dual;

SELECT is_ssn('123-45=67890') FROM dual;
0
 
LVL 4

Author Closing Comment

by:pinkuray
ID: 34931455
Thanks for explaining...
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 34935279
Good.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and theā€¦
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question