• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerServer
  • PowerServer and calling overloaded database functions

Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (3)

Resolved PowerServer and calling overloaded database functions

  1. Advice
0
Votes
Undo
  1. Kari Paukku
    Kari Paukku
  2. PowerServer
  3. Monday, 24 April 2023 03:31 AM UTC

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


  1. External Functions
  2. Oracle
  3. PowerServer
Updating Cloud App Launcher
How to cancel a long running query in PowerServer ...
Responses (3)
Andreas Mykonios
Andreas Mykonios Accepted Answer Pending Moderation
  1. Monday, 24 April 2023 06:50 AM UTC
  2. PowerServer
  3. # 1

Hi.

Are those function declared to package test? If they exists only in package body you won't be able to call them.

Andreas.

Comment
Load more comments
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Monday, 24 April 2023 11:45 AM UTC
Hi Andreas... I don't believe that it's documented.
  1. Helpful
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. 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
  1. Helpful 1
Kari Paukku
  1. Kari Paukku
  2. 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.



  1. Helpful
There are no comments made yet.
David Xiong @Appeon
David Xiong @Appeon Accepted Answer Pending Moderation
  1. Thursday, 27 April 2023 06:34 AM UTC
  2. PowerServer
  3. # 2

Hi Kari,

 

We have seen the private ticket that you submitted, and we will follow up on this problem in the ticket system.

 

Regards,

David

Comment
Load more comments
Kari Paukku
  1. Kari Paukku
  2. 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"



  1. Helpful
Kari Paukku
  1. Kari Paukku
  2. 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.







  1. Helpful 1
Armeen Mazda @Appeon
  1. Armeen Mazda @Appeon
  2. Tuesday, 19 September 2023 14:56 PM UTC
Thanks for sharing workaround!
  1. Helpful
There are no comments made yet.
Kari Paukku
Kari Paukku Accepted Answer Pending Moderation
  1. Friday, 15 September 2023 00:04 AM UTC
  2. PowerServer
  3. # 3

David,

any updates on this?

Thanks,
Kari

Comment
Armeen Mazda @Appeon
  1. Armeen Mazda @Appeon
  2. Friday, 15 September 2023 01:45 AM UTC
Please communicate with David in the ticket since this sounds like a bug.
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.