尝试传递自定义 oracle 类型对象映射时无效的名称模式 [英] invalid name pattern when trying to pass custom oracle type object mapping

问题描述

我正在尝试使用 Java spring 自定义 Oracle 类型作为参数并收到以下错误:

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?

这是我的 Oracle 类型声明:

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 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 类型处理程序:

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

这是我的存储过程 pojo 类:

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:

如何通过使用 MyBatis 到 Oracle 存储过程的 Java 对象列表?

推荐答案

您用于应用的 oracle 用户 ID 无权访问 MY_SCHEMA.mkt_list_tab 类型.

The oracle user id, you use for your app, doesn't have access to the type MY_SCHEMA.mkt_list_tab.

还要确保以下几点.

1) 在您的 descriptor 调用中，它必须是像 MY_SCHEMA.MKT_LIST_TAB 这样的全部大写字母.
2) 如果你没有在代码中使用架构名称，并且你的应用 id 与不同的架构相关联，最好为类型(父级和子级)创建一个 PUBLIC SYNONYM，并且将 EXECUTE 权限授予您的应用程序 ID，否则，请在代码中使用架构名称.(仍然需要授予权限)

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)

这篇关于尝试传递自定义 oracle 类型对象映射时无效的名称模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！