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:
Post a Comment