Bug 4252

Oracle 18c compatibility with PB2019 Build version 2170 30 June, 2020

Prasanth Kalivarapu
11 March, 2020
Product: PowerBuilder Category: PowerScript
Version: 2019 Build:
Classification: Publishing: Public
Priority: P2
Status: Verifying Reason:
armando herrera @Appeon 30 June, 2020
#13
Hi Prasanth, 

I'm checking with you if the response provided to you was helpful enough to resolve
your case. If so, can we proceed to close this ticket?.
 
Otherwise, do let us know if we can provide you with any further assistance.

Also, please keep in mind, that you are always welcome to open another ticket in case you need it.

Regards!
Ken Guo @Appeon 08 April, 2020
#12
Hi Prasanth,

Good news that Appeon PowerBuilder 2019 R2 GA (build 2323) has been officially released. The issue you reported has been fixed in this release. Please download and install it to verify your reported issue and let us know if any problems. 
You can get the installer from our Downloads portal at https://account.appeon.com/download/2019 (login required). 
Upgrade Notes:
If you are currently using PowerBuilder/InfoMaker 2019 (build 2082 or 2170) or PowerBuilder/InfoMaker 2019 R2 Beta (build 2279), please directly apply this GA (build 2323) on the top of your current 2019 or 2019 R2 Beta.

Notes:
If your application performs a remote procedure call (RPC) that passes an array parameter to an Oracle stored procedure, the array size in the stored procedure must not be zero. If the array size is uninitialized (has no size), the PBVM returns an error.
For Oracle 18c and 19c, the string array parameter can only be the VarChar type and VarChar2 type, and cannot be the NVarChar2, Char, or NChar type.
For Oracle 18c and 19c, the date array parameter can only be the Date type (cannot be the DateTime type).
https://docs.appeon.com/appeon_online_help/pb2019r2/connecting_to_your_database/ch11s06.html#d0e6701 

We welcome all feedback from you!

Regards,
Ken
Ken Guo @Appeon 17 March, 2020
#11
Hi Prasanth,

It is not supported to use DateTime type array in Oracle 18c/19c RPC at present.

Furthermore, in order to better reproduce and analyze the issue, could you please send the whole SQLs of the Package to us?

Regards,
Ken
Chris Pollach @Appeon 17 March, 2020
#10
Hi Prasanth;

  Thanks for the extra trace information!

  I will now pass this ticket over to the main Support / Engineering Team for their assessment & feedback on your issue. This seems a bit similar to ticket #3112 for O18C when accessing SP's. 

  Lets see what Engineering has to say.


Regards ... Chris
Prasanth Kalivarapu 17 March, 2020
#9
Date Array issue.docx (14KB)

Hi Chris,

Trace file information is included in the previous attached document. Please be informed that there are 2 different errors with Datetime array and Date array being passed to SP. Attaching the document again.
Chris Pollach @Appeon 16 March, 2020
#8
PS:  https://www.oraexcel.com/database-oracle-12cR1-ORA-01866
Chris Pollach @Appeon 16 March, 2020
#7
Hi Prasanth;

  Yes, the ORA setting is the correct one from either PB2017Rx or PB2019Rx.

  Please now run an SQL trace on this latest problem (ORA-01866: the datetime class is invalid) and attach the SQL trace file to this ticket.

SQLCA.DBMS = "TRACE ORA"

Many thanks in advance!

Regards ... Chris
Prasanth Kalivarapu 16 March, 2020
#6
Date Array issue.docx (14KB)

Hi Chris

It worked by setting our DBMS connection type to ORA upto some point. But It raised another issue with date array parameter, Detailed information has been attached.
What would be the recommended DBMS connection type for PB2019 with Oracle 18c?
 
Please let me know if you need any more information.
Chris Pollach @Appeon 13 March, 2020
#5
Hi Prasanth;

  Thank you for that extra information!

  Your answer to Q#2 is probably your problem. The "O90" setting would be used only if you are connecting to Oracle 9i. In your case with O18C (or 19C for that matter), please set your DBMS connection type to "ORA"

Regards ... Chris
Prasanth Kalivarapu 13 March, 2020
#4
Hi Chris

Requested Information is available as below:
1) Are you using the O18C client (Full or "instant")?  Full
2) How is your App connecting to O18C. 
   That is SQLCA.DBMS = "O90"	
3) Have you tried using an SQL Trace in your PB App?
   That is SQLCA.DBMS = "TRACE O90"
   Below is the information logged in trace file
		(03485C10):  NEW DBPARM=PBTrace=1(DBI_NEW_DBPARM) (0.026 MS / 1001.353 MS)
		(03485C10): (DBI_SUPPORT_RPC) (0.014 MS / 1001.367 MS)
		(03485C10): (DBI_SPECIAL_DOUBLE) (0.030 MS / 1001.397 MS)
		(03485C10): EXECUTE REMOTE PROCEDURE: ASAP.SP_LERG1_BLK_IMPORT
		 ARRAY TYPE VCHAR
		 ARRAY TYPE VCHAR
		 ARRAY TYPE VCHAR
		 TYPE VCHAR
		 TYPE VCHAR
		 TYPE VCHAR REFERENCE (DBI_RPC_EXECUTE)
		(03485C10): BEGIN         ASAP.SP_LERG1_BLK_IMPORT( :0,:1,:2,:3,:4,:5); END; (DBI_DEBUG_MESSAGE) (0.018 MS / 1001.415 MS)
		(03485C10): *** ERROR 932 ***(rc -1) : ORA-00932: inconsistent datatypes: expected %s got %s
		(03485C10): ORA-00932: inconsistent datatypes: expected %s got %s
		(DBI_DEBUG_MESSAGE) (0.011 MS / 1001.426 MS)
		(03485C10): *** ERROR 932 ***(rc -1) : ORA-00932: inconsistent datatypes: expected %s got %s
		 (8.736 MS / 1010.162 MS)
		(03485C10): *** ERROR 932 ***(rc -1) : ORA-00932: inconsistent datatypes: expected %s got %s
		(03485C10):  NEW DBPARM=PBTrace=0
		   Turning off Trace Log Output
4) What special connection settings are you using?
   That is SQLCA.DBParm = DisableBind=0,DelimitIdentifier='No',CommitOnDisconnect='No',PBDBMS=0,SQLCache=8
5) Note that PB 7 was ANSI based and PB v10.0 and higher are Unicode.
   Could your O18C issue be related to now passing Unicode strings?
   No Powerscript changes made in PB2019 after migration from PB7 so as to allow ANSI encode.  

Thanks
Prasanth
Chris Pollach @Appeon 12 March, 2020
#3
Hi Prasanth;

  Many thanks for the Oracle Package / SP code!

  Yes, PB2019 supports O18C and 19C but, that support was just added in the latest  maintenance release (MR) - build #2170. 

Can you tell us ....

1) Are you using the O18C client (Full or "instant")?
2) How is your App connecting to O18C. 
   That is SQLCA.DBMS = "???"
3) Have you tried using an SQL Trace in your PB App?
   That is SQLCA.DBMS = "TRACE ???"
4) What special connection settings are you using?
   That is SQLCA.DBParm = "???????????"
5) Note that PB 7 was ANSI based and PB v10.0 and higher are Unicode.
   Could your O18C issue be related to now passing Unicode strings?


Regards .... Chris
Prasanth Kalivarapu 12 March, 2020
#2
Sample Code.docx (11KB)

Hi Chris

Please find the attached file for the sample code that we are using in PB2019. 
We verified same code is working in PB7 with Oracle 12c. Currently we have migrated source code to PB2019. Working with Oracle 12c and getting the error mentioned with Oracle 18c database.

Does Powerbuilder 2019 Supports Oracle 18c?
May we know whether there is some tweak is available for us to proceed, since we are going live by EOM. 

Note: attached sample document with steps for above error message.
Thanks
Prasanth
Chris Pollach @Appeon 11 March, 2020
#1
Hi Prasanth;

  Please create a simple reproducible PB App test case for this problem. Also, please create any Stored Procedure, Table, etc DDL to allow Appeon to recreate your 18C DB structures for the Test Case app.

  Many thanks in advance!

Regards .... Chris
Prasanth Kalivarapu 11 March, 2020
#0
*Phenomenon: Getting error message : ORA-00932: inconsistent datatypes: expected %s got %s
while calling procedure having string array in its parameter.


*Reproduce Steps: Using the procedure itself is throwing the below error message.
ORA-00932: inconsistent datatypes: expected %s got %s

Remarks:
Environment: PB2019 B2170 with Oracle 18c database
OS:
Windows 10
Platform:
64-bit
Database Type:
Oracle
Database Version:
18C