4

I wrote a PL/SQL procedure to connect to an FTP server. I am able to write a file to that FTP server. Using the same code I tried to connect to an SFTP server, but it failed. How do I connect to SFTP using PL/SQL?

Ry-
  • 218,210
  • 55
  • 464
  • 476
Naveenraja Subramaniam
  • 367
  • 2
  • 6
  • 17
  • You can't perform SFTP from PL/SQL but you can using Java in Oracle. See [this article](http://dbashavefeelingstoo.wordpress.com/2011/07/22/sftp-with-plsql/) for an example of how it might be done. – John Doyle Feb 09 '12 at 12:13
  • That's not entirely true. You can use [`utl_http`](http://docs.oracle.com/cd/B10500_01/appdev.920/a96612/u_http.htm) over an SSL protocol. It's not quite the same as SFTP but it's possible... – Ben Feb 09 '12 at 16:31
  • Thanks Ben - do you have any links to any articles about that? Is it straightforward? (not questioning, just curious) :) – Jeffrey Kemp Feb 10 '12 at 02:50
  • @JeffreyKemp; sorry, I don't know of anything specific that's not proprietary. It's simple-ish, use `utl_http` to connect securely and `utl_file` to transfer the file in chunks over the connection. – Ben Feb 11 '12 at 18:06
  • 1
    Good original question. I need this as well. The gov't scenario I'm in they will not allow mapped drives so I need SFTP. Just wondering if you ever gave John Doyle's recommendation a try http://dbashavefeelingstoo.wordpress.com/2011/07/22/sftp-with-plsql/ If so, did you run into any issues? – gfrobenius Jan 03 '14 at 18:57

2 Answers2

3

You can try the commercial ORA_SFTP package provided from DidiSoft:

connection_id := ORA_SFTP.CONNECT_HOST(...
ORA_SFTP.UPLOAD(connection_id, data, 'remote_file.dat');

Disclaimer: I work for DidiSoft

Peter Kalef ' DidiSoft
  • 1,454
  • 17
  • 21
2

SFTP requires SSH plus the implementation of a protocol. As far as my PL/SQL knowledge reaches and Google's, there are currently no available implementation of SSH or this protocol in PL/SQL. There are some alternatives:

  • Use Java in the database and open sufficient ports. Not recommended when this is the only reason to use Java in the database; it is not as well designed as PL/SQL and can be expensive to maintain by a DBA since most DBA's have no experience with it.
  • Use PL/SQL to start a job outside the database. For instance, in the past I've used often Pentaho Data Integration (formerly known as Kettle) which provides a free solution to draw your data flow from table/procedure to sftp recipient and then run it. Running from PL/SQL requires a scheduler (I always used our own because it also integrates Kettle, but you can also consider the scheduler integrated with Oracle Apps, Redwood JCS/Cronacle or others). Coding in PL/SQL then becomes something like: 'begin package.submit('SFTP it'); package.wait; end;'.

I would go for the second option. If you need further details, please let me know.

Community
  • 1
  • 1
Guido Leenders
  • 4,232
  • 1
  • 23
  • 43