CODEWITHSUNDEEP
oracle-databaseplsqltypesalter
Raghvendra
Raghvendra

Reputation: 134

how to increase column length without making dependent types invalid in oracle

I need to increase a column length in oracle table. But there is type defined on same table inside a package specification. This package is being used in more than 1000 packages.

So when I alter column, all packages become invalid.

Is there a way, so I can increase column length without making dependent type invalid.

Thanks.

Upvotes: 1

Views: 1307

Answers (2)

psaraj12
psaraj12

Reputation: 5072

You can check online table redefinition in which case only affected column's dependent objects will become invalid. For example if the column you are altering is not dependant on the type in the same table declared in packages then those packages will remain valid.Even for normal alter command this condition applies but online redefinition allows using a interim table to support dml operations during the alterations

Anyway since it is a production system I suggest testing this feature in UAT and then work it out in production

Upvotes: 2

Littlefoot
Littlefoot

Reputation: 143103

You don't need to do anything; Oracle will recompile (or, at least try to do so) those packages and make them VALID (unless something else prevents that).

Here's a simple demonstration.

Test case first:

SQL> create table test (name varchar2(5));

Table created.

SQL> insert into test
  2    select 'Littl'  from dual union all
  3    select 'Foot'   from dual;

2 rows created.

SQL> create or replace package pkg_test as
  2    function f_test return test.name%type;
  3  end;
  4  /

Package created.

SQL> create or replace package body pkg_test as
  2    function f_test return test.name%type is
  3      retval test.name%type;
  4    begin
  5      select max(name) into retval from test;
  6      return retval;
  7    end;
  8  end;
  9  /

Package body created.

SQL>

Let's check package's status and see function's result:

SQL> select object_name, status From user_objects where object_name = 'PKG_TEST';

OBJECT_NAM STATUS
---------- -------
PKG_TEST   VALID
PKG_TEST   VALID

SQL> select pkg_test.f_test from dual;

F_TEST
--------------------------------------------------------------------------------
Littl

SQL>

Everything is OK.

Now, enlarge the column and see what happens:

SQL> alter table test modify name varchar2(10);

Table altered.

SQL> select object_name, status From user_objects where object_name = 'PKG_TEST';

OBJECT_NAM STATUS
---------- -------
PKG_TEST   INVALID
PKG_TEST   INVALID

SQL> select pkg_test.f_test from dual;

F_TEST
--------------------------------------------------------------------------------
Littl

SQL> select object_name, status From user_objects where object_name = 'PKG_TEST';

OBJECT_NAM STATUS
---------- -------
PKG_TEST   VALID
PKG_TEST   VALID

SQL>

See? It just works. Package was initially INVALID, was recompiled in the background and became VALID.

So, no worries. I'd just say congratulations you decided to declare everything so that it inherits column's datatype. If you hardcoded it, then you'd have a BIG problem in manually modifying all those variables, whatnot, to make everything work.

Upvotes: 2

Related Questions