PowerServer and calling overloaded database functions

Hi,

I'm using PB2022, build 1900 with Oracle 19 database.

My question has to do with overloading and calling functions in a Oracle package. In native PB (Desktop) all works fine but with PowerServer there are issues.

Here is my test case. The package is TEST and it has two funtions

FUNCTION F_get_lucky
  RETURN  number IS
begin
    RETURN 777;

END;

FUNCTION F_get_lucky(p_number in number)  
  RETURN  number IS
begin
    RETURN p_number;
END;

In the application SQLCA these are defined as

function long f_get_lucky() RPCFUNC ALIAS FOR "test.f_get_lucky"
function long f_get_lucky(Long LuckyNumber ) RPCFUNC ALIAS FOR "test.f_get_lucky"

When calling the function from the app using e.g.

MessageBox("f_get_lucky", sqlca.f_get_lucky())

This the error message I get in the PowerServer console window.

"ErrorMessage": "ORA-06550: line 1, column 7:\nPLS-00306: wrong number or types of arguments in call to 'F_GET_LUCKY'\nORA-06550: line 1, column 7:\nPL/SQL: Statement ignored"

When calling a non-overloaded version of the function, all works fine.

Is it so that PowerServer doesn't not suport this type of overloading or should I do things differently?

Thanks,
Kari

Comment

Load more comments

Kari Paukku
Thursday, 14 September 2023 17:35 PM UTC

I forgot to show the definitions in PB for the functions

FUNCTION String IsBrokerCompany (Long CompanyKey) RPCFUNC ALIAS FOR "COMP.IsBrokerCompany"

FUNCTION String IsBrokerCompany (String CompanyID) RPCFUNC ALIAS FOR "COMP.IsBrokerCompany"

FUNCTION String IsBrokerCompanyByKey (Long CompanyKey) RPCFUNC ALIAS FOR "COMP.IsBrokerCompanyByKey"

Helpful 0

Kari Paukku
Tuesday, 19 September 2023 10:04 AM UTC

Hi,

Some additional info:

- The good news is that Appeon informed me that they have fixed the overloading issue and the fix will be included in the next PS release

- I did come up with a feasible workaround:

When I previously called SQLCA.IsBrokerCompany now I call SQLCA.of_IsBrokerCompany which looks like this

of_IsBrokerCompany(CompanyKey)

SELECT COMP:IsBrokerCompany(CompanyKey) INTO :ReturnValue FROM DUAL;

The above approach works and is easy to implement, I only need to change the original SQLCA.IsBrokerCompany tobe SQLCA.of_IsBrokerCompany

This way no changes are required to the database.

One additional comment: I sometimes get an error that the DW column type doesn't match what is returned from the DB.

The solution was to use CAST in the SQL statement for that column to make clear what data type DW expects to get.

An example would be when the query returns a field resulting from a function call and the function's return value is defined in Oracle as NUMBER, and in the DW the field is defined as a number. PS translates the field to double, although no decimals exist - the CAST solves this.

The last remaining issue is the "Transaction timeout"-error, which I haven't managed to sort out although I have tried all the suggestions I have found. It would be good if there were a mechanism in the SQLCA to reconnect automatically when this happens.

If I find a solution, I'll post it here.

Thanks.

Helpful 1

Armeen Mazda @Appeon
Tuesday, 19 September 2023 14:56 PM UTC

Thanks for sharing workaround!

There are no comments made yet.

Chris Pollach @Appeon
Monday, 24 April 2023 11:45 AM UTC

Hi Andreas... I don't believe that it's documented.

Chris Pollach @Appeon
Monday, 24 April 2023 11:50 AM UTC

Hi Kari;

As a workaround, I would suggest creating an "interface" DB function that includes all possible arguments. Then internally within the Oracle DBMS that function could then call the overloaded functions as required. HTH

Regards... Chris

Kari Paukku
Tuesday, 25 April 2023 06:24 AM UTC

Thanks, but unfortunately, I don't think that is an option for us as we rely heavily on packages and overloading..I guess I can't do much more than wait and hope this gets solved in the next PB/PS update.

This is a pity as everything else was working quite fine.

Resolved PowerServer and calling overloaded database functions