- Remove any backup (_BK) views
- Create a new view to replace the root XXK_MTL_CAT view
- Go through the ALL_VIEWS system view looking for the structure-specific views and then individually replace each of them with a view based on the new XXK_MTL_CAT view created above
- Rebuild all the objects that have been invalidated by this change
@utlspon xu6_2012_replace_xxk_mtl_cat_view
BEGIN
for v_Data in (SELECT av.view_name
FROM ALL_VIEWS av
WHERE av.owner = USER
and av.view_name LIKE 'XXK_MTL_CAT_%BK') loop
execute immediate 'drop view ' || v_Data.view_name; -- Remove an existing backup view if it exists
end loop;
END;
/
RENAME XXK_MTL_CAT TO XXK_MTL_CAT_BK
/
CREATE OR REPLACE VIEW XXK_MTL_CAT AS
BEGIN
for v_Data in (SELECT av.view_name
FROM ALL_VIEWS av
WHERE av.owner = USER
and av.view_name LIKE 'XXK_MTL_CAT_%BK') loop
execute immediate 'drop view ' || v_Data.view_name; -- Remove an existing backup view if it exists
end loop;
END;
/
RENAME XXK_MTL_CAT TO XXK_MTL_CAT_BK
/
CREATE OR REPLACE VIEW XXK_MTL_CAT AS
*
/
begin
for v_view in (select av.*
from all_views av
where av.owner = USER
and av.view_name like 'XXK_MTL_CAT_%') loop
if instr(v_view.text, 'NOETIX_SYS.N_KFF_MTL_CAT') > 0 then
execute immediate 'CREATE OR REPLACE VIEW ' || v_view.view_name || '_BK AS ' || v_view.text;
execute immediate 'CREATE OR REPLACE VIEW ' || v_view.view_name || ' AS ' || replace(v_view.text, 'NOETIX_SYS.N_KFF_MTL_CAT', 'NOETIX_SYS.XXK_MTL_CAT');
end if;
end loop;
end;
/
begin
dbms_output.put_line('Recompiling invalid Views under NOETIX_SYS Schema');
dbms_output.put_line('-------------------------------------------------');
dbms_output.put_line('Remove Invalid Noetix Views (that are not set to be ommitted)');
for v_SQL in (select 'ALTER VIEW NOETIX_SYS.' || do.object_name || ' COMPILE' text
from dba_objects do
where do.status = 'INVALID'
and do.OWNER = 'NOETIX_SYS'
and do.OBJECT_TYPE = 'VIEW'
and do.OBJECT_NAME not in
(select view_name
from n_views
where nvl(omit_flag, 'N') = 'N')) loop
dbms_output.put_line(' ' || v_SQL.Text);
execute immediate v_SQL.Text;
end loop;
dbms_output.put_line('END');
end;
/
COMMIT;
@utlspoff
begin
for v_view in (select av.*
from all_views av
where av.owner = USER
and av.view_name like 'XXK_MTL_CAT_%') loop
if instr(v_view.text, 'NOETIX_SYS.N_KFF_MTL_CAT') > 0 then
execute immediate 'CREATE OR REPLACE VIEW ' || v_view.view_name || '_BK AS ' || v_view.text;
execute immediate 'CREATE OR REPLACE VIEW ' || v_view.view_name || ' AS ' || replace(v_view.text, 'NOETIX_SYS.N_KFF_MTL_CAT', 'NOETIX_SYS.XXK_MTL_CAT');
end if;
end loop;
end;
/
begin
dbms_output.put_line('Recompiling invalid Views under NOETIX_SYS Schema');
dbms_output.put_line('-------------------------------------------------');
dbms_output.put_line('Remove Invalid Noetix Views (that are not set to be ommitted)');
for v_SQL in (select 'ALTER VIEW NOETIX_SYS.' || do.object_name || ' COMPILE' text
from dba_objects do
where do.status = 'INVALID'
and do.OWNER = 'NOETIX_SYS'
and do.OBJECT_TYPE = 'VIEW'
and do.OBJECT_NAME not in
(select view_name
from n_views
where nvl(omit_flag, 'N') = 'N')) loop
dbms_output.put_line(' ' || v_SQL.Text);
execute immediate v_SQL.Text;
end loop;
dbms_output.put_line('END');
end;
/
COMMIT;
@utlspoff
First of all do a search for the object N_KFF_Mtl_Cat in all the .SQL files in your install directory. You shouldn't find many, the one you're after is named something like N_KFF_Mtl_Cat_pkg.sql, open the file and then scroll down to the line;
INSERT /*+ APPEND */ INTO N_KFF_Mtl_Cat
You'll see that that the basis for this insert statement is a select statement. You need to take this select statement, take the columns in the table and re-format the SQL to return all the columns as specified in table.
When you've done this you'll notice that the column in the source table STRUCTURE_ID is mapping to both the STRUCTURE_ID and STRUCTURE_NAME in the view. If you scroll down quite a bit further you'll notice that there is also an UPDATE statement;
UPDATE N_KFF_Mtl_Cat dtbl
SET dtbl.STRUCTURE_NAME =
SET dtbl.STRUCTURE_NAME =
You need to extract this UPDATE statement and put it into the view you're building (as the STRUCTURE_NAME) column.
In the end you'll have a piece of SQL that you can drop into the script above. If everything works when you run the script there will be no (well, the same number as you started with!) invalid views.
