CODEWITHSUNDEEP
plsqltypesoracle11g
Michele M
Michele M

Reputation: 147

PL/SQL type doesn't change when change the table

I've created this package with a PL/SQL type inside in Oracle 11.2.0.3 database:

CREATE OR REPLACE PACKAGE "DBATCK"."PKG_TCK_MGMTCK" AS

...

TYPE TYPE_SHOW_USR
is table of DBATCK.VW_SHOW_USR%ROWTYPE;

...

END PKG_TCK_MGMTCK;
/

Oracle automatically creates a type named 

create or replace type SYS_PLSQL_342468_34_1 as object 
(
 "IDCODEAMB" NUMBER,
 "DESCRAMB" VARCHAR2(50 BYTE),
 "IDCODEAPP" VARCHAR2(7 BYTE),
 "DESCRAPP" VARCHAR2(50 BYTE)
);

but if I change the column DESCRAPP from varchar2(50) to varchar2(100) in the table SHOW_USR and in the view VW_TCK_USERS, when I recompile the package PKG_TCK_MGMTCK the PL/SQL type doesn't change.

Why?

--TABLE
create table DBATCK.SHOW_USR 
(
 "IDCODEAMB" NUMBER,
 "DESCRAMB" VARCHAR2(50 BYTE),
 "IDCODEAPP" VARCHAR2(7 BYTE),
 "DESCRAPP" VARCHAR2(50 BYTE)
); 

--VIEW
create view DBATCK.VW_SHOW_USR as select * from DBATCK.SHOW_USR;

Upvotes: 2

Views: 125

Answers (1)

Alex Poole
Alex Poole

Reputation: 191235

This seems to be a bug when defining the collection type against a view rather than a table. Altering the table definition invalidates the view and package specification/body as expected, but not the types1; so when the view and package are implicitly recompiled on next reference the types seem to be overlooked.

Recompiling the package or view, or even explicitly reissuing the create or replace command for the package specification and body, doesn't seem to have any effect.

Even completely dropping the package before recreating it doesn't help, as the type is linked to the view rather than the package (the related object's ID is part of the type name).

You could drop the view and then recreate that, and the next package reference would then recreate the type too; but then you would have to reinstate privileges on it (and of course it won't exist briefly, so if you try to do that outside a maintenance window other things might fail as a result).

You don't have to be quite that brutal though. Although recompiling the view doesn't help, redefining it in-place does:

create or replace view VW_SHOW_USERS as select * from SHOW_USR;

which ought to be less disruptive than dropping it, as privileges against the view will be retained.

I've created a db<>fiddle demo showing the initial problem and various unsuccessful attempts to correct it, followed by view redefinition.

1 If the collection is defined directly against the table instead of against the view then the type is invalidated by the alter table too, so it does get recompiled with the new definition, and this problem doesn't appear. When using the view it looks like something is being missed when the dependencies are evaluated. It may be worth raising a service request with Oracle to get this investigated. (I've only been able to very this behaviour up to 12cR2; perhaps someone can verify what happens in 18?)

Upvotes: 3

Related Questions