Bug 3112

Error calling SP with OUT VARCHAR2 parameters in Oracle 18C 08 April, 2020

Dennis C. Clark
30 July, 2019
Product: PowerBuilder Category: PowerScript
Version: 2017 R2 Build:
Classification: Enhancement Publishing: Public
Priority: P1
Status: Verifying Reason:
Ken Guo @Appeon 08 April, 2020
#12
Hi Dennis,

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 18 December, 2019
#11
Hi Dennis,
  
Good news that Appeon PowerBuilder 2017 R3 MR (build 1915) 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 may download and install this latest release from our Downloads portal at https://www.appeon.com/user/center/index?menu=download (login required) or you may manually go to the Downloads portal from Login (to the Appeon Website) > User Center (under your login user icon) > Downloads. 

Upgrade Notes:
If you are currently using PowerBuilder 2017 R3 (build 1858 or 1880), please directly apply this MR (build 1915) on top of your current PowerBuilder 2017 R3. 
If you are currently using PowerBuilder 2017 or 2017 R2, please first upgrade your current PowerBuilder 2017 or 2017 R2 to PowerBuilder 2017 R3 LTS Build 1858 and then apply this MR (build 1915). 

We welcome all feedback from you!

Regards,
Ken
Mark Lee @Appeon 30 September, 2019
#10
Hi Dennis,
 
Thanks for providing the test case.
We reproduced it on our side. The code works well when using Oracle 11g and has problems when using Oracle 18c.
And sorry to let you know that for Oracle 18c, PB 2017/2019 doesn't officially support it yet.
https://docs.appeon.com/appeon_online_help/pb2019/connecting_to_your_database/ch11s01.html
Currently the problem is that you use the arrays in remote procedure calls (RPCs) to Oracle 18c.
We'll record it as a new requirement/enhancement request and will transfer it to our product team for consideration.

BTW, we'll keep you posted about the progress on when O18C and/or 19C will be officially supported.

Regards,
Mark Lee
Mark Lee @Appeon 25 September, 2019
#9
Hi Dennis,

Thanks for provide the sample SP.
I will working on it and get back to you soon.
Thanks for your patience and understanding.

Regards,
Mark Lee
Dennis C. Clark 24 September, 2019
#8
Oracle array error pb code.txt (1KB)

(In reply to Dennis C. Clark from comment #7)
p_test_security.pks (0KB)

(In reply to Dennis C. Clark from comment #6)
p_test_security.pkb (1KB)

(In reply to Mark Lee from comment #5)
Hi Dennis,
 
Thanks for reporting this problem.
1. Please refer to the following links and see if they can resolve your
problem.
https://www.appeon.com/developers/get-help/knowledgebase/powerbuilder-
pipelne-issue-connected-oracle-11g-using-o10-oracle10g-1010-db-interface.html
 
https://www.appeon.com/developers/get-help/knowledgebase/powerbuilder-
calling-oracle-stored-procedures-and-functions.html
 
2.If not, kindly can you please provide a sample test case (with PBT/PBL +
database) for us to reproduce it?
Thanks in advance.

Regards,
Mark Lee
Here attached is a sample SP in its package that illustrates the problem and the PB script that calls it. Fairly simple as you can see. Thanks.
Dennis C. Clark 24 September, 2019
#7
p_test_security.pks (0KB)

(In reply to Dennis C. Clark from comment #6)
p_test_security.pkb (1KB)

(In reply to Mark Lee from comment #5)
Hi Dennis,
 
Thanks for reporting this problem.
1. Please refer to the following links and see if they can resolve your
problem.
https://www.appeon.com/developers/get-help/knowledgebase/powerbuilder-
pipelne-issue-connected-oracle-11g-using-o10-oracle10g-1010-db-interface.html
 
https://www.appeon.com/developers/get-help/knowledgebase/powerbuilder-
calling-oracle-stored-procedures-and-functions.html
 
2.If not, kindly can you please provide a sample test case (with PBT/PBL +
database) for us to reproduce it?
Thanks in advance.

Regards,
Mark Lee
Here attached is a sample SP in its package that illustrates the problem and the PB script that calls it. Fairly simple as you can see. Thanks.
Dennis C. Clark 24 September, 2019
#6
p_test_security.pkb (1KB)

(In reply to Mark Lee from comment #5)
Hi Dennis,
 
Thanks for reporting this problem.
1. Please refer to the following links and see if they can resolve your
problem.
https://www.appeon.com/developers/get-help/knowledgebase/powerbuilder-
pipelne-issue-connected-oracle-11g-using-o10-oracle10g-1010-db-interface.html
 
https://www.appeon.com/developers/get-help/knowledgebase/powerbuilder-
calling-oracle-stored-procedures-and-functions.html
 
2.If not, kindly can you please provide a sample test case (with PBT/PBL +
database) for us to reproduce it?
Thanks in advance.

Regards,
Mark Lee
Here attached is a sample SP in its package that illustrates the problem and the PB script that calls it. Fairly simple as you can see. Thanks.
Mark Lee @Appeon 13 August, 2019
#5
Hi Dennis,
 
Thanks for reporting this problem.
1. Please refer to the following links and see if they can resolve your problem.
https://www.appeon.com/developers/get-help/knowledgebase/powerbuilder-pipelne-issue-connected-oracle-11g-using-o10-oracle10g-1010-db-interface.html
 
https://www.appeon.com/developers/get-help/knowledgebase/powerbuilder-calling-oracle-stored-procedures-and-functions.html
 
2.If not, kindly can you please provide a sample test case (with PBT/PBL + database) for us to reproduce it?
Thanks in advance.

Regards,
Mark Lee
Chris Pollach @Appeon 02 August, 2019
#4
Hi Dennis;

  Sorry to hear that.   :-(

I am just starting my vacation today but, I have CC'ed in Cedric Pernet also from our Appeon support team to help you continue to resolve your problem.

Regards ... Chris
Dennis C. Clark 02 August, 2019
#3
(In reply to Chris Pollach from comment #2)
Hi Dennis;

  We have not heard back from you for a while. Were you able to resolve this
issue?

Regards ... Chris
Sorry did not receive any email about your comments till today. No, it's still not working. We currently are using the 12C Client. We are in the process of setting up a test bed with the 18C Client to see it might get around this, but I do not have much hope that it will. We will let you know what the results of that test are. Thanks! Dennis
Chris Pollach @Appeon 02 August, 2019
#2
Hi Dennis;

  We have not heard back from you for a while. Were you able to resolve this issue?

Regards ... Chris
Chris Pollach @Appeon 30 July, 2019
#1
Hi Dennis;

  At this time, the latest version of Oracle that is supported (even by PB2019) is Oracle 12C.

  What DB client connection mechanism are you using for O18C?

Regards ... Chris
Dennis C. Clark 30 July, 2019
When calling SP that has worked correctly in many previous versions of Oracle and PowerBuilder for almost 20 years, getting Oracle data type error on bind variables :

PLS-00418: array bind type must match PL/SQL table row type

This is call in our PB script:

// local variables
string	sql,su_priv, ls_user_id, ls_arr_user_info[18], ls_err_message
integer li_err_status, i, li_err_num
decimal ldec_session_id
// save arguments
is_owner = a_owner
is_user_id = a_user_id

// initialize array to be used in the RPC Call
FOR i= 1 to 18
	ls_arr_user_info[i] = space(30)
NEXT	

//Call the stored procedure
sqlca.SP_SECURITY_INIT(is_user_id, ls_arr_user_info, li_err_status, &
                       li_err_num, ls_err_message,ldec_session_id)

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

TYPE sec_array IS TABLE OF VARCHAR2 (30)
                      INDEX BY BINARY_INTEGER;

The Oracle Trace file has this error information:

(00B94838): (DBI_SUPPORT_RPC) (0.003 MS / 459.217 MS)
(00B94838): (DBI_SPECIAL_DOUBLE) (0.001 MS / 459.218 MS)
(00B94838): EXECUTE REMOTE PROCEDURE: HSD_SECURITY_ONL_BROKER_1.SP_SECURITY_INIT
 TYPE VCHAR 
 ARRAY TYPE VCHAR REFERENCE 
 TYPE INT REFERENCE 
 TYPE INT REFERENCE 
 TYPE VCHAR REFERENCE 
 TYPE VCHAR REFERENCE (DBI_RPC_EXECUTE)
(00B94838): BEGIN         HSD_SECURITY_ONL_BROKER_1.SP_SECURITY_INIT( :0,:1,:2,:3,:4,:5); END; (DBI_DEBUG_MESSAGE) (0.001 MS / 459.219 MS)
(00B94838): *** ERROR 6550 ***(rc -1) : ORA-06550: line 1, column 62:
PLS-00418: array bind type must match PL/SQL table row type

We have discovered in the past that such arrays must be initialized to avoid other errors, and I was hoping in this case the error was related again to changes in how Oracle handles these situations, but trying different string lengths or even avoiding the initialization altogether makes no difference here - still get this same error. 

I suspect but cannot prove that somehow in 18C has 'tightened' it up here where the above ARRAY TYPE VCHAR REFERENCE, if it's really just VARCHAR2 and not specifically VARCHAR2 (30), was once allowed but now is not? Obviously not able to see exactly how the required array variable on the PB generated code is declared, but I do wonder if this is what has changed, since all these years this has worked fine. The 418 error is specifically to do with a type mismatch, but we are still dealing with VARCHAR2 on both sides here, client and server, so it almost has to be something to do with the specification of the length? Cannot declare in Oracle an array of just type VARCHAR2 without length in order to test if that is the issue here, so we are stopped cold here and we cannot rewrite all the code expecting OUT array parms in our application. We realize 18C is not yet supported, but we are migrating to Oracle 18C for several requirements. Can we expect a patch to deal with this perhaps to the latest PB release?

Thanks, 
Dennis Clark
First Choice Health Network
OS:
Windows 10
Platform:
All
Database Type:
Oracle
Database Version:
18C