rem
rem
rem
REM  Copyright (c) 1997 by Oracle Corporation
REM    NAME
REM      ckextint.sql
REM    DESCRIPTION
REM      Drops/Create UE Extract Tables
REM    RETURNS
REM
REM    NOTES
REM      <other useful comments, qualifications, etc.>
REM    MODIFIED   (MM/DD/YY)
REM     aobhrai     26/01/95 -  Initial Version
REM     aobhrai     16/05/97 -  Rewrite so that this script can be run through RM

PROMPT
PROMPT **********************************************************************
PROMPT * Designer/2000 Repository Administration Utilities:
PROMPT * Initialising Extract Tables For User Extension Meta Data.
PROMPT *
PROMPT **********************************************************************
PROMPT

DECLARE 
  type names is table of varchar2(100) index by binary_integer;
  tablist names;
  sourcelist names;
  numtab number;
  csr NUMBER;
BEGIN
  tablist(1) := 'XT_CIUE_TEXT_TYPES';
  tablist(2) := 'XT_CIUE_TEXT_USAGES';
  tablist(3) := 'XT_CIUE_ELEMENT_TYPES';
  tablist(4) := 'XT_CIUE_ASSOCIATION_TYPES';
  tablist(5) := 'XT_CIUE_PROPERTIES';

  sourcelist(1) := 'CIUE_TEXT_TYPES';
  sourcelist(2) := 'CIUE_TEXT_USAGES';
  sourcelist(3) := 'CIUE_ELEMENT_TYPES';
  sourcelist(4) := 'CIUE_ASSOCIATION_TYPES';
  sourcelist(5) := 'CIUE_PROPERTIES';

  numtab := 5;
  csr := DBMS_SQL.OPEN_CURSOR;
  for tabnum in 1..numtab loop
    begin
      DBMS_OUTPUT.PUT_LINE('Dropping table '||tablist(tabnum)||'...');
      DBMS_SQL.PARSE(csr,'DROP TABLE '||tablist(tabnum),DBMS_SQL.V7);
    exception when others then
      null;
    end;
  end loop;
  DBMS_SQL.CLOSE_CURSOR(CSR);

  csr := DBMS_SQL.OPEN_CURSOR;
  begin
     DBMS_OUTPUT.PUT_LINE('Dropping table XT_CIUE_VERSION...');
         DBMS_SQL.PARSE(csr,'drop table XT_CIUE_VERSION',DBMS_SQL.V7);
  exception when others then
      null;
  end;
  DBMS_SQL.CLOSE_CURSOR(CSR);


  DBMS_OUTPUT.PUT_LINE(' ');
  DBMS_OUTPUT.PUT_LINE('Table dropping complete');

  csr := DBMS_SQL.OPEN_CURSOR;
  for tabnum in 1..numtab loop
    begin
      DBMS_OUTPUT.PUT_LINE('Creating table '||tablist(tabnum)||'...');
      DBMS_SQL.PARSE(csr,'CREATE TABLE '||tablist(tabnum)||' as select * from '
      ||sourcelist(tabnum)||' where 1=2',DBMS_SQL.V7);
    exception when others then
      null;
    end;
  end loop;
  DBMS_SQL.CLOSE_CURSOR(CSR);

  csr := DBMS_SQL.OPEN_CURSOR;
  begin
     DBMS_OUTPUT.PUT_LINE('Creating table XT_CIUE_VERSION...');
         DBMS_SQL.PARSE(csr,'create table XT_CIUE_VERSION( pv_version varchar2(70) NOT NULL)',DBMS_SQL.V7);
  exception when others then
      null;
  end;
  DBMS_SQL.CLOSE_CURSOR(CSR);


  DBMS_OUTPUT.PUT_LINE(' ');
  DBMS_OUTPUT.PUT_LINE('Table creation complete');


  csr := DBMS_SQL.OPEN_CURSOR;
  DBMS_OUTPUT.PUT_LINE('Altering table XT_CIUE_TEXT_USAGES...');
      DBMS_SQL.PARSE(csr,'ALTER TABLE XT_CIUE_TEXT_USAGES ADD (xt_tu_short_name VARCHAR2(10))'
     ,DBMS_SQL.V7);
  DBMS_SQL.CLOSE_CURSOR(CSR);
  DBMS_OUTPUT.PUT_LINE(' ');
  DBMS_OUTPUT.PUT_LINE('Table alteration complete');




EXCEPTION WHEN OTHERS THEN
  NULL;
END;
/



