Java 尝试传递自定义 oracle 类型对象映射时无效的名称模式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21589155/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
invalid name pattern when trying to pass custom oracle type object mapping
提问by d-man
Java spring custom Oracle type as a param and getting following error.
Java spring 自定义 Oracle 类型作为参数并出现以下错误。
I don't understand what does that mean by invalid name pattern ?
我不明白无效名称模式是什么意思？
Any help 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)
Following is my oracle type decleration
以下是我的 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)
Here is my java type handler
这是我的 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
}
}
Here is my stored procedure pojo class
这是我的存储过程 pojo 类
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
我正在尝试遵循以下解决方案
How to Pass Java List of Objects to Oracle Stored Procedure Using MyBatis?
采纳答案by Maheswaran Ravisankar
The oracle user id, you use for your app, doesn't have access to the type MY_SCHEMA.mkt_list_tab.
您用于应用程序的 oracle 用户 ID 无权访问 type MY_SCHEMA.mkt_list_tab。
Also make sure the below points.
还要确保以下几点。
1) It has to be ALL caps like MY_SCHEMA.MKT_LIST_TABin your descriptorcall.
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 SYNONYMto the type(both the parent and child), and grant EXECUTEprivilege to your app id, else, use the schema name in the code.(privileges still needed to be given)
1）它必须像MY_SCHEMA.MKT_LIST_TAB您的描述符调用中一样全部大写。
2）如果你没有在代码中使用schema名称，并且你的app id与不同的schema相关联，最好为PUBLIC SYNONYM类型（父子和子）创建一个，并EXECUTE为你的app id授予权限，否则，在代码中使用模式名称。（仍然需要给予特权）
回答by Shiraaz.M
One of 2 things:
两件事之一：
- The oracle user that you are connecting to the database with does not have privileges to execute the stored procedure (unlikely)
- Your ArrayDescriptor and StructDescriptor should have "mkt_list_tab" and "mkt_list_rec" in uppercase respectively. (more likely)
- 您连接到数据库的 oracle 用户没有执行存储过程的权限（不太可能）
- 你的 ArrayDescriptor 和 StructDescriptor 应该分别有大写的“mkt_list_tab”和“mkt_list_rec”。（更倾向于）
Update: This questionis similar to yours.
更新：这个问题和你的类似。
回答by Waqas Ahmed
I Was facing the same issue while calling a Oracle Stored procedure.
我在调用 Oracle 存储过程时遇到了同样的问题。
PACKAGE_NAME.PROCEDURE_NAME(?, ?, ?, ?,?, ?, ?, ?, ?)}];
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_Paramat 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)
13:52:24.202 [main] DEBUG osjsSQLStateSQLExceptionTranslator - 从值 '99999' org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback 中提取 SQL 状态类 '99'；未分类的 SQL 异常 SQL [{call PACKAGE_NAME.PROCEDURE_NAME(?, ?, ?, ?,?, ?, ?, ?, ?)}]; SQL 状态 [99999]；错误代码 [17074]; 无效的名称模式：SCHEMA.Type_Param；嵌套异常是 java.sql.SQLException： 无效的名称模式：SCHEMA.Type_Param在 org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83) 在 org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80) 在 org.springframework. (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 param to Schema level param and solved the issue.
我的程序采用 1 Table type input parameter 。此参数是在包级别范围内定义的。因此，我将此包级别范围参数移动到架构级别参数并解决了该问题。
PLSQL types created within a package can't be accessed directly from java.
在包中创建的 PLSQL 类型不能直接从 java 访问。
