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)

Answer 2

One of 2 things:

1. The oracle user that you are connecting to the database with does not have privileges to execute the stored procedure. (unlikely)
2. Your ArrayDescriptor and StructDescriptor should have "mkt_list_tab" and "mkt_list_rec" in uppercase respectively. (more likely)

See also this question.

Answer 3

I was facing the same issue while calling a Oracle Stored procedure:

PACKAGE_NAME.PROCEDURE_NAME(?, ?, ?, ?,?, ?, ?, ?, ?)}]; 

13:52:24.202 [main] DEBUG o.s.j.s.SQLStateSQLExceptionTranslator - Extracted SQL state class '99' 
from value '99999' org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; 
uncategorized SQLException for SQL [{call PACKAGE_NAME.PROCEDURE_NAME(?, ?, ?, ?,?, ?, ?, ?, ?)}]; 
SQL state [99999]; error code [17074]; invalid name pattern: SCHEMA.Type_Param; nested exception is java.sql.SQLException: 
***invalid name pattern: SCHEMA.Type_Param***
        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.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:1036)
        at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:1070)
        at org.springframework.jdbc.object.StoredProcedure.execute(StoredProcedure.java:144)

My procedure was taking 1 Table type input parameter. This parameter was defined at package level scope. So I move this package level scope parameter to Schema level parameter and solved the issue.

PLSQL types created within a package can't be accessed directly from java.