formatting (or supplying string to) exception's messages in oracle

Question

There are parameterized error messages in Oracle database. For example, there is 01919, 00000, "role '%s' does not exist" in oraus.msg. If one issue some nonsense GRANT ... TO ... %s is substituted by this nonexistent privilege. It is possible to raise exception -1919 and supply some string to %s?

Code:

not_system_privilege EXCEPTION;
    PRAGMA EXCEPTION_INIT(not_system_privilege, -01919);
.......
RAISE not_system_privilege;

produces only ORA-01919: role '' does not exist message.

Answer 1

The purpose of user-defined exceptions is that we can trap specific exceptions in the exception section of our PL/SQL program and handle them elegantly. For instance, if we put some flesh around your code snippet....

create or replace grant_priv 
    ( p_priv in varchar2
      , p_grantee in varchar2 )
is
    not_system_privilege EXCEPTION;
    PRAGMA EXCEPTION_INIT(not_system_privilege, -01919);  
begin
    execute immediate 'grant '||p_priv||' to '||p_grantee;
exception
    when not_system_privilege then
        raise_application_error(-20000, p_priv||' is not a real privilege', true);
    when others then
        raise;

end;

We can put anything in the EXCEPTIONS section. Log the error in a table or file, raise alerts, whatever. It is good practice to propagate the exception upwards: only the toppermost layer of the callstack - the user-facing layer - shouldn't hurl exceptions.

Answer 2

An observation - it looks like you can use utl_lms.format_message for C-style printing - wish I'd known this earlier (as would have saved writing it). Seems to be Ora10 and above only.

begin
    dbms_output.put_line(
       utl_lms.format_message( 
       'A %s is here and a %s is there and a %s too','Giraffe','Lion','Spider'));
 end;

I can't see any way to meet the OPs requirement - to RAISE a system-level exception and substitute in the right parameter.

However, if you can live with using a different exception number, you could write your own exception handling procedure that could

a) take in the serial of the required exception b) use utl_lms.get_message to retrieve the text c) use format_message to substitute in the parameters d) raise a user defined exception using the generated text

The problem is that this would not work if your calling system expects an ORA-01919.