Edit D:\app\Administrator\product\11.2.0\dbhome

Edit D:\app\Administrator\product\11.2.0\dbhome_1\RDBMS\ADMIN\dbmsobj.sql

Rem
Rem $Header: dbmsobj.sql 02-may-2008.02:11:05 atomar Exp $
Rem
Rem dbmsobj.sql
Rem
Rem Copyright (c) 2006, 2008, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      dbmsobj.sql - General Objects Procedure and Functions
Rem
Rem    DESCRIPTION
Rem      This contains procedures and functions for SQL Objects.
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    atomar      05/01/08 - bug upgrade 6770913
Rem    skabraha    06/07/07 - add get_oldvsn_hashcode
Rem    skabraha    12/18/06 - Created
Rem

create or replace package dbms_objects_utils is
procedure upgrade_dict_image;
end;
/

create or replace package body dbms_objects_utils is

-- This procedure upgrades the type dictionary images from 8.0 to 8.1. The
-- dictionary tables that could be in 8.0 are kottd$, kottb$, kottbx$, 
-- kotad$ and kotmd$, which existed in 8.0 or 8.1, when 8.0 compatibility
-- was possible, kotadx$ was created in 9iR2 when we required 8.1 minimum
-- compatibility.

procedure upgrade_dict_image is

oldimg number;
origcnt number;
inscnt  number;
begin

oldimg := 0;
-- first make sure that there are dictionary tables in 8.0 format
execute immediate 'select bitand(flags, 128) from coltype$ where 
obj#=(select obj# from obj$ where name=''KOTTD$'')' into oldimg;
if (oldimg = 0) then
  dbms_output.put_line ('No type dictionary table to upgrade');
  return;
end if;

execute immediate 'alter session set events ''22372 trace name 
context forever''';

-- upgrade kottd$

dbms_output.put_line('Upgrading kottd$ ...');
execute immediate 'create table kottd_temp$ of kottd';
execute immediate 'select count(*) from kottd$' into origcnt;
execute immediate 'insert into kottd_temp$(sys_nc_oid$, sys_nc_rowinfo$)
select sys_nc_oid$, sys_nc_rowinfo$ from kottd$';
commit;
execute immediate  'update coltype$ set flags=flags-bitand(flags,128)
where obj#=(select obj# from obj$ where name=''KOTTD$'')';
commit;
execute immediate 'alter system flush shared_pool';
--error expected discarding it
-- it is just to build the cursor
execute immediate 'begin insert into kottd$(sys_nc_oid$, sys_nc_rowinfo$)
select sys_nc_oid$, sys_nc_rowinfo$ from kottd_temp$;
exception when others then null; end;';
execute immediate 'delete from kottd$';
execute immediate 'begin insert into kottd$(sys_nc_oid$, sys_nc_rowinfo$)
select sys_nc_oid$, sys_nc_rowinfo$ from kottd_temp$;
exception when others then null; end;';
execute immediate 'select count(*) from kottd$' into inscnt;
if(origcnt = inscnt) then
  commit;
  dbms_output.put_line('kottd$ Upgraded ...');
  execute immediate 'drop table kottd_temp$';
else
  dbms_output.put_line('Upgrade failed for kottd$ ...');
  rollback;
  return;
end if;
-- now kottb$

dbms_output.put_line('Upgrading kottb$ ...');
origcnt := 0;
inscnt  := 0;

execute immediate 'create table kottb_temp$ of kottb';
execute immediate 'insert into kottb_temp$(sys_nc_oid$, sys_nc_rowinfo$)
select sys_nc_oid$, sys_nc_rowinfo$ from kottb$';
execute immediate 'select count(*) from kottb$' into origcnt;
commit;
execute immediate  'update coltype$ set flags=flags-bitand(flags,128)
where obj#=(select obj# from obj$ where name=''KOTTB$'')';
commit;
execute immediate 'alter system flush shared_pool';
execute immediate 'begin insert into kottb$(sys_nc_oid$, sys_nc_rowinfo$)
select sys_nc_oid$, sys_nc_rowinfo$ from kottb_temp$ ; exception when
others then null;end;';
execute immediate 'delete from kottb$';
execute immediate 'begin insert into kottb$(sys_nc_oid$, sys_nc_rowinfo$)
select sys_nc_oid$, sys_nc_rowinfo$ from kottb_temp$ ; exception when
others then null;end;';
execute immediate 'select count(*) from kottb$' into inscnt;
if(inscnt = origcnt) then
  commit;
  dbms_output.put_line('kottb$ Upgraded ...');
  execute immediate 'drop table kottb_temp$';
else
  dbms_output.put_line('Upgrade failed for kottb$ ...');
  rollback;
  return;
end if;

-- now kottbx$

dbms_output.put_line('Upgrading kottbx$ ...');
origcnt := 0;
inscnt  := 0;

execute immediate 'create table kottbx_temp$ of kottbx';
execute immediate 'insert into kottbx_temp$(sys_nc_oid$, sys_nc_rowinfo$)
select sys_nc_oid$, sys_nc_rowinfo$ from kottbx$';
execute immediate 'select count(*) from kottbx$' into origcnt;
commit;
execute immediate  'update coltype$ set flags=flags-bitand(flags,128)
where obj#=(select obj# from obj$ where name=''KOTTBX$'')';
execute immediate 'alter system flush shared_pool';
execute immediate 'begin insert into kottbx$(sys_nc_oid$, sys_nc_rowinfo$)
select sys_nc_oid$, sys_nc_rowinfo$ from kottbx_temp$  ; exception when
others then null; end;';
commit;
execute immediate 'delete from kottbx$';
execute immediate 'begin insert into kottbx$(sys_nc_oid$, sys_nc_rowinfo$)
select sys_nc_oid$, sys_nc_rowinfo$ from kottbx_temp$  ; exception when
others then null; end;';
execute immediate 'select count(*) from kottbx$' into inscnt;
if(inscnt = origcnt) then
  commit;
  dbms_output.put_line('kottbx$ Upgraded ...');
  execute immediate 'drop table kottbx_temp$';
else
  dbms_output.put_line('Upgrade failed for kottbx$ ...');
  rollback;
  return;
end if;

-- now kotad$
dbms_output.put_line('Upgrading kotad$ ...');
origcnt := 0;
inscnt  := 0;

execute immediate 'create table kotad_temp$ of kotad';
execute immediate 'insert into kotad_temp$(sys_nc_oid$, sys_nc_rowinfo$)
select sys_nc_oid$, sys_nc_rowinfo$ from kotad$';
commit;
execute immediate 'select count(*) from kotad$' into origcnt;
execute immediate  'update coltype$ set flags=flags-bitand(flags,128)
where obj#=(select obj# from obj$ where name=''KOTAD$'')';
execute immediate 'alter system flush shared_pool';

execute immediate 'begin insert into kotad$(sys_nc_oid$, sys_nc_rowinfo$)
select sys_nc_oid$, sys_nc_rowinfo$ from kotad_temp$; exception when others
 then null; end;';
commit;
execute immediate 'delete from kotad$';
execute immediate 'begin insert into kotad$(sys_nc_oid$, sys_nc_rowinfo$)
select sys_nc_oid$, sys_nc_rowinfo$ from kotad_temp$; exception when others
 then null; end;';
execute immediate 'select count(*) from kotad$' into inscnt;

if(inscnt = origcnt) then
  commit;
  dbms_output.put_line('kotad$ Upgraded ...');
  execute immediate 'drop table kotad_temp$';
else
  dbms_output.put_line('Upgrade failed for kotad$ ...');
  rollback;
  return;
end if;

-- now kotmd$

dbms_output.put_line('Upgrading kotmd$ ...');
origcnt := 0;
inscnt  := 0;

execute immediate 'create table kotmd_temp$ of kotmd';
execute immediate 'insert into kotmd_temp$(sys_nc_oid$, sys_nc_rowinfo$)
select sys_nc_oid$, sys_nc_rowinfo$ from kotmd$';
commit;
execute immediate 'select count(*) from kotmd$' into origcnt;
execute immediate  'update coltype$ set flags=flags-bitand(flags,128)
where obj#=(select obj# from obj$ where name=''KOTMD$'')';
execute immediate 'alter system flush shared_pool';
execute immediate 'begin insert into kotmd$(sys_nc_oid$, sys_nc_rowinfo$)
select sys_nc_oid$, sys_nc_rowinfo$ from kotmd_temp$; exception when others
 then null; end;';
execute immediate 'delete from kotmd$';
execute immediate 'begin insert into kotmd$(sys_nc_oid$, sys_nc_rowinfo$)
select sys_nc_oid$, sys_nc_rowinfo$ from kotmd_temp$; exception when others
 then null; end;';
execute immediate 'select count(*) from kotmd$' into inscnt;
if(inscnt = origcnt) then
  commit;
  dbms_output.put_line('kotmd$ Upgraded ...');
  execute immediate 'drop table kotmd_temp$';
else
  dbms_output.put_line('Upgrade failed for kotmd$ ...');
  rollback;
  return;
end if;

-- reset the event
execute immediate 'alter session set events ''22372 trace name 
context off''';

end; /* end of procedure upgrade_dict_image */
end; /* end of package dbms_objects_utils */
/

show errors

-- For now let's use the same library that we use for upgrade callouts.
-- At
CREATE OR REPLACE LIBRARY UTL_OBJECTS_LIB TRUSTED AS STATIC
/

CREATE OR REPLACE FUNCTION get_oldversion_hashcode
(schema varchar2, slen pls_integer, typename varchar2, tlen pls_integer)
return raw is
LANGUAGE C
NAME "GET_OLDVSN_HASHCODE"
LIBRARY UTL_OBJECTS_LIB
parameters(schema String, slen ub2, typename String, tlen ub2,
return OCIRaw);
/

CREATE OR REPLACE PUBLIC SYNONYM get_oldversion_hashcode 
for sys.get_oldversion_hashcode
/
GRANT EXECUTE ON get_oldversion_hashcode TO PUBLIC
/

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de