Finally, sftp from an Oracle database using plain plsql

7

Just a quick blog to get me through the Christmas eve. More than 5 years ago I wrote a blog about FTPS from an Oracle databse. In that blog I already mentioned that SFTP using plsql is possible. Not loading some Java classes in the database and adding some plsql wrappers. No, just plain old plsql. At that time I had a “prove of concept”-script which was huge.

But, after moving almost all encrypting/decrypting code to as_crypto, I switched to using dbms_crypto instead of my own code. And it turns out that transferring files to or from a SFTP-server can be done using a small package with less than 3000 lines of code, as_sftp.

At this moment it is only possible to use a user/password to login to the SFTP-server, but if I have time (might take another 5 years 😁) I will add code to use a private RSA key to login

Anton

About Author

Oracle Consultant at AMIS

7 Comments

  1. Anton, what would be involved in adding support to delete a file on the SFTP server? That is the step we are missing at this point. I’m certainly not asking you to do it – just looking for some pointers. Thanks much.

  2. Hello Anton: I am running your PKG as_xlsx for generate file EXCEL and my directory is defined for UNIX, the process ends Ok, then I pass the file to Windows and when I open it it shows me strange characters (it does not show the data), what should I do. Thanks for your attention.

  3. Hi, excellent work !
    I managed to connect to one another linux server.
    But I have troubles to connect to others (ORA-01426: numeric overflow, or Timeout because of wrong l_packet_len in read_packet)

    Line 1711 of as_sftp.pkb : There is something wrong (add 4 aes to my_encr_algo_client_to_server). It should be added to my_encr_algo_server_to_client, no ?

    1707 :
    my_encr_algo_client_to_server := tp_name_list( ‘aes128-cbc’, ‘3des-cbc’ );
    add2name_list( my_encr_algo_client_to_server, ‘aes256-cbc’ );
    add2name_list( my_encr_algo_client_to_server, ‘aes192-cbc’ );

    1711 :
    my_encr_algo_server_to_client := tp_name_list( ‘aes128-cbc’, ‘aes128-ctr’, ‘3des-cbc’ );
    add2name_list( my_encr_algo_client_to_server, ‘aes256-cbc’ );
    add2name_list( my_encr_algo_client_to_server, ‘aes256-ctr’ );
    add2name_list( my_encr_algo_client_to_server, ‘aes192-cbc’ );
    add2name_list( my_encr_algo_client_to_server, ‘aes192-ctr’ );

    • Anton Scheffer on

      You are right, those 4 methods should be added to my_encr_algo_server_to_client. But I not sure if that causes the error. I will try to reproduce (and solve) it.
      P.S. I’ve changed the code to fix this issue.

  4. Anton, this is fantastic. Thank you so much! I’ve already successfully tested this against one of our SFTP servers.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.