Solved

Handling PL/SQL Errors

Posted on 2011-02-17
9
411 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
  • 7
  • 2
9 Comments
 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
so is it not displaying "something to display" here ?

What is the issue with your code ?
0
 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
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
Comment Utility
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
 
LVL 28

Expert Comment

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

Something to display
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 4

Author Comment

by:pinkuray
Comment Utility
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
Comment Utility
<<<<<< 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
Comment Utility
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
Comment Utility
Thanks for explaining...
0
 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
Good.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Space Delimited Sql File 4 70
SSN Format in Oracle 2 59
apply oracle patches on windows error 2 42
PAYER_ID has both atributes 4 19
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…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to recover a database from a user managed backup
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

772 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now