Help - Search - Members - Calendar
Full Version: copy files using PL/SQL
Oracle DBA Forums > Oracle > Oracle Forum
jetpilot
Hi All

I need to copy files inside PL/SQL, i found that this can be done using UTL_FILE package, my script looks something like:

BEGIN
UTL_FILE.FCOPY('C', 'test.sql' , 'C', 'test2.sql', 1, 6);
END;
/

but i get the following error:
*
ERROR at line 1:
ORA-29280: invalid directory path
ORA-06512: at "SYS.UTL_FILE", line 262
ORA-06512: at "SYS.UTL_FILE", line 1139
ORA-06512: at line 2

Anyone has an idea what is wrong here? Where is this UTL_FILE package located?Do i need some additional download?

Tnx.
Asma
QUOTE (jetpilot @ Jul 9 2008, 05:46 PM) *
Hi All

I need to copy files inside PL/SQL, i found that this can be done using UTL_FILE package, my script looks something like:

BEGIN
UTL_FILE.FCOPY('C', 'test.sql' , 'C', 'test2.sql', 1, 6);
END;
/

but i get the following error:
*
ERROR at line 1:
ORA-29280: invalid directory path
ORA-06512: at "SYS.UTL_FILE", line 262
ORA-06512: at "SYS.UTL_FILE", line 1139
ORA-06512: at line 2

Anyone has an idea what is wrong here? Where is this UTL_FILE package located?Do i need some additional download?

Tnx.


>> ORA-29280: invalid directory path ; pretty obvious.

following link might help you.

http://www.pafumi.net/utl_file_package9i.html

you've another option; DBMS_FILE_TRANSFER.copy_file- if you're on 10g.
jetpilot
Tnx, i fix the problem
jetpilot
But, there's one thing that i still don't understand

This one works fine:
---------------------------
CREATE OR REPLACE DIRECTORY SOURCE_DIR AS 'c:\test\src';
CREATE OR REPLACE DIRECTORY DEST_DIR AS 'c:\test\dest';

BEGIN
dbms_file_transfer.copy_file(source_directory_object =>
'SOURCE_DIR', source_file_name => 'test.dbf',
destination_directory_object => 'DEST_DIR',
destination_file_name => 'filet.dbf');
END;
/

But this one doesn't:
---------------------------------------------------------------

DECLARE
src VARCHAR2(100);
dest VARCHAR2(100);
BEGIN
src := 'c:\test\src';
dest := 'c:\test\dest';
dbms_file_transfer.copy_file(source_directory_object =>
'src', source_file_name => 'test.dbf',
destination_directory_object => 'dest',
destination_file_name => 'filet.dbf');
END;
/

*
ERROR at line 1:
ORA-06564: object SRC does not exist
ORA-06512: at "SYS.DBMS_FILE_TRANSFER", line 84


I appreciate any help, tnx
dave
QUOTE (jetpilot @ Jul 9 2008, 08:45 PM) *
But, there's one thing that i still don't understand

This one works fine:
---------------------------
CREATE OR REPLACE DIRECTORY SOURCE_DIR AS 'c:\test\src';
CREATE OR REPLACE DIRECTORY DEST_DIR AS 'c:\test\dest';

BEGIN
dbms_file_transfer.copy_file(source_directory_object =>
'SOURCE_DIR', source_file_name => 'test.dbf',
destination_directory_object => 'DEST_DIR',
destination_file_name => 'filet.dbf');
END;
/

But this one doesn't:
---------------------------------------------------------------

DECLARE
src VARCHAR2(100);
dest VARCHAR2(100);
BEGIN
src := 'c:\test\src';
dest := 'c:\test\dest';
dbms_file_transfer.copy_file(source_directory_object =>
'src', source_file_name => 'test.dbf',
destination_directory_object => 'dest',
destination_file_name => 'filet.dbf');
END;
/

*
ERROR at line 1:
ORA-06564: object SRC does not exist
ORA-06512: at "SYS.DBMS_FILE_TRANSFER", line 84
I appreciate any help, tnx


because you have to supply the name of the directory object you created and not the actual path
jetpilot
What if the file that i want to copy is already in created directory, meaning i don't create any directory, directory exists?
dave
then just reference the oracle directory object
wangfans
QUOTE (jetpilot @ Jul 9 2008, 07:45 PM) *
But, there's one thing that i still don't understand

This one works fine:
---------------------------
CREATE OR REPLACE DIRECTORY SOURCE_DIR AS 'c:\test\src';
CREATE OR REPLACE DIRECTORY DEST_DIR AS 'c:\test\dest';

BEGIN
dbms_file_transfer.copy_file(source_directory_object =>
'SOURCE_DIR', source_file_name => 'test.dbf',
destination_directory_object => 'DEST_DIR',
destination_file_name => 'filet.dbf');
END;
/

But this one doesn't:
---------------------------------------------------------------

DECLARE
src VARCHAR2(100);
dest VARCHAR2(100);
BEGIN
src := 'c:\test\src';
dest := 'c:\test\dest';
dbms_file_transfer.copy_file(source_directory_object =>
'src', source_file_name => 'test.dbf',
destination_directory_object => 'dest',
destination_file_name => 'filet.dbf');
END;
/

*
ERROR at line 1:
ORA-06564: object SRC does not exist
ORA-06512: at "SYS.DBMS_FILE_TRANSFER", line 84
I appreciate any help, tnx


Because 'dbms_file_transfer.copy_file(source_directory_object =>' need one directory value other than one common variable value
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.