Issue using arrays as IN parameters

[Savahn]

I'm running into an issue trying to bind an array of VARCHAR2 to a simple PL/SQL procedure using oracledb. The procedure takes a record input where one element of the record is a table of VARCHAR2. The scalar binds are working fine, and I have been using your guide for decomposing and recomposing records and tables (https://jsao.io/2017/01/plsql-record-types-and-the-node-js-driver/).

I'm trying to rebuild the table inside the PL/SQL as mentioned in the link above like this after creating a connection.

var bindvars = {
	...req.body.bindvars,
	res_source_id: {
		type: oracledb.NUMBER,
		dir: oracledb.BIND_OUT
	},
	res_operating_unit: {
		type: oracledb.STRING,
		dir: oracledb.BIND_OUT
	},
	res_contract_number: {
		type: oracledb.STRING,
		dir: oracledb.BIND_OUT
	},
	res_response_code: {
		type: oracledb.STRING,
		dir: oracledb.BIND_OUT
	},
	res_response_mesg: {
		type: oracledb.STRING,
		dir: oracledb.BIND_OUT
	},
	res_response_sev: {
		type: oracledb.STRING,
		dir: oracledb.BIND_OUT
	}
};

var plsql =
`
DECLARE 
	P_REQUEST_TBL               <Request_Table_Declaration>;
	type varchar2_aat is table of varchar2(150)
		INDEX BY BINARY_INTEGER;
	l_asset_numbers             varchar2_aat;
BEGIN
	P_REQUEST_TBL(1).Contract_Number           := :contract_number;
	P_REQUEST_TBL(1).partial_term_flag             := :partial_term_flag;
	P_REQUEST_TBL(1).Quote_term_date            := to_date(:quote_term_date,'YYYY-MM-DD');
	P_REQUEST_TBL(1).Quote_buyout_reason     := :quote_buyout_reason;
	P_REQUEST_TBL(1).Late_Charges                   := :late_charges;
	P_REQUEST_TBL(1).Processing_Fees              := :processing_fees;
	P_REQUEST_TBL(1).Requested_by                 := :requested_by;
	l_asset_numbers                         := :asset_numbers;

	for asset in 1 .. l_asset_numbers.count
        loop
             P_REQUEST_TBL(1).asset_number_tbl(asset).ASSET_NUMBER := l_asset_numbers(asset);
        end loop;
	
	CUSTOM_PKG.Custom_Procedure(P_REQUEST_TBL);
END;
`;

Running that gives the error: NJS-044: named JSON object is not expected in this context.

The asset_numbers variable is an array of strings being passed from the calling function (e.x. ['ASDFQWER','UIOPVBNM']).

For reference I'm running Node.js v8.12.0 on Windows 64 with oracledb version 3.1.1.

[anthony-tuininga]

Can you clarify a few things? The PL/SQL looks like it is accepting IN parameters but your bind definitions show OUT parameters? And the bind variable names don't match -- so it looks like some are missing from your definition? The only array looks to be the bind variable :asset_numbers but that also appears to be missing!

[Savahn]

The :asset_numbers value is coming from the request object

var bindvars = {
	...req.body.bindvars

The request is built from a front-end form, with default values for testing

bindvars: {
	contract_number: '123456789',
	partial_term_flag: 'N',
	asset_numbers: ['ASDFQWER','UIOPVBNM'],
	quote_term_date: '2019-01-30',
	quote_buyout_reason: 'Other',
	late_charges: '',
	processing_fees: '',
	requested_by: 'Jane Doe'
}

[anthony-tuininga]

Can you use this instead?

asset_numbers: { type: oracledb.STRING, val: ['ASDFQWER', 'UIOPVBNM'] }

Let me know if that works better for you.

[Savahn]

Interesting, I was able to get it to work by assigning the request's asset_numbers to the value of the object you sent above.

bindvars.asset_numbers = {
	type: oracledb.STRING,
	val: bindvars.asset_numbers
}

Is there a reason you can think why the scalar values do not require the object binding?

[anthony-tuininga]

Well, if the value is an object the code assumes you are specifying the type, value, direction, etc. in that object, but if the value is not an object, the code assumes that is the bind value itself. There are explicit checks for dates, buffers and LOB objects (which are themselves objects but can be bound directly) but not for arrays. That should probably be corrected in order to avoid this sort of issue. I'll take this as an enhancement request, then. :-)

[Savahn]

Thanks for the help and the explanation! I had not dealt with sending objects over yet, so I didn't run into any issues like this yet. It would be exceptionally helpful if simple-typed arrays could be passed directly in the future.

[cjbj]

@Savahn Node-oracledb 4.0 has been released with an enhancement for this. The release announcement is at https://blogs.oracle.com/opal/oracle-db-named-objects-and-advanced-queuing-support-new-in-node-oracledb-40