Hi,
(Using PB 2021 Build 1506, Oracle 19 back end)
I have been working with Don C of Intertech trying to sort out some of our calls to Oracle stored procedures / functions / packages. Found that the following call doesn't work when deploying to PowerServer in my local environment, but works fine in PB 2021
DECLARE get_tank_name PROCEDURE FOR f_getTankName (:as_company_id,:as_battery_id,:as_tank_id);
EXECUTE get_tank_name;
If (SQLCA.SQLCode = 0) Then
FETCH get_tank_name INTO :is_tankName;
ElseIf (SQLCA.SQLCode = 100) Then
is_tankName = ''
End If
CLOSE get_tank_name;
RETURN is_tankName
it returns the following error:
dbug: PowerServer[0]
Validate Session Id: '177C8A6A-2ACD-49E9-94F5-4EBBD6B4F7D2' Session Status: 'Actived' Session Last Visit Time: '2022-06-15 5:54:42 PM'
dbug: PowerServer[0]
Non Db Transaction Request, Transaction Id: '36B79D50-DB52-4180-A7D3-074D1A3C3E95-1', ModuleOrModel Name: '', DbRequest: 'EStoreProcedure', NonSelectCommandWasExecuted: true
dbug: PowerServer[0]
Non Db Transaction Request, Transaction Id: '36B79D50-DB52-4180-A7D3-074D1A3C3E95-1', Transaction Status: 'Created', Transaction Last Visit Time: '2022-06-15 5:54:43 PM', Namespace Name: 'Pvr', ModuleOrModel Name: '', DbRequest: 'EStoreProcedure', NonSelectCommandWasExecuted: 'False'
fail: PowerServer.Api.ServerApiController[0]
'PowerServer.Api.ServerApiController.StoreProcedureReturnDataSetAsync (PowerServer.Api)' get result ({
"IsSuccess": false,
"ErrorCode": 0,
"ErrorMessage": "Failed to parse the statement 'execute fget_tank_name ( ? , ? , ? ) ': The function or stored procedure might not exist.",
"Version": null, "RequestId": null,"AppName": null,"Session": {"ErrCode": 0,"ErrMsg": null,"SessionId": "177C8A6A-2ACD-49E9-94F5-4EBBD6B4F7D2"},
"Type": 0,"Transaction": {"TransactionId": null,"SQLCode": -1,"SQLDBCode": -1,"SQLErrText": "Failed to parse the statement 'execute fget_tank_name ( ? , ? , ? ) ': The function or stored procedure might not exist.","SQLNRows": 0,"SQLReturnData": null},"Content": null,"Namespace": null}).
If if move the function into a package body with the same permissions and use the same call:
DECLARE get_tank_name PROCEDURE FOR PKGPM_TANK_MASTER.getTankName(:as_company_id,:as_battery_id,:as_tank_id);
it returns a tank name.
Is there another way I can call functions to have them work in PowerServer / PowerBuilder?
One of the things that I use as a test is if you can create a dw from the sp I'm calling. In this case you can't as it is a function without a sys_refcursor return value. What Don and I found odd is that it worked if I put it into a package and called. So, I was wondering what the difference in method calls is require.