By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,197 Members | 972 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,197 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Jul 19 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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 --
Jul 19 '05 #2

P: n/a
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
Jul 19 '05 #3

P: n/a
>
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
Jul 19 '05 #4

P: n/a
> 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
Jul 19 '05 #5

P: n/a
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
Jul 19 '05 #6

P: n/a
> 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
Jul 19 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.