Re: RAISE_APPLICATION_ERROR

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 1996/06/05
Message-ID: <31b5ad1a.7707983_at_dcsun4>#1/1

On Wed, 05 Jun 1996 00:11:56 GMT, Paul Rice <tanner_at_mcs.net> wrote:

>I am working with a product called Clarify. Clarify does not provide for
>field validation, so I am using triggers and stored procedures. I use the
>raise_application_error procedure to send a message to the application but
>it is very ugly.
>
>Are there any configurable parameters for the raise_application_error?
>
 No
>I want to clean up the message.
>
>Or is there a way to send a message from a trigger of sproc to a windows
>application?
>
Sort of, try the following:

create or replace package msg_pkg
as

	procedure set_msg( p_msg in varchar2 );
	function  get_msg return varchar2;

end msg_pkg;
/

create or replace package body msg_pkg
as

g_msg varchar2(2000);

procedure set_msg( p_msg in varchar2 )
is
begin

        g_msg := p_msg;
end set_msg;

function get_msg return varchar2
is
begin

        return g_msg;
end get_msg;

end msg_pkg;
/

And then in your trigger you would code:

create or replace trigger my_trigger
after update of x on y
for each row
begin

   if ( some_error_condition ) then

	msg_pkg.set_msg( 'Something you like' );
	raise_application_error( -20000, 'Some Error' );

   end if;
end;
/

And in your code you would have:

   exec sql update y set x = 5;
   if ( error ) then

	exec sql execute begin :msg := msg_pkg.get_msg; end; end-exec;
	print( msg );

   end if;

Hope this helps.         

>I am using win95 on the client and the server version is 7.1.6.2.1
>
>
>
>

Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com                          

http://govt.us.oracle.com -- Check out our web site! Brand new, uses Oracle Web Server and Database

---

statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Wed Jun 05 1996 - 00:00:00 CEST