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

[Bob M]

Hi all,

I am trying to update a record with a function value. Here is the
function:

update ITSM_SER_CUSTOM_FIELDS
set SCF_SCDATE2 = SLA_PENDING_DATE.GET_DL(2292,'servicecall')
where SCF_SER_OID = (select SER_OID from ITSM_SERVICECALLS where
SER_ID = 2292);

When I run this statement, I get the message "1 row updated". After
doing a commit, I look at the record in question and the SCF_SCDATE2
field is blank.

When I run the statement:

select SLA_PENDING_DATE.GET_DL(2292,'servicecall') from dual;

I get a valid date returned.

If I modify my SQL statement to substitute "sysdate" for the function
value, the current date appears in the field.

Any ideas why is the date field not being updated in the db when I use
the function?

Thanks for any help,
Bob

[Mark D Powell]

rj*****@shaw.ca (Bob M) wrote in message news:<e1**************************@posting.google. com>...

Hi all,

I am trying to update a record with a function value. Here is the
function:

update ITSM_SER_CUSTOM_FIELDS
set SCF_SCDATE2 = SLA_PENDING_DATE.GET_DL(2292,'servicecall')
where SCF_SER_OID = (select SER_OID from ITSM_SERVICECALLS where
SER_ID = 2292);

When I run this statement, I get the message "1 row updated". After
doing a commit, I look at the record in question and the SCF_SCDATE2
field is blank.

When I run the statement:

select SLA_PENDING_DATE.GET_DL(2292,'servicecall') from dual;

I get a valid date returned.

If I modify my SQL statement to substitute "sysdate" for the function
value, the current date appears in the field.

Any ideas why is the date field not being updated in the db when I use
the function?

Thanks for any help,
Bob

Bob, verify that the table column type and the function return type
are the same or add the necessary explicit conversion.

You show that the function returns the expected value, but does the
subquery return the expected value.

Also by blank, do you mean NULL?

HTH -- Mark D Powell --

[Dave]

rj*****@shaw.ca (Bob M) wrote in message news:<e1**************************@posting.google. com>...

Hi all,

I am trying to update a record with a function value. Here is the
function:

update ITSM_SER_CUSTOM_FIELDS
set SCF_SCDATE2 = SLA_PENDING_DATE.GET_DL(2292,'servicecall')
where SCF_SER_OID = (select SER_OID from ITSM_SERVICECALLS where
SER_ID = 2292);

When I run this statement, I get the message "1 row updated". After
doing a commit, I look at the record in question and the SCF_SCDATE2
field is blank.

When I run the statement:

select SLA_PENDING_DATE.GET_DL(2292,'servicecall') from dual;

I get a valid date returned.

If I modify my SQL statement to substitute "sysdate" for the function
value, the current date appears in the field.

Any ideas why is the date field not being updated in the db when I use
the function?

Thanks for any help,
Bob

Bob -

For giggles, try this and see what you get as a result...

SELECT SLA_PENDING_DATE.GET_DL(2292,'servicecall')
FROM ITSM_SER_CUSTOM_FIELDS
where SCF_SER_OID = (select SER_OID from ITSM_SERVICECALLS where SER_ID = 2292);

Dave

[Bob M]

>

Bob, verify that the table column type and the function return type
are the same or add the necessary explicit conversion.

You show that the function returns the expected value, but does the
subquery return the expected value.

Also by blank, do you mean NULL?

HTH -- Mark D Powell --

Mark,

Thanks for the reply.

The column datatype and the function return both have a DATE datatype.

The subquery returns a OBJid. I have tried substituting the OBJid
value for the subquery
update ITSM_SER_CUSTOM_FIELDS
set SCF_SCDATE2 = SLA_PENDING_DATE.GET_DL(2292,'servicecall')
where SCF_SER_OID = 281479223967745;

but same result.
And finally the SCF_SCDATE2 field is null not blank (sorry for the
slip there).

I thought I read somewhere on the Net about a restriction on calling a
PL/SQL function or procedure within an insert or update statement.
Any idea if this is true?

Cheers,
Bob

[Bob M]

> Bob -

For giggles, try this and see what you get as a result...

SELECT SLA_PENDING_DATE.GET_DL(2292,'servicecall')
FROM ITSM_SER_CUSTOM_FIELDS
where SCF_SER_OID = (select SER_OID from ITSM_SERVICECALLS where SER_ID = 2292);

Dave

Dave,

This select statement returns a date.
The same if I perform a select function(p1,p2) from dual.

Bob

[Dave]

rj*****@shaw.ca (Bob M) wrote in message news:<e1*************************@posting.google.c om>...

Bob -

For giggles, try this and see what you get as a result...

SELECT SLA_PENDING_DATE.GET_DL(2292,'servicecall')
FROM ITSM_SER_CUSTOM_FIELDS
where SCF_SER_OID = (select SER_OID from ITSM_SERVICECALLS where SER_ID = 2292);

Dave

Dave,

This select statement returns a date.
The same if I perform a select function(p1,p2) from dual.

Bob

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

[Bob M]

> 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