Java – invalid name pattern when trying to pass custom Oracle type object mapping
Java spring customizes the Oracle type as a parameter and gets the following error
I don't understand what the invalid name pattern means?
Any help is appreciated
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)
The following is my Oracle type declination
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)
This is my java type handler
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,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 } }
This is my stored procedure POJO class
public class StoredProcedurePojo { private String market; public String getMarket() { return market; } public void setMarket(String market) { this.market = market; } }
I try to follow the following solution
How to Pass Java List of Objects to Oracle Stored Procedure Using MyBatis?
Solution
The Oracle user ID you used for the app does not have permission to access my_ SCHEMA. mkt_ list_ Tab type
Also make sure the following points
1) Must be my in descriptor call_ SCHEMA. MKT_ LIST_ All uppercase letters such as tab. 2) if you do not use the schema name in your code and your application ID is associated with a different schema, it is best to create public synonym for the type (parent and child) and grant execute privilege to your application ID. otherwise, use the schema name in your code (privileges are still required)