Problem retrieving value from Oracle-Function

Dear SysOp,

we are intensively using Stored Procedure calls with the 24x7 scheduler (v 3.6.5) and no probs so far, everything working fine! But when we try to call a function in an Oracle Database (10.2) we do not retrieve the expected value.

Sample JAL:

Dim ret_year, number
DatabaseConnect("HDCT")

// Calling the function encapsulated in a package
DatabaseRetrieve("select my_package.get_current_year() from dual", ret_year)
messagebox(ret_year)

DatabaseDisconnect()

The Problem ist that the returnvalue 'ret_year' is a zero, but we do expect the value '2009' as a number (it is even the one and only value in the table). Testing this function directly in SQL-Plus or via Java works fine. Do you have a solution for us? To illustrate the problem the functions code below:

-- determine current year
FUNCTION "GET_CURRENT_YEAR" RETURN NUMBER AS
year NUMBER;
BEGIN
year := 0;
SELECT current_year INTO year FROM batch_prog_date;
RETURN year;
EXCEPTION
WHEN OTHERS THEN
RETURN - 1;
END;

Kind regards from germany,
Jens

Hi,

There is a logical error is your JAL code. DatabaseRetrieve statement returns number of rows affected, not a return value of a function referenced in SELECT statement. To give you a better idea, please consider the following valid code
DatabaseRetrieve("SELECT * FROM all_objects", rows)

Here is how you could change the code to obtain the retuned year value.

	Code:
	Dim ret_year, number Dim rows, number DatabaseConnect("HDCT") // Calling the function encapsulated in a package DatabaseRetrieve("select my_package.get_current_year() from dual", rows) // Get the value in first colum of first row of the returned result-set DatabaseGet( 1, 1, ret_number) messagebox(ret_year) DatabaseDisconnect()

Hope this helps.

Good morning!

That helped a lot! Unfortunately we only had some experiences working with Stored Procedure Calls, so working without retrieving Resultsets.. Now everything works fine, so thanx a lot for your speedy reply!

Jens