clean up

DECLARE

  v_handle NUMBER;

  v_job_state VARCHAR2(30);

  v_status ku$_Status;

  v_le ku$_LogEntry;

  v_ind NUMBER;

BEGIN

  v_handle := DBMS_DATAPUMP.ATTACH(job_name => 'FULL_EXPORT_HEMANT');

  DBMS_DATAPUMP.GET_STATUS(

    handle => v_handle,

    mask => DBMS_DATAPUMP.KU$_STATUS_JOB_ERROR + DBMS_DATAPUMP.KU$_STATUS_JOB_STATUS,

    timeout => 0,

    job_state => v_job_state,

    status => v_status

  );

  DBMS_OUTPUT.PUT_LINE('Job state: ' || v_job_state);

  IF BITAND(v_status.mask, DBMS_DATAPUMP.KU$_STATUS_JOB_ERROR) != 0 THEN

    v_le := v_status.error;

    IF v_le IS NOT NULL THEN

      v_ind := v_le.FIRST;

      WHILE v_ind IS NOT NULL LOOP

        DBMS_OUTPUT.PUT_LINE(v_le(v_ind).LogText);

        v_ind := v_le.NEXT(v_ind);

      END LOOP;

    END IF;

  END IF;

  DBMS_DATAPUMP.DETACH(v_handle);

END;

/

================================================================

DECLARE

  v_sql VARCHAR2(4000);

  v_count NUMBER := 0;

  v_error_count NUMBER := 0;

  v_pre_count NUMBER;

  v_post_count NUMBER;

  v_schema_names VARCHAR2(4000) := UPPER(:schema_names); -- Bind variable, no default

  -- Cursor for all objects (tables, views, synonyms, sequences, procedures, functions, packages, triggers, types)

  CURSOR c_objects (p_schema IN VARCHAR2) IS

    SELECT owner, object_name, object_type,

           CASE object_type

             WHEN 'TABLE' THEN 'TABLE CASCADE CONSTRAINTS'

             WHEN 'VIEW' THEN 'VIEW'

             WHEN 'SYNONYM' THEN 'SYNONYM'

             WHEN 'SEQUENCE' THEN 'SEQUENCE'

             WHEN 'PROCEDURE' THEN 'PROCEDURE'

             WHEN 'FUNCTION' THEN 'FUNCTION'

             WHEN 'PACKAGE' THEN 'PACKAGE'

             WHEN 'TRIGGER' THEN 'TRIGGER'

             WHEN 'TYPE' THEN 'TYPE FORCE'

           END AS drop_type

    FROM all_objects

    WHERE owner = p_schema

      AND object_type IN ('TABLE', 'VIEW', 'SYNONYM', 'SEQUENCE', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'TRIGGER', 'TYPE')

      AND generated = 'N'

      AND (object_type != 'TABLE' OR temporary = 'N')

    ORDER BY CASE WHEN object_type = 'TABLE' THEN 1 ELSE 2 END; -- Tables first

  -- Cursor for counting objects

  CURSOR c_count (p_schema IN VARCHAR2) IS

    SELECT COUNT(*) AS obj_count

    FROM all_objects

    WHERE owner = p_schema

      AND object_type IN ('TABLE', 'VIEW', 'SYNONYM', 'SEQUENCE', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'TRIGGER', 'TYPE')

      AND generated = 'N'

      AND (object_type != 'TABLE' OR temporary = 'N');

BEGIN

  -- Check for valid schema input

  IF v_schema_names IS NULL OR TRIM(v_schema_names) IS NULL THEN

    RAISE_APPLICATION_ERROR(-20001, 'Schema names required (e.g., SCHEMA1,SCHEMA2).');

  END IF;

  -- Create simple log table

  BEGIN

    EXECUTE IMMEDIATE 'CREATE TABLE drop_log (log_message VARCHAR2(4000), log_time TIMESTAMP DEFAULT SYSTIMESTAMP)';

  EXCEPTION

    WHEN OTHERS THEN

      IF SQLCODE = -955 THEN NULL; -- Table exists

      ELSE RAISE;

      END IF;

  END;

  -- Enable parallel DDL

  BEGIN

    EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DDL';

  EXCEPTION

    WHEN OTHERS THEN NULL;

  END;

  -- Process each schema

  FOR i IN 1 .. (REGEXP_COUNT(v_schema_names, ',') + 1) LOOP

    DECLARE

      v_schema VARCHAR2(128) := TRIM(REGEXP_SUBSTR(v_schema_names, '[^,]+', 1, i));

    BEGIN

      IF v_schema IS NULL THEN CONTINUE; END IF;

      -- Verify schema exists

      EXECUTE IMMEDIATE 'SELECT 1 FROM all_users WHERE username = :1' INTO v_count USING v_schema;

      -- Count objects before drop

      OPEN c_count(v_schema);

      FETCH c_count INTO v_pre_count;

      CLOSE c_count;

      EXECUTE IMMEDIATE 'INSERT INTO drop_log (log_message) VALUES (:1)'

        USING 'Pre-drop count for ' || v_schema || ': ' || v_pre_count || ' objects';

      -- Drop all objects

      FOR rec IN c_objects(v_schema) LOOP

        BEGIN

          v_sql := 'DROP ' || rec.drop_type || ' ' || DBMS_ASSERT.SCHEMA_NAME(rec.owner) || '.' || DBMS_ASSERT.SQL_OBJECT_NAME(rec.object_name);

          EXECUTE IMMEDIATE v_sql;

          v_count := v_count + 1;

        EXCEPTION

          WHEN OTHERS THEN

            v_error_count := v_error_count + 1;

            EXECUTE IMMEDIATE 'INSERT INTO drop_log (log_message) VALUES (:1)'

              USING 'Error dropping ' || rec.object_type || ' ' || rec.owner || '.' || rec.object_name || ': ' || SQLERRM;

        END;

      END LOOP;

      -- Count objects after drop

      OPEN c_count(v_schema);

      FETCH c_count INTO v_post_count;

      CLOSE c_count;

      EXECUTE IMMEDIATE 'INSERT INTO drop_log (log_message) VALUES (:1)'

        USING 'Post-drop count for ' || v_schema || ': ' || v_post_count || ' objects';

    EXCEPTION

      WHEN OTHERS THEN

        v_error_count := v_error_count + 1;

        EXECUTE IMMEDIATE 'INSERT INTO drop_log (log_message) VALUES (:1)'

          USING 'Error processing schema ' || v_schema || ': ' || SQLERRM;

    END;

  END LOOP;

  -- Log summary

  EXECUTE IMMEDIATE 'INSERT INTO drop_log (log_message) VALUES (:1)'

    USING 'Dropped ' || v_count || ' objects. Errors: ' || v_error_count;

  DBMS_OUTPUT.PUT_LINE('Dropped ' || v_count || ' objects. Errors: ' || v_error_count);

EXCEPTION

  WHEN OTHERS THEN

    EXECUTE IMMEDIATE 'INSERT INTO drop_log (log_message) VALUES (:1)'

      USING 'Script error: ' || SQLERRM;

    RAISE;

END;

/