I have a PLSQL code with the following signature.
create or replace PACKAGE employee_details AS type string_array is table of varchar2(32); end employee_details;
When I execute the below PL/SQL block it works fine in sql developer.
set serveroutput on;
declare
p_id ICE10_1_10_NEW.employee_details.string_array;
begin
DBMS_OUTPUT.ENABLE();
p_id := ICE10_1_10_NEW.employee_details.string_array();
p_id.EXTEND(4);
p_id(1) := 'kunal';
p_id(2) := 'utpal';
p_id(3) := 'a';
p_id(4) := 'm';
dbms_output.put_line('-----------------------------------------------');
FOR i in 1 .. p_id.count LOOP
dbms_output.put_line('row '|| i ||' = ' || p_id(i) );
END LOOP;
dbms_output.put_line('-----------------------------------------------');
end;
Now when I am trying to call same Block from java using JDBC (odbc14.jar). It is throwing exception as
java.sql.SQLException: invalid name pattern: ICE10_1_10_NEW.EMPLOYEE_DETAILS.STRING_ARRAY
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
at oracle.jdbc.oracore.OracleTypeADT.initMetadata(OracleTypeADT.java:503)
at oracle.jdbc.oracore.OracleTypeADT.init(OracleTypeADT.java:406)
at oracle.sql.ArrayDescriptor.initPickler(ArrayDescriptor.java:1952)
at oracle.sql.ArrayDescriptor.<init>(ArrayDescriptor.java:199)
at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:118)
at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:169)
at com.ice.test.CallPLSQLBlock.main(CallPLSQLBlock.java:46)
My Java Code is:
import java.sql.Array;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Types;
import oracle.jdbc.driver.OracleCallableStatement;
import oracle.jdbc.driver.OracleTypes;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
public class CallPLSQLBlock {
public static void main(String args[])
{
System.out.println("==================== START ======================");
Connection con = null;
PreparedStatement ps = null;
OracleCallableStatement cs = null;
ResultSet rs = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
con = DriverManager.getConnection("jdbc:oracle:thin:@icebox-pc:1521:orcl", "ICE10_1_10_new", "icedq123");
String plsql = "" +
" declare " +
" p_id ICE10_1_10_NEW.employee_details.string_array;"+
" begin " +
"p_id := ICE10_1_10_NEW.employee_details.string_array(); " +
"p_id.EXTEND(4); " +
"p_id(1) := 'kunal'; " +
"p_id(2) := 'utpal'; " +
"p_id(3) := 'a'; " +
"p_id(4) := 'm'; "+
" ? := p_id; " // return array
+" end;";
cs = (OracleCallableStatement)con.prepareCall(plsql);
cs.registerOutParameter(1, OracleTypes.ARRAY,"ICE10_1_10_NEW.EMPLOYEE_DETAILS.STRING_ARRAY");
cs.execute();
Array simpleArray = cs.getArray(1);
System.out.println("----------------------Start Array-------------------------");
ResultSet res = cs.getArray(1).getResultSet();
if (res.next())
{
System.out.println("ssssssssssssssssss ===============>>>>>>>>>>>>> "+res.getObject(2));
}
} catch (Exception e) {
e.printStackTrace();
}
finally {
try {
cs.close();
rs.close();
ps.close();
con.close();
} catch (Exception e) {
// e.printStackTrace();
}
}
System.out.println("==================== STOP ======================");
}
}
Please help Thanks in advance.