• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

PL/SQL Exception Handling

 
Ranch Hand
Posts: 226
1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I have a stored procedure that calls another stored procedure. I'll call those PL/SQL stored procedures, stored procedure A and stored procedure B. Stored procedure A calls stored procedure B. Stored procedure B raises a NO_DATA_FOUND exception and throws it to stored procedure A. Stored procedure A has an exception block that handles the NO_DATA_FOUND exception. However, I've found that stored procedure A is not catching the NO_DATA_FOUND exception that is thrown by stored procedure B. Does anyone know why?
 
Bartender
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
We'd probably need to see some code as it's hard to guess otherwise e.g. have you got your "WHEN" clauses in the right order, are you sure the exception is being raised, are you sure it's not being caught in the calling proc?
 
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Exceptions (PL/SQL runtime errors) can arise from design faults, coding mistakes, hardware failures, and many other sources. You cannot anticipate all possible exceptions, but you can write exception handlers that let your program to continue to operate in their presence.

Any PL/SQL block can have an exception-handling part, which can have one or more exception handlers. For example, an exception-handling part could have this syntax:

EXCEPTION
WHEN ex_name_1 THEN statements_1 -- Exception handler
WHEN ex_name_2 OR ex_name_3 THEN statements_2 -- Exception handler
WHEN OTHERS THEN statements_3 -- Exception handler
END;
In the preceding syntax example, ex_name_n is the name of an exception and statements_n is one or more statements

Java training in chennai | Android training in chennai | Oracle dba Training in Chennai | Python Training in chennai
 
Fred Victa
Ranch Hand
Posts: 226
1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

chris webster wrote:We'd probably need to see some code as it's hard to guess otherwise e.g. have you got your "WHEN" clauses in the right order, are you sure the exception is being raised, are you sure it's not being caught in the calling proc?



I found out what was causing the problem. I was trying to set the value of error_code_in to -390, but this error code was not initialized with the pragma exception_init() function.

q$error_manager.raise_error
(
error_code_in => -390,
text_in => 'Cannot lock revision.'
);
 
reply
    Bookmark Topic Watch Topic
  • New Topic