Отправить вложенную таблицу с помощью sys_refcursor в Oracle10gv2

Question

Мне нужно отправить некоторые данные в Jasper Reports, используя sys_refcursor. Эти данные являются результатом запроса и оценки результатов запроса в pl / sql. Идея состоит в том, чтобы подсчитать некоторые значения, фильтрующиеся по нескольким столбцам в одной таблице, и этого нельзя сделать в запросе с подвыборками из-за ограничений фильтрации. Извините, что не очень ясно, но я под NDA. Тем не менее, я могу опубликовать некоторый код и объяснить важную часть функциональности, которую я должен достичь. Проект основан на Java и использует Oracle 10gv2 и JasperReports 3.6.1, и это не может быть обновлено (поэтому нет Oracle v12).

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

create or replace PROCEDURE test_proc02(test_cursor OUT sys_refcursor) IS

    /* associative arrays declaration */

    TYPE transfer_type IS TABLE OF NUMBER
        INDEX BY VARCHAR2(10);
    transfer_table transfer_type; 

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

Select * from table(cast(transfer_table AS transfer_type))

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

/* nested table declaration */

TYPE transfer_nt_type IS TABLE OF VARCHAR2(20);

/* nested table initilization */
transfer_nt transfer_nt_type := transfer_nt_type();

/* some variables */
transfer_id VARCHAR2(10);
transfer_number NUMBER;
nt_counter INTEGER := 0;
nt_iter VARCHAR2(10);


/* copying AA into NT */
nt_iter := transfer_table.FIRST;
WHILE (nt_iter IS NOT NULL)
LOOP        
    nt_counter := nt_counter+1;
    transfer_nt.EXTEND;
    transfer_nt(nt_counter):=transfer_table(nt_iter);
    dbms_output.put_line('nested table ('||nt_counter||'): '||transfer_nt(nt_counter));
    nt_iter := transfer_table.NEXT(nt_iter);
END LOOP;

/* Trying to send NT to JR */
OPEN travelCursor FOR SELECT * FROM TABLE(cast(transfer_nt AS transfer_nt_type));

/* ERROR */
PLS-00382: expression is of wrong type

Меня не волнует метод, я просто хочу отправить данные в JR, чтобы сгенерировать отчет, так что если мне придется заменить полную структуру процедуры, то это нормально для меня. Я искал здесь несколько дней в stackru и других источниках, но, похоже, ничего не работает, поэтому я не уверен, что все мои концептуальные идеи ошибочны или нет.

Есть идеи? Благодарю.

РЕДАКТИРОВАТЬ:

Объявление типа для Transfer_nt_type было неверным, скопировано из предыдущей версии. Теперь это правильный. Данные АА примерно такие:

Key       value
--------------
A548521     5
A325411     12
A329471     9

Общее количество пар - 32, ключ - varchar2(10), а значение - число. Содержимое (VARCHAR(20)) последней вложенной таблицы:

A548521#5,A325411#12,A329471#9

Тип объявлен на уровне схемы. Я пробовал также:

OPEN travelCursor FOR 
        SELECT CAST(MULTISET(
            SELECT * FROM TABLE(transfer_nt)
            ORDER BY 1) AS transfer_nt_type)
        INTO transfer_nt_out FROM DUAL;

С такими же результатами. Обе структуры данных были протестированы и напечатаны с использованием dbms_output без ошибок, данные внутри структур верны. Мне нужно, по крайней мере, отправить значения в указанном порядке, если это возможно. Ключи не важны, если я могу поддерживать определенный порядок в ответе значения.

Отредактировано, чтобы отразить предложение Алекса Пула. Перед началом процедуры:

FUNCTION transfer_func (transfer_table transfer_type)RETURN transfer_nt_type PIPELINED IS

      --TYPE transfer_type IS TABLE OF NUMBER INDEX BY VARCHAR2(10);
      --transfer_table transfer_type; 

      nt_iter VARCHAR2(10);

      BEGIN

          nt_iter := transfer_table.FIRST;
          WHILE (nt_iter IS NOT NULL)
          LOOP
            PIPE ROW (nt_iter || '#' || transfer_table(nt_iter));
            nt_iter := transfer_table.NEXT(nt_iter);
          END LOOP;

    END transfer_func;

Перед процедурой КОНЕЦ:

OPEN travelCursor for select * from table(transfer_func(transfer_table));

Та же ошибка:

PLS-00382: expression is of wrong type

Окончательное редактирование и решение:

Наконец я решил проблему с помощью GTT. Я не знаю почему, но в первый раз, когда я попробовал этот метод, Oracle Developer вернул ту же ошибку, что и другие возможные решения. Я попробовал самый старый способ: закройте программу, перезагрузите компьютер и начните с самого начала. И это сработало! конечно, только с GTT.

nt_iter := transfer_table.FIRST;
WHILE (nt_iter IS NOT NULL)
LOOP        
        nt_counter := nt_counter+1;
        INSERT INTO transfer_temp VALUES(nt_iter,transfer_table(nt_iter),06);

        nt_iter := transfer_table.NEXT(nt_iter);
END LOOP;

    OPEN test_cursor FOR select * from transfer_temp order by transfer_temp.id;

CREATE GLOBAL TEMPORARY TABLE transfer_temp (
        id           VARCHAR(20),
        value         NUMBER,
        month         NUMBER
        )
        ON COMMIT PRESERVE ROWS;

Спасибо всем за помощь!

Answer 1

Простое решение (возможно, с низкой производительностью) было бы записать результат во временную таблицу, а затем

OPEN travelCursor FOR SELECT * FROM That_Temp_Table;

Answer 2

Вместо этого вы можете использовать конвейерную функцию, используя тип таблицы уровня схемы:

create or replace TYPE transfer_nt_type AS TABLE OF VARCHAR2(20)
/

create or replace FUNCTION test_func02
RETURN transfer_nt_type PIPELINED IS

  TYPE transfer_type IS TABLE OF NUMBER INDEX BY VARCHAR2(10);
  transfer_table transfer_type; 

  nt_iter VARCHAR2(10);

BEGIN

  -- sample data
  transfer_table('A548521') := 5;
  transfer_table('A325411') := 12;
  transfer_table('A329471') := 9;

  nt_iter := transfer_table.FIRST;
  WHILE (nt_iter IS NOT NULL)
  LOOP
    PIPE ROW (nt_iter || '#' || transfer_table(nt_iter));
    nt_iter := transfer_table.NEXT(nt_iter);
  END LOOP;

  RETURN;

END test_func02;
/

Тогда вы можете сделать:

select * from table(test_func02);

который получает:

Result Sequence     
--------------------
A325411#12
A329471#9
A548521#5

Если вы ограничены указателем ref, вы можете добавить процедуру-обертку:

create or replace PROCEDURE test_proc02(test_cursor OUT sys_refcursor) IS
BEGIN

  OPEN test_cursor FOR SELECT * FROM TABLE(test_func02);

END test_proc02;
/

var rc refcursor;

exec test_proc02(:rc);

print rc;

Result Sequence     
--------------------
A325411#12
A329471#9
A548521#5

Answer 3

Я считаю, что у вас есть проблемы, потому что:

1) Вы определили свой тип внутри процедуры

Так что попробуйте создать тип, как в примере выше

CREATE OR REPLACE TYPE "TSTRINGTABLE" AS TABLE OF VARCHAR2(20)
/

2) Вы используете select * from в индексированной таблице...

если вам нужно хранить 2 значения в вашей вложенной таблице, то создайте таблицу типов записей с записями типа

CREATE OR REPLACE TYPE "TSTRING2LIST_RECORD" AS OBJECT
(
  column1_value VARCHAR2(4000),
  column2_value VARCHAR2(4000)
)
/

CREATE OR REPLACE TYPE "TSTRING2LIST_TABLE" IS TABLE OF TSTRING2LIST_RECORD
/

после этого вы можете сделать объявление в своей процедуре и использовать его как угодно, мой код ниже может быть нарушен, потому что я пишу это без IDE, но вам нужно уловить мою идею:

PROCEDURE idk(cur out sys_refcursor) IS
  l_table tstring2list_table := tstring2list_table();

BEGIN
  l_table.extend;
  l_table(l_table.count) := tstring2list_record('idx1', 'value1');
  l_table.extend;
  l_table(l_table.count) := tstring2list_record('idx2', 'value2');
  l_table.extend;
  l_table(l_table.count) := tstring2list_record('idx3', 'value3');

  OPEN cur for select column2_value from table(l_table);
END;

Вы также можете использовать массовый сбор или динамический SQL или любой другой:

SELECT tstring2list_record(t.col1, t.col2) BULK COLLECT
  INTO l_table
  FROM some_table t