Column not allowed here oracle ошибка

Обновлено: 14.01.2025

Для генерации уникальных значений ключа в Oracle используется объект - последовательность (sequence). Однако, на использование последовательности наложен ряд ограничений, например - запрет на использование в подзапросах. В данной заметке мы рассмотрим как обойти это ограничение и как произвести его оптимизацию.

Иногда, когда совсем этого не ждешь, появляется ошибка ORA-02287 - использование sequence там, где этого делать нельзя. Если посмотреть что скаано по этому повода на OraDoc, видим:

Restrictions on Sequence Values You cannot use CURRVAL and NEXTVAL in the following constructs:

A subquery in a DELETE , SELECT , or UPDATE statement

A SELECT statement with a GROUP BY clause or ORDER BY clause

A SELECT statement that is combined with another SELECT statement with the UNION , INTERSECT , or MINUS set operator

The DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement

Причем, самое неприятное из перечисленного - невозможность использования последовательности в подзапросах. Например:

INSERT ALL WHEN
MOD(id, 2 ) = 0 THEN INTO t1(id, VALUE)
VALUES
(id, VALUE)
WHEN MOD (id, 2 ) = 1 THEN INTO t2(id, VALUE)
VALUES
(id, VALUE)
SELECT sq$t_pk.NEXTVAL id, VALUE FROM TABLE (tbl_values);

где tbl_values - переменная типа коллекции, содержащей поле value. Т.е. в данном случае, пример иллюстрирует ситуацию, когда необходимо генерируемое уникальное значение ключа вставлять в одну таблицу и в другую. Условие разделение записи между двумя таблицами - в данном случае четное значение в одну таблицу, нечетное в другую. Решить данную проблему можно использовав функцию вместо последовательности, которая уже в свою очередь обращается к последовательности. Ora Doc это не запрещает. function get_sq_value return number
is
Result number ;
begin
select sq$t_pk.nextval into Result from dual;
return Result;
end ; WHEN MOD (id, 2 ) = 1 THEN INTO t2(id, VALUE)
VALUES
(id, VALUE)
SELECT get_sq_value id, VALUE FROM TABLE (tbl_values); Данный вариант хорош, когда записей в таблице источнике (будь то коллекция tbl_values или другая таблица) немного. Однако если их много, скажем больше 1000, то в данном случае внутренний запрос: будет вызывать переключение контекста, что приведет к повышенной нагрузке на сервер и дополнительными, совершенно не нужными затратами на выполнение. Можно размышлять следующим образом - что такое переключение контекста? Это когда PL/SQL движок останавливает свое выполнение и отдает команду SQL движку. Получается нужно избавиться от команды SELECT в самой функции. Совсем избавиться от нее, понятно, не получиться (надо же как то достать nextval), но можно свести к минимуму. Как? Выполним select. nextval один раз.

-- необходимые объекты
create table t1(id number primary key , value varchar2 ( 1000 ));
create table t2(id number primary key , value varchar2 ( 1000 ));
create sequence SQ$T_PK minvalue 1 maxvalue 999999999999999999999999999 start with 1 increment by 1 cache 100 order ;

-- спека пакета
create or replace package pkg_test_insert is

function get_sq_value return number ;

function get_sq_value(i_count in number ) return number ;

procedure insert_batch(tbl_values in tbl_t_type);

procedure insert_batch_cache_sq(tbl_values in tbl_t_type);

end pkg_test_insert;
/
-- тело пакета
create or replace package body pkg_test_insert is
-- тип для хранения идентификаторов.

type sq_values is table of number index by binary_integer ;

-- коллекция заданного типа.
g_sq_values sq_values;

g_pointer number := 1 ;

function get_sq_value(i_count in number ) return number
is
begin
if g_pointer = 1 then

select sq$t_pk. nextval

bulk collect into g_sq_values
from ( select rownum as l_date
from ( select 'x' as con from dual ) q
connect by q.con = q.con
) ql
where rownum <= i_count;
g_pointer := g_pointer + 1 ;
return g_sq_values(g_pointer - 1 )

else
g_pointer := g_pointer + 1 ;
return g_sq_values(g_pointer - 1 );
end if ;

-- получение идентификатора (контекст)
function get_sq_value return number
is
Result number ;
begin
select sq$t_pk. nextval into Result from dual ;
return Result ;
end ;

-- вставка записей (переключение контекста)
procedure insert_batch(tbl_values in tbl_t_type)
is
begin
INSERT ALL WHEN
MOD (id, 2 ) = 0 THEN INTO t1(id, VALUE )
VALUES
(id, VALUE )
WHEN MOD (id, 2 ) = 1 THEN INTO t2(id, VALUE )
VALUES
(id, VALUE )
SELECT get_sq_value id, VALUE FROM TABLE (tbl_values);
end ;

-- вставка записей (переключение контекста - один раз)
procedure insert_batch_cache_sq(tbl_values in tbl_t_type)
is
l_number number ;
begin
g_pointer := 1 ;
l_number := tbl_values. count ;
INSERT ALL WHEN
MOD (id, 2 ) = 0 THEN INTO t1(id, VALUE )
VALUES
(id, VALUE )
WHEN MOD (id, 2 ) = 1 THEN INTO t2(id, VALUE )
VALUES
(id, VALUE )
SELECT get_sq_value(l_number) id, VALUE FROM TABLE (tbl_values);
end ;

end pkg_test_insert;
/

Вставка записей с использованием метода с большим переключением контекста ( insert_batch). На каждую запись будет вызывать внутрений select для получения nextval.
Вставка записей с использованием метода с одним переключением контекста ( insert_batch_cache_sq).

pkg_test_insert.insert_batch(tbl_values => tbl_values);

pkg_test_insert.insert_batch_cache_sq(tbl_values => tbl_values);

runStats_pkg.rs_stop(p_difference_threshold => 0 ,p_output => 'WORKLOAD' );

commit ;
end ;

Для 1000 записей в tbl_values:

Run1 ran in 11 hsecs
Run2 ran in 4 hsecs
run 1 ran in 275% of the time

Name Run1 Run2 Diff
STAT. Elapsed Time 16 10 -6
STAT. CPU used by this session 18 9 -9
STAT. recursive cpu usage 18 9 -9
STAT. session logical reads 138 88 -50
STAT. redo size 16,172 9,652 -6,520
STAT. session pga memory 0 65,536 65,536

Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
19,565 9,805 -9,760 199.54%

Для 50000 записей в tbl_values:

Run1 ran in 580 hsecs
Run2 ran in 187 hsecs
run 1 ran in 310.16% of the time

Name Run1 Run2 Diff
STAT. CPU used by this session 584 194 -390
STAT. recursive cpu usage 584 194 -390
STAT. Elapsed Time 586 193 -393
STAT. session logical reads 5,063 2,550 -2,513
STAT. redo size 654,652 328,812 -325,840
STAT. session uga memory 0 3,597,440 3,597,440
STAT. session pga memory 0 3,670,016 3,670,016

Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
1,266,940 482,741 -784,199 262.45% Из результатов видно, что второй вариант легче (используется меньше процессорных ресурсов, выполняется быстрее в 2-4 раза), да и защелок аж в 2-3 раза меньше первого варианта. Однако метрики использования памяти во втором варианте больше, причем они повышаются с увеличением числа входных строк. Что вполне объяснимо, с учетом того, что используем коллекцию, которая храниться в uga memory, которая в свою очередь (dedicated server) храниться в pga memory. Сделать так, чтобы рост числа памяти был закономерен и всегда контролировался - это уже дело техники. Можно например в функции генерации идентификаторов: function get_sq_value(i_count in number ) return number генерировать идентификаторы фиксированной пачкой, тогда рост использования памяти uga будет всегда фиксирован и равен числу идентификаторов, которые генерируются за один раз. В примере, в качестве источника строк используется коллекция (например, внешнее приложение передает в БД Oracle набор строк, через коллекцию). Когда же источником является другая таблица, то выгоднее использовать аналитику для подсчета количества обрабатываемых строк, чем выполнять count(1) по таблице - будет один проход по таблице. Создадим другую функцию, где с помощю аналитики выполним подсчет строк (при этом, будем так же выполнять select из коллекции - для нас не важно из чего на данном этапе выполнять выборку):

procedure insert_batch_cache_sq_over(tbl_values in tbl_t_type)
is
begin

INSERT ALL WHEN
MOD (id, 2 ) = 0 THEN INTO t1(id, VALUE )
VALUES
(id, VALUE )
WHEN MOD (id, 2 ) = 1 THEN INTO t2(id, VALUE )
VALUES
(id, VALUE )
SELECT get_sq_value(cnt) id, VALUE
FROM
( select count ( 1 ) over() cnt, value from TABLE (tbl_values) );
end ;

А теперь, произведем сравнение работы новой функции и функции insert_batch_cache_sq примерно таким образом:

\Untitled.html
declare
tbl_values tbl_t_type := tbl_t_type();
l_cnt_values number := 50000 ;
begin

for xxx in 1. .l_cnt_values loop
tbl_values(xxx) := t_type( to_char (xxx));
end loop ;

runStats_pkg.rs_start;
pkg_test_insert.insert_batch_cache_sq(tbl_values => tbl_values);

pkg_test_insert.insert_batch_cache_sq_over(tbl_values => tbl_values);

runStats_pkg.rs_stop(p_difference_threshold => 0 ,p_output => 'WORKLOAD' );

commit ;
end ;

Мне нужно изменить предыдущий столбец PK Id, определенный как VARCHAR(36) NOT NULL , на инкрементное целочисленное значение. Я пытаюсь написать сценарий для этого, но когда я запускаю оператор alter table, он терпит неудачу с ошибкой в заголовке.

Таблица ранее определялась как:

Сценарий, который я запускаю, таков:

Когда я пытаюсь создать триггер для обновления инкремента, он терпит неудачу с SQL Error [4098] [42000]: ORA-04098: trigger 'TRG_SEQ_JOURNAL_MSG' is invalid and failed re-validation , когда я пытаюсь вставить новый Кортеж:

1 ответ

Я пытаюсь сделать вставку из файла в базу данных oracle. я в основном следовал учебнику, который нашел здесь . Я сделал некоторые незначительные изменения, чтобы сделать его только вставкой, но Порт Insert sent теперь забирает файл, но выдает следующую ошибку. Что здесь может быть не так или есть.

Я делаю простой Oracle INSERT и продолжаю получать эту ошибку: [Err] ORA-00984: column not allowed here INSERT INTO MY.LOGFILE (id,severity,category,logdate,appendername,message,extrainfo) VALUES ( dee205e29ec34, FATAL, facade.uploader.model, 2013-06-11 17:16:31, LOGDB, NULL, NULL )

Ммм . не так, а вот так:

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

Если бы вы были на 12c, вы могли бы использовать столбец идентификации .

[EDIT: прочитав ваш комментарий и увидев ваше издание]

Это все еще работает OK - я буквально скопировал/вставил ваш код и получил это:

Как видите, все вроде бы в порядке. Попробуйте выполнить следующее: перекомпилируйте триггер и покажите ошибки (если таковые имеются; если да, пожалуйста, опубликуйте их здесь):

Строки и символьные функции в Оracle

Строковые литералы в Оracle - это последовательность из нуля, одного или более символов, заключенных в одинарные кавычки.
У новичков часто встречается ошибка, когда строки заключаются в двойные кавычки или наоборот, названия объектов в одинарные.

insert into 'MY_DOC' ('DOC_ID', 'DOC_NAME') values ("1", "Документ 1");

Error at Command Line:1 Column:12
SQL Error: ORA-00903: неверно имя таблицы
00903. 00000 - "invalid table name"

Error at Command Line:1 Column:57
SQL Error: ORA-00984: употребление столбца здесь недопустимо
00984. 00000 - "column not allowed here"

Строка нулевой длины, т.е. '' это не то же самое что NULL. Если в строке NULL, это говорит о том, что значение не известно или
не установлено. Если в строке '', это значит что строка пустая, но ее значение известно.

Конкатенация (соеднинение) строк выполняется оператором || (две вертикальные черты) или функцией CONCAT

select 'Employee: ' || initcap(ename), concat('Dept: ',deptno)
from emp;

Oracle предлагает обширный набор функций для манипулирования строковыми данными:
CHR (N) - Возвращает символ ASCII кода для десятичного кода N
ASCII (S) - Возвращает десятичный ASCII код первого символа строки
INSTR (S2.S1.pos[,N] - Возвращает позицию строки S1 в строке S2 большую или равную pos.N - число вхождений
LENGHT (S) - Возвращает длину строки
LOWER (S) - Заменяет все символы строки на прописные символы
INITCAP (S) - Устанавливает первый символ каждого слова в строке на заглавный, а остальные символы каждого слова - на прописные
SUBSTR (S,pos,[,len]) - Выделяет в строке S подстроку длиной len, начиная с позиции pos
UPPER (S) - Преобразует прописные букцвы в строке на заглавные буквы
LPAD (S,N[,A]) - Возвращает строку S, дополненную слева симолами A до числа символов N. Символ - наполнитель по умолчанию - пробел
RPAD (S,N[,A]) - Возвращает строку S, дополненную справа симолами A до числа символов N. Символ - наполнитель по умолчанию - пробел
LTRIM (S,[S1]) - Возвращает усеченную слева строку S. Символы удаляются до тех пор, пока удаляемый символ входит в строку - шаблон S1 (по умолчанию - пробел)
RTRIM (S,[S1]) - Возвращает усеченную справа строку S. Символы удаляются до тех пор, пока удаляемый символ входит в строку - шаблон S1 (по умолчанию - пробел
TRANSLATE (S,S1,S2) - Возвращает строку S, в которой все вхождения строки S1 замещены строкой S2. Если S1 <> S2, то символы, которым нет соответствия,
исключаются из результирующей строки
REPLACE (S,S1,[,S2]) - Возвращает строку S, для которой все вхождения строки S1 замещены на подстроку S2. Если S2 не указано, то все вхождения подстроки S1
удаляются из результирующей строки
NVL (X,Y) - Если Х есть NULL, то возвращает в Y либо строку, либо число, либо дату в зависимости от исходного типа Y
SOUNDEX (S) - Возвращает фонетическое представление строки
REGEX_INSTR (S,S1,[N1],[N2],[N3],[S2]) - Возвращает позицию в строке S текста, удовлетворяющего регулярному выражению S1
REGEX_REPLACE (S,S1,S2,[N1],[N2],[S3]) - Возвращает строку S, в которой все вхождения строк, удовлетворяющих регулярному выражению S1, замещены на S2
REGEX_SUBSTR (S,S1,[N1],[N2],[S2]) - Возвращает подстроку из строки S, удовлетворяющего регулярному выражению S1

Использование объектно-реляционных средств 1423

ности объектной таблицы доступны: из нее можно выбрать ссылки на объекты, вложенная таблица создана и т.д. Преимущество такой реализации в том, что мы явно указываем, как соединять таблицы ЕМР и DEPT, используя естественное отношение - главный/подчиненный .

Итак, создано объектно-реляционное представление, позволяющее запрашивать данные. Но изменять его данные пока нельзя:

scott@TKYTE816> update TABLE (select p.emps

2 from dept or p

3 where deptno = 2 0)

4 set ename = lower(ename)

set e*name = lower(ename) RCR at line 4:

ORA-25015: cannot perform on this nested table view column

2 l emps emp tab type;

4 select p.emps into l emps

5 from dept or p

6 where deptno = 10;

8 for i in 1 .. l emps.count

10 l emps(i).ename := lower(l emps(i).ename);

13 update dept or

14 set emps = l emps

15 where deptno = 10;

ERROR at line 1:

ORA-01733: virtual column not allowed here ORA-06512: at line 13

Необходимо заставить представление обновляться . У нас реализовано сложное сопоставление реляционных данных объектно-реляционным (на самом деле оно может иметь любую степень сложности). Так как же заставить представление обновляться ? Сервер Oracle обеспечивает для этого соответствующий механизм - триггеры INSTEAD 0F. Можно реализовать алгоритм, который должен выполняться сервером Oracle вместо (INSTEAD OF) стандартного при изменении содержимого представления. Чтобы продемонстрировать это, давайте обеспечим возможность изменения показанного ранее представления.

Сервер Oracle позволяет создать триггер INSTEAD OF по представлению DEPT OR и по любому типу вложенной таблицы, входящей в это представление. Создав триггер

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

scott@TKYTE816> create or replace trigger EMPS IO UPDATE

2 instead of UPDATE on nested table emps of dept or

4 if (:new.empno = :old.empno)

7 set ename = :new.ename, job = :new.job, mgr = :ne.mgr,

9 where empno = :old.empno;

11 raise application error(-20001,Значение столбца empno -> изменять нельзя);

Как видите, триггер будет срабатывать при изменении (INSTEAD OF UPDATE) столбца типа вложенной таблицы, EMPS, представления DEPT OR. Он будет срабат1вать для каждой изменяемой строки вложенной таблицы и имеет доступ к значениям до и после изменения (:OLD и :NEW), как и обычный триггер. В данном случае понятно, что надо делать, - изменить строку таблицы ЕМР с соответствующим значением EMPNO, задав ее столбцам новые значения. В этом триггере я принудительно запрещаю изменять первичный ключ (мы используем объектно-реляционные средства, но это не значит, что можно нарушать основные принципы проектирования реляционных баз данных).

Теперь, выполним следующие операторы:

scott@TKYTE816> update TABLE (select p.emps

2 from dept or p

3 where deptno = 20)

4 set ename = lower(ename)

scott@TKYTE816> select ename from emp where deptno = 20; ENAME

smith jones scott adams ford

scott@TKYTE816> select ename

2 from TABLE(select p.emps

3 from dept or p

4 where deptno = 20);

Использование объено-реляционн1х средств 142 5

smith jones scott adams ford

Как видите, изменение вложенной таблицы успешно преобразовано в изменение реляционной таблицы, как и ожидалось. Так же легко написать триггеры на события INSERT и DELETE, поскольку UPDATE - самый сложный случай. Так что на этом и остановимся.

Если сейчас выполнить следующее изменение:

2 l emps emp tab type;

4 select p.emps into l emps

5 from dept or p

6 where deptno = 10; 7

8 for i in 1 .. l emps.count

10 l emps(i).ename := lower(l emps(i).ename);

13 update dept or

14 set emps = l emps

15 where deptno = 10;

ERROR at line 1:

ORA-01732: data manipulation operation not legal on this view

ORA-06512: at line 13

1. Удалять из таблицы ЕМР все записи, значение EMPNO которых б1ло в наборе :OLD, но отсутствует в наборе :NEW. Для этого прекрасно подходит реляционный оператор MINUS.

Читайте также: