-- Comments and Script Documentation
-- Script Name   : extract_schema_menu.sql
-- Purpose       : Control an interactive menu to extract schema dependencies and/or schema ddl
-- Requirements  : User calling script must be logged in with DBA privs
--                 The directory the script is being called FROM must be writeable by Oracle
-- Parameters    : None
-- Author        : Daniel W. Fink
-- Created Date  : July 12, 2004
-- Updates      
--               : 07/12/2004 dwf Original version
-- Comments      : 



COLUMN menu_opt FORMAT 99 HEADING '#'
COLUMN menu_action  FORMAT a40 HEADING 'Task'
COLUMN schema_owner FORMAT a40 HEADING 'Schema Owner'
COLUMN session_info FORMAT a80 HEADING 'Session Information' WORD_WRAP
SET VERIFY OFF FEEDBACK OFF PAGESIZE 45 LINESIZE 132
-- WHENEVER SQLERROR EXIT FAILURE
-- WHENEVER OSERROR EXIT FAILURE

PROMPT
PROMPT DBA level privileges required for this process
PROMPT

SELECT 'Database --> '||SYS_CONTEXT('USERENV', 'DB_NAME')||CHR(10)||
       'User name -> '||SYS_CONTEXT('USERENV', 'SESSION_USER')||CHR(10)||
       'DBA Privs -> '||SYS_CONTEXT('USERENV', 'ISDBA') session_info
FROM dual
/

COLUMN dba_option FORMAT a100 WORD_WRAP
SET PAGESIZE 0 FEEDBACK OFF
SPOOL next_step.sql
SELECT CASE WHEN SYS_CONTEXT('USERENV', 'ISDBA') = 'TRUE' THEN '-- Using DBA logon'
            ELSE 'PROMPT Not using DBA logon. Please login with DBA account'||CHR(10)||
                 'PAUSE Press <enter> to exit or <ctl-c> to terminate script'||CHR(10)||
                 'exit'
      END dba_option
FROM dual
/
SPOOL OFF
@@next_step

SET PAGESIZE 45
SELECT 0 menu_opt, '*** Enter 0 to exit ***' menu_action
FROM dual
UNION
SELECT 1 menu_opt, 'Perform Schema Dependency Check' menu_action
FROM dual
UNION
SELECT 2 menu_opt, 'Perform Schema Extract' menu_action
FROM dual
ORDER BY menu_opt
/


ACCEPT action_num NUMBER PROMPT 'Enter the number of the task to be performed: '

COLUMN script_step FORMAT a100 PRINT
SET PAGESIZE 0 LINESIZE 125

SPOOL next_step.sql
SELECT CASE WHEN &action_num =  0 
            THEN 'exit'
            WHEN &action_num > 2
            THEN '-- Invalid selection. Please try again.'||CHR(10)||'@extract_schema_menu.sql'
       END script_step
FROM dual
/

SPOOL OFF
@@next_step.sql

SELECT ROWNUM menu_opt, schema_owner
FROM (SELECT DISTINCT owner schema_owner
      FROM dba_objects
      WHERE owner NOT IN ('SYSTEM', 'SYS', 'OUTLN', 'DBSNMP', 'PUBLIC')
      ORDER BY owner)
UNION 
SELECT 0 menu_opt, '*** Enter 0 to exit ***' schema_owner
FROM dual
ORDER BY menu_opt
/

ACCEPT user_num NUMBER PROMPT 'Enter the number of the schema owner to be used: '

COLUMN script_step FORMAT a100 PRINT
SET PAGESIZE 0 LINESIZE 125

SPOOL next_step.sql
SELECT CASE WHEN &user_num =  0 
            THEN 'exit'
            WHEN (SELECT COUNT(*)
                  FROM (SELECT ROWNUM menu_opt, schema_owner
		        FROM (SELECT DISTINCT owner schema_owner
		              FROM dba_objects
                              WHERE owner NOT IN ('SYSTEM', 'SYS', 'OUTLN', 'DBSNMP', 'PUBLIC')
                              ORDER BY owner))
		  WHERE menu_opt = &user_num) =  0 
            THEN '-- Invalid selection. Please try again.'||CHR(10)||'@extract_schema_menu.sql'
            ELSE (SELECT CASE WHEN &action_num = 2 THEN '@extract_schema_ddl '|| schema_owner
                              WHEN &action_num = 1 THEN '@extract_schema_dependencies '||schema_owner
                         END
                  FROM (SELECT ROWNUM menu_opt, schema_owner
		        FROM (SELECT DISTINCT owner schema_owner
		              FROM dba_objects
                              WHERE owner NOT IN ('SYSTEM', 'SYS', 'OUTLN', 'DBSNMP', 'PUBLIC')))
		  WHERE menu_opt = &user_num)
       END script_step
FROM dual
/
SPOOL OFF

@@next_step