Skip to Main Content
  • Questions
  • Invalids are getting created in ACTIVE edition when we increase column length

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: January 06, 2025 - 2:33 pm UTC

Last updated: January 08, 2025 - 5:05 am UTC

Version: 19C

Viewed 100+ times

You Asked

Hi Tom & Team,

we have ORACLE Database 19C and it is editionable.
ALPHA/BETA/ORA$BASE

we have created our tables with XXX_E (non editionable) and created views(editionable) on top of it.

Active edition is ALPHA.

in BETA(Inactive edition), when we increase column length for one of table, creating invalids in ALPHA edition.
Can you advise how to find why are those invalid objects are getting created?

no errors are shown in dba_errors.

when we recompile, all invalids are getting cleared.

and Connor said...

Changing *existing* table structure will likely cause this, eg


SQL> conn demo/demo
Connected.
SQL> select tname from tab;

TNAME
--------------------------------------------------------------------------------------------------------------------------------
BONUS
DEPT
EMP
SALGRADE

SQL> desc emp
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- ------------------------------------------------
 EMPNO                                                                   NOT NULL NUMBER(4)
 ENAME                                                                            VARCHAR2(10)
 JOB                                                                              VARCHAR2(9)
 MGR                                                                              NUMBER(4)
 HIREDATE                                                                         DATE
 SAL                                                                              NUMBER(7,2)
 COMM                                                                             NUMBER(7,2)
 DEPTNO                                                                           NUMBER(2)

SQL> set lines 60
SQL> alter user demo enable editions;

User altered.

SQL>
SQL> rename EMP to "_EMP";

Table renamed.

SQL>
SQL> create editioning view EMP as select * from "_EMP";

View created.

SQL> create or replace
  2  procedure my_proc is
  3    l_row emp%rowtype;
  4  begin
  5    select *
  6    into   l_row
  7    from   emp
  8    where  rownum = 1;
  9  end;
 10  /

Procedure created.

SQL>
SQL> create edition v2;

Edition created.

SQL> alter session set edition = v2;

Session altered.

SQL>
SQL> alter table "_EMP" modify ename varchar2(20);

Table altered.

SQL>
SQL> select status
  2  from   user_objects
  3  where  object_name = 'MY_PROC';

STATUS
-------
INVALID

SQL>
SQL> alter session set edition = ora$base;

Session altered.

SQL>
SQL> select status
  2  from   user_objects
  3  where  object_name = 'MY_PROC';

STATUS
-------
INVALID

SQL>
SQL>


This might not be an issue for some customers - dynamic recompilation can be ok during quiet times in your application. If you can't allow impact on your base edition at all, then you'd need a new column. Starting again from scratch, I could do this


SQL> conn demo/demo
Connected.
SQL> select tname from tab;

TNAME
--------------------------------------------------------------------------------------------------------------------------------
BONUS
DEPT
EMP
SALGRADE

SQL> desc emp
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- ------------------------------------------------
 EMPNO                                                                   NOT NULL NUMBER(4)
 ENAME                                                                            VARCHAR2(10)
 JOB                                                                              VARCHAR2(9)
 MGR                                                                              NUMBER(4)
 HIREDATE                                                                         DATE
 SAL                                                                              NUMBER(7,2)
 COMM                                                                             NUMBER(7,2)
 DEPTNO                                                                           NUMBER(2)

SQL> set lines 60
SQL> alter user demo enable editions;

User altered.

SQL>
SQL> rename EMP to "_EMP";

Table renamed.

SQL>
SQL> create editioning view EMP as select * from "_EMP";

View created.

SQL> create or replace
  2  procedure my_proc is
  3    l_row emp%rowtype;
  4  begin
  5    select *
  6    into   l_row
  7    from   emp
  8    where  rownum = 1;
  9  end;
 10  /

Procedure created.

SQL>
SQL> create edition v2;

Edition created.

SQL> alter session set edition = v2;

Session altered.

SQL>
SQL> alter table "_EMP" add new_ename varchar2(20);

Table altered.

SQL>
SQL> select status
  2  from   user_objects
  3  where  object_name = 'MY_PROC';

STATUS
-------
VALID

SQL>
SQL> create editioning view EMP as
  2  select  empno, new_ename, job  , mgr  , hiredate, sal     , comm    , deptno
  3  from "_EMP";
create editioning view EMP as
                       *
ERROR at line 1:
ORA-00955: name is already used by an existing object


SQL>
SQL> update "_EMP" set new_ename = ename;

14 rows updated.

SQL>
SQL> create or replace
  2  procedure my_proc is
  3    l_row emp%rowtype;
  4  begin
  5    select *
  6    into   l_row
  7    from   emp
  8    where  rownum = 1;
  9  end;
 10  /

Procedure created.

SQL>
SQL> select status
  2  from   user_objects
  3  where  object_name = 'MY_PROC';

STATUS
-------
VALID

SQL>
SQL> alter session set edition = ora$base;

Session altered.

SQL>
SQL> select status
  2  from   user_objects
  3  where  object_name = 'MY_PROC';

STATUS
-------
VALID

SQL>
SQL>


with the appropriate triggers etc to keep things in sync until you are ready to retire the old version

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library