| 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: 8931
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;orclz> select editions_enabled from dba_users where username='SCOTT';
E
-
N
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.
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: 3273
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>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
