Home » SQL & PL/SQL » SQL & PL/SQL » function changes not recognised
function changes not recognised [message #193620] Mon, 18 September 2006 08:49 Go to next message
tessdeveloper
Messages: 17
Registered: August 2006
Location: Ireland
Junior Member
Hi,

I've made changes to a pl/sql function. The function has compiled properly with the new changes, however when I run the system via forms interface the values calculated from that function are got based on the old function, in other words the system seems to be calling the old function when it is run. why this is happening, I don't know because the new function should have overwritten the old function on the database repository.

The strange thing is that when you go to view that function through pl/sql developer, you can see that the function contains the new changes that were made. also when I step through the function, the values calculated do reflect the new changes that were made.

Any help at all would be great. If ye need any more info regarding d function etc, just let me know.
Re: function changes not recognised [message #193623 is a reply to message #193620] Mon, 18 September 2006 09:02 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The Form isn't running the function that you think it's running is the simple answer.
There must be another copy of this function kicking about in the Db somewhere.
As system (or any other DBA account), run
SELECT * 
FROM   dba_objects
WHERE  object_name = '<Function Name>';

and see if there are any other versions of the code listed.
Re: function changes not recognised [message #193740 is a reply to message #193623] Tue, 19 September 2006 03:53 Go to previous messageGo to next message
tessdeveloper
Messages: 17
Registered: August 2006
Location: Ireland
Junior Member
I ran the sql statement that you suggested for the function and then checked it for other funcitons and it returned the same amount of rows each time, i.e. for all the functions including the one that we changed, two rows of data were returned.

one row had owner = public and object_type = synonym, the other row had owner = tess_dba and object_type = function.

we deleted the function and recreated it again and still the same problem.

Sorry for not getting back to you earlier.
Re: function changes not recognised [message #193741 is a reply to message #193740] Tue, 19 September 2006 03:59 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That's odd. You did definitely do this as a DBA account?

When you run the functiob from SQL*Plus, do you get the right results?

Re: function changes not recognised [message #193801 is a reply to message #193740] Tue, 19 September 2006 08:32 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
tessdeveloper wrote on Tue, 19 September 2006 04:53


we deleted the function and recreated it again and still the same problem.



Sounds to me like one of two possibilities.

1) You are not connecting to the database you think you are.
2) The function is returning the correct value even if you think it isn't.

Why not post the function for us to see. Before and After
Re: function changes not recognised [message #193815 is a reply to message #193801] Tue, 19 September 2006 09:21 Go to previous message
tessdeveloper
Messages: 17
Registered: August 2006
Location: Ireland
Junior Member
we're after finding out now that when we run the system, it is running the function with the new changes, which only makes sense, however its still not executing all of the code correctly. However when we step through the code using pl/sql, it works properly

Here is the piece of code that's causing all the agro

update table_a rp
set rp.rp_uip = (select decode(sign(rp.rp_ui)
0,0,
1, decode(sign(rp.rp_xyii),
-1,1,
2),
-1, decode(sign(rp.rp_xyii),
1,3,
4)
)
from table_b pr
where rp.pd_id = pr.pd_id
)
where rp.pd_id between v_start_period and v_start_period + x_periods;

when we run the system, the only values returned are 2 and 4. when we step through it, all the other values are returned for any given day
Previous Topic: Distinct within a procedure
Next Topic: aggregate functions
Goto Forum:
  


Current Time: Sun Dec 04 06:36:41 CST 2016

Total time taken to generate the page: 0.10176 seconds