Оракул процедуры вызова без типа параметров представляет собой таблицу varchar2 с использованием java SimpleJdbcCall

Я создал оракул процедуры с двумя параметрами, один из них - тип параметра out TABLE OF VARCHAR2 . как вызвать его в java и получить результат?

Моя тестовая процедура, созданная ниже:

/* creating package with specs */
create or replace PACKAGE PACK1 AS
  TYPE name_array IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
  PROCEDURE proc_filter_and_return_array( p_name_in  IN  VARCHAR2, p_name_out_array OUT name_array );
END PACK1;

/* creating package body with procedure */
create or replace PACKAGE BODY PACK1
as
    PROCEDURE proc_filter_and_return_array(
                p_name_in   IN       VARCHAR2,
                p_name_out_array OUT name_array
    )IS
                CURSOR c_table1_select is
                         select name FROM table1_test where name like '%' || p_name_in  || '%';
                v_index NUMBER := 0;
    BEGIN
            FOR x IN c_table1_select
             LOOP     
               p_name_out_array( v_index ) := x.name;
               v_index := v_index + 1;  
             END LOOP; 
    
    END proc_filter_and_return_array; 
END PACK1;

Когда я тестирую его в оракуле, я успешно получил код ниже:

DECLARE
    p_name_array pack1.name_array;
BEGIN
    pack1.proc_filter_and_return_array(p_name_in => 'name_to_filter', p_name_out_array => p_name_array);
    dbms_output.put_line(' number from table: ' || p_name_array(1) );
END;

Но в java у меня есть некоторые ошибки, я делаю так, чтобы вызвать процедуру:

    SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate)
        .withCatalogName("PACK1") 
        .withProcedureName("PROC_FILTER_AND_RETURN_ARRAY") 
        .declareParameters( new SqlParameter("P_NAME_IN", Types.VARCHAR) )
        .declareParameters( new SqlOutParameter("P_NAME_OUT_ARRAY", Types.ARRAY, "PACK1.NAME_ARRAY" ));
        
    MapSqlParameterSource map = new MapSqlParameterSource();
    map.addValue("P_NAME_IN", "name_to_filter");

    Map<String, Object> result = simpleJdbcCall.execute(map);

Итак, я получил это при запуске из java:

org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{call PACK1.PROC_FILTER_AND_RETURN_ARRAY(?, ?)}]; 
SQL state [99999]; error code [17074]; invalid name pattern: PACK1.NAME_ARRAY; nested exception is java.sql.SQLException: invalid name pattern: PACK1.NAME_ARRAY] with root cause
java.sql.SQLException: invalid name pattern: PACK1.NAME_ARRAY
        at oracle.jdbc.oracore.OracleTypeADT.initMetadata11_2(OracleTypeADT.java:764)
        at oracle.jdbc.oracore.OracleTypeADT.initMetadata(OracleTypeADT.java:479)
        at oracle.jdbc.oracore.OracleTypeADT.init(OracleTypeADT.java:443)
        at oracle.sql.ArrayDescriptor.initPickler(ArrayDescriptor.java:1499)
        at oracle.sql.ArrayDescriptor.<init>(ArrayDescriptor.java:274)
        at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:127)
        at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:79)
        at oracle.jdbc.driver.NamedTypeAccessor.otypeFromName(NamedTypeAccessor.java:83)
        at oracle.jdbc.driver.TypeAccessor.initMetadata(TypeAccessor.java:76)
        at oracle.jdbc.driver.T4CCallableStatement.allocateAccessor(T4CCallableStatement.java:599)
        at oracle.jdbc.driver.OracleCallableStatement.registerOutParameterInternal(OracleCallableStatement.java:201)
        at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:240)
        at oracle.jdbc.driver.OracleCallableStatementWrapper.registerOutParameter(OracleCallableStatementWrapper.java:1243)
        at com.zaxxer.hikari.pool.HikariProxyCallableStatement.registerOutParameter(HikariProxyCallableStatement.java)
        at org.springframework.jdbc.core.CallableStatementCreatorFactory$CallableStatementCreatorImpl.createCallableStatement(CallableStatementCreatorFactory.java:188)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:1090)
        at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:1147)
        at org.springframework.jdbc.core.simple.AbstractJdbcCall.executeCallInternal(AbstractJdbcCall.java:412)
        at org.springframework.jdbc.core.simple.AbstractJdbcCall.doExecute(AbstractJdbcCall.java:372)
        at org.springframework.jdbc.core.simple.SimpleJdbcCall.execute(SimpleJdbcCall.java:198)

к сожалению, я не мог ничего изменить в базе данных клиента :( поэтому я не могу изменить объявление TYPE name_array IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;, и мне нужно создать приложение в java, весенняя загрузка. Есть ли способ сделать это без процедуры изменения и пакета на оракуле.

Что я делаю неправильно? Заранее спасибо.

01.04.2021

Ответы:

один из них является выходным типом параметра TABLE OF VARCHAR2

Вы ошибаетесь, один из них - это TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER, который является ассоциативным массивом, и вы не можете сопоставить этот тип с помощью JDBC.

См. этот ответ или этот ответ .

Вам необходимо определить тип данных в области SQL с помощью CREATE TYPE (а не в области PL/SQL в пакете), поскольку JDBC может работать только с типами данных, определенными в SQL.
Следствием пункта 1 является то, что JDBC не поддерживает ассоциативные массивы, поскольку они являются типом данных только PL/SQL, и вам необходимо использовать коллекции (в отличие от C#, который поддерживает только ассоциативные массивы и не поддерживает коллекции). Поэтому вам нужно удалить предложение INDEX BY из типа.

Объявите тип вне пакета, используя:

CREATE TYPE name_array IS TABLE OF VARCHAR2(50);

Затем удалите объявление ассоциативного массива из пакета и вместо этого используйте новый тип коллекции. После этого могут быть другие ошибки, которые вам нужно отладить; но он никогда не будет работать с использованием ассоциативного массива.

к сожалению, я не мог ничего изменить в базе данных клиента :( поэтому я не могу изменить объявление TYPE name_array IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;, и мне нужно создать приложение в java, весенняя загрузка. Есть ли способ сделать это без процедуры изменения и пакета на оракуле.

Вы можете попытаться обойти это, вызвав анонимный блок PL/SQL (который, в свою очередь, вызывает пакет), чтобы преобразовать ассоциативный массив в VARRAY, такой как SYS.ODCIVARCHAR2LIST:

DECLARE
  v_name_assoc_array PACK1.NAME_ARRAY;
  v_name_list        SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST();
  v_idx              BINARY_INTEGER;
BEGIN
  PACK1.proc_filter_and_return_array( :p_name_in, v_name_assoc_array );

  v_idx := v_name_assoc_array.FIRST;
  WHILE v_idx IS NOT NULL LOOP
    v_name_list.EXTEND;
    v_name_list(v_name_list.COUNT) := v_name_assoc_array(v_idx);
    v_idx := v_name_assoc_array.NEXT(v_idx);
  END LOOP;

  :p_name_out_array := v_name_list;
END;
/

01.04.2021

Спасибо @MTO Это было именно то, что я искал. Я разместил ответ с вашим предложением и решением 06.04.2021

Я здесь, чтобы показать код, поскольку MTO ответил мне. работа с вызовом анонимного блока PL/SQL из java и получением результата была лучшим решением.

Наконец-то я успешно получил код ниже:

    // declaring a plsql block calling procedure and treating out return parameters
    String plSql =  " DECLARE "+
                    "    p_name_array PACK1.NAME_ARRAY "+
                    "    p_name_out_array_return SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST(); "+
                    "    v_idx BINARY_INTEGER; "+
                    " BEGIN "+
                    " "+
                    "     pack1.proc_filter_and_return_array( p_name_in => ? , p_name_out_array => p_name_array); "+
                    " "+ 
                    "     v_idx := p_name_array.first; "+
                    "     WHILE v_idx IS NOT NULL LOOP  "+
                    "        p_name_out_array_return.extend;  "+
                    "        p_name_out_array_return(idx + 1 ) :=  p_name_array(idx); "+
                    "        v_idx := p_name_array.next(v_idx); "+
                    "    END LOOP;  "+
                    " "+  
                    "   ? :=  p_name_out_array_return; "+
                    " END; ";

    // calling plsql from jdbcTemplate
    jdbcTemplate.execute( new CallableStatementCreator(){
        @Override
        public CallableStatement createCallableStatement(Connection con) throws SQLException {
            CallableStatement cs = con.prepareCall(plSql);   
                              cs.setString(1, "value to first parameter ? in plsql");
                              // registring out second param p_name_out_array_return used in plsql
                              cs.registerOutParameter(2, Types.ARRAY , SYS.ODCIVARCHAR2LIST);
            return cs;
        }
    } , new CallableStatementCallback<Object>(){
        @Override
        public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
            cs.execute();
            // do something with result out param 2 
            Arrays.asList((Object[])cs.getArray(2).getArray()).forEach(System.out::println);

            return null;
        }

    } );

06.04.2021

Новые материалы

Кластеризация: более глубокий взгляд

Кластеризация — это метод обучения без учителя, в котором мы пытаемся найти группы в наборе данных на основе некоторых известных или неизвестных свойств, которые могут существовать. Независимо от..

Как написать эффективное резюме

Предложения по дизайну и макету, чтобы представить себя профессионально Вам не позвонили на собеседование после того, как вы несколько раз подали заявку на работу своей мечты? У вас может..

Частный метод Python: улучшение инкапсуляции и безопасности

Введение Python — универсальный и мощный язык программирования, известный своей простотой и удобством использования. Одной из ключевых особенностей, отличающих Python от других языков, является..

Как я автоматизирую тестирование с помощью Jest

Шутка для победы, когда дело касается автоматизации тестирования Одной очень важной частью разработки программного обеспечения является автоматизация тестирования, поскольку она создает..

Работа с векторными символическими архитектурами, часть 4 (искусственный интеллект)

Hyperseed: неконтролируемое обучение с векторными символическими архитектурами (arXiv) Автор: Евгений Осипов , Сачин Кахавала , Диланта Хапутантри , Тимал Кемпития , Дасвин Де Сильва ,..

Понимание расстояния Вассерштейна: мощная метрика в машинном обучении

В обширной области машинного обучения часто возникает необходимость сравнивать и измерять различия между распределениями вероятностей. Традиционные метрики расстояния, такие как евклидово..

Обеспечение масштабируемости LLM: облачный анализ с помощью AWS Fargate и Copilot

В динамичной области искусственного интеллекта все большее распространение получают модели больших языков (LLM). Они жизненно важны для различных приложений, таких как интеллектуальные..

Machine Learning JavaScript Blockchain Artificial Intelligence Data Science Cryptocurrency Software Development Python Web Development Coding Deep Learning AI Bitcoin React Software Engineering Ethereum Web3 Business Crypto Nodejs Solidity Development Front End Development Data Finance Money Java Trading Typescript Smart Contracts Productivity Tech Startup Investing Neural Networks Developer Computer Science NLP