Как сослаться на PARTITION в операторе DML через переменную ? Способы

Задача: Указать название partition в виде переменной в операторе DML. Например

update INVENT_ITEMS partititon (p_Partition_Name) set QUANTITY = 0;

Я столкнулся с этим, когда мне в хранимую процедуру для апдейта партиционированной таблицы имя раздела поступало в виде строки через Advanced Queing (AQ).

Что показал анализ: штатными средствами указать название partition в виде переменной невозможно. Дискуссия была на сайте Тома Кайта, ссылка внизу.

Решение:

1. Использовать DATAOBJ_TO_PARTITION (функция из Data Cartridge), на мой взгляд -- предпочтительный вариант. Я стараюсь использовать его. Я проверил этот вариант для всех пяти типов партиционирования HEAP-таблиц -- работает везде.
2. Определить границы partition по ALL_TAB_PARTITIONS.HIGH_VALUE и построить фразу WHERE (тоже вариант)
3. Использовать динамический SQL (но он обычно хуже оптимизируется)
4. Использовать CASE (если количество partition ограничено и невелико)

Убедимся, что указание названия partition в виде переменной не работает

Создадим таблицу, строки можно не добавлять

SYS@ORTE> create table T (N number) partition by hash (N) (partition P1, partition P2, partition P3, partition P4);
Table created.

Обычный update для одной partition работает:
SYS@ORTE> update T partition (P1) set N = N + 1;
0 rows updated.

Укажем название строки в виде литерала -- не работает
SYS@ORTE> update T partition ('P1') set N = N + 1;

update T partition ('P1') set N = N + 1

                   *

ERROR at line 1:

ORA-00971: missing SET keyword

Укажем название строки в виде переменной -- не работает
SYS@ORTE> var PARTNAME varchar2(8);

SYS@ORTE> exec :PARTNAME := 'P1';

PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00

SYS@ORTE> print :PARTNAME

PARTNAME

--------------------------------

P1

SYS@ORTE> update T partition (:PARTNAME) set N = N + 1;
update T partition (:PARTNAME) set N = N + 1

                    *

ERROR at line 1:

ORA-14108: illegal partition-extended table name syntax

Elapsed: 00:00:00.02
В коде PL/SQL ошибка будет точно такая-же.

Решение с помощью DATAOBJ_TO_PARTITION (я выполняю в SQLPLUS, но в PL/SQL тоже будет работать).

Определим ID сегмента данных partition P1;
SYS@ORTE> select DATA_OBJECT_ID from ALL_OBJECTS where OWNER = 'SYS' and OBJECT_NAME = 'T' and SUBOBJECT_NAME = 'P1';
DATA_OBJECT_ID

--------------

         77140

Elapsed: 00:00:00.09

Создадим связываемую переменную и сошлёмся на partition через ID сегмента.
SYS@ORTE> var PART_SEG_ID number;

SYS@ORTE> exec :PART_SEG_ID := 77140
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00

SYS@ORTE> update T partition (DATAOBJ_TO_PARTITION (T, :PART_SEG_ID)) set N = N + 1;
0 rows updated.
Elapsed: 00:00:00.02

Это работает. В PL/SQL будет работать тоже
Обратите внимание, что название таблицы не надо заключать в кавычки
Полная дискуссия -- здесь http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:953139476145