Rem
Rem $Header: a0801050.sql 17-may-2000.11:14:06 mjungerm Exp $
Rem
Rem a0801050.sql
Rem
Rem  Copyright (c) Oracle Corporation 1999, 2000. All Rights Reserved.
Rem
Rem    NAME
Rem      a0801050.sql - additional ANONYMOUS BLOCK dictionary upgrade.
Rem      		Upgrade Oracle RDBMS from 8.1.5 to the new release
Rem
Rem
Rem    DESCRIPTION
Rem
Rem      Additional upgrade script to be run during the migration of an
Rem      8.1.5 database to the new release.
Rem
Rem	 This script is called from u0801050.sql and a0801040.sql
Rem
Rem	 Put any anonymous block related changes here.
Rem	 Any dictionary create, alter, updates and deletes  
Rem 	 that must be performed before catalog.sql and catproc.sql go 
Rem	 in c0801050.sql
Rem
Rem      The upgrade is performed in the following stages:
Rem        STAGE 1: additional steps to upgrade from 8.1.5 to 8.1.6
Rem        STAGE 2: upgrade from 8.1.5 to the new release
Rem
Rem    RETURNS
Rem 
Rem    NOTES
Rem      You must connect internal to run this script
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    tchou       06/27/00 - remove JAVA SRO
Rem    mjungerm    05/17/00 - javavm up/downgrade
Rem    rshaikh     12/01/99 - bug 1094821: dont run type upg twice
Rem    rshaikh     11/10/99 - add commit
Rem    rshaikh     10/29/99 - call 8.1.6
Rem    rshaikh     09/28/99 - bug 933439: mark all func ind w/imark all func in
Rem    rshaikh     07/26/99 - uncomment upgrade_system_types
Rem    attran      07/13/99 - PIOT: overloaded TAB$/pctused with IOTPK_OBJN
Rem    rshaikh     04/20/99 - add datetime system type initialization          
Rem    rshaikh     02/12/99 - Created
Rem

Rem =========================================================================
Rem BEGIN STAGE 1: upgrade from 8.1.5 to 8.1.6
Rem =========================================================================

REM INSERT YOUR UPGRADE STATEMENTS HERE

REM initialize datetime system types
DROP LIBRARY UPGRADE_LIB
/

CREATE LIBRARY UPGRADE_LIB TRUSTED AS STATIC
/


CREATE OR REPLACE PROCEDURE upgrade_system_types_to_816 IS
LANGUAGE C
NAME "TO_816"
LIBRARY UPGRADE_LIB;
/

set serveroutput on size 10000;

DECLARE
total NUMBER;
BEGIN
  select count(*) into total from obj$ o, user$ u where o.name in
    ('TIME', 'TIME WITH TZ', 'TIMESTAMP', 'TIMESTAMP WITH TZ',
     'INTERVAL YEAR TO MONTH', 'INTERVAL DAY TO SECOND',
     'TIMESTAMP WITH LOCAL TZ') and
     o.owner#=u.user# and u.name='SYS' and o.type#=13;     

  -- Only run this once
  IF total < 7 THEN
    dbms_output.put_line('Initializing new system types');
    upgrade_system_types_to_816();
  ELSE
    dbms_output.put_line('New system types already initialized');
  END IF;

END;
/


REM set TAB$.pctused$ to IND$.obj# if the table is an IOT.
DECLARE
index_stmt      VARCHAR2(100);
index_cursor_id NUMBER;
index_objn NUMBER;
table_stmt      VARCHAR2(100);
table_cursor_id NUMBER;
table_objn NUMBER;
rows       NUMBER;
BEGIN
  index_stmt := 'select obj#,bo# from ind$ where type#=4';
  index_cursor_id := dbms_sql.open_cursor;
  dbms_sql.parse(index_cursor_id, index_stmt, dbms_sql.native);
  dbms_sql.define_column(index_cursor_id, 1, index_objn);
  dbms_sql.define_column(index_cursor_id, 2, table_objn);

  table_stmt := 'update tab$ set pctused$=:index_objn where obj#=:table_objn';
  table_cursor_id := dbms_sql.open_cursor;
  dbms_sql.parse(table_cursor_id, table_stmt, dbms_sql.native);

  rows := dbms_sql.execute(index_cursor_id);
  LOOP
    rows := dbms_sql.fetch_rows(index_cursor_id);
    IF (rows = 0) THEN
      EXIT;
    ELSE
      dbms_sql.column_value(index_cursor_id, 1, index_objn);
      dbms_sql.column_value(index_cursor_id, 2, table_objn);
      dbms_sql.bind_variable(table_cursor_id, ':index_objn', index_objn);
      dbms_sql.bind_variable(table_cursor_id, ':table_objn', table_objn);
      rows := dbms_sql.execute(table_cursor_id);
    END IF;
  END LOOP;

  dbms_sql.close_cursor(table_cursor_id);
  dbms_sql.close_cursor(index_cursor_id);
END;
/

commit
/

REM 
REM bug 933439 - mark all functional indexes unusable that are created
REM on expressions whose return types are internal types.  The indexes
REM are incorrect and must be rebuilt.
REM

DECLARE
 obj_number number := 0;

 cursor C1 is select o.obj#, 
     'ALTER INDEX "' || u.name || '"."' || o.name || '" UNUSABLE'
        from ind$ i, user$ u, obj$ o, obj$ bo, col$ c, icol$ ic
 	where o.obj# > obj_number
	  and bitand(i.property, 16)=16 
	  and i.obj#=o.obj# 
	  and o.owner#=u.user# 
	  and i.bo#=bo.obj#
	  and bo.obj#=c.obj# 
	  and ic.col#=c.col#
	  and ic.intcol#=c.intcol# 
	  and ic.obj#=i.obj# 
	  and c.type# in (3,4,5,6,7,9,13,14,15,21,22,25,26,27,28,29,
		 30,31,68,69,94,95,97,100,101,104,108,110,
 		 116,118,136,152,153,154,155,156,172,184,185,
		 186,187,188,189,190,195,196,197,232,233)
	  order by o.obj#;

  DDL_CURSOR integer;
  ddl_statement varchar2(200);
  iterations number;
  loop_count number;
  my_err     number;
BEGIN
 loop_count := 0;
 -- To make sure we eventually stop, pick a max number of iterations
 select count(*) into iterations from ind$ i, user$ u, obj$ o, 
	obj$ bo, col$ c, icol$ ic
 	where bitand(i.property, 16)=16 
	  and i.obj#=o.obj# 
	  and o.owner#=u.user# 
	  and i.bo#=bo.obj#
	  and bo.obj#=c.obj# 
	  and ic.col#=c.col#
	  and ic.intcol#=c.intcol# 
	  and ic.obj#=i.obj# 
	  and c.type# in (3,4,5,6,7,9,13,14,15,21,22,25,26,27,28,29,
		 30,31,68,69,94,95,97,100,101,104,108,110,
 		 116,118,136,152,153,154,155,156,172,184,185,
		 186,187,188,189,190,195,196,197,232,233);


 DDL_CURSOR := dbms_sql.open_cursor;
 OPEN C1;

 dbms_output.put_line ('The following indexes are marked "UNUSABLE" due ');
 dbms_output.put_line ('to bug# 933439');

 LOOP

   BEGIN
     FETCH C1 INTO obj_number, ddl_statement;
     EXIT WHEN C1%NOTFOUND OR loop_count > iterations;
   EXCEPTION
    WHEN OTHERS THEN
      my_err := SQLCODE;
      IF my_err = -1555 THEN -- snapshot too old, re-execute fetch query
       CLOSE C1;
       -- Here is why C1 orders by obj#.  When we restart the query, we 
       -- will only find object with obj# greater than the last one tried.
       -- This keeps us from re-trying bjects that failed.
       OPEN  C1;
       GOTO continue;
      ELSE
       RAISE;
      END IF;
   END;

   BEGIN
       -- Issue the Alter Statement  (Parse implicitly executes DDLs)
       dbms_sql.parse(DDL_CURSOR, ddl_statement, dbms_sql.native);
       dbms_output.put_line (ddl_statement);
   EXCEPTION
       WHEN OTHERS THEN
        null; -- ignore, and proceed.
   END;

 <<continue>>
   loop_count := loop_count + 1;
 END LOOP;
 dbms_sql.close_cursor(DDL_CURSOR);
 CLOSE C1;

 dbms_output.put_line ('These indexes must be rebuilt');
END;
/

COMMIT
/


Rem =========================================================================
Rem END STAGE 1: upgrade from 8.1.5 to 8.1.6
Rem =========================================================================

Rem =========================================================================
Rem BEGIN STAGE 2: upgrade from 8.1.5 to the new release
Rem =========================================================================

@@a0801060

Rem =========================================================================
Rem END STAGE 2: upgrade from 8.1.5 to the new release
Rem =========================================================================

Rem *************************************************************************
Rem END a0801050.sql
Rem *************************************************************************
