Home » SQL & PL/SQL » SQL & PL/SQL » Move File from directories to another directories! (Plsql)
Move File from directories to another directories! [message #515049] Thu, 07 July 2011 22:34 Go to next message
dophuong_cs
Messages: 90
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?
Re: Move File from directories to another directories! [message #515051 is a reply to message #515049] Thu, 07 July 2011 22:36 Go to previous messageGo to next message
BlackSwan
Messages: 22686
Registered: January 2009
Senior Member
>I want to move files from directories1 to directories2.How do i do?

mv /home/oracle/xml/*xml /home/oracle/xml/move/
Re: Move File from directories to another directories! [message #515052 is a reply to message #515051] Thu, 07 July 2011 22:40 Go to previous messageGo to next message
dophuong_cs
Messages: 90
Registered: May 2011
Location: Viet Nam
Member

i want to move file in plsql, no on linux.Ok
Re: Move File from directories to another directories! [message #515053 is a reply to message #515052] Thu, 07 July 2011 22:43 Go to previous messageGo to next message
BlackSwan
Messages: 22686
Registered: January 2009
Senior Member
>i want to move file in plsql,
PL/SQL runs inside RDBMS engine & has NO direct contact with OS filesystem.
Re: Move File from directories to another directories! [message #515054 is a reply to message #515053] Thu, 07 July 2011 23:00 Go to previous messageGo to next message
dophuong_cs
Messages: 90
Registered: May 2011
Location: Viet Nam
Member

i want to a example for my question. Please help me!
Re: Move File from directories to another directories! [message #515061 is a reply to message #515054] Fri, 08 July 2011 00:33 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7966
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>

Re: Move File from directories to another directories! [message #515062 is a reply to message #515061] Fri, 08 July 2011 00:38 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7966
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.
Re: Move File from directories to another directories! [message #515063 is a reply to message #515052] Fri, 08 July 2011 00:49 Go to previous messageGo to next message
Michel Cadot
Messages: 58847
Registered: March 2007
Location: Nanterre, France, http://...
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

Re: Move File from directories to another directories! [message #515086 is a reply to message #515063] Fri, 08 July 2011 01:41 Go to previous messageGo to next message
dophuong_cs
Messages: 90
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!
Re: Move File from directories to another directories! [message #515109 is a reply to message #515086] Fri, 08 July 2011 02:14 Go to previous messageGo to next message
Michel Cadot
Messages: 58847
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Move File from directories to another directories! [message #515213 is a reply to message #515109] Fri, 08 July 2011 11:29 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7966
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.
Re: Move File from directories to another directories! [message #515216 is a reply to message #515213] Fri, 08 July 2011 12:33 Go to previous message
joy_division
Messages: 4503
Registered: February 2005
Location: East Coast USA
Senior Member
...and that these files are on the database server, not the client machine.
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:
  


Current Time: Wed Aug 20 22:17:20 CDT 2014

Total time taken to generate the page: 0.11621 seconds