Re: ORA-06571: Function USER_DEF_CASE does not guarantee not to update database

"Sagar Nandela" <nandela_v_at_yahoo.com> wrote in message news:fdbe4d9b.0111010726.2ebe8f17_at_posting.google.com... > "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:<tu0tiotaoh485b_at_corp.supernews.com>...
> > "Sagar Nandela" <nandela_v_at_yahoo.com> wrote in message
> > news:fdbe4d9b.0110311342.66d4b1a3_at_posting.google.com...
> > > Hi
> > >
> > > I wrote a function called user_def_case.i want to call this function
> > > in select statement.
> > >
> > > Example: SELECT user_def_case(1500,1,'0_2000') from dual;
> > >
> > > I am getting the following error.
> > >
> > > ORA-06571: Function USER_DEF_CASE does not guarantee not to update
> > > database
> > >
> > > Inside function i want to insert errors into error_log table in
> > > exception handler if function fails due to data problem.If i remove
> > > this insert statement in exception handler section it will work.I saw
> > > posted messages related to this error.They suggested use pragram
> > > wnds.but it will not solve my problem.If i put this pragma wnds,it
> > > will not write errors into error_log table.I want to write errors into
> > > error_log table.Please help me,how to solve this problem.i am using
> > > oracle 8.0.5.
> > >
> > > Sagar
> >
> > Is there any reason why you can't use
> > begin
> > dbms_output.put_line(user_def_case(1500,1,'0_2000'));
> > end;
> > /
> >
> > You don't do anything with the function result, so the code above is
> > equivalent and also doesn't result in 6571.
> >
> > Hth

>
> Hi Sybrand Bakker,
>
>    Thanks for your reply.But my requirement is after getting the
> results from function,i have to do some calculation based on results
> in select statement, not pl/sql anonymous block.If my own function
> fails due to some input data ,
> I want to insert error message details (i.e which area is giving
> problem) into error_log table in exception handler of my own function
> and Return as NULL.
> Is there any work around solution to slove this problem?
>
> Sagar

Not in 8.0.5, which has been desupported more than a year ago anyway. In 8i you can get around this by using autonomous transactions for your logging. I do not want to debate whether what you are up to is good programming practice. I have learned to avoid such use of stored functions like hell, the performance is usually really awful. But that's up to you.

Hth

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address