Home » SQL & PL/SQL » SQL & PL/SQL » How to get values of parameters in exception block?
How to get values of parameters in exception block? [message #203168] Tue, 14 November 2006 03:15 Go to next message
sottm
Messages: 2
Registered: November 2006
Junior Member
Here is my problem. I need to develop errors log system, i mean insert into table info about the error raised in the procedure or packege. I decide to create function wich call in exception block of every procedure or function, this function must insert into table values and names of parameters which sended to procedure or function which raise error. The question is how to get parameters values in time when error is raised from my log function.
Example:
1 Create Or Replase Function Foo (a number, b Varchar2)
2 Is
3 Begin
4 <Do_Somesing>;
5 Exception
6 When Others Then
7 MyLogFunc();
8 End;

Create Or Replase Function MyLogFunc ()
Is
Begin
<Get_Names "a,b">; -- I know how
<Get_Values : Values of a,b In 7>; -- I dont know how Sad
Insert Into LogTable (...) ...
End;


[Updated on: Tue, 14 November 2006 03:17]

Report message to a moderator

Re: How to get values of parameters in exception block? [message #203173 is a reply to message #203168] Tue, 14 November 2006 03:24 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Your call to MyLogFunc will need to have the parameters passed to it. There isn't any way of getting at the values of parameters from the calling procedure that weren't passed to MyLogFunc explicitly.

You will also want to include a RAISE statement in the exception blocks, otherwise errors that really should propogate to the calling program will simply disapear.
Re: How to get values of parameters in exception block? [message #203177 is a reply to message #203168] Tue, 14 November 2006 03:33 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
I don't believe the values and names of parameters are saved in any sort of structure. The only way you will be able to do it is if you pass the parameters to your error proc from the calling proc.

To make it generic you could have two PL/SQL table parameters called parameter_name_list and parameter_value_list. You could then pack the names of the variables into the first table and values into the second e.g.

v_param_nm_list ErrorLogPackage.t_name_list;
v_param_val_list ErrorLogPackage.t_name_val;
...

exception
   v_param_nm_list(1) := 'A';
   v_param_nm_list(2)  := 'B';
   v_param_val_list(1) := to_char(a);
   v_param_val_list(2) := b;
   ErrorLogPackage.MyLogFunc(v_param_val_list, v_param_nm_list,...);
end;

You'd probably want a few other parameters as well such as the sqlcode, sql error text, name of the calling procedure etc.

[Updated on: Tue, 14 November 2006 03:36]

Report message to a moderator

Re: How to get values of parameters in exception block? [message #203179 is a reply to message #203177] Tue, 14 November 2006 03:43 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Just to generalize the solution.

I think better to call the procedure with the parameter name and the value one by one. It is useful if you don't know the number of parameters.
It might be a performance issue.
Or you can concatenate everything and pass it to the procedure.

By
Vamsi.
Re: How to get values of parameters in exception block? [message #203186 is a reply to message #203168] Tue, 14 November 2006 03:58 Go to previous message
sottm
Messages: 2
Registered: November 2006
Junior Member
Thanks everyone, realy exist table where names of parameters func and proc, about values - no way, only pass parameters in MyLogFunc
Previous Topic: Using UTL_MAIL
Next Topic: Schema v/s Database
Goto Forum:
  


Current Time: Mon Dec 05 21:19:20 CST 2016

Total time taken to generate the page: 0.20225 seconds