CODEWITHSUNDEEP
sqloracle-databaseplsql
Vijay
Vijay

Reputation: 21

How to read .csv file from FTP or SFTP server using Oracle PL SQL?

I want to read data of .csv file which is located at FTP or SFTP server using Oracle SQL or PL SQL. I tried the below code and it showing output like SSH-2.0-OpenSSH_5.3 that means connected i hope.

 declare
  c  utl_tcp.connection;  -- TCP/IP connection to the Web server
  ret_val pls_integer;
BEGIN
  c := utl_tcp.open_connection(remote_host => 'ftp.******.******.com'
                              ,remote_port =>  21
                              ,charset => 'US7ASCII'
--                              ,wallet_path     => '****************'
--                              ,wallet_password => '**********'
                              );  -- open connection
--  ret_val := utl_tcp.write_line(c, 'GET / HTTP/1.0');    -- send HTTP request
  ret_val := utl_tcp.write_line(c);
  BEGIN
    LOOP
      dbms_output.put_line(utl_tcp.get_line(c, TRUE));  -- read result
    END LOOP;
  EXCEPTION
    WHEN utl_tcp.end_of_input THEN
      NULL; -- end of input
  END;
  utl_tcp.close_connection(c);
END;
/

Could someone help me on next steps on How to open and read the .csv file present in SFTP/FTP server and load it into Oracle DB table ?

Upvotes: 0

Views: 6182

Answers (3)

Peter Kalef ' DidiSoft
Peter Kalef ' DidiSoft

Reputation: 1464

If you need an SFTP client in PL/SQL you can take a look at the commercial OraSFTP package from DidiSoft.

Here is a sample usage:

DECLARE 
  connection_id NUMBER;
  private_key_handle BFILE;
  private_key BLOB;  
  PRIVATE_KEY_PASSWORD VARCHAR2(500);  
  downloaded_file BLOB;
BEGIN
    DBMS_LOB.createtemporary(PRIVATE_KEY, TRUE);
    private_key_handle := BFILENAME('PGP_KEYS_DIR', 'test_putty_private.ppk'); -- directory name must be Upper case
    DBMS_LOB.OPEN(private_key_handle, DBMS_LOB.LOB_READONLY);
    DBMS_LOB.LoadFromFile( private_key, private_key_handle, DBMS_LOB.GETLENGTH(private_key_handle) );
    DBMS_LOB.CLOSE(private_key_handle);
    PRIVATE_KEY_PASSWORD := 'changeit';
 
    connection_id := ORA_SFTP.CONNECT_HOST('localhost', 22, 'nasko', private_key, private_key_password);
 
    downloaded_file := ORA_SFTP.DOWNLOAD(connection_id, 'remote_file.dat');
 
    ORA_SFTP.DISCONNECT_HOST(connection_id);
END;
/

Disclaimer: I work for DidiSoft

Upvotes: 0

Kris Rice
Kris Rice

Reputation: 3410

Tim Hall over at Oracle-base.com did exactly this and has the ftp plsql API on his blog post.

Here's an excerpt which is what you are asking about.

  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);
END;
/

Here's the full blog post : https://oracle-base.com/articles/misc/ftp-from-plsql

Upvotes: 2

Karthik
Karthik

Reputation: 1

You require some tools/ application to open the file present in FTP or SFTP and load the data in it to Database, there are some tools which can be used to load them to database like Dollar Universe, Tidal scheduler, etc. made up of sql and pl/sql code. these tools are linked with unix/windows OS which needs to be triggered manually

Upvotes: 0

Related Questions