rem
rem $Header: catq8003.sql 01-may-97.17:42:44 schandra Exp $
rem
Rem  Copyright (c) 1992, 1996, 1997 by Oracle Corporation
Rem    NAME
Rem      cat8003.sql - migrate Oracle RDBMS from version 8.0.2 to 8.0.3 for
Rem                    AQ
Rem    DESCRIPTION
Rem      It drops all queues and also AQ meta tables when migrate
Rem      from 8.0.2 to 8.0.3.
Rem      Customers do not need invoke this script explicitly
Rem    RETURNS
Rem
Rem    NOTES
Rem      You must connect INTERNAL to run this script
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem     schandra  05/01/97 -  Bug# 486549: dont assume 802MIG803 exists
Rem     nbhatt    04/25/97 -  Creation

-- before creating new views and tables, drop qtables from the 8.02 database.
-- applications will have to create new queue_tables.

DECLARE
  table_exists       INTEGER;       
  qtable_exists      INTEGER;
  cursor_name        INTEGER;
  rows_processed     INTEGER;
  drop_table_text    VARCHAR2(50);
  error_flag         BOOLEAN := FALSE; 
  migration_cursor   INTEGER;
  schema_name	     VARCHAR2(30);
  table_name 	     VARCHAR2(30);
  ignore	     INTEGER;

BEGIN
  -- Check if sys.AQ$_802MIG803 exists
  SELECT count (*) INTO table_exists FROM dba_tables 
  WHERE table_name = 'AQ$_802MIG803' and owner ='SYS';

  IF table_exists > 0 THEN   
    BEGIN 
      migration_cursor := dbms_sql.open_cursor;
      dbms_sql.parse(migration_cursor, 
		'SELECT schema, name FROM sys.AQ$_802MIG803', dbms_sql.v7);
      dbms_sql.define_column(migration_cursor, 1, schema_name, 30);
      dbms_sql.define_column(migration_cursor, 2, table_name, 30);
      ignore := dbms_sql.execute(migration_cursor);

      cursor_name := dbms_sql.open_cursor;           -- cursor for drop table
      -- loop through all the table names in AQ$_802MIG803
      LOOP
      IF dbms_sql.fetch_rows(migration_cursor) > 0 THEN
	dbms_sql.column_value(migration_cursor, 1, schema_name);
	dbms_sql.column_value(migration_cursor, 2, table_name);

        -- Check if the queue table listed in AQ$_802MIG803 exists 
        SELECT count (*) INTO qtable_exists from dba_tables 
        WHERE table_name = table_name AND owner = schema_name;

        IF qtable_exists > 0  THEN
          BEGIN
            -- drop the queue table 
            drop_table_text :='drop table '||schema_name||'.'||table_name;
            dbms_sql.parse(cursor_name, drop_table_text, dbms_sql.v7);
            rows_processed := dbms_sql.execute(cursor_name);
          EXCEPTION
           -- if there is an exception - log it in the ora file
            WHEN OTHERS THEN
             DBMS_SYSTEM.KSDWRT(1,'AQ Migration, Failed to drop queue table '||
                                 schema_name||'.'||table_name); 
             error_flag := TRUE;
          END;
          BEGIN
            -- drop the view associated with the queue table
            drop_table_text := 'drop view '||
                                   schema_name||'.AQ$'||table_name;
            dbms_sql.parse(cursor_name, drop_table_text, dbms_sql.v7);
            rows_processed := dbms_sql.execute(cursor_name); 
          EXCEPTION
           -- if there is an exception - log it in the ora file
            WHEN OTHERS THEN
             DBMS_SYSTEM.KSDWRT(1,'AQ Migration, Failed to drop queue view '||
                                 schema_name||'.AQ$'||table_name);
             error_flag := TRUE;
          END;
        ELSE    -- table listed in AQ$_802MIG803 does not exist, log a warning
           DBMS_SYSTEM.KSDWRT(2,'AQ Migration, queue table does not exist ' ||
			      schema_name ||'.'|| table_name);
        END IF;
      ELSE
	EXIT;
      END IF;
      END LOOP; 
    EXCEPTION
       WHEN OTHERS THEN
        -- if there is an exception - log it in the ora file
        DBMS_SYSTEM.KSDWRT(1, 'AQ Migration, Error while trying to drop ' ||
				 'old queue tables');
        error_flag := TRUE;
    END;

    IF NOT error_flag THEN --drop sys.AQ$_802MIG803 only if no errors occured
      BEGIN
        dbms_sql.parse(cursor_name,'drop table sys.AQ$_802MIG803',dbms_sql.v7); 
        rows_processed := dbms_sql.execute(cursor_name);
      EXCEPTION
        WHEN OTHERS  THEN
          DBMS_SYSTEM.KSDWRT(2, 'AQ Migration, Error while trying '||
	                        'to drop AQ$_802MIG803');
      END;
    END IF; 
 
     -- close cursor
    dbms_sql.close_cursor(cursor_name);        
    dbms_sql.close_cursor(migration_cursor);        
  END IF;

   EXCEPTION 
    WHEN OTHERS THEN
      DBMS_SYSTEM.KSDWRT(1, 'AQ Migration, Error while trying to drop ' ||
			    'old queue tables');
END;
/

