尝试传递自定义oracle类型对象映射时的无效名称模式

Question

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类型整理

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;
    }


}

我正在尝试遵循以下解决方案

如何使用MyBatis将对象的Java列表传递给Oracle存储过程？

Answer 1

您用于应用程序的oracle用户ID无权访问type MY_SCHEMA.mkt_list_tab。

还要确保以下几点。

1）必须像MY_SCHEMA.MKT_LIST_TAB您的 描述符 调用中那样全部大写。
2）如果您未在代码中使用架构名称，并且您的应用ID与其他架构相关联，则最好为PUBLIC SYNONYM类型（父级和子级）创建一个，并EXECUTE为您的应用ID 授予特权，否则，在代码中使用架构名称。（仍然需要提供特权）