ORA-38824: A CREATE OR REPLACE command may not change the EDITIONABLE property of an existing object [message #688107] |
Wed, 23 August 2023 06:06 |
John Watson
Messages: 8938 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
This procedure is created, by default, as editionable:orclz> create procedure p1 as begin
2 null;
3 end;
4 /
Procedure created.
orclz> select dbms_metadata.get_ddl('PROCEDURE','P1') from dual;
DBMS_METADATA.GET_DDL('PROCEDURE','P1')
--------------------------------------------------------------------------------
CREATE OR REPLACE EDITIONABLE PROCEDURE "SCOTT"."P1" as begin
null;
end; Even though my schema is not enabled for editions:orclz> select editions_enabled from dba_users where username='SCOTT';
E
-
N
From the 19c Database Development guide:
Quote:27.2.1.5.2 Replacing or Altering EDITIONABLE and NONEDITIONABLE Objects
When you replace or alter an existing object (with the CREATE OR REPLACE or ALTER statement):
If the schema is not enabled for editions, then you can change the property of the object from EDITIONABLE to NONEDITIONABLE, or the reverse.
If the schema is enabled for editions for the type of the object being replaced or altered, then you cannot change the property of the object from EDITIONABLE to NONEDITIONABLE, or the reverse.
Altering an editioned object is a live operation with respect to the editions in which the altered object is invisible. So why is this throwing an error:orclz> CREATE OR REPLACE nonEDITIONABLE PROCEDURE "SCOTT"."P1" as begin
2 null;
3 end;
4 /
CREATE OR REPLACE nonEDITIONABLE PROCEDURE "SCOTT"."P1" as begin
*
ERROR at line 1:
ORA-38824: A CREATE OR REPLACE command may not change the EDITIONABLE property of an existing object. According to the doc, shouldn't it work? Or am I missing something? Could it be a documentation bug? Or an actual bug?
Looking in MOS, there are a few mentions of problems on these lines when upgrading. The environment where we are hitting this issue is in a sense an upgrade (Data Pump export from 12.1.0.2 imported into 19.20) but the articles say the problems are fixed by release 19.whatever
This is causing issues with running various scripts to patch up the applications.
I know very little about using editions. Thank you for any insight.
|
|
|
|
|
Re: ORA-38824: A CREATE OR REPLACE command may not change the EDITIONABLE property of an existing object [message #688111 is a reply to message #688109] |
Wed, 23 August 2023 13:07 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Not a bug, just misleading. It should clarify that EDITIONABLE/NONEDITIONABLE must match if procedure already exists:
SQL> create or replace noneditionable procedure non_edition_proc is begin null; end;
2 /
Procedure created.
SQL> create or replace noneditionable procedure non_edition_proc is begin null; end;
2 /
Procedure created.
SQL> create or replace editionable procedure edition_proc is begin null; end;
2 /
Procedure created.
SQL> create or replace editionable procedure edition_proc is begin null; end;
2 /
Procedure created.
SQL>
SY.
|
|
|