The error occurred while setting parameters

I'm trying to call a procedure with MyBatis. This is the procedure signature: PROCEDURE pr_start(io_calc IN OUT TYPE_CALC, in_restart BOOLEAN DEFAULT TRUE,

I’m trying to call a procedure with MyBatis.

This is the procedure signature:

PROCEDURE pr_start(io_calc    IN OUT TYPE_CALC,
                   in_restart BOOLEAN DEFAULT TRUE,
                   in_user    VARCHAR2 DEFAULT NULL);

It’s in a package named PACKAGE_PP.

This is how TYPE_CALC is declared:

CREATE OR REPLACE TYPE TYPE_CALC AS OBJECT (
    modelField   VARCHAR2(5 CHAR),
    sysField     VARCHAR2(5 CHAR),
    hexField     VARCHAR2(5 CHAR)
);

This is my XML mapping:

<select id="pr_start" 
    statementType="CALLABLE" parameterType="map">
    {
        exec PACKAGE_PP.pr_start(
            #{io_calc,mode=INOUT,jdbcType=STRUCT,jdbcTypeName=TYPE_CALC},
            #{in_restart,mode=IN,jdbcType=BOOLEAN,jdbcTypeName=BOOLEAN},
            #{in_user,mode=IN,jdbcType=VARCHAR,jdbcTypeName=VARCHAR2}
        )
    }
</select>

(p.s. I also tried with call instead of exec, it produces the same error)

My java mapper:

public interface PackagePPMapper {
    Object pr_start(Map<String, Object> param);
}

This is how I call it:

Object[] typeCalcArr = new Object[]{"A", "s", "0xD"};

WebSphereNativeJdbcExtractor wsn = new WebSphereNativeJdbcExtractor();
Connection connection = wsn.getNativeConnection(session.getConnection()); //session is a SqlSession (properly initialized)
StructDescriptor descriptor = StructDescriptor.createDescriptor("TYPE_CALC", connection);
STRUCT typeCalc = new STRUCT(descriptor, connection, typeCalcArr);

Map<String, Object> parametersMap = new HashMap<String, Object>();
parametersMap.put("io_calc", typeCalc);
parametersMap.put("in_restart", restart);
parametersMap.put("in_user", user);

getMapper(session).pr_start(parametersMap);

StackTrace:

org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: java.sql.SQLException: Non supported SQL92 token at position: 6
### The error may exist in com/jsfproj/mybatis/xmlmapper/PackagePPMapper.xml
### The error may involve com.jsfproj.mybatis.PackagePPMapper.pr_start-Inline
### The error occurred while setting parameters
### SQL: {    exec PACKAGE_PP.pr_start(     ?,     ?,     ?    )   }
### Cause: java.sql.SQLException: Non supported SQL92 token at position: 6

What am I missing? Should procedures be called in other ways?

Источник

org.apache.ibatis.exceptions.PersistenceException:

Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘�湱[肺呒y笸g渜?泶觮喬A ＼躙摀搒茹?烘歬��C 唸!b�憯?@]W?苘箂g’ at line 6

The error may involve org.activiti.engine.impl.persistence.entity.ResourceEntity.bulkInsertResource-Inline

The error occurred while setting parameters

SQL: INSERT INTO ACT_GE_BYTEARRAY(ID_, REV_, NAME_, BYTES_, DEPLOYMENT_ID_, GENERATED_) VALUES (?, 1, ?, ?, ?, ?) , (?, 1, ?, ?, ?, ?)

Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘�湱[肺呒y笸g渜?泶觮喬A ＼躙摀搒茹?烘歬��C 唸!b�憯?@]W?苘箂g’ at line 6

at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:23)
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:150)
at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:137)
at org.activiti.engine.impl.db.DbSqlSession.flushBulkInsert(DbSqlSession.java:850)
at org.activiti.engine.impl.db.DbSqlSession.flushPersistentObjects(DbSqlSession.java:820)
at org.activiti.engine.impl.db.DbSqlSession.flushInserts(DbSqlSession.java:797)
at org.activiti.engine.impl.db.DbSqlSession.flush(DbSqlSession.java:618)
at org.activiti.engine.impl.interceptor.CommandContext.flushSessions(CommandContext.java:212)
at org.activiti.engine.impl.interceptor.CommandContext.close(CommandContext.java:138)
at org.activiti.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:66)
at org.activiti.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:31)
at org.activiti.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:40)
at org.activiti.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:35)
at org.activiti.engine.impl.RepositoryServiceImpl.deploy(RepositoryServiceImpl.java:79)
at org.activiti.engine.impl.repository.DeploymentBuilderImpl.deploy(DeploymentBuilderImpl.java:156)
at myFirstAct.CreatTable.deploy(CreatTable.java:63)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:86)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:459)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:675)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:382)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192)

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘�湱[肺呒y笸g渜?泶觮喬A ＼躙摀搒茹?烘歬��C 唸!b�憯?@]W?苘箂g’ at line 6
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:377)
at com.mysql.jdbc.Util.getInstance(Util.java:360)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:978)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3887)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3823)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2530)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1907)
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1199)
at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:41)
at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:66)
at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:45)
at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:100)
at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:75)
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:148)
… 37 more

Источник

I encountered an error today. Running rankdao. Selectbyid (ID) directly reported an error to me. I didn’t understand it

Error message:

2021-09-22 10:40:58.824 ERROR 8364 --- [nio-8888-exec-4] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: 
### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'rank WHERE id=1' at line 1
### The error may exist in com/brilliantZC/music/dao/MusicRankDao.java (best guess)
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT id,song_list_id,consumer_id,score FROM rank WHERE id=?
### Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'rank WHERE id=1' at line 1
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'rank WHERE id=1' at line 1] with root cause

At the beginning, Baidu has various methods, some say

    an error is reported when the value of the column in the created table is less than the actual value. The @ resource or @ Autowired annotation is forgotten on the service interface introduced by the controller layer. The method called by the controller layer is private, resulting in distribution failure. Check whether the mapper interface code conforms to the specification (automatically ignored by mybatis plus)

    I tried again, but I still couldn’t, so I took the wrong SQL statement and executed it

    SELECT id,song_list_id,consumer_id,score FROM rank WHERE id=1

    Still report an error

    music> SELECT id,song_list_id,consumer_id,score FROM rank WHERE id=1
[2021-09-22 10:55:58] [42000][1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'rank WHERE id=1' at line 1

    Then I was confused. Then I removed the query statement and still reported an error

    SELECT id,song_list_id,consumer_id,score FROM rank

    Then Baidu saw that because rank is a keyword, it can not be directly used as a non keyword. It needs to be used with `. It can be executed after replacement

    SELECT id,song_list_id,consumer_id,score FROM `rank`

    Then I modified the table name and solved the error!!!

Read More:

Источник

Вопрос:

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)

Следующее – это отклонение типа моего оракула

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


}

Я пытаюсь выполнить следующее решение

Как передать Java-список объектов в хранимую процедуру Oracle с помощью MyBatis?

Лучший ответ:

Идентификатор пользователя oracle, который вы используете для своего приложения, не имеет доступа к типу MY_SCHEMA.mkt_list_tab.

Также убедитесь, что нижеследующие пункты.

1) В вашем вызове дескриптора должны быть ВСЕ шапки типа MY_SCHEMA.MKT_LIST_TAB.
2) Если вы не используете имя схемы в коде, а ваш идентификатор приложения связан с другой схемой, лучше создать PUBLIC SYNONYM для типа (как родителя, так и дочернего) и предоставить привилегию EXECUTE вашему идентификатору приложения, else, используйте имя схемы в коде. (необходимы еще привилегии)

Ответ №1

Одна из двух вещей:

  • Пользователь oracle, к которому вы подключаетесь к базе данных, не имеет привилегий для выполнения хранимой процедуры (маловероятно)
  • Ваш ArrayDescriptor и StructDescriptor должны иметь “mkt_list_tab” и “mkt_list_rec” соответственно в верхнем регистре. (более вероятно)

Обновление: этот вопрос похож на ваш.

Ответ №2

Я столкнулся с такой же проблемой при вызове процедуры хранения Oracle.

PACKAGE_NAME.PROCEDURE_NAME (?,?,?,?,?,?,?,?,?)}];

13: 52: 24.202 [main] DEBUG osjsSQLStateSQLExceptionTranslator – Извлеченный класс состояний SQL ’99’ из значения ‘99999’ org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; не классифицированный SQLException для SQL [{вызов PACKAGE_NAME.PROCEDURE_NAME (?,?,?,?,?,?,?,?,?)}]; Состояние SQL [99999]; код ошибки [17074]; неверный шаблон имени: SCHEMA.Type_Param; Вложенное исключение – это java.sql.SQLException: неправильный шаблон имени: SCHEMA.Type_Param at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83) в org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator. java: 80) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80) в org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:1036) на org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:1070) в org.springframework.jdbc.object.StoredProcedure.execute(StoredProcedure.java:144)

Моя процедура принимала 1 входной параметр типа таблицы. Этот параметр был определен в области уровня пакета. Поэтому я перемещаю этот параметр уровня пакета на уровень параметра Schema и решал проблему.

Типы PLSQL, созданные в пакете, не могут быть доступны непосредственно из java.

Источник

Понравилась статья? Поделить с друзьями:
  • The amazing spider man ошибка сохранения
  • The above error occurred in the component
  • The aa number has not been programmed on the system как исправить
  • That name is already taken minecraft как исправить
  • The error code reported is enotimplementedyet 3d max