Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004
    Location
    Munich, Germany
    Posts
    35

    Unanswered: dynamic error message to raise_application_error

    Hi everyone,

    I want to pass a dynamic string to the SQLERRM parameter to raise_application_error. For example, in SQL Server I could do like:

    RAISERROR ('InputNamingDefinitions_T does not have a value entered for this InternalName %s', 11,1, @_InternalName)

    I would like to know how I can achieve the same functionality in an Oracle stored procedure.

    Thanks,

    Sinecerely,
    Pankaj

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You can write your own procedure to do that. Something like this:

    Code:
    create or replace procedure raise_error
    ( p_message in varchar2
    , p_val1 in varchar2 default null
    , p_val2 in varchar2 default null
    , p_val3 in varchar2 default null
    )
    is
      v_message varchar2(200) := p_message;
      v_pos integer;
    begin
      v_pos := instr( v_message, '<P1>' );
      if v_pos > 0 then
        v_message := substr(v_message,1,v_pos-1) || p_val1 || substr(v_message,v_pos+4 );
      end if;
      v_pos := instr( v_message, '<P2>' );
      if v_pos > 0 then
        v_message := substr(v_message,1,v_pos-1) || p_val2 || substr(v_message,v_pos+4 );
      end if;
      v_pos := instr( v_message, '<P3>' );
      if v_pos > 0 then
        v_message := substr(v_message,1,v_pos-1) || p_val3 || substr(v_message,v_pos+4 );
      end if;
      raise_application_error(-20001,v_message);
    end;
    I have allowed for up to 3 values, but you could obviously extend.
    Examples:
    Code:
    SQL> exec raise_error('This error has no args');
    BEGIN raise_error('This error has no args'); END;
    
    *
    ERROR at line 1:
    ORA-20001: This error has no args
    ORA-06512: at "TANDREWS.RAISE_ERROR", line 23
    ORA-06512: at line 1
    
    
    SQL> exec raise_error('This error has one arg: <P1>','ARG1')
    BEGIN raise_error('This error has one arg: <P1>','ARG1'); END;
    
    *
    ERROR at line 1:
    ORA-20001: This error has one arg: ARG1
    ORA-06512: at "TANDREWS.RAISE_ERROR", line 23
    ORA-06512: at line 1
    
    
    SQL> exec raise_error('Employee <P1> has rate <P2> on date <P3>','KING',123.45,SYSDATE)
    BEGIN raise_error('Employee <P1> has rate <P2> on date <P3>','KING',123.45,SYSDATE); END;
    
    *
    ERROR at line 1:
    ORA-20001: Employee KING has rate 123.45 on date 23-JUL-2004
    ORA-06512: at "TANDREWS.RAISE_ERROR", line 23
    ORA-06512: at line 1

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •