[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5252
  • Last Modified:

File transfer using PL/SQL Procedure



Hi.

 I need to transfer set of files  (txt,rpt etc..) from one server to other using a Oracle PL/SQL procedure/function. Using FTP.GET function we can transfer files one by one and also we have to give the file names as a parameter. How can we do this if we don't know the file names and we need to transfer all the file in a folder. Please help.


Regards.  
0
hiranya
Asked:
hiranya
  • 2
  • 2
1 Solution
 
MohanKNairCommented:
Oracle PL/SQL procedure/function cannot transfer files. Oracle installed in two servers can communicate using a DB LINK. It is not possible to transfer LOB fields over a db link.

The best option for transfering a complete folder is to ZIP the entrire folder to a single file or using tar compress the folder to a tar file. FTP the zipped file and unzip the file at other server.
0
 
actonwangCommented:
to MohankNair: It is possible to transfer files using FTP between servers. There are some existing packages out there to do this.
0
 
actonwangCommented:
>> How can we do this if we don't know the file names
     There is no direct function in PL/SQL to do this. The workaround can be that you can have a file in that folder to describe which files in the folder.
     
0
 
ram_0218Commented:
CREATE OR REPLACE DIRECTORY my_docs AS '/u01/app/oracle/';
SET SERVEROUTPUT ON SIZE 1000000
@c:\ftp.pks
@c:\ftp.pkb

-- Retrieve an ASCII file from a remote FTP server.
DECLARE
  l_conn  UTL_TCP.connection;
BEGIN
  l_conn := ftp.login('ftp.company.com', '21', 'ftpuser', 'ftppassword');
  ftp.ascii(p_conn => l_conn);
  ftp.get(p_conn      => l_conn,
          p_from_file => '/u01/app/oracle/test.txt',
          p_to_dir    => 'MY_DOCS',
          p_to_file   => 'test_get.txt');
  ftp.logout(l_conn);
  utl_tcp.close_all_connections;
END;
/

-- Send an ASCII file to a remote FTP server.
DECLARE
  l_conn  UTL_TCP.connection;
BEGIN
  l_conn := ftp.login('ftp.company.com', '21', 'ftpuser', 'ftppassword');
  ftp.ascii(p_conn => l_conn);
  ftp.put(p_conn      => l_conn,
          p_from_dir  => 'MY_DOCS',
          p_from_file => 'test_get.txt',
          p_to_file   => '/u01/app/oracle/test_put.txt');
  ftp.logout(l_conn);
  utl_tcp.close_all_connections;
END;
/

-- Retrieve a binary file from a remote FTP server.
DECLARE
  l_conn  UTL_TCP.connection;
BEGIN
  l_conn := ftp.login('ftp.company.com', '21', 'ftpuser', 'ftppassword');
  ftp.binary(p_conn => l_conn);
  ftp.get(p_conn      => l_conn,
          p_from_file => '/u01/app/oracle/product/9.2.0.1.0/sysman/reporting/gif/jobs.gif',
          p_to_dir    => 'MY_DOCS',
          p_to_file   => 'jobs_get.gif');
  ftp.logout(l_conn);
  utl_tcp.close_all_connections;
END;
/

-- Send a binary file to a remote FTP server.
DECLARE
  l_conn  UTL_TCP.connection;
BEGIN
  l_conn := ftp.login('ftp.company.com', '21', 'ftpuser', 'ftppassword');
  ftp.binary(p_conn => l_conn);
  ftp.put(p_conn      => l_conn,
          p_from_dir  => 'MY_DOCS',
          p_from_file => 'jobs_get.gif',
          p_to_file   => '/u01/app/oracle/jobs_put.gif');
  ftp.logout(l_conn);
  utl_tcp.close_all_connections;
END;
/
0
 
ram_0218Commented:
ofcourse the copyright is to:-

http://www.oracle-base.com/articles/9i/FTPFromPLSQL9i.php

let us know if anything doesnt work. We'll be glad to solve it.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now