Разбирал сегодня с коллегами один случай возникновения ORA-4068, упрощенно который выглядит так:
1. Сессия1: создание пакета, где в body используется constant.
Код пакета:
| + |
create or replace package pkg
is
procedure p;
end;
/
create or replace package body pkg
is
c constant &1. := &2.;
procedure p
is
begin
null;
end;
end;
/
|
Где &1 пусть скалярный тип, а &2 литерал.
2. Сессия2: вызов пакета
3. Сессия1: компиляция тела
4. Сессия2: вызов пакета
Что есть: если тип указан как NUMBER, то ошибок на шаге 4 не возникает.
Если же это строка (допустим, CHAR(1)), то на шаге 4 мы сталкиваемся с ORA-4068: на версиях 12.1.0.2, 11.2.0.4.
Версия 9.2.0.8 приятно удивляет тем, в ней ошибок не возникает.
Как обойти понятно: pragma serially_reusable, отдельный пакет с константами, EBR.
В соответствии с документацией:
PL/SQL Feature for Oracle Database 11g Release 2 (11.2.0.2) |
|---|
Package Treated as Stateless if State is Constant for Life of Session
Before Release 11.2.0.2, if a session recompiled the body of a stateful package, and then another session that had instantiated that package referenced it, the latter session got the severely disruptive error ORA-04068 (”existing state of packages … has been discarded”). Therefore, "hot patching" packages was likely to disrupt their users.
As of Release 11.2.0.2, Oracle Database treats a package as stateless if its state is constant for the life of a session (or longer). This is the case for a package whose items are all compile-time constants. Therefore, "hot patching" packages (especially noneditioned packages) is much less likely to disrupt sessions that are using them.
For more information, see "Package State".
|
Package State |
|---|
As of Oracle Database 11g Release 2 (11.2.0.2), Oracle Database treats a package as stateless if its state is constant for the life of a session (or longer). This is the case for a package whose items are all compile-time constants.
A compile-time constant is a constant whose value the PL/SQL compiler can determine at compilation time. A constant whose initial value is a literal is always a compile-time constant. A constant whose initial value is not a literal, but which the optimizer reduces to a literal, is also a compile-time constant. Whether the PL/SQL optimizer can reduce a nonliteral expression to a literal depends on optimization level. Therefore, a package that is stateless when compiled at one optimization level might be stateful when compiled at a different optimization level. For information about the optimizer, see "PL/SQL Optimizer".
|
Я не вижу из документации, почему со строковыми типами (для примера: c char/varchar2) возникает ошибка при повторном вызове.
ВОПРОС: Может кто подсказать, что я упустил и где описано, что пакеты с CHAR/VARCHAR2 константами, которые инициализируются литералами, не stateless?
Сами тесты (везде устанавливается plsql_optimize_level=2).
12.1.0.2, char(1), ORA-4068
| + |
SESSION1> @compile_time_const char(1) "'x'"
SESSION1> set echo on
SESSION1>
SESSION1> alter session set plsql_optimize_level=2;
Session altered.
SESSION1>
SESSION1> create or replace package pkg
2 is
3 procedure p;
4 end;
5 /
Package created.
SESSION1>
SESSION1> create or replace package body pkg
2 is
3 c constant &1. := &2.;
4 procedure p
5 is
6 begin
7 null;
8 end;
9 end;
10 /
old 3: c constant &1. := &2.;
new 3: c constant char(1) := 'x';
Package body created.
SESSION1>
SESSION1> pause Switch to the other session. Press enter when you go back
Switch to the other session. Press enter when you go back
SESSION2> exec pkg.p
PL/SQL procedure successfully completed.
SESSION1>
SESSION1> alter package pkg compile body;
Package body altered.
SESSION2> exec pkg.p
BEGIN pkg.p; END;
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "TC.PKG" has been invalidated
ORA-04065: not executed, altered or dropped package body "TC.PKG"
ORA-06508: PL/SQL: could not find program unit being called: "TC.PKG"
ORA-06512: at line 1
|
12.1.0.2, number, без ошибок.
| + |
SESSION1> @compile_time_const int 0
SESSION1> set echo on
SESSION1>
SESSION1> alter session set plsql_optimize_level=2;
Session altered.
SESSION1>
SESSION1> create or replace package pkg
2 is
3 procedure p;
4 end;
5 /
Package created.
SESSION1>
SESSION1> create or replace package body pkg
2 is
3 c constant &1. := &2.;
4 procedure p
5 is
6 begin
7 null;
8 end;
9 end;
10 /
old 3: c constant &1. := &2.;
new 3: c constant int := 0;
Package body created.
SESSION1>
SESSION1> pause Switch to the other session. Press enter when you go back
Switch to the other session. Press enter when you go back
SESSION2> exec pkg.p
PL/SQL procedure successfully completed.
SESSION1>
SESSION1> alter package pkg compile body;
Package body altered.
SESSION2> exec pkg.p
PL/SQL procedure successfully completed.
|
9.2.0.8, char(1), без ошибок (2248 не в счет).
| + |
SESSION1> @compile_time_const char(1) "'x'"
SESSION1> set echo on
SESSION1>
SESSION1> alter session set plsql_optimize_level=2;
alter session set plsql_optimize_level=2
*
ERROR at line 1:
ORA-02248: invalid option for ALTER SESSION
SESSION1>
SESSION1> create or replace package pkg
2 is
3 procedure p;
4 end;
5 /
Package created.
SESSION1>
SESSION1> create or replace package body pkg
2 is
3 c constant &1. := &2.;
4 procedure p
5 is
6 begin
7 null;
8 end;
9 end;
10 /
old 3: c constant &1. := &2.;
new 3: c constant char(1) := 'x';
Package body created.
SESSION1>
SESSION1> pause Switch to the other session. Press enter when you go back
Switch to the other session. Press enter when you go back
SESSION2> exec pkg.p
PL/SQL procedure successfully completed.
SESSION1>
SESSION1> alter package pkg compile body;
Package body altered.
SESSION2> exec pkg.p
PL/SQL procedure successfully completed.
|
Ссылки по теме:
AskTom-а заменитель:
ORA-4068 and CONSTANT keyword…good and bad| Kevan Gelling |
|---|
As of 12.2.0.4, this only works for numbers and booleans.
|
Последнее похоже на правду, т.к. в 11.2.0.3 ошибок не возникает.
Bug 19017678 : ORA-4068 FROM PACKAGE WHICH HAS CONSTANT DECLAREDПохож, но в нем SQL используется, возможно, из-за этого закрыт как "Not a bug"