java - invalid name pattern when trying to pass custom oracle type object mapping

Question

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?

Answer 1

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)