CallableStatementCallback; uncategorized SQLException for SQL

[muthukumar]

Hi,

I am getting this exception when the SP recieves an input as array

here's code for the Oracle type created

Type

CREATE OR REPLACE TYPE SCHEMA."MY_ARRAY" as table of varchar2(20)

SP

CREATE OR REPLACE PROCEDURE SCHEMA.PROC(

p_date IN DATE,
p_my_array IN MY_ARRAY,
p_recordset OUT Types.cursor_type,
p_recordset1 OUT Types.cursor_type)

IS

BEGIN
END



This type is being mapped as per the below

Java Source
Public class MyDAO {

private class MyStoredProcedure extends StoredProcedure {

static final String procName = "SCHEMA.PROC";

public MyStoredProcedure(DataSource ds, NativeJdbcExtractor jdbcExtractor) throws Exception{
setDataSource(ds);
setFunction(false);
setSql(procName);
this.getJdbcTemplate().setNativeJdbcExtractor(jdbc Extractor);

declareParameter(new SqlParameter("p_date", Types.DATE));
declareParameter(new SqlParameter("p_my_array", Types.ARRAY,"MY_ARRAY"));
declareParameter(new SqlOutParameter("p_recordset", OracleTypes.CURSOR,new ResultsMapper()));
declareParameter(new SqlOutParameter("p_recordset1", OracleTypes.CURSOR,new RecordMapper()));
compile();
}

public Map execute(Set mySet,Date date) throws Exception{

Map map = new HashMap<String, Object>();
final String[] a = new String[mySet.size()];
Iterator itrt = mySet.iterator();
int i = 0;
while(itrt.hasNext()){
a[i++] = itrt.next().toString();
}

map.put("p_date",date);

map.put("p_my_array", new SqlTypeValue() {
public void setTypeValue(PreparedStatement preparedStatement, int index, int sqltype, String typeName) throws SQLException {
Connection con = preparedStatement.getConnection();
OracleConnection oracleConnection;
if (con instanceof WLConnection) {
oracleConnection = (OracleConnection) ((WLConnection) con).getVendorConnection();
} else {
oracleConnection = (OracleConnection) con;
}
System.out.println("connection is after cast " + con.getClass());
ArrayDescriptor des = ArrayDescriptor.createDescriptor("SCHEMA.MY_ARRAY" , con); //Failing here

ARRAY array = new ARRAY(des, oracleConnection, a);

preparedStatement.setObject(index, array);

};
});
Map out = execute(map);
return out;
} }
}

Please suggest the direction or am I missing anything here.

Adding the stacktrace

org.springframework.jdbc.UncategorizedSQLException : CallableStatementCallback; uncategorized SQLException for SQL [{call SCHEMA.PROC(?, ?, ?, ?)}]; SQL state [99999]; error code [17074]; invalid name pattern:SCHEMA.MY_ARRAY; nested exception is java.sql.SQLException: invalid name pattern: SCHEMA.MY_ARRAY
at org.springframework.jdbc.support.AbstractFallbackS QLExceptionTranslator.translate(AbstractFallbackSQ LExceptionTranslator.java:83)
at org.springframework.jdbc.support.AbstractFallbackS QLExceptionTranslator.translate(AbstractFallbackSQ LExceptionTranslator.java:80)
at org.springframework.jdbc.support.AbstractFallbackS QLExceptionTranslator.translate(AbstractFallbackSQ LExceptionTranslator.java:80)
at org.springframework.jdbc.core.JdbcTemplate.execute (JdbcTemplate.java:952)
at org.springframework.jdbc.core.JdbcTemplate.call(Jd bcTemplate.java:985)
at org.springframework.jdbc.object.StoredProcedure.ex ecute(StoredProcedure.java:117)
......
Caused by: java.sql.SQLException: invalid name pattern: CBFAAPPUSER.CLIENT_ARRAY
at oracle.jdbc.driver.SQLStateMapping.newSQLException (SQLStateMapping.java:70)
at oracle.jdbc.driver.DatabaseError.newSQLException(D atabaseError.java:133)
at oracle.jdbc.driver.DatabaseError.throwSqlException (DatabaseError.java:199)
at oracle.jdbc.driver.DatabaseError.throwSqlException (DatabaseError.java:263)
at oracle.jdbc.driver.DatabaseError.throwSqlException (DatabaseError.java:271)
at oracle.jdbc.oracore.OracleTypeADT.initMetadata(Ora cleTypeADT.java:543)
at oracle.jdbc.oracore.OracleTypeADT.init(OracleTypeA DT.java:462)
at oracle.sql.ArrayDescriptor.initPickler(ArrayDescri ptor.java:1678)
at oracle.sql.ArrayDescriptor.<init>(ArrayDescriptor. java:291)
at oracle.sql.ArrayDescriptor.createDescriptor(ArrayD escriptor.java:206)
at oracle.sql.ArrayDescriptor.createDescriptor(ArrayD escriptor.java:175)
at oracle.sql.ArrayDescriptor.createDescriptor(ArrayD escriptor.java:158)
at oracle.sql.ArrayDescriptor.createDescriptor(ArrayD escriptor.java:125)
at ....
at org.springframework.jdbc.core.StatementCreatorUtil s.setValue(StatementCreatorUtils.java:262)
at org.springframework.jdbc.core.StatementCreatorUtil s.setParameterValueInternal(StatementCreatorUtils. java:217)
at org.springframework.jdbc.core.StatementCreatorUtil s.setParameterValue(StatementCreatorUtils.java:128 )
at org.springframework.jdbc.core.CallableStatementCre atorFactory$CallableStatementCreatorImpl.createCal lableStatement(CallableStatementCreatorFactory.jav a:213)
at org.springframework.jdbc.core.JdbcTemplate.execute (JdbcTemplate.java:930)
... 98 more

[dr_pompeii]

Hello

1) use code tags it is more readable for us
2) the createDescriptor method expect the first parameter a String value type? "SCHEMA.MY_ARRAY"

org.springframework.jdbc.UncategorizedSQLException : 
CallableStatementCallback; 
uncategorized SQLException for SQL [{call SCHEMA.PROC(?, ?, ?, ?)}]; 
SQL state [99999]; error code [17074]; 
invalid name pattern:SCHEMA.MY_ARRAY; 
nested exception is 
java.sql.SQLException: invalid name pattern: SCHEMA.MY_ARRAY
at org.springframework.jdbc.support.AbstractFallbackS QLExceptionTran

[muthukumar]

Yes.This is the type I created

CREATE OR REPLACE TYPE SCHEMA."MY_ARRAY" as table of varchar2(20)

[dr_pompeii]

I am not an expert about procedures

1) SCHEMA."MY_ARRAY" is not the same than "SCHEMA.MY_ARRAY"

2)About your stack error

......
Caused by: java.sql.SQLException:
invalid name pattern: CBFAAPPUSER.CLIENT_ARRAY
at oracle.jdbc.driver.SQLStateMapping.newSQLException (SQLStateMapping.java:70)

Could you post such CBFAAPPUSER.CLIENT_ARRAY??

[muthukumar]

Hi,

SCHEMA.MY_ARRAY is same as CBFAAPPUSER.CLIENT_ARRAY (this is our actual code)

Here's the sample script

CREATE OR REPLACE TYPE SCHEMA."MY_ARRAY" as table of varchar2(20)

Original script

CREATE OR REPLACE TYPE CBFAAPPUSER.CLIENT_ARRAY as table of varchar2(20)

Both are same..only the name is different. I posted with sample name.