Error 38824 : A CREATE OR REPLACE Command May not Change the EDITIONABLE Property of an Existing Object

user_7258144 0
Viewed 19841

we are implementing a unit test system of our database production object like package, procedure, trigger & co. To do so, we have a container which create a database from https://github.com/oracle/docker-images/tree/master/OracleDatabase . Then we execute scripts which are creating schema, users, grant some privileges, create good tablespaces... Then we use Data Pump Oracle to expdp and impdp the schema. These steps are successful.

The problem appear when we try to exec test on our Procedures and Packages. They are not find in schema due to compilation error "ORA-38824 : A CREATE OR REPLACE Command May not Change the EDITIONABLE Property of an Existing Object".

But if we drop the procedure tested, then recreate it, the error does not appear anymore. Things is we do not want to manually drop & re create the procedure.

the only difference between our environment & the container database is that when we create a procedure/package from scratch, Editionable parameter in object detail are at 'N' in container when it is at 'Y' in our env.

It seems that Editionable parameter for objects has to be set at Database level. But the question is where can it be set ?

We tried to 'ALTER USER X ENABLE EDITIONS;' without success since it is causing 'ORA-38813: editions not supported for schema X'.

Is Impdp & Expdp causing trouble ?

We tried using 'source_edition' & 'target_edition' for our expdp impdp process. We are clearly missing something or doing something wrong but we are not able to find what.

I have searched on internet, but not too much people have been facing this issue..

Could you please provide us help?

Thanks in advance. Vincent.

oracle
2 Answers

I checked the Oracle Support website (should have done this earlier) and this error is related to a bug in 12c. For example in one such document (Meta Link Document) it is said as follows. So please refer this issue to Oracle by raising an SR and most likely they will come back with a suggestion for an Oracle version upgrade:

Please apply the merge patch Bug 27314007 : MERGE REQUEST ON TOP OF 12.2.0.1.0 FOR BUGS 25557064 26645487

Using these steps for the upgrade:

  1. Stop the current DBUA session.
  2. Download and apply the patch to 12.2 Oracle home.
  3. Restore the 12.1 database.
  4. Re-run the upgrade from 12.1 to 12.2.

Thanks,

user_70209150

I went through this recently, and analyzing it we would not use the EDITIONABLE functionality, so I ran the command below, leaving the NONEDITIONABLE objects, because that way the objects are replaced. And in the DBA_OBJECTS table, the EDITIONABLE column is N, so when it is time to do the imp, there is no problem.

DECLARE
  TYPE t_cursor IS REF CURSOR;
  TYPE t_string_array IS TABLE OF VARCHAR2(1000) INDEX BY BINARY_INTEGER;
  vcursor      t_cursor;
  varraystring t_string_array;
  vstring      VARCHAR2(500);
  i            BINARY_INTEGER;
BEGIN
  OPEN vcursor FOR
    select 'ALTER ' || do.object_type || ' ' || do.owner || '.' || do.object_name || ' NONEDITIONABLE'
      from DBA_OBJECTS do
     where do.editionable = 'Y'
       and do.owner NOT IN('QS_CB','DIP','PERFSTAT','QS_ADM','PM','SH','HR','OE','ODM_MTR','WKPROXY','ANONYMOUS','OWNER','SYS','SYSTEM','SCOTT','SYSMAN','XDB','DBSNMP','EXFSYS','OLAPSYS','MDSYS','WMSYS','WKSYS','DMSYS','ODM','EXFSYS','CTXSYS','LBACSYS','ORDPLUGINS','SQLTXPLAIN','OUTLN','TSMSYS','XS$NULL','TOAD','STREAM','SPATIAL_CSW_ADMIN','SPATIAL_WFS_ADMIN','SI_INFORMTN_SCHEMA','QS','QS_CBADM','QS_CS','QS_ES','QS_OS','QS_WS','PA_AWR_USER','OWBSYS_AUDIT','OWBSYS','ORDSYS','ORDDATA','ORACLE_OCM','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','MGMT_VIEW','MDDATA','FLOWS_FILES','FLASHBACK','AWRUSER','APPQOSSYS','APEX_PUBLIC_USER','DVSYS')
       and do.object_type IN ('FUNCTION','LIBRARY','PACKAGE BODY','PACKAGE','PROCEDURE','TRIGGER','TYPE','TYPE BODY','SYNONYM','VIEW');
  LOOP
    FETCH vcursor BULK COLLECT
      INTO varraystring;
    EXIT WHEN varraystring.count = 0;
    FOR i IN varraystring.first .. varraystring.last LOOP
      EXECUTE IMMEDIATE varraystring(i);
    END LOOP;
    EXIT WHEN vcursor%NOTFOUND;
  END LOOP;
  CLOSE vcursor;
END;
user_37221110
Related