• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
  • Campbell Ritchie
  • Ron McLeod
  • Rob Spoor
  • Tim Cooke
  • Junilu Lacar
  • Henry Wong
  • Liutauras Vilda
  • Jeanne Boyarsky
Saloon Keepers:
  • Jesse Silverman
  • Tim Holloway
  • Stephan van Hulst
  • Tim Moores
  • Carey Brown
  • Al Hobbs
  • Mikalai Zaikin
  • Piet Souris

trouble passing array to stored procedure in spring HELP

Ranch Hand
Posts: 123
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I dont know why the hell this is so complicated, i am trying to pass a simple array of Strings to an oracle stored procedure from Spring in my package here it is

In my package decleration

i have the method declared

then in my stored procedure i have

i purposely did not use the array variable, i want to make it sure i want the stored proc atleast without any error.

Now coming to the java side, there are so many people who are having issues with this how to pass some say use array descriptor some say do this, this is what i was trying

i get an exception saying java.sql.SQLException: invalid name pattern: evdb.dct_2_2.t_att_name

Can anyone please tell me whats the easiest way to pass this array.


Posts: 17346
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Personally, I don't use Stored Procedures anymore, especially for these types of reasons. But soon Hibernate in version 4 will have full support for stored procedures like these.

Anyway, the only thing that I think I notice is that in the execute method you are setting the parameter's value to an array of Oracle ARRAYs, I would think you would just set it to a single instance of Oracle's ARRAY object.

And it goes to show how Oracle just decides to do things their own way, rather than follow standards. I mean a class named "ARRAY" all caps. That totally goes against the standard naming of classes.

Anyway, maybe you want your returnStringArray method to just return a single oracle.sql.ARRAY class, instead of ARRAY[]

Darvesh Niz
Ranch Hand
Posts: 123
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
The error i am getting is at the line where i do

i tried ddifferent things like removing the schema name adding the schema name but still the same issue cant help it.

Exception in thread "main" java.sql.SQLException: invalid name pattern: EVDB.t_att_name
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:162)
at oracle.jdbc.oracore.OracleTypeADT.initMetadata(OracleTypeADT.java:412)
at oracle.jdbc.oracore.OracleTypeADT.init(OracleTypeADT.java:327)
at oracle.sql.ArrayDescriptor.initPickler(ArrayDescriptor.java:1300)
at oracle.sql.ArrayDescriptor.<init>(ArrayDescriptor.java:156)
at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:113)
at com.edmunds.evdb.SimpleSPArray$ObtainStringArrayFromJava.returnStringArray(SimpleSPArray.java:112)
at com.edmunds.evdb.SimpleSPArray.execute(SimpleSPArray.java:47)
at com.edmunds.evdb.SimpleSPArray.main(SimpleSPArray.java:34)
Posts: 12
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I got it to work by hacking I just passed the datasource into the execute. I think spring closes the connection and by passing the data source the connection stays open it is really strange

This Spring stored procedure passes in an array of tab delimited strings the procedure validates the strings if there are errors in validation an error is added to the error array and the array is passed back

public class JdbcUpLoadPOLinesDAOImpl extends StoredProcedure implements JdbcUploadPOLinesDAO {

private static final String UPLOAD_PO_LINES = "necxadm.po_upload_lines";
private static final String p_po_line_array= "p_po_line_array";

public JdbcUpLoadPOLinesDAOImpl(DataSource dataSource) {
super(dataSource, UPLOAD_PO_LINES);
final String[] errorList=new String[50];
declareParameter(new SqlParameter("poid", Types.BIGINT));
declareParameter(new SqlParameter("insert_user",Types.BIGINT));
declareParameter(new SqlParameter("version",Types.VARCHAR));
declareParameter(new SqlParameter("PO_LINES", Types.ARRAY,"STRING_ARRAY"));
declareParameter(new SqlOutParameter("results",Types.VARCHAR));
declareParameter(new SqlOutParameter("ERRORS", Types.ARRAY, "STRING_ARRAY", new SqlReturnType() {
public Object getTypeValue(CallableStatement cs, int paramIndex, int sqlType, String typeName) throws SQLException {
Connection con = cs.getConnection();
Map typeMap = con.getTypeMap();
typeMap.put(typeName, errorList);
Object o = cs.getObject(paramIndex);
return o;

public Map upLoadLines(DriverManagerDataSource ds ,Long poid,Long loginUser,String version,String[] poLines) {
Map inParameters = new HashMap();
final String[] temp = poLines;
inParameters.put( "poid", poid );
inParameters.put( "insert_user", loginUser );
inParameters.put( "version", version );
inParameters.put("PO_LINES", new AbstractSqlTypeValue() {

protected Object createTypeValue(Connection con, int type, String typeName)
throws SQLException {
if( con.isClosed()){
System.out.println("connection is closed");
System.out.println("connection is open");
ArrayDescriptor desc = new ArrayDescriptor(typeName, con);
return new ARRAY(desc, con, temp);

Map out = execute( inParameters ); // Call on parent class

ARRAY simpleArray=(ARRAY )out.get("ERRORS");
Object[] obj;
try {
obj = (Object[]) simpleArray.getArray(out);
} catch (SQLException e) {
// TODO Auto-generated catch block

return out;

This is the the outpput and it is what I expected
[STDOUT] obj[0]Header: Invalid Label for 'Part Number': xPart Number

    Bookmark Topic Watch Topic
  • New Topic