PL/SQL Array vs.Scalar mismatch - 6.2 SP1 build 39136

[satyavangala]

Hi,

We haven't used stored procedure calls much though we have been using Gupta tools for many years.We need to call a stored prodcedure method with array ( table) parameters. We followed the help and other documentation. We have been getting the error PL/SQL Array vs.scalar mismatch whenever we call a procedure with array parameters. Same prodcedure works without any errors when called from any other program. Also, we can call from Gupta 6.2 program if we remove the array parameter from the procedure specification. I don't see any references to this type of problems in 6.2 forum but there are some reports about the location of a parameter with array input.

We use 6.2 SP1 build 39136. We also need to call several stored procedures similar to this from the TDMobile app which we are starting to build. So, we need to resolve this to build base APL classes to be used in both apps.

Tried to attach a document with all the details. But this site has been refusing to upload .docx, .pdf, or .rtf files. Not sure why.

Appreciate your quick help in this matter.

Regards,
Satya Vangala


Error From Gupta 6.2 App
Since I'm not able to upload a file. Here's the text.

SQL Error:
PL/SQL array vs.scalar mismatch, parameter #1.
Halt application?
Yes No



Gupta Function
Function: Search
Description:
Returns
Number:
Parameters
Static Variables
Local variables
String: sSQL
String: primaryValues[2]
String: secFields[2]
Long String: secValues[2]
Number: resultCount

Actions
Set sSQL = 'CME_MOBILE_SEARCH.SEARCH'
Set sSQL= sSQL || ' (primaryValues, secFields, secValues, resultCount) '
Set primaryValues[0] = '1'
Set primaryValues[1] = '1'
Set secFields[0] = '2'
Set secFields[1] = '2'
Set secValues[0] = '1'
Set secValues[1 ]= '1'
Call SqlPLSQLCommand(hSqlSearch,sSQL) --- Error thrown here
Return resultCount

Oracle Package and Method



PACKAGE CME_MOBILE_SEARCH IS
TYPE primary_field_values_tbl IS TABLE OF varchar2(50);
TYPE sec_field_names_tbl is TABLE OF varchar2(50);
TYPE sec_field_values_tbl is TABLE OF NUMBER;
TYPE tbl is TABLE of varchar2(20);
PROCEDURE SEARCH
( i_primary IN primary_field_values_tbl,
i_sec_field_names IN sec_field_names_tbl,
i_secondary IN sec_field_values_tbl,
o_result_count OUT NUMBER
);

PACKAGE BODY CME_MOBILE_SEARCH as

PROCEDURE SEARCH
( i_primary IN primary_field_values_tbl,
i_sec_field_names IN sec_field_names_tbl,
i_secondary IN sec_field_values_tbl,
o_result_count OUT NUMBER
)

IS
BEGIN
o_result_count := 100;
END SEARCH;
END CME_MOBILE_SEARCH;

[Kumuthini Ragavan]

Hi Satya
You should include SqlOraPLSQLStringBindType() to bind string arrays before compiling using SqlOra functions. Please see online help for this function and include it for each string array before you call SqlPLSQLCommand() function and you will be fine.
Hope this helps.
Kumuthini Ragavan

[satyavangala]

Kumuthini,

Thanks for the input. I have tried to bind but it still gave me errors. I'm not sure which method to call but tried both without any success. I removed all the parameters but a table type and an output variable which is a number to test. I tried both WIN32 and .NET compilers. Strangely, the error I got from .NET compiler isn't the same. It gave an OCI error saying the method isn't even there.

Also, when I used just a single output parameter which is a number, everything worked. Obviously, something isn't working when calling any procedure that has table types as parameters.

Appreciate your help.

Regards,
Satya Vangala



1) sqlPLSQLCommand: Example provided for this method clearly describes how to use arrays to pass values via parameters declared as tables in the procedure. Syntax doesn't involve any array binds.Also, syntax does not involve semicolon. When I tried to call this way, I go the scalar error.
Online help shows the following.
Set strCommand = 'INVOICES.INSERT_INVOICE'
Set strCommand = strCommand || '(ninv_id,strClient,dt_inv_dt,'


2) I have tried use PLSQLPrepare, PLStringBind, and PLSQLExecute. It is giving me the following error. I did use semicolon before the variable. I belive this is the right method to call.

Here is the call. I left one table parameter which is varchar2 and an out parameter which is a number in the test package procedure.

Set sSQL = 'CME_MOBILE.SEARCH (:primaryValues, utNum) '
If SqlOraPLSQLPrepare(hSqlSearch,sSQL)
Set primaryValues[1] = 'TEST'
Set primaryValues[2] = 'TEST2'
Call SqlOraPLSQLStringBindType(hSqlSearch,'primaryValues',5)
Call SqlOraPLSQLExecute(hSqlSearch)
Return resultCount

Error:

ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'SEARCH'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

[Kumuthini Ragavan]

Hi Satya
The first parameter (the varchar table has to be indexed).

CREATE OR REPLACE PACKAGECME_MOBILE IS
TYPE primary_fields_tbl IS TABLE OFvarchar(50) INDEX BY BINARY_INTEGER;

PROCEDURE SEARCH
( i_in_primary primary_fields_tbl,
i_out out NUMBER
);
END CME_MOBILE;
Once this is corrected everything works as usual.
Hope this helps.
Kumuthini