invalid name pattern when trying to pass custom oracle type object mapping
java, oracle, oracle-type, plsql, spring-jdbc
Solution
The oracle user id, you use for your app, doesn't have access to the type `MY_SCHEMA.mkt_list_tab`.
Also make sure the below points.
1) It has to be ALL caps like `MY_SCHEMA.MKT_LIST_TAB` in your descriptor call. 2) If you don't use the schema name in code, and your app id is associated with a different schema, better to create a `PUBLIC SYNONYM` to the type(both the parent and child), and grant `EXECUTE` privilege to your app id, else, use the schema name in the code.(privileges still needed to be given)
Problem
I am trying to use Java spring custom Oracle type as a parameter and getting the following error: ``` 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) ``` I don't understand: what does it mean by invalid name pattern? This is my Oracle type declaration: ``` 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) ``` Here 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, 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 } } ``` Here 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 have tried to follow this solution: How to Pass Java List of Objects to Oracle Stored Procedure Using MyBatis?