File dump procedure : Utility Procedure « Stored Procedure Function « Oracle PL / SQL






File dump procedure

 
SQL>
SQL> create or replace procedure file_dump( p_directory in varchar2,p_filename  in varchar2 )
  2  as
  3      type array is table of varchar2(5) index by binary_integer;
  4
  5      l_chars  array;
  6      l_bfile  bfile;
  7      l_buffsize number default 15;
  8      l_data   varchar2(30);
  9      l_len    number;
 10      l_offset number default 1;
 11      l_char   char(1);
 12  begin
 13      l_chars(0)  := '\0';
 14      l_chars(13) := '\r';
 15      l_chars(10) := '\n';
 16      l_chars(9)  := '\t';
 17
 18      l_bfile := bfilename( p_directory, p_filename );
 19      dbms_lob.fileopen( l_bfile );
 20
 21      l_len := dbms_lob.getlength( l_bfile );
 22      while( l_offset < l_len )
 23      loop
 24          l_data := utl_raw.cast_to_varchar2(dbms_lob.substr( l_bfile, l_buffsize, l_offset ));
 25
 26          for i in 1 .. length(l_data)
 27          loop
 28              l_char := substr(l_data,i,1);
 29
 30              if ascii( l_char ) between 32 and 126
 31              then
 32                  dbms_output.put( lpad(l_char,3) );
 33              elsif ( l_chars.exists( ascii(l_char) ) )
 34              then
 35                  dbms_output.put( lpad( l_chars(ascii(l_char)), 3 ) );
 36              else
 37                  dbms_output.put( to_char(ascii(l_char),'0X') );
 38              end if;
 39          end loop;
 40          dbms_output.new_line;
 41
 42          l_offset := l_offset + l_buffsize;
 43      end loop;
 44      dbms_lob.close( l_bfile );
 45  end;
 46  /

Procedure created.

SQL>
SQL>
SQL> exec file_dump( 'MY_FILES', 'demo17.dat' );
BEGIN file_dump( 'MY_FILES', 'demo17.dat' ); END;

*
ERROR at line 1:
ORA-22285: non-existent directory or file for
FILEOPEN operation
ORA-06512: at "SYS.DBMS_LOB", line 523
ORA-06512: at "JAVA2S.FILE_DUMP", line 19
ORA-06512: at line 1


SQL>
SQL> --

 








Related examples in the same category

1.Assert procedure
2.Use stored procedure to output table content
3.emp table lookup
4.Use stored procedure to log message
5.Create a stored procedure to measure a table usage
6.Procedure does not count space
7.Copy tables
8.Count credits