Problem calling PLSQL Function

[andyskinner]

Hya,

Have a huge PLSQL function that does a bunch of calculations. The function works fine when tested in a begin statement like so;

begin
-- Call the function
:result := something.fnc_calculate_cost(cicpiid => :cicpiid,
cdstart => :cdstart,
cdend => :cdend);
end;

However, when the function is used as part of a select statement (with the same input parameters) like so;

select fnc_calculate_cost(48438,to_date('14-06-2004','dd-mm-yyyy'),to_date('20-06-2004','dd-mm-yyyy')) as test
from dual

It consistently returns zero! I have no idea why this would happen, I would have expected it to work in both cases. Anyone got any ideas as to what could be going wrong?

Cheers,

Andy Skinner.

 

[SantaMufasa]

Andy,

Among the issues that concern me are that you really aren't invoking the two function calls in the same manner. In the successful invocation, you are using non-positional argument passing ("=>") versus positional arguments in the test "from dual;". It would be helpful to see (pasted here) at least your function header (i.e., everything down to your "BEGIN" statement in the function. The other thing you can try is to test your failing invocation using non-positional parameters as you did in the successful invocation.

Looking forward to your update to this thread,

Mufasa
(aka Dave of Sandy, Utah, USA @ 17:02 (18Jun04) UTC (aka "GMT" and "Zulu"), 10:02 (18Jun04) Mountain Time)

 

[andyskinner]

Heres the function header (slightly anonymised) it`s just an interface function for a function that lives in a package;

CREATE OR REPLACE FUNCTION PARIS.FNC_CALCULATE_COST(ciCpiID IN cpl_inter.cpi_id%TYPE,
cdStart IN cpl_inter.cpi_act_start_date%TYPE,
cdEnd IN cpl_inter.cpi_act_end_date%TYPE)
RETURN lib_cost.lco_cost%TYPE
IS
Result lib_cost.lco_cost%TYPE;
BEGIN

 

[andyskinner]

BTW, You can`t call a SQL Function in a Select statment without using positional notation, not in Oracle 8i anyway.
Named and mixed notations are not allowed.

Ta,

Andy.

 

[sem]

Is cpl_inter.cpi_act_start_date%TYPE DATE? It's not quite obvious, because in pl/sql block you SUCCESSFULLY passed VARCHAR2 that might be implicitly converted to DATE or not.

Regards, Dima

http://seminihin.narod.ru

 

[andyskinner]

Sorry.

cpi_act_start_date is a date type.
cpi_act_end_date is a date type.
cpi_id is a number.

Thx for the help.

Andy.

 

[andyskinner]

Problem over.

It appears that the developer of this function had put a catch all exception in that returned zero. On removing this, the real error was showing. The function was doing an UPDATE somewhere in the code and so Oracle wouldn`t allow it from a SELECT Statement!

Andy.