Handling PL/SQL Errors

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

LVL 4
pinkurayAsked:
Who is Participating?
 
Naveen KumarConnect With a Mentor Production Manager / Application Support ManagerCommented:
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
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
so is it not displaying "something to display" here ?

What is the issue with your code ?
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Naveen KumarProduction Manager / Application Support ManagerCommented:
if you try the code given in update : 34923937, i am getting the output which you wanted :

Something to display
0
 
pinkurayAuthor Commented:
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
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
<<<<<< 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
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
pinkurayAuthor Commented:
Thanks for explaining...
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
Good.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.