aspose file tools*
The moose likes JDBC and the fly likes Passing Array as a parameter to Stored procedures Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of JavaScript Promises Essentials this week in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Passing Array as a parameter to Stored procedures" Watch "Passing Array as a parameter to Stored procedures" New topic
Author

Passing Array as a parameter to Stored procedures

Vijaya Bobbala
Greenhorn

Joined: Jul 15, 2003
Posts: 1
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.
Joost Sparreboom
Greenhorn

Joined: Nov 18, 2005
Posts: 1
Originally posted by Vijaya Bobbala:
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.
Amit Sehgal
Greenhorn

Joined: Jul 24, 2006
Posts: 2
Hey,

Try using the type name in CAPS in ArrayDescriptor.
Susanta Chatterjee
Ranch Hand

Joined: Aug 12, 2002
Posts: 102
As far as I remember, you cannot pass an array from Java to PL/SQL Package or procedure. There is no way to bind an variable array to PL/SQL from Java/C++ layer.

Read about that here

That article also shows a work around for this.
bis ani
Ranch Hand

Joined: Mar 09, 2005
Posts: 36
The link you have provided is broken. Can you please update it?
Sudheer Bhat
Ranch Hand

Joined: Feb 22, 2011
Posts: 75
You cannot call the PLSQL arrays from java. You need to create your Array as TYPE in the database and then use it with ArrayDescriptor. The problem with PLSQL Array/collection is that, when the ArrayDescriptor tries to create the descriptor the package which contains the PLSQL array may or may not be loaded (in the DB server). If the package is not loaded there is no way the ArrayDescriptor is going find metadata about the PLSQL array.

 
wood burning stoves
 
subject: Passing Array as a parameter to Stored procedures