Java – invalid name pattern when trying to pass custom Oracle type object mapping

Java spring customizes the Oracle type as a parameter and gets the following error

I don't understand what the invalid name pattern means?

Any help is appreciated

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)

The following is my Oracle type declination

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)

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

This 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 try to follow the following solution

How to Pass Java List of Objects to Oracle Stored Procedure Using MyBatis?

Solution

The Oracle user ID you used for the app does not have permission to access my_ SCHEMA. mkt_ list_ Tab type

Also make sure the following points

1) Must be my in descriptor call_ SCHEMA. MKT_ LIST_ All uppercase letters such as tab. 2) if you do not use the schema name in your code and your application ID is associated with a different schema, it is best to create public synonym for the type (parent and child) and grant execute privilege to your application ID. otherwise, use the schema name in your code (privileges are still required)

The content of this article comes from the network collection of netizens. It is used as a learning reference. The copyright belongs to the original author.
THE END
分享
二维码
< <上一篇
下一篇>>