Ошибка выбора объектов коллекции

На самом деле у EXECUTE IMMEDIATE есть using_clause , который позволяет передавать переменные привязки

Чтобы адаптировать пример @Bob, простая настройка

strQuery := 'SELECT * FROM DATA_TABLE WHERE ' || strField_name || '= :x' ;

EXECUTE IMMEDIATE strQuery BULK COLLECT INTO colVals USING nField_val;

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

 SELECT * FROM DATA_TABLE WHERE FIELD1= :x

Конечно, у этого подхода есть ограничение, количество переменных связывания является статическим , определенным как список в операторе EXECUT EIMMEDIATE, например USING a,b,c,d,e

На ум приходят две проблемы . Первый — это необязательные параметры , т. Е. Некоторые запросы выполняют поиск по name, а birth_date некоторые только по name

Вы можете найти множество решений с OR или NVL, например здесь или здесь, но превосходное решение, популяризированное Томом Кайтом, использовало трюк быстрого доступа 1=1 OR:

Если используется переменная связывания, вы создаете ее в предложении WHERE

 birth_date = :birth_date 

Если он не используется , вы избавитесь от него, создав следующий предикат

(1=1 or :birth_date  is NULL)

Эффект таков: 1) количество связываемых переменных остается фиксированным (даже если: Birth_date не используется) и 2) оценка 1=1 как true пропустит остальную часть предиката, т. Е. переменная привязки не действует.

Вторая проблема связана со списками IN , которые могут иметь динамическое количество членов; от 1 до 1000 на Oracle.

эталонный тест — это решение Hibernate, которое подсчитывает длину списка IN и генерирует соответствующий динамический SQL, например col1 in (?,?,?,?) для четырех мебмеров в списке IN. В итоге вы получаете до 1000 жестко проанализированных SQL-запросов (по одному на каждую длину), но в любом случае это логарифмическое улучшение по сравнению с наихудшим случаем вставки списка в динамический SQL .

Из ограничения, описанного выше, вы уже знаете, что это невозможно с помощью EXECUTE IMMEDIATE (и вам нужно будет узнать что-то новое;), например DBMS_SQL)

Вы можете попробовать уловку и ограничить список IN, скажем, 10 участниками и заполнить его NULL s.

Т.е. чтобы передать 4 члена, вы свяжете следующие переменные:

 col1 IN (1,2,3,4,null,null,null,null,null,null)

Но обратите внимание, что я не знаю, будет ли производительность в длинном списке IN (для короткого списка IN это будет работать нормально). Кроме того, этот подход окончательно не работает для col1 NOT IN, которые ничего не возвращают, если в списке IN есть NULL.

Здесь у вас есть несколько вариантов. Вы можете открыть свой запрос как явный курсор, а затем FETCH … BULK COLLECT INTO соответствующую коллекцию; вы можете использовать EXECUTE IMMEDIATE … BULK COLLECT INTO; или, как вы говорите, вы не хотите слышать, вы можете использовать DBMS_SQL.

Чтобы использовать EXECUTE IMMEDIATE … BULK COLLECT, вы должны использовать что-то вроде

CREATE TABLE DATA_TABLE(FIELD1         NUMBER,
                        FIELD2         VARCHAR2(100));

INSERT INTO DATA_TABLE (FIELD1, FIELD2)
  SELECT 1, 'ONE' FROM DUAL UNION ALL
  SELECT 1, 'TWO' FROM DUAL UNION ALL
  SELECT 2, 'THREE' FROM DUAL UNION ALL
  SELECT 2, 'FOUR' FROM DUAL UNION ALL
  SELECT 3, 'LAST' FROM DUAL;

DECLARE
  TYPE typeCol IS TABLE OF DATA_TABLE%ROWTYPE;
  colVals        typeCol;
  strField_name  VARCHAR2(30) := 'FIELD1';
  nField_val     NUMBER := 2;

  strQuery       VARCHAR2(4000);
BEGIN
  strQuery := 'SELECT * FROM DATA_TABLE WHERE ' || strField_name || '=' || nField_val;

  DBMS_OUTPUT.PUT_LINE(strQuery);

  EXECUTE IMMEDIATE strQuery BULK COLLECT INTO colVals;

  FOR i IN colVals.FIRST..colVals.LAST LOOP
    DBMS_OUTPUT.PUT_LINE(i || ': FIELD1=' || colVals(i).FIELD1 || '  FIELD2=' || colVals(i).FIELD2);
  END LOOP;
END;
/

dbfiddle здесь

документы здесь (с 10.1 — лучше, чем более поздние версии IMO)

Просто используйте EXTEND, чтобы добавить новую строку в коллекцию!

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

SUB_TYPE_LST.extend;
                                one_row.SVAR1 := 'VALUE1';
                                one_row.SVAR2 := sysdate;
                                one_row.SVAR3 := 'value2';
                                SUB_TYPE_LST(1) := one_row; -- 1 - number of row in the table - you can put a variable which will be incremented inside a loop