I know how to pass array as a parameter to stored procedure.. But when I did, I'm getting the error message...
I have written a Oracle stored procedure. That procedure takes an array as
Input parameter.
Here is the syntax :
Package Header
PACKAGE
test
IS
TYPE tab_arr IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
PROCEDURE prc_ins (p_arr IN tab_arr);
END test;
Note : Here I have used the PL/SQL Table as an array
Package Body
PACKAGE BODY test IS
PROCEDURE prc_ins (p_arr tab_arr)
IS
BEGIN
FOR i IN p_arr.FIRST..p_arr.LAST
LOOP
INSERT INTO temp (temp_name) VALUES (p_arr(i));
END LOOP;
END;
END test;
This what I have written in backend. By using
java I need to pass an array
to this stored procedure. Sometimes I may need to pass hetrogeneous array
too (2-D array. 1 st column may benumber, 2 column may be string...like
that).
The Java code is as follows.
package pmsstudy.dataaccess;
import java.sql.*;
public class arr_ins {
public static void main(
String[] args) {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
try {
Connection connection = DriverManager.getConnection(
"jdbc
racle:thin:@rndserver:1521:TAS", "test_pms", "test_pms");
System.out.println("Got Connection");
CallableStatement cs;
String s[] = new String[5];
for ( int i =0;i<4;i++)
{
s[i] = "Message_TEXT_"+i;
}
System.out.println("Array is Created");
for ( int i =0;i<4;i++)
{
System.out.println(s[i]);
}
try {
cs = connection.prepareCall("{call test.prc_ins(?)}");
System.out.println("Prepare statement is formed");
oracle.sql.ArrayDescriptor descrip =
oracle.sql.ArrayDescriptor.createDescriptor("test.tab_arr",connection);
oracle.sql.ARRAY a = new oracle.sql.ARRAY(descrip, connection, s);
cs.setArray(1,a);
cs.execute();
System.out.println ("output of procedure ");
}catch(Exception oException){
oException.printStackTrace();
}
connection.close();
}
catch (SQLException se) {
System.out.println("SqlException: " + se.getMessage());
se.printStackTrace(System.out);
}
}
catch (ClassNotFoundException e) {
System.out.println("ClassNotFound: " + e.getMessage());
}
}
}
I didnt get any compilation error. But while running I have got the
following message.
Got Connection
Array is Created
Message_TEXT_0
Message_TEXT_1
Message_TEXT_2
Message_TEXT_3
Prepare statement is formed
java.sql.SQLException: invalid name
pattern: test.tab_arr
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:180)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:222)
at
oracle.jdbc.oracore.OracleTypeADT.initMetadata(OracleTypeADT.java:495)
at oracle.jdbc.oracore.OracleTypeADT.init(OracleTypeADT.java:405)
at oracle.sql.ArrayDescriptor.initPickler(ArrayDescriptor.java:1020)
at oracle.sql.ArrayDescriptor.<init>(ArrayDescriptor.java:136)
at
oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:101)
at
pmsstudy.dataaccess.arr_ins.main(pmsstudy/dataaccess/arr_ins.java:29)
Can you please help me out why I'm getting this error message... Thanks in advance - Vijaya.
