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

问题描述

Java spring自定义Oracle类型作为参数并收到以下错误。

Java spring custom Oracle type as a param and getting following error.

我不明白无效名称模式的含义是什么？

I don't understand what does that mean by invalid name pattern ?

感谢任何帮助。

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类型decleration

Following is my oracle type decleration

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

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 am trying to follow the following solution

推荐答案

您用于应用的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）必须是所有上限，如 MY_SCHEMA.MKT_LIST_TAB 描述符调用中的code>。

2）如果您不在代码中使用模式名称，并且您的应用程序ID与不同的模式关联，则更好为该类型（父级和子级）创建 PUBLIC SYNONYM ，并为您的应用ID授予 EXECUTE 特权否则，在代码中使用模式名称。（仍然需要提供特权）

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类型对象映射时，名称模式无效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！