Re: Oracle Function to Return the error text associated with an oracle error code

  • From: Adam Musch <ahmusch@xxxxxxxxx>
  • To: norman.dunbar@xxxxxxxxxxxxxxxxxxxxxxxxx
  • Date: Thu, 7 Jan 2010 08:40:46 -0600

That certainly seems safe to put into a deterministic pl/sql function
to reference from SQL.

in 10.2.0.4:

SQL> l
  1  create or replace function oerr (p_error in number)
  2  return varchar2
  3  deterministic
  4  as
  5  begin
  6    return sqlerrm(p_error);
  7* end;
SQL> /

Function created.

SQL> select oerr(-1) from dual;

OERR(-1)
--------------------------------------------
ORA-00001: unique constraint (.) violated

On Thu, Jan 7, 2010 at 8:07 AM, Dunbar, Norman
<norman.dunbar@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
> Hi Rumpi,
>
> this works in PL/SQL:
>
> SQL> begin
>  2    dbms_output.put_line(sqlerrm(-949));
>  3  end;
>  4 /
> ORA-00949: illegal reference to remote database
>
> But I'm not sure about pure SQL though, other than:
>
> SQL> var oops varchar2(4000)
> SQL> begin
>  2     :oops := sqlerrm(-949);
>  3  end;
>  4  /
>
> PL/SQL procedure successfully completed.
>
> SQL> print oops
>
> OOPS
> ------------------------------------------------
> ORA-00949: illegal reference to remote database
>
>
> Or, calling a function, passing the error code and getting a string
> back?
>
>
> Cheers,
> Norm.
>
>
> Information in this message may be confidential and may be legally 
> privileged. If you have received this message by mistake, please notify the 
> sender immediately, delete it and do not copy it to anyone else.   We have 
> checked this email and its attachments for viruses. But you should still 
> check any attachment before opening it. We may have to make this message and 
> any reply to it public if asked to under the Freedom of Information Act, Data 
> Protection Act or for litigation.  Email messages and attachments sent to or 
> from any Environment Agency address may also be accessed by someone other 
> than the sender or recipient, for business purposes.  If we have sent you 
> information and you wish to use it please read our terms and conditions which 
> you can get by calling us on 08708 506 506.  Find out more about the 
> Environment Agency at www.environment-agency.gov.uk
>
> Information in this message may be confidential and may be legally 
> privileged. If you have received this message by mistake, please notify the 
> sender immediately, delete it and do not copy it to anyone else.
>
> We have checked this email and its attachments for viruses. But you should 
> still check any attachment before opening it.
> We may have to make this message and any reply to it public if asked to under 
> the Freedom of Information Act, Data Protection Act or for litigation.  Email 
> messages and attachments sent to or from any Environment Agency address may 
> also be accessed by someone other than the sender or recipient, for business 
> purposes.
>
> If we have sent you information and you wish to use it please read our terms 
> and conditions which you can get by calling us on 08708 506 506.  Find out 
> more about the Environment Agency at www.environment-agency.gov.uk
> --
> //www.freelists.org/webpage/oracle-l
>
>
>



-- 
Adam Musch
ahmusch@xxxxxxxxx
--
//www.freelists.org/webpage/oracle-l


Other related posts: