rem  Change History
rem    02-Jun-00 : Commented out duplicated bind variables because of problems
rem                with Java RSJ binding - part fixes bug 1316312 (dlgoldsm)
rem    03-May-99 : bug 1275707 PKey constraint violated on XTSYS_TABS_EXPORTED 
rem    09-Feb-00 : Fixed bug 1169926 added CHECKED_IN_OBJ_FLAG
rem    03-Sep-99 : Fixed bug 972414 allow Dependency data to be filtered out.
rem    16-Jun-99 : Fixed bug 911327 allow renaming of root folders in Import.
DECLARE
utility_name   varchar2(27)  := 'Designer - Export:';
unhdl_orcl_err    constant number := 2105;

Procedure create_xtschema(tbsp varchar2) IS
 proc   varchar2(100) := 'create_xtschema ';

Type dxt_schema_t IS TABLE OF Varchar2(100);
dxtschema dxt_schema_t := dxt_schema_t 
(
 '  DROP   table xtsys_export_objects '
,
 '  DROP   table xtsys_tabs_exported '
,
 '  DROP   table xtsys_import_irid_mapping '
,
 '  DROP   table xtsys_import_ivid_mapping '
);

-- object_type in 'W'orkarea, 'C'onfiguation, 'F'older
-- wa_mode in 'R'esolved, 'C'hecked out, 'U'nresolved
-- fd_mode in 'D'ecomposed, 'S'ubfolder, 'T'op folder
--    'T'op folder mode not currently used
-- mkmem_fldr_irid reference to folder which becomes parent of imported folder
-- conflict_res not currently used.
-- 'all_versions' exports whole object families and 
-- only applies to unresolved mode.
-- 'include_deps' = 'Y'  includes all the dependency data associated
-- with a Workarea.
-- If given as an export parameter, root folders may be given 
-- a new name. These are mapped in to the NEW_NAME column
-- of the XTSYS_IMPORT_IVID_MAPPING table based on an IVID join 
-- with XTSYS_EXPORT_OBJECTS
Type cxt_schema_t IS TABLE OF Varchar2(2000);
cxtschema cxt_schema_t := cxt_schema_t 
(
'    CREATE table xtsys_export_objects (                   '||
'       object_irid         NUMBER(38)           NOT NULL  '||
'         PRIMARY KEY                                      '||
'    ,  object_ivid         NUMBER(38)               NULL  '||
'         UNIQUE                                           '||
'    ,  object_type         VARCHAR2(20)             NULL  '||
'         CHECK (object_type IN (''W'', ''C'', ''F''))     '||
'    ,  default_import_name VARCHAR2(256)            NULL  '||
'    ,  wa_mode             VARCHAR2(1)              NULL  '||
'         CHECK (wa_mode IN (''C'', ''R'', ''U''))         '||
'    ,  fd_mode             VARCHAR2(1)              NULL  '||
'         CHECK (fd_mode IN (''D'', ''T'', ''S'',NULL))    '||
'    ,  mkmem_fldr_irid     NUMBER(38)               NULL  '||
'         UNIQUE                                           '||
'    ,  root_flag           VARCHAR2(1) DEFAULT ''N'' NULL '||
'         CHECK (root_flag IN (''Y'', ''N''))              '||
'    ,  all_versions        VARCHAR2(1) DEFAULT ''N'' NULL '||
'         CHECK (all_versions IN (''Y'', ''N''))           '||
'    ,  include_deps        VARCHAR2(1) DEFAULT ''N'' NULL '||
'         CHECK (include_deps IN (''Y'', ''N''))           '||
'    ,  extract_reg_metad   VARCHAR2(1) DEFAULT ''N'' NULL '||
'         CHECK (extract_reg_metad IN (''Y'', ''N''))      '||
'    ,  conflict_res        VARCHAR2(1) DEFAULT ''M'' NULL '||
'         CHECK ( conflict_res IN (''I'',''M''))           '||
'    ,  checked_in_obj_flag VARCHAR2(1)              NULL  '||
'         CHECK ( checked_in_obj_flag IN (''Y'',''N''))    '||
'    ,  status              VARCHAR2(1)              NULL  '||
'    ,  import_order        INTEGER                  NULL  '||
'    ,  notes               VARCHAR2(200)            NULL )'
,

'    CREATE table xtsys_tabs_exported (               '||
'       table_irid     NUMBER(38)           NOT NULL  '||
'    ,  family_irid    NUMBER(38)           NOT NULL  '||
'    ,  table_name     VARCHAR2(30)         NOT NULL  '||
'    ,  exp_table_name VARCHAR2(30)         NOT NULL  '||
'    ,  created        VARCHAR2(1)              NULL  '||
'    ,  row_cnt        INTEGER                  NULL  '||
'    ,  pass           INTEGER                  NULL '||
'    , CONSTRAINT xtsys_te_pkey PRIMARY KEY(exp_table_name, family_irid) ) '
,

'    CREATE table xtsys_import_irid_mapping (         '||
'       old_irid    NUMBER(38)              NOT NULL  '||
'         PRIMARY KEY                                 '||
'    ,  new_irid    NUMBER(38)                  NULL  '||
'         UNIQUE                                      '||
'    ,  exclude     VARCHAR2(1) DEFAULT ''N''   NULL  '||
'         CHECK (exclude IN (''Y'', ''N''))    '||
'    ,  conflict    VARCHAR2(1)                 NULL  '||
'    ,  table_irid     NUMBER(38)               NULL )'
,

'    CREATE table xtsys_import_ivid_mapping (          '||
'       old_ivid        NUMBER(38)           NOT NULL  '||
'         PRIMARY KEY                                  '||
'    ,  new_ivid        NUMBER(38)               NULL  '||
'         UNIQUE                                       '||
'    ,  new_name        VARCHAR2(256)            NULL  '||
'    ,  table_irid      NUMBER(38)               NULL  '||
'    ,  import_sequence INTEGER                  NULL )'
);

csr integer;
i   binary_integer;
exe integer;

BEGIN
  rmdbg.trace(utility_name||proc || 'is started.');
  dbms_output.put_line(utility_name||proc || 'is started.');
  ck_util.register(utility_name,'Init');

  csr := DBMS_SQL.OPEN_CURSOR;

  FOR i in dxtschema.first..dxtschema.last LOOP
   BEGIN
    dbms_output.put_line('Dropping : '||dxtschema(i));
    DBMS_SQL.PARSE(csr, dxtschema(i), DBMS_SQL.NATIVE);
    exe := DBMS_SQL.EXECUTE(csr);
   EXCEPTION
    WHEN OTHERS THEN NULL;
   END;
   dbms_output.put_line('Dropping : '||dxtschema(i));
  END LOOP;
  DBMS_SQL.CLOSE_CURSOR(csr);

  csr := DBMS_SQL.OPEN_CURSOR;
  FOR i in cxtschema.first..cxtschema.last LOOP
    IF tbsp IS NULL THEN
      DBMS_SQL.PARSE(csr, cxtschema(i), DBMS_SQL.NATIVE);
    ELSE
      DBMS_SQL.PARSE(csr, cxtschema(i) ||' tablespace '||tbsp, DBMS_SQL.NATIVE);
    END IF;
    exe := DBMS_SQL.EXECUTE(csr);
  END LOOP;
  DBMS_SQL.CLOSE_CURSOR(csr);

EXCEPTION
  WHEN OTHERS THEN
      rmdbg.trace(utility_name||proc || 'loc 106');  
      rmmes.post('RME', unhdl_orcl_err, SQLERRM);
      ck_util.ora_error(utility_name,proc,sql%rowcount);
      rollback;
      dbms_output.put_line(utility_name||proc || 'loc 105'||SQLERRM);

      ck_util.init(utility_name,'Finish');
      rmdbg.trace(utility_name||proc || 'Finish');
      ck_util.register(utility_name,'Finish');
      rmdbg.flush_trace;
      raise;

END create_xtschema;

BEGIN

  rmmes.clear;
  create_xtschema(:tablespace_name);

--  :iserror := 0;
--  :sqlcode := 0;
  exception when others then
  :iserror := 1;
  :sqlcode := sqlcode;

END;
