Bug 4872

Error calling SP (Remote Procedure Call) with DATE as IN parameters in Oracle 19C. 01 July, 2020

Kumaran Subramanian
30 June, 2020
Product: PowerBuilder Category: PowerScript
Version: 2019 R2 Build: 2328
Classification: Publishing: Public
Priority: P1
Status: Closed Reason: RESOLVED
Ken Guo @Appeon 01 July, 2020
#5
Hi Kumaran,

Glad to hear that. We will close this ticket! 
If you have any further question, please open a new ticket.

Regards,
Ken
Kumaran Subramanian 30 June, 2020
#4
I changed it to date array and tried.  It worked.  Please close the ticket.  Regards Kumaran
Ken Guo @Appeon 30 June, 2020
#3
Hi Kumaran,

You used datetime array in your RPC, it is not supported. I suggest you change it to date array and then try again to see if it works.

“For Oracle 18c and 19c, the date array parameter can only be the Date type (cannot be the DateTime type)”. You can refer to the link below for detail.
https://docs.appeon.com/pb2019r2/connecting_to_your_database/ch11s06.html#d0e6701 



Regards,
Ken
Kumaran Subramanian 30 June, 2020
#2
test_case.zip (8KB)

I created a case to  reproduce the issue. See the attachment.
Kumaran Subramanian 30 June, 2020
#1
test_case.zip (8KB)

Error calling SP (Remote Procedure Call) with DATE AS IN  parameters in Oracle 19C.  

Getting error  in  Oracle 19c:

ORA-01866  the datetime class is invalid
This is call in our PB script:

// local variables
string ls_arr_user_info[18]
integer li_err_status
datetime ldt_datetime
DateTime ldt_sysdate,ldt_occurrence_date[3]
// initialize array to be used in the RPC Call
int i, J
FOR i= 1 to 18
	ls_arr_user_info[i] = Space(40)
NEXT	

FOR J = 1 TO 3
	ldt_occurrence_date[J] = Datetime(today())
NEXT

ldt_sysdate = DateTime(today())
li_err_status = 100
//Call the stored procedure
sqlca.sp_test_security_init('test', ldt_sysdate, ldt_occurrence_date, ref ls_arr_user_info, ref li_err_status)

SP third parm is the source of the error here - it's defined as 

TYPE sec_array1 IS TABLE OF DATE INDEX BY BINARY_INTEGER;

I created a case to test to  reproduce the issue. See the attachment.
OS:
Windows 10
Platform:
64-bit
Database Type:
Oracle
Database Version:
19C