SET LINES 200 PAGES 2000
SET LONG 9999999
SET SERVEROUTPUT ON
DECLARE
  -- parameters
  p_dir  VARCHAR2(128) := 'DP_DIR';
  p_file VARCHAR2(128) := 'expdp_HERO_20250421_01.dmp';

  v_separator   VARCHAR2(80) := '--------------------------------------' ||
                                '--------------------------------------';
  v_path        all_directories.directory_path%TYPE := '?';
  v_filetype    NUMBER;
  v_fileversion VARCHAR2(15);
  v_info_table  sys.ku$_dumpfile_info;

  TYPE valtype IS VARRAY(23) OF VARCHAR2(2048);
  var_values    valtype := valtype();
  no_file_found EXCEPTION;
  PRAGMA exception_init(no_file_found, -39211);

BEGIN
  -- Dump file details
  DBMS_OUTPUT.PUT_LINE(v_separator);
  DBMS_OUTPUT.PUT_LINE('Purpose..: Obtain details about export dumpfile.        Version: 18-DEC-2013');
  DBMS_OUTPUT.PUT_LINE('Required.: RDBMS version: 10.2.0.1.0 or higher');
  DBMS_OUTPUT.PUT_LINE('.          Export dumpfile version: 7.3.4.0.0 or higher');
  DBMS_OUTPUT.PUT_LINE('.          Export Data Pump dumpfile version: 10.1.0.1.0 or higher');
  DBMS_OUTPUT.PUT_LINE('Usage....: Execute this block with directory and dumpfile name');
  DBMS_OUTPUT.PUT_LINE('Example..: Set p_dir := ''MY_DIR'' and p_file := ''expdp_s.dmp''');
  DBMS_OUTPUT.PUT_LINE(v_separator);
  DBMS_OUTPUT.PUT_LINE('Filename.: ' || p_file);
  DBMS_OUTPUT.PUT_LINE('Directory: ' || p_dir);

  -- Retrieve directory path
  SELECT directory_path INTO v_path
    FROM all_directories
   WHERE directory_name = UPPER(p_dir);

  -- Retrieve export dumpfile info
  DBMS_DATAPUMP.GET_DUMPFILE_INFO(
    filename   => p_file,
    directory  => UPPER(p_dir),
    info_table => v_info_table,
    filetype   => v_filetype
  );

  var_values.EXTEND(23);
  FOR i IN 1 .. 23 LOOP
    BEGIN
      SELECT value INTO var_values(i)
        FROM TABLE(v_info_table)
       WHERE item_code = i;
    EXCEPTION
      WHEN OTHERS THEN
        var_values(i) := '';
    END;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Disk Path: ' || v_path);

  IF v_filetype >= 1 THEN
    -- Charset ID to name
    BEGIN
      SELECT var_values(5) || ' (' || NLS_CHARSET_NAME(var_values(5)) || ')'
        INTO var_values(5)
        FROM dual;
    EXCEPTION
      WHEN OTHERS THEN NULL;
    END;

    IF v_filetype = 2 THEN
      DBMS_OUTPUT.PUT_LINE('Filetype.: ' || v_filetype || ' (Original Export dumpfile)');
      DBMS_OUTPUT.PUT_LINE(v_separator);
      SELECT DECODE(var_values(13), '0', '0 (Conventional Path)', '1', '1 (Direct Path)', var_values(13))
        INTO var_values(13) FROM dual;
      DBMS_OUTPUT.PUT_LINE('...Characterset ID of source db..: ' || var_values(5));
      DBMS_OUTPUT.PUT_LINE('...Direct Path Export Mode.......: ' || var_values(13));
      DBMS_OUTPUT.PUT_LINE('...Export Version................: ' || var_values(15));

    ELSIF v_filetype = 1 OR v_filetype = 3 THEN
      SELECT SUBSTR(var_values(1), 1, 15) INTO v_fileversion FROM dual;
      SELECT DECODE(var_values(1),
                    '0.1', '0.1 (Oracle10g Release 1: 10.1.0.x)',
                    '1.1', '1.1 (Oracle10g Release 2: 10.2.0.x)',
                    '2.1', '2.1 (Oracle11g Release 1: 11.1.0.x)',
                    '3.1', '3.1 (Oracle11g Release 2: 11.2.0.x)',
                    '4.1', '4.1 (Oracle12c Release 1: 12.1.0.x)',
                    var_values(1)) INTO var_values(1) FROM dual;
      SELECT DECODE(var_values(2), '0', '0 (No)', '1', '1 (Yes)', var_values(2)) INTO var_values(2) FROM dual;
      SELECT DECODE(var_values(14), '0', '0 (No)', '1', '1 (Yes)', var_values(14)) INTO var_values(14) FROM dual;
      SELECT DECODE(var_values(18), '0', '0 (No)', '1', '1 (Yes)', var_values(18)) INTO var_values(18) FROM dual;
      SELECT DECODE(var_values(19), '0', '0 (No)', '1', '1 (Yes)', var_values(19)) INTO var_values(19) FROM dual;
      SELECT DECODE(var_values(20), '0', '0 (No)', '1', '1 (Yes)', var_values(20)) INTO var_values(20) FROM dual;
      SELECT DECODE(var_values(21), '0', '0 (No)', '1', '1 (Yes)', var_values(21)) INTO var_values(21) FROM dual;
      SELECT DECODE(var_values(22),
                    '1', '1 (Unknown)',
                    '2', '2 (None)',
                    '3', '3 (Password)',
                    '4', '4 (Password and Wallet)',
                    '5', '5 (Wallet)',
                    var_values(22)) INTO var_values(22) FROM dual;
      SELECT DECODE(var_values(23),
                    '2', '2 (None)',
                    '3', '3 (Basic)',
                    '4', '4 (Low)',
                    '5', '5 (Medium)',
                    '6', '6 (High)',
                    var_values(23)) INTO var_values(23) FROM dual;

      IF v_filetype = 1 THEN
        DBMS_OUTPUT.PUT_LINE('Filetype.: ' || v_filetype || ' (Export Data Pump dumpfile)');
        DBMS_OUTPUT.PUT_LINE(v_separator);
        DBMS_OUTPUT.PUT_LINE('...Database Job Version..........: ' || var_values(15));
        DBMS_OUTPUT.PUT_LINE('...Internal Dump File Version....: ' || var_values(1));
        DBMS_OUTPUT.PUT_LINE('...Creation Date.................: ' || var_values(6));
        DBMS_OUTPUT.PUT_LINE('...File Number (in dump file set): ' || var_values(4));
        DBMS_OUTPUT.PUT_LINE('...Master Present in dump file...: ' || var_values(2));

        IF DBMS_DATAPUMP.KU$_DFHDR_MAX_ITEM_CODE > 15 AND v_fileversion >= '2.1' THEN
          DBMS_OUTPUT.PUT_LINE('...Master in how many dump files.: ' || var_values(16));
          DBMS_OUTPUT.PUT_LINE('...Master Piece Number in file...: ' || var_values(17));
        END IF;

        DBMS_OUTPUT.PUT_LINE('...Operating System of source db.: ' || var_values(9));

        IF v_fileversion >= '2.1' THEN
          DBMS_OUTPUT.PUT_LINE('...Instance Name of source db....: ' || var_values(10));
        END IF;

        DBMS_OUTPUT.PUT_LINE('...Characterset ID of source db..: ' || var_values(5));
        DBMS_OUTPUT.PUT_LINE('...Language Name of characterset.: ' || var_values(11));
        DBMS_OUTPUT.PUT_LINE('...Job Name......................: ' || var_values(8));
        DBMS_OUTPUT.PUT_LINE('...GUID (unique job identifier)..: ' || var_values(3));
        DBMS_OUTPUT.PUT_LINE('...Block size dump file (bytes)..: ' || var_values(12));
        DBMS_OUTPUT.PUT_LINE('...Metadata Compressed...........: ' || var_values(14));

        IF DBMS_DATAPUMP.KU$_DFHDR_MAX_ITEM_CODE > 15 THEN
          DBMS_OUTPUT.PUT_LINE('...Data Compressed...............: ' || var_values(18));

          IF DBMS_DATAPUMP.KU$_DFHDR_MAX_ITEM_CODE > 22 AND v_fileversion >= '4.1' THEN
            DBMS_OUTPUT.PUT_LINE('...Compression Algorithm.........: ' || var_values(23));
          END IF;

          DBMS_OUTPUT.PUT_LINE('...Metadata Encrypted............: ' || var_values(19));
          DBMS_OUTPUT.PUT_LINE('...Table Data Encrypted..........: ' || var_values(20));
          DBMS_OUTPUT.PUT_LINE('...Column Data Encrypted.........: ' || var_values(21));
          DBMS_OUTPUT.PUT_LINE('...Encryption Mode...............: ' || var_values(22));
        END IF;

      ELSE
        DBMS_OUTPUT.PUT_LINE('Filetype.: ' || v_filetype || ' (External Table dumpfile)');
        DBMS_OUTPUT.PUT_LINE(v_separator);
        DBMS_OUTPUT.PUT_LINE('...Database Job Version..........: ' || var_values(15));
        DBMS_OUTPUT.PUT_LINE('...Internal Dump File Version....: ' || var_values(1));
        DBMS_OUTPUT.PUT_LINE('...Creation Date.................: ' || var_values(6));
        DBMS_OUTPUT.PUT_LINE('...File Number (in dump file set): ' || var_values(4));
        DBMS_OUTPUT.PUT_LINE('...Operating System of source db.: ' || var_values(9));

        IF v_fileversion >= '2.1' THEN
          DBMS_OUTPUT.PUT_LINE('...Instance Name of source db....: ' || var_values(10));
        END IF;

        DBMS_OUTPUT.PUT_LINE('...Characterset ID of source db..: ' || var_values(5));
        DBMS_OUTPUT.PUT_LINE('...Language Name of characterset.: ' || var_values(11));
        DBMS_OUTPUT.PUT_LINE('...GUID (unique job identifier)..: ' || var_values(3));
        DBMS_OUTPUT.PUT_LINE('...Block size dump file (bytes)..: ' || var_values(12));

        IF DBMS_DATAPUMP.KU$_DFHDR_MAX_ITEM_CODE > 15 THEN
          DBMS_OUTPUT.PUT_LINE('...Data Compressed...............: ' || var_values(18));

          IF DBMS_DATAPUMP.KU$_DFHDR_MAX_ITEM_CODE > 22 AND v_fileversion >= '4.1' THEN
            DBMS_OUTPUT.PUT_LINE('...Compression Algorithm.........: ' || var_values(23));
          END IF;

          DBMS_OUTPUT.PUT_LINE('...Table Data Encrypted..........: ' || var_values(20));
          DBMS_OUTPUT.PUT_LINE('...Encryption Mode...............: ' || var_values(22));
        END IF;
      END IF;

      DBMS_OUTPUT.PUT_LINE('...Internal Flag Values..........: ' || var_values(7));
      DBMS_OUTPUT.PUT_LINE('...Max Items Code (Info Items)...: ' ||
                          DBMS_DATAPUMP.KU$_DFHDR_MAX_ITEM_CODE);
    END IF;
  ELSE
    DBMS_OUTPUT.PUT_LINE('Filetype.: ' || v_filetype);
    DBMS_OUTPUT.PUT_LINE(v_separator);
    DBMS_OUTPUT.PUT_LINE('ERROR....: Not an export dumpfile.');
  END IF;

  DBMS_OUTPUT.PUT_LINE(v_separator);

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Disk Path: ?');
    DBMS_OUTPUT.PUT_LINE('Filetype.: ?');
    DBMS_OUTPUT.PUT_LINE(v_separator);
    DBMS_OUTPUT.PUT_LINE('ERROR....: Directory Object does not exist.');
    DBMS_OUTPUT.PUT_LINE(v_separator);

  WHEN no_file_found THEN
    DBMS_OUTPUT.PUT_LINE('Disk Path: ' || v_path);
    DBMS_OUTPUT.PUT_LINE('Filetype.: ?');
    DBMS_OUTPUT.PUT_LINE(v_separator);
    DBMS_OUTPUT.PUT_LINE('ERROR....: File does not exist.');
    DBMS_OUTPUT.PUT_LINE(v_separator);
END;
/