Java spring自定义Oracle类型作为参数并获得跟随错误.
我不明白无效名称模式是什么意思?
任何帮助赞赏.
org.springframework.jdbc.UncategorizedSQLException:
### Error updating database. Cause: java.sql.SQLException: invalid name pattern: UPSELL.mkt_list_tab
### The error may involve com.comcast.upsell.dao.ProviderAndRegionalDao.getCorpsToMarketsList-Inline
### The error occurred while setting parameters
### SQL: call upsell_tx_etl_report.GET_OFFER_CORPS_TO_MARKETS( ?, ?, ? )
### Cause: java.sql.SQLException: invalid name pattern: MY_SCHEMA.mkt_list_tab
; uncategorized SQLException for SQL []; SQL state [99999]; error code [17074]; invalid name pattern: MY_SCHEMA.mkt_list_tab; nested exception is java.sql.SQLException: invalid name pattern: MY_SCHEMA.mkt_list_tab
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:71)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:364)
at com.sun.proxy.$Proxy15.update(Unknown Source)
以下是我的oracle类型decleration
create or replace
type mkt_list_tab is table of mkt_list_rec
create or replace
type mkt_list_rec as object
(
market VARCHAR2(100)
)
Procedure call as following
PROCEDURE GET_OFFER_CORPS_TO_MARKETS(p_division IN VARCHAR2, --ALL/Particular
p_market_list IN mkt_list_tab,
o_offer_corp_market_cur OUT SYS_REFCURSOR)
这是我的java类型处理程序
public class MarketListTypeHandler implements TypeHandler {
@SuppressWarnings("unchecked")
@Override
public void setParameter(PreparedStatement ps, int i, Object parameter, JdbcType jdbcType) throws SQLException {
C3P0NativeJdbcExtractor cp30NativeJdbcExtractor = new C3P0NativeJdbcExtractor();
OracleConnection connection = (OracleConnection) cp30NativeJdbcExtractor.getNativeConnection(ps.getConnection());
List<StoredProcedurePojo> objects = (List<StoredProcedurePojo>) parameter;
StructDescriptor structDescriptor = StructDescriptor.createDescriptor("mkt_list_rec", connection);
STRUCT[] structs = new STRUCT[objects.size()];
for (int index = 0; index < objects.size(); index++)
{
StoredProcedurePojo pack = objects.get(index);
Object[] params = new Object[2];
params[0] = pack.getMarket();
STRUCT struct = new STRUCT(structDescriptor, ps.getConnection(), params);
structs[index] = struct;
}
ArrayDescriptor desc = ArrayDescriptor.createDescriptor("mkt_list_tab", ps.getConnection());
ARRAY oracleArray = new ARRAY(desc, ps.getConnection(), structs);
ps.setArray(i, oracleArray);
}
@Override
public Object getResult(ResultSet arg0, String arg1) throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public Object getResult(ResultSet arg0, int arg1) throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public Object getResult(CallableStatement arg0, int arg1) throws SQLException {
// TODO Auto-generated method stub
return null;
}
public MarketListTypeHandler() {
super();
// TODO Auto-generated constructor stub
}
}
这是我的存储过程pojo类
public class StoredProcedurePojo {
private String market;
public String getMarket() {
return market;
}
public void setMarket(String market) {
this.market = market;
}
}
我试图遵循以下解决方案
How to Pass Java List of Objects to Oracle Stored Procedure Using MyBatis?
解决方法:
您用于应用的oracle用户ID无权访问MY_SCHEMA.mkt_list_tab类型.
还要确保以下几点.
1)在描述符调用中必须是MY_SCHEMA.MKT_LIST_TAB之类的所有大写字母.
2)如果您不在代码中使用模式名称,并且您的应用程序ID与不同的模式相关联,则最好为该类型(父级和子级)创建PUBLIC SYNONYM,并为您的应用程序ID授予EXECUTE特权否则,在代码中使用模式名称.(仍需要提供特权)