Questions? Feedback?powered byOlark live chat software
Bug 3112

Error calling SP with OUT VARCHAR2 parameters in Oracle 18C 13 August, 2019

Dennis C. Clark
30 July, 2019
Product: PowerBuilder Category: PowerScript
Version: 2017 R2 Publishing: Public
Status: Reproducing Priority: P1
Classification: Resolution:
Mark Lee 13 August, 2019
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 02 August, 2019
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
(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 02 August, 2019
Hi Dennis;

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

Regards ... Chris
Chris Pollach 30 July, 2019
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