passing index-by tables to stored procedure [message #39857] |
Mon, 19 August 2002 05:00 |
James
Messages: 120 Registered: June 2000
|
Senior Member |
|
|
I have a stored proc which takes 2 IN index-by tables and one OUT index-by table.
IndProc(stations IN type_bank.stations, airlines IN type_bank.airlines, results OUT NOCOPY type_bank.indices)
The types of these tables are defined in the package type_bank which is given below
CREATE OR REPLACE PACKAGE type_bank AS
TYPE stations IS TABLE OF CHAR(3) INDEX BY BINARY_INTEGER;
TYPE airlines IS TABLE OF CHAR(3) INDEX BY BINARY_INTEGER;
TYPE indices IS TABLE OF PLS_INTEGER INDEX BY BINARY_INTEGER;
END type_bank;
I am trying to call the procedure from a Java client so I use a OracleCallableStatement object, and the setPlsqlIndexTable method to pass the first 2 paremeters and registerIndexTableOutParameter method to register the 3rd (out) parameter.
OracleCallableStatement ocs = (OracleCallableStatement)parent.conn.prepareCall("begin IndProc(?, ?, ?); end;");
ocs.setPlsqlIndexTable(1, stas, stas.length, stas.length, OracleTypes.CHAR, 3);
ocs.setPlsqlIndexTable(2, airs, airs.length, airs.length, OracleTypes.CHAR, 2);
ocs.registerIndexTableOutParameter(3, 200, OracleTypes.NUMBER, 0);
ocs.execute();
System.out.println(ocs.getString(2));
Here stas, and airs are my arrays that contain CHAR values....
When I run the code, I get the following error:
ORA-06550: line 1, column 15:
PLS-00418: array bind type must match PL/SQL table row type
ORA-06550: line 1, column 19:
PLS-00418: array bind type must match PL/SQL table row type
ORA-06550: line 1, column 23:
PLS-00418: array bind type must match PL/SQL table row type
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Someone help me with this, please!
James.
|
|
|
|