Monday, August 13, 2012

DBMS_FILE_TRANSFER

I read about this command, which allows you to copy OS files in SQL*Plus, and decided to try it out on Oracle 11.2. First I created a file:
 
Solaris > pwd
/export/home/oracle/andrew/dir1
Solaris > echo "Andrew was here" > file1
Solaris >
 
Then I tried to use the command but it failed:
 
SQL> create or replace directory source_dir as
  2  '/export/home/oracle/andrew/dir1'
  3  /
 
Directory created.
 
SQL> create or replace directory target_dir as
  2  '/export/home/oracle/andrew/dir2'
  3  /
 
Directory created.
 
SQL> begin
  2  dbms_file_transfer.copy_file(
  3  source_directory_object=>'source_dir',
  4  source_file_name=>'file1',
  5  destination_directory_object=>'target_dir',
  6  destination_file_name=>'file2');
  7  end;
  8  /
begin
*
ERROR at line 1:
ORA-19505: failed to identify file
"/export/home/oracle/andrew/dir1/file1"
ORA-27046: file size is not a multiple of logical
block size
Additional information: 1
ORA-06512: at "SYS.DBMS_FILE_TRANSFER", line 84
ORA-06512: at "SYS.DBMS_FILE_TRANSFER", line 193
ORA-06512: at line 2
 
SQL>
 
I found some Oracle 10.2 documentation for the command and it said:
The size of the copied file must be a multiple of 512 bytes. I checked the size of my test file but it was only 16 bytes:
 
Solaris > pwd
/export/home/oracle/andrew/dir1
Solaris > ls -l
total 2
-rw-r--r--   1 oracle   dba           16 Aug 13 12:17 file1
Solaris >
 
I recreated the file with a size of 512 bytes:
 
Solaris > pwd
/export/home/oracle/andrew/dir1
Solaris > dd if=/dev/zero of=file1 bs=1 count=512
512+0 records in
512+0 records out
Solaris > ls -l
total 2
-rw-r--r--   1 oracle   dba          512 Aug 13 12:28 file1
Solaris >
 
When I tried the command again, it worked:
 
SQL> begin
  2  dbms_file_transfer.copy_file(
  3  source_directory_object=>'source_dir',
  4  source_file_name=>'file1',
  5  destination_directory_object=>'target_dir',
  6  destination_file_name=>'file2');
  7  end;
  8  /
PL/SQL procedure successfully completed.
SQL>
 
... and UNIX could see no difference between the input and output files:
 
Solaris > pwd
/export/home/oracle/andrew/dir1
Solaris > diff file1 \
> /export/home/oracle/andrew/dir2/file2
Solaris >

No comments: