-- Usage @copyTablesToBackupAccount.sql
-- Create copies of the following ODMRSYS tables into <backup account>
--   ODMR$PROJECTS  (project data)
--   ODMR$WORKFLOWS (workflow data)
--   ODMR$REPOSITORY_PROPERTIES (repository properties, may contain user entered data)
--   ODMR$WF_LOG (workflow event log history)
--   ODMR$USER_GRANTS_BACKUP (backup of all users account names that were granted access to ODMRSYS. This was generated by the createusersgrantbackup.sql script.
-- Example: @copyTablesToBackupAccount.sql <backup account>

WHENEVER SQLERROR EXIT SQL.SQLCODE;

EXECUTE dbms_output.put_line('Start copy ODMRSYS tables to backup account. ' || systimestamp);

DEFINE TEMP_ACCT = &&1

BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE "&TEMP_ACCT"."ODMR$PROJECTS_BK" PURGE';
EXCEPTION WHEN OTHERS THEN
  NULL;
END;
/
CREATE TABLE "&TEMP_ACCT"."ODMR$PROJECTS_BK"
AS SELECT * FROM ODMRSYS.ODMR$PROJECTS;

BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE "&TEMP_ACCT"."ODMR$WORKFLOWS_BK" PURGE';
EXCEPTION WHEN OTHERS THEN
  NULL;
END;
/
CREATE TABLE "&TEMP_ACCT"."ODMR$WORKFLOWS_BK"
AS SELECT 
  x.WORKFLOW_ID,
  x.WORKFLOW_NAME,
  x.WORKFLOW_DATA.createNonSchemaBasedXML() "WORKFLOW_DATA",
  x.CHAIN_NAME,
  x.RUN_MODE,
  x.STATUS,
  x.CREATION_TIME,
  x.LAST_UPDATED_TIME,
  x.COMMENTS,
  x.PROJECT_ID
  FROM ODMRSYS.ODMR$WORKFLOWS x;

BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE "&TEMP_ACCT"."ODMR$WORKFLOW_JOBS_BK" PURGE';
EXCEPTION WHEN OTHERS THEN
  NULL;
END;
/
CREATE TABLE "&TEMP_ACCT"."ODMR$WORKFLOW_JOBS_BK"
AS SELECT * FROM ODMRSYS.ODMR$WORKFLOW_JOBS;

BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE "&TEMP_ACCT"."ODMR$WF_LOG_BK" PURGE';
EXCEPTION WHEN OTHERS THEN
  NULL;
END;
/
CREATE TABLE "&TEMP_ACCT"."ODMR$WF_LOG_BK"
AS SELECT * FROM ODMRSYS.ODMR$WF_LOG;

BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE "&TEMP_ACCT"."ODMR$REPOSITORY_PROPERTIES_BK" PURGE';
EXCEPTION WHEN OTHERS THEN
  NULL;
END;
/
CREATE TABLE "&TEMP_ACCT"."ODMR$REPOSITORY_PROPERTIES_BK"
AS SELECT * FROM ODMRSYS.ODMR$REPOSITORY_PROPERTIES;

BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE "&TEMP_ACCT"."ODMR$USER_GRANTS_BACKUP_BK" PURGE';
EXCEPTION WHEN OTHERS THEN
  NULL;
END;
/
CREATE TABLE "&TEMP_ACCT"."ODMR$USER_GRANTS_BACKUP_BK"
AS SELECT * FROM ODMRSYS.ODMR$USER_GRANTS_BACKUP;

EXECUTE dbms_output.put_line('Finished copy ODMRSYS tables to backup account. ' || systimestamp);
