OraFAQ Forum: SQL & PL/SQL » Move File from directories to another directories!

Home » SQL & PL/SQL » SQL & PL/SQL » Move File from directories to another directories! (Plsql)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Move File from directories to another directories! [message #515049]

Thu, 07 July 2011 22:34

dophuong_cs
Messages: 92
Registered: May 2011
Location: Viet Nam

Member

I have two directories follow:
- The first directories1: "/home/oracle/xml" obitan file as: abc.xml, emp.xml...etc
- The second directories2: "/home/oracle/xml/move" is empty.

I want to move files from directories1 to directories2.How do i do?

Report message to a moderator

Re: Move File from directories to another directories! [message #515051 is a reply to message #515049]

Thu, 07 July 2011 22:36

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I want to move files from directories1 to directories2.How do i do?

mv /home/oracle/xml/*xml /home/oracle/xml/move/

Report message to a moderator

Re: Move File from directories to another directories! [message #515052 is a reply to message #515051]

Thu, 07 July 2011 22:40

dophuong_cs
Messages: 92
Registered: May 2011
Location: Viet Nam

Member

i want to move file in plsql, no on linux.Ok

Report message to a moderator

Re: Move File from directories to another directories! [message #515053 is a reply to message #515052]

Thu, 07 July 2011 22:43

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>i want to move file in plsql,
PL/SQL runs inside RDBMS engine & has NO direct contact with OS filesystem.

Report message to a moderator

Re: Move File from directories to another directories! [message #515054 is a reply to message #515053]

Thu, 07 July 2011 23:00

dophuong_cs
Messages: 92
Registered: May 2011
Location: Viet Nam

Member

i want to a example for my question. Please help me!

Report message to a moderator

Re: Move File from directories to another directories! [message #515061 is a reply to message #515054]

Fri, 08 July 2011 00:33

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

The following uses the get_dir_list function from one of your previous posts. It loops through a select from that function, copying one file at a time. The directory object names must be used in upper case. Substitute your own directory paths for mine in the example below when creating the directory objects. You may also need to grant read and write privileges through Oracle and the operating system.

-- starting files in directories:

SCOTT@orcl_11gR2> host dir c:\my_oracle_files\*.xml
 Volume in drive C has no label.
 Volume Serial Number is F0BB-93C9

 Directory of c:\my_oracle_files

12/22/2010  11:57 AM               402 cadd_pass.xml
12/23/2010  02:27 AM               401 test.xml
06/15/2011  10:01 PM               596 your_file.xml
               3 File(s)          1,399 bytes
               0 Dir(s)  405,547,393,024 bytes free

SCOTT@orcl_11gR2> host dir c:\my_oracle_files\move\*.xml
 Volume in drive C has no label.
 Volume Serial Number is F0BB-93C9

 Directory of c:\my_oracle_files\move

File Not Found

-- substitute your own directory paths when creating the directory objects:

SCOTT@orcl_11gR2> create or replace directory my_dir as 'c:\my_oracle_files'
  2  /

Directory created.

SCOTT@orcl_11gR2> create or replace directory move_dir as 'c:\my_oracle_files\move'
  2  /

Directory created.

-- copy the files:

SCOTT@orcl_11gR2> begin
  2    for r in
  3      (select *
  4       from table (sys.get_dir_list ('C:\my_oracle_files\*.xml')))
  5    loop
  6      utl_file.fcopy
  7        ('MY_DIR', -- must be in upper case
  8         r.column_value,
  9         'MOVE_DIR', -- must be in upper case
 10         r.column_value);
 11    end loop;
 12  end;
 13  /

PL/SQL procedure successfully completed.

-- results:

SCOTT@orcl_11gR2> host dir c:\my_oracle_files\move\*.xml
 Volume in drive C has no label.
 Volume Serial Number is F0BB-93C9

 Directory of c:\my_oracle_files\move

07/07/2011  10:26 PM               402 cadd_pass.xml
07/07/2011  10:26 PM               401 test.xml
07/07/2011  10:26 PM               596 your_file.xml
               3 File(s)          1,399 bytes
               0 Dir(s)  405,547,393,024 bytes free

SCOTT@orcl_11gR2>

Report message to a moderator

Re: Move File from directories to another directories! [message #515062 is a reply to message #515061]

Fri, 08 July 2011 00:38

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

What I provided above just copies the files. To accomplish the same as a move, you would then need to delete the originals. You could delete using utl_file.fremove. You might want to confirm that the copies were made first, before deleting the originals.

Report message to a moderator

Re: Move File from directories to another directories! [message #515063 is a reply to message #515052]

Fri, 08 July 2011 00:49

Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

dophuong_cs wrote on Fri, 08 July 2011 05:40

i want to move file in plsql, no on linux.Ok

And your Oracle version is?

ALWAYS post it, with 4 decimals. It is a prerequisite and not optional.

Regards
Michel

Report message to a moderator

Re: Move File from directories to another directories! [message #515086 is a reply to message #515063]

Fri, 08 July 2011 01:41

dophuong_cs
Messages: 92
Registered: May 2011
Location: Viet Nam

Member

CREATE OR REPLACE Procedure Pro_Move_File_Xml(c_File_Name_In Varchar2) Is
  c_Ten_File Varchar2(1000);
Begin
	c_Ten_File := c_File_Name_In || '.xml';
	Utl_File.Fcopy('XML', c_Ten_File, 'XMLDIR',c_Ten_File);
	Utl_File.Fremove('XML', c_Ten_File);
End Pro_Move_File_Xml;

when i run proceduere that, see errors in file attach.
Please help me!

Attachment: errors_utl_file.jpg
(Size: 42.60KB, Downloaded 1247 times)

Report message to a moderator

Re: Move File from directories to another directories! [message #515109 is a reply to message #515086]

Fri, 08 July 2011 02:14

Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

ORA-29289: directory access denied
 *Cause:  A directory object was specified for which no access is granted.
 *Action: Grant access to the directory object using the command
          GRANT READ ON DIRECTORY [object] TO [username];

And we still have not your Oracle version.

Regards
Michel

Report message to a moderator

Re: Move File from directories to another directories! [message #515213 is a reply to message #515109]

Fri, 08 July 2011 11:29

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

Make sure that the user creating the procedure has been granted read and write privileges to the Oracle directory object:

GRANT READ, WRITE ON DIRECTORY XML TO CTH2;
GRANT READ, WRITE ON DIRECTORY XMLDIR TO CTH2;

You also need to make sure that the operating system user that Oracle runs under has read and write privileges to the directory and files at the operating system level.

Report message to a moderator

Re: Move File from directories to another directories! [message #515216 is a reply to message #515213]

Fri, 08 July 2011 12:33

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

...and that these files are on the database server, not the client machine.

Report message to a moderator

Previous Topic:	Trigger calling an autonomous stored procedure(2 Merged)
Next Topic:	ORA-27369: job of type EXECUTABLE failed with exit code: Exchange full

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Aug 08 03:46:09 CDT 2025