Friday, September 20, 2024

identify the character set of a Data Pump file

many times have a need to get information how export (dmp) file was created (what nls setup).
The following PL/SQL code enables you to identify the character set of a Data Pump file:
DECLARE
    et_directory_name VARCHAR2(30) := '';
                                         -- for example, 'DATA_PUMP_DIR'
    et_file_name       VARCHAR2(4000) := '';
                                         -- for example, 'EXPDAT.DMP'
    et_file_info       ku$_dumpfile_info;
    et_file_type       NUMBER;
BEGIN
  dbms_output.put_line('Scanning...');
   dbms_datapump.get_dumpfile_info
      ( filename => et_file_name
      , directory => et_directory_name
      , info_table => et_file_info
      , filetype => et_file_type );
   FOR i IN et_file_info.FIRST..et_file_info.LAST LOOP
      IF et_file_info.EXISTS(i) THEN
         IF et_file_info(i).item_code = 11 THEN
            dbms_output.put_line( 'Character set of the file is ' ||
                                       et_file_info(i).value );
         END IF;
      END IF;
   END LOOP;
END;

Hope this helps anyone.
Cheers!