Just to elaborate a bit more on Henry's answer, you can also use specific error codes, from raise_application_error and handle them accordingly on the client side. For example:
Suppose you had a PL/SQL procedure like this to check for the existence of a location record:
PROCEDURE chk_location_exists
(
p_location_id IN location.gie_location_id%TYPE
)
AS
l_cnt INTEGER := 0;
BEGIN
SELECT COUNT(*)
INTO l_cnt
FROM location
WHERE gie_location_id = p_location_id;
IF l_cnt = 0
THEN
raise_application_error(
gc_entity_not_found,
'The associated location record could not be found.');
END IF;
END;
The raise_application_error allows you to raise a specific error code. In your package header, you can define:
gc_entity_not_found INTEGER := -20001;
If you need other error codes for other types of errors, you can define other error codes using -20002, -20003, etc.
Then on the client side, you can do something like this (this example is for C#):
/// <summary>
/// <para>Represents Oracle error number when entity is not found in database.</para>
/// </summary>
private const int OraEntityNotFoundInDB = 20001;
And you can execute your code in a try/catch
try
{
// call the chk_location_exists SP
}
catch (Exception e)
{
if ((e is OracleException) && (((OracleException)e).Number == OraEntityNotFoundInDB))
{
// create an EntityNotFoundException with message indicating that entity was not found in
// database; use the message of the OracleException, which will indicate the table corresponding
// to the entity which wasn't found and also the exact line in the PL/SQL code where the application
// error was raised
return new EntityNotFoundException(
"A required entity was not found in the database: " + e.Message);
}
}