Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.oracle -> Re: Problem trying to update a record with a value returned by a function

Re: Problem trying to update a record with a value returned by a function

From: Bob M <rjm2004_at_shaw.ca>
Date: 13 Feb 2004 14:30:07 -0800
Message-ID: <e151b418.0402131430.13517848@posting.google.com>

> Bob -
>
> I'm stumped. Another thought at isolating the problem....use the NVL
> function as shown below. If the column is updated with the hard-coded
> date, this would further prove your function IS returning NULL in this
> context.
>
> update ITSM_SER_CUSTOM_FIELDS
> set SCF_SCDATE2 = NVL(SLA_PENDING_DATE.GET_DL(2292,'servicecall'),
> TO_DATE('3000/12/31', 'YYYY/MM/DD'))
> where SCF_SER_OID = (select SER_OID from ITSM_SERVICECALLS where
> SER_ID = 2292);
>
> Dave

Thanks for the suggetion Dave. I tried the NVL function and I am indeed inserting a null value in the table. Or at least I was (now I have an entry of 3000/12/31) ;-)

I'm baffled as to why the fuction returns a null inside a SQL statement but returns a valid date when I run it as a separate SQL statement.

Oh well. C'est la vie!

Guess I'll play around with calling the function from a trigger which was going to be my next development step anyway.

Thanks again for the suggestion.

Bob Received on Fri Feb 13 2004 - 16:30:07 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US