rem
rem NAME : ckarchue.sql
rem
rem DESCRIPTION
rem   This package contains the following procedures.
rem
rem   PUBLIC :
rem
rem   archue                 This is the main procedure.
rem                          It controls the order of processing and the
rem                          error handling.
rem                          * Populate user extensibility tables 
rem                               
rem   PRIVATE :
rem 
rem   None
rem 
rem NOTES
rem   This package uses the following tables :
rem 
rem            XT_CIUE_TEXT_TYPES           (C)
rem            XT_CIUE_TEXT_USAGES          (C)
rem            XT_CIUE_ELEMENT_TYPES        (C)
rem            XT_CIUE_ASSOCIATION_TYPES    (C)
rem            XT_CIUE_PROPERTIES           (C)
rem
rem
rem MODIFIED   (MM/DD/YY)
rem   loldham    08/09/95 -  Creation
rem   loldham    30/04/96    Fix US Bug 361938
rem   dlgoldsm   05/11/99 -  Rewritten for export wizard in 6i
rem

DECLARE

/* Error conditions placed on to the PL/SQL error stack */

  unhdl_orcl_err    constant number := 2105;
  obj_already_exists exception;
  col_already_exists exception;

  PRAGMA EXCEPTION_INIT(obj_already_exists, -955);
  PRAGMA EXCEPTION_INIT(col_already_exists, -1430);

/*
  NAME
    archue

  INPUTS / OUTPUTS
    prollback_segment: Rollback segment to be used by the transaction
	ptbl_tabspace: Tablespace to create xt_ciue tables in

  ACCESS
    Public
    
  PURPOSE
    This is the main procedure.
    It controls the order of processing and the error handling.
    The following tables are dropped and then recreated :
        XT_CIUE_TEXT_TYPES
        XT_CIUE_TEXT_USAGES
        XT_CIUE_ELEMENT_TYPES
        XT_CIUE_ASSOCIATION_TYPES
        XT_CIUE_PROPERTIES

  CALLS
    drop_xt_tables
	create_xt_ciue_tables
*/

PROCEDURE archue ( prollback_segment varchar2 DEFAULT NULL,
                   ptbl_tabspace varchar2     DEFAULT NULL ) IS

				 
/*
  NAME
    drop_xt_ciue_tables

  INPUTS / OUTPUTS
    None
	
  ACCESS
    Private
    
  PURPOSE
    This procedure loops and drops any tables beginning with
	xt_ciue%

  CALLS
    None
*/

PROCEDURE drop_xt_ciue_tables IS

  drop_csr NUMBER;
  curr_stmt VARCHAR2(60);

  CURSOR drop_tabs IS
     SELECT 'DROP TABLE '||object_name   stmt
     FROM   user_objects
     WHERE  object_name like 'XT\_CIUE%' ESCAPE'\'
     AND    object_type = 'TABLE';

BEGIN

  drop_csr := DBMS_SQL.OPEN_CURSOR;

  OPEN drop_tabs;
  LOOP
    FETCH drop_tabs INTO curr_stmt;
    EXIT WHEN drop_tabs%NOTFOUND;
    DBMS_SQL.PARSE(drop_csr, curr_stmt, DBMS_SQL.NATIVE);
  END LOOP;
  CLOSE drop_tabs;

  DBMS_SQL.CLOSE_CURSOR(drop_csr);

  EXCEPTION
    WHEN OTHERS THEN
      rmmes.post('RME', unhdl_orcl_err, SQLERRM);
      RAISE;

END drop_xt_ciue_tables;


/*
  NAME
    create_xt_ciue_tables

  INPUTS / OUTPUTS
    tblsp - Tablespace to create tables in

  ACCESS
    Private
    
  PURPOSE
    This procedure creates XT tables for the following CIUE tables:
        XT_CIUE_TEXT_TYPES
        XT_CIUE_TEXT_USAGES
        XT_CIUE_ELEMENT_TYPES
        XT_CIUE_ASSOCIATION_TYPES
        XT_CIUE_PROPERTIES

  CALLS
    None
*/

PROCEDURE create_xt_ciue_tables ( ptbl_tabspace varchar2 ) IS

TYPE CreXtUeTab IS TABLE OF VARCHAR2(1000)
     INDEX BY BINARY_INTEGER;
  
  xt_stmt CreXtUeTab;
  create_csr NUMBER;
  row_cnt    NUMBER;
  i_str      NUMBER := 1; 
  tblsp      VARCHAR2(40);
  
BEGIN
  
/* if no tablespace given user default */

  SELECT NVL(ptbl_tabspace, default_tablespace)
  INTO   tblsp
  FROM   user_users
  WHERE  username = USER;
  
/* Create xt_ciue% tables */  

  xt_stmt(1) := 'CREATE TABLE xt_ciue_text_types
				 TABLESPACE ' || tblsp || ' AS (  
                 SELECT *                  
                 FROM   ciue_text_types   ctt
                 WHERE  ctt.is_extended  = ''Y''
                 AND    ctt.is_published = ''Y'')';

  
  xt_stmt(2) := 'CREATE TABLE xt_ciue_text_usages 
  				 TABLESPACE ' || tblsp || ' AS ( 
                 SELECT
                   ctu.element_type
                 , ctu.seq
                 , ctu.text_type
                 , ctu.max_length
                 , ctu.nls_desc
                 , ctu.is_extended
                 , ctu.is_published
                 , ret.short_name xt_tu_short_name
                 FROM   ciue_text_usages   ctu
                 ,      rm_element_types   ret
                 WHERE  ctu.is_extended  = ''Y''
                 AND    ctu.is_published = ''Y''
                 AND    ctu.element_type = ret.id)';					 
 

  xt_stmt(3) := 'CREATE TABLE xt_ciue_element_types 
  				 TABLESPACE ' || tblsp || ' AS (  
                 SELECT * 
                 FROM   ciue_element_types   cet
                 WHERE  cet.is_extended  = ''Y''
                 AND    cet.is_published = ''Y'')';
				 
  xt_stmt(4) := 'CREATE TABLE xt_ciue_association_types 
  				 TABLESPACE ' || tblsp || ' AS (  
                 SELECT * 
                 FROM   ciue_association_types   cat
                 WHERE  cat.is_extended  = ''Y''
                 AND    cat.is_published = ''Y'')';

  xt_stmt(5) := 'CREATE TABLE xt_ciue_properties 
  				 TABLESPACE ' || tblsp || ' AS (  
                 SELECT * 
                 FROM   ciue_properties   cpr
                 WHERE  cpr.is_extended  = ''Y''
                 AND    cpr.is_published = ''Y'')';
				 
  create_csr := DBMS_SQL.OPEN_CURSOR;

  FOR i IN 1..5 LOOP
    BEGIN
      i_str := i;
      DBMS_SQL.PARSE(create_csr, xt_stmt(i), DBMS_SQL.NATIVE);

      EXCEPTION
        WHEN obj_already_exists OR col_already_exists THEN
          NULL;
    END;
  END LOOP;

  DBMS_SQL.CLOSE_CURSOR(create_csr);
				 
  
  EXCEPTION
    WHEN OTHERS THEN
      rmmes.post('RME', unhdl_orcl_err, SQLERRM);
      RAISE;

END create_xt_ciue_tables;

BEGIN

  /*If the user has specified a rollback segment then set transaction accordingly */
		
  IF ( prollback_segment IS NOT NULL ) THEN 
    COMMIT;
    dbms_transaction.use_rollback_segment(prollback_segment);
  END IF;
  
  drop_xt_ciue_tables;
  create_xt_ciue_tables(ptbl_tabspace);
  
  EXCEPTION
    WHEN OTHERS THEN
      RAISE;
END archue;

BEGIN

  rmmes.clear;
  archue(:prollback_segment, :ptbl_tabspace);
--  :iserror := 0;
--  :sqlcode := 0;

  EXCEPTION
    WHEN OTHERS THEN
    :iserror := 1;
    :sqlcode := sqlcode;

END;
 