Rem
Rem $Header: sdo/admin/semvmrecreate.sql /main/1 2009/05/28 09:16:02 matperry Exp $
Rem
Rem semvmrecreate.sql
Rem
Rem Copyright (c) 2009, Oracle and/or its affiliates. All rights reserved. 
Rem
Rem    NAME
Rem      semvmrecreate.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    matperry    05/27/09 - Created
Rem

----------------------------------------------------------------------------
-- This script consults metadata tables for virtual models and creates    --
-- a script that can be used to recreate all the virtual models in        --
-- the semantic network. The name of the generated script is given as     --
-- a command line argument, for example:                                  --
-- @semvmrecreate.sql my_recreate_script.sql                              --
----------------------------------------------------------------------------

create table MDSYS.semvm_recreate$ (stmt varchar2(4000), stmtId number);

declare
  vmOwner    varchar2(30);
  vmName     varchar2(30);
  type       cursor_type is REF CURSOR;
  outer_crs  cursor_type;
  query_crs  cursor_type;
  stmt       varchar2(2000);
  modelName  varchar2(30);
  modelsStr  varchar2(4000);
  rbName     varchar2(30);
  rbsStr     varchar2(4000);
  priv_crs   cursor_type;
  privStmt   varchar2(2000);
  privStr    varchar2(4000);
  viewName1  varchar2(40);
  viewName2  varchar2(40);
  grantee    varchar2(30);
  privilege  varchar2(40);
  grantable  varchar2(3);
  firstRow   boolean;
  stmtCtr    number := 0;
  user_id    number;
  grantStmt  varchar2(4000);
begin

  open outer_crs for 'select owner, virtual_model_name from mdsys.sem_vmodel_info
                      order by virtual_model_name';
  loop
  begin
    fetch outer_crs into vmOwner, vmName;
    exit when outer_crs%NOTFOUND;

    execute immediate '
      insert into mdsys.semvm_recreate$(stmt, stmtId) values (:1, :2)'
      using 'alter session set current_schema=' || vmOwner || ';',
            stmtCtr;
      stmtCtr := stmtCtr + 1;

    execute immediate '
      insert into mdsys.semvm_recreate$(stmt, stmtId) values (:1, :2)'
      using 'begin',
            stmtCtr;
      stmtCtr := stmtCtr + 1;

    execute immediate '
      insert into mdsys.semvm_recreate$(stmt, stmtId) values (:1, :2)'
      using 'sem_apis.create_virtual_model(''' || vmName || ''', ',
            stmtCtr;
      stmtCtr := stmtCtr + 1;

    execute immediate '
      insert into mdsys.semvm_recreate$(stmt, stmtId) values (:1, :2)'
      using '   sem_models(',
            stmtCtr;
      stmtCtr := stmtCtr + 1;

    -- build the models string --
    firstRow := true;

    stmt := 'select data_name 
             from mdsys.sem_vmodel_datasets
             where virtual_model_name = ''' || 
             sys.dbms_assert.qualified_sql_name(vmName) || '''
             and data_type = ''MODEL''';

    open query_crs for stmt;
    loop 
    begin
      fetch query_crs into  modelName;
      exit when query_crs%NOTFOUND;

      if (firstRow) then
        execute immediate '
          insert into mdsys.semvm_recreate$(stmt, stmtId) values (:1, :2)'
          using '   '''   || modelName || '''',
                stmtCtr;
          stmtCtr := stmtCtr + 1;   
        firstRow := false;
      else
        execute immediate '
          insert into mdsys.semvm_recreate$(stmt, stmtId) values (:1, :2)'
          using ',  '''   || modelName || '''',
                stmtCtr;
          stmtCtr := stmtCtr + 1;   
      end if;
    end;
    end loop;
    close query_crs;

    execute immediate '
      insert into mdsys.semvm_recreate$(stmt, stmtId) values (:1, :2)'
      using '   ),',
            stmtCtr;
      stmtCtr := stmtCtr + 1;

    -- build the rulebases string --
    execute immediate '
      insert into mdsys.semvm_recreate$(stmt, stmtId) values (:1, :2)'
      using '   sem_rulebases(',
            stmtCtr;
      stmtCtr := stmtCtr + 1;

    firstRow := true;

    stmt := 'select data_name 
             from mdsys.sem_vmodel_datasets
             where virtual_model_name = ''' || 
             sys.dbms_assert.qualified_sql_name(vmName) || '''
             and data_type = ''RULEBASE''';

    open query_crs for stmt;
    loop 
    begin
      fetch query_crs into  rbName;
      exit when query_crs%NOTFOUND;

      if (firstRow) then
        execute immediate '
          insert into mdsys.semvm_recreate$(stmt, stmtId) values (:1, :2)'
          using '   '''   || rbName || '''',
                stmtCtr;
          stmtCtr := stmtCtr + 1;
        firstRow := false;
      else
        execute immediate '
          insert into mdsys.semvm_recreate$(stmt, stmtId) values (:1, :2)'
          using ',  ''' || rbName || '''',
                stmtCtr;
          stmtCtr := stmtCtr + 1;
      end if;
    end;
    end loop;
    close query_crs;

    execute immediate '
      insert into mdsys.semvm_recreate$(stmt, stmtId) values (:1, :2)'
      using '   ));',
            stmtCtr;
      stmtCtr := stmtCtr + 1;            

    execute immediate '
      insert into mdsys.semvm_recreate$(stmt, stmtId) values (:1, :2)'
      using 'end;',
            stmtCtr;
      stmtCtr := stmtCtr + 1;

    execute immediate '
      insert into mdsys.semvm_recreate$(stmt, stmtId) values (:1, :2)'
      using '/',
            stmtCtr;
      stmtCtr := stmtCtr + 1;            

    -- handle privileges --
    -- semv view --
    privStmt := 'select p.grantee, p.privilege, p.grantable, u.user_id
                 from dba_tab_privs p, all_users u
                 where p.owner = ''MDSYS'' and 
                       p.table_name = ''SEMV_' || 
                       sys.dbms_assert.qualified_sql_name(vmName) ||
                       ''' and p.grantee <> ''SYS''
                       and p.grantor = u.username';

    open priv_crs for privStmt;
    loop
    begin
      fetch priv_crs into grantee, privilege, grantable, user_id;
      exit when priv_crs%NOTFOUND;
      
      if (grantable = 'YES') then
        grantStmt := 'grant '||privilege||' on "MDSYS"."SEMV_'||
          sys.dbms_assert.qualified_sql_name(vmName)||'" to '||
          sys.dbms_assert.enquote_name(grantee, false)||' with grant option';
      else
        grantStmt := 'grant '||privilege||' on "MDSYS"."SEMV_'||
          sys.dbms_assert.qualified_sql_name(vmName)||'" to '||
          sys.dbms_assert.enquote_name(grantee, false);
      end if; 

      execute immediate '
        insert into mdsys.semvm_recreate$(stmt, stmtId) values (:1, :2)'
        using 'declare', stmtCtr;
      stmtCtr := stmtCtr + 1;

      execute immediate '
        insert into mdsys.semvm_recreate$(stmt, stmtId) values (:1, :2)'
        using '  dumV number;', stmtCtr;
      stmtCtr := stmtCtr + 1;

      execute immediate '
        insert into mdsys.semvm_recreate$(stmt, stmtId) values (:1, :2)'
        using '  curnum number;', stmtCtr;
      stmtCtr := stmtCtr + 1;

      execute immediate '
        insert into mdsys.semvm_recreate$(stmt, stmtId) values (:1, :2)'
        using 'begin', stmtCtr;
      stmtCtr := stmtCtr + 1;

      execute immediate '
        insert into mdsys.semvm_recreate$(stmt, stmtId) values (:1, :2)'
        using '  curnum := dbms_sql.open_cursor();', stmtCtr;
      stmtCtr := stmtCtr + 1;

      execute immediate '
        insert into mdsys.semvm_recreate$(stmt, stmtId) values (:1, :2)'
        using '  sys.dbms_sys_sql.parse_as_user(curnum, '''||
              grantStmt||''', dbms_sql.native, '||user_id||');', 
              stmtCtr;
      stmtCtr := stmtCtr + 1;

      execute immediate '
        insert into mdsys.semvm_recreate$(stmt, stmtId) values (:1, :2)'
        using '  dumv := dbms_sql.execute(curnum);', stmtCtr;
      stmtCtr := stmtCtr + 1;

      execute immediate '
        insert into mdsys.semvm_recreate$(stmt, stmtId) values (:1, :2)'
        using '  dbms_sql.close_cursor(curnum);', stmtCtr;
      stmtCtr := stmtCtr + 1;

      execute immediate '
        insert into mdsys.semvm_recreate$(stmt, stmtId) values (:1, :2)'
        using 'end;', stmtCtr;
      stmtCtr := stmtCtr + 1;

      execute immediate '
        insert into mdsys.semvm_recreate$(stmt, stmtId) values (:1, :2)'
        using '/', stmtCtr;
      stmtCtr := stmtCtr + 1;

    end; 
    end loop;
    close priv_crs;

    -- semu view --
    privStmt := 'select p.grantee, p.privilege, p.grantable, u.user_id
                 from dba_tab_privs p, all_users u
                 where p.owner = ''MDSYS'' and 
                       p.table_name = ''SEMU_' || 
                       sys.dbms_assert.qualified_sql_name(vmName) ||
                       ''' and p.grantee <> ''SYS''
                       and p.grantor = u.username';

    open priv_crs for privStmt;
    loop
    begin
      fetch priv_crs into grantee, privilege, grantable, user_id;
      exit when priv_crs%NOTFOUND;
      
      if (grantable = 'YES') then
        grantStmt := 'grant '||privilege||' on "MDSYS"."SEMU_'||
          sys.dbms_assert.qualified_sql_name(vmName)||'" to '||
          sys.dbms_assert.enquote_name(grantee, false)||' with grant option';
      else
        grantStmt := 'grant '||privilege||' on "MDSYS"."SEMU_'||
          sys.dbms_assert.qualified_sql_name(vmName)||'" to '||
          sys.dbms_assert.enquote_name(grantee, false);
      end if; 

      execute immediate '
        insert into mdsys.semvm_recreate$(stmt, stmtId) values (:1, :2)'
        using 'declare', stmtCtr;
      stmtCtr := stmtCtr + 1;

      execute immediate '
        insert into mdsys.semvm_recreate$(stmt, stmtId) values (:1, :2)'
        using '  dumV number;', stmtCtr;
      stmtCtr := stmtCtr + 1;

      execute immediate '
        insert into mdsys.semvm_recreate$(stmt, stmtId) values (:1, :2)'
        using '  curnum number;', stmtCtr;
      stmtCtr := stmtCtr + 1;

      execute immediate '
        insert into mdsys.semvm_recreate$(stmt, stmtId) values (:1, :2)'
        using 'begin', stmtCtr;
      stmtCtr := stmtCtr + 1;

      execute immediate '
        insert into mdsys.semvm_recreate$(stmt, stmtId) values (:1, :2)'
        using '  curnum := dbms_sql.open_cursor();', stmtCtr;
      stmtCtr := stmtCtr + 1;

      execute immediate '
        insert into mdsys.semvm_recreate$(stmt, stmtId) values (:1, :2)'
        using '  sys.dbms_sys_sql.parse_as_user(curnum, '''||
              grantStmt||''', dbms_sql.native, '||user_id||');', 
              stmtCtr;
      stmtCtr := stmtCtr + 1;

      execute immediate '
        insert into mdsys.semvm_recreate$(stmt, stmtId) values (:1, :2)'
        using '  dumv := dbms_sql.execute(curnum);', stmtCtr;
      stmtCtr := stmtCtr + 1;

      execute immediate '
        insert into mdsys.semvm_recreate$(stmt, stmtId) values (:1, :2)'
        using '  dbms_sql.close_cursor(curnum);', stmtCtr;
      stmtCtr := stmtCtr + 1;

      execute immediate '
        insert into mdsys.semvm_recreate$(stmt, stmtId) values (:1, :2)'
        using 'end;', stmtCtr;
      stmtCtr := stmtCtr + 1;

      execute immediate '
        insert into mdsys.semvm_recreate$(stmt, stmtId) values (:1, :2)'
        using '/', stmtCtr;
      stmtCtr := stmtCtr + 1;

    end; 
    end loop;
    close priv_crs;

  end;
  end loop;
  close outer_crs;
  
end;
/
show errors;

COMMIT;

SET LINESIZE 160;
SET PAGESIZE 20000;
SET ECHO OFF;
SET SERVEROUT OFF;
SET FEEDBACK OFF;
SET HEADING OFF;

SPOOL &1;

SELECT stmt
FROM MDSYS.SEMVM_RECREATE$
ORDER BY stmtId;

SPOOL OFF;

SET FEEDBACK ON;
SET HEADING ON;

DROP TABLE MDSYS.SEMVM_RECREATE$;

COMMIT;