| Error while calling PL/SQL from java, during VARRAY access [message #92700] |
Sun, 05 December 2004 18:18  |
Paromita
Messages: 2
Registered: December 2004
|
Junior Member |
|
|
I am getting the followng error while executing PL/SQL from java:
-------------------------------------------------------------
PLS-00306: wrong number or types of arguments in call to 'GET_ARRAY_OF_ROLE_ID'
[[java]] ORA-06550: line 1, column 7:
[[java]] PL/SQL: Statement ignored
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
[[java]] at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
[[java]] at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:583)
[[java]] at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1986)
[[java]] at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1144)
[[java]] at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2176)
[[java]] at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:2050)
[[java]] at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2931)
[[java]] at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:662)
[[java]] at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:738)
Here is my procedure:
-----------------------------------------------
SQL>CREATE OR REPLACE TYPE V_ROLE_ID IS VARRAY(20) OF NUMBER;
---------------------------------------------------
PROCEDURE GET_ARRAY_OF_ROLE_ID
(
p_role_id OUT V_ROLE_ID
)
IS
BEGIN
select ROLE_ID BULK COLLECT INTO p_role_id
From ROLE_TAB
Where ROLE_CODE = 'ENT';
END GET_ARRAY_OF_ROLE_ID;
---------------------------------------------
Here is my java code:
StringBuffer strBuf = new StringBuffer(("BEGIN ctb_os_hr_sync_pkg.get_array_of_role_id(").concat
("p_role_id => :1 ").concat
(" ); ").concat
("END;"));
Connection con = getConnection();
CallableStatement cStmt = (CallableStatement)con.prepareCall(strBuf.toString());
cStmt.registerOutParameter(1, Types.ARRAY, "V_ROLE_ID");
cStmt.execute();
--------------------------------------------
Any help will be highly appreciated.
Thanks
Paromita
|
|
|
|
| Re: Error while calling PL/SQL from java, during VARRAY access [message #92736 is a reply to message #92700] |
Mon, 27 December 2004 23:01  |
Gerard Chiva
Messages: 2
Registered: December 2004
|
Junior Member |
|
|
Hi,
this piece of code is for a input varray parameter to a plsql procedure from java jdbc. But its similar for your case:
ArrayDescriptor l_desc = ArrayDescriptor.createDescriptor("you_schema_name.your_array_type", p_conn);
ARRAY arrayParams = new ARRAY(l_desc, p_conn, l_values);
l_cstm.setObject(l_name,arrayParams,OracleTypes.ARRAY);
You cannot register an out paremeter from java directly if this parameter is a varray, you need to create an array descriptor.
Hope it helps.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
