Finally, sftp from an Oracle database using plain plsql

Anton Scheffer 24

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

24 thoughts on “Finally, sftp from an Oracle database using plain plsql

    1. I was referring to a closed GitHub ticket “my_encr_algo_client_to_server and server_to_client list #3”, but that is indeed not the solution to your problem. I now see that your SFTP-server only supports “CTR” encryption methods, so adding
      add2name_list( my_encr_algo_client_to_server, ‘aes128-ctr’ );
      alter this line
      my_encr_algo_client_to_server := tp_name_list( ‘aes128-cbc’, ‘3des-cbc’ );
      might solve it.

      1. I got another error ‘ORA-20017: Host fingerprint not OK.’ Full log below

        everything closed
        try to connect to umems572.us.oracle.com, port 22
        SSH-2.0-OpenSSH_5.3
        diffie-hellman-group-exchange-sha256,diffie-hellman-group-exchange-sha1,diffie-hellman-group14-sha1,diffie-hellman-group1-sha1
        ssh-rsa,ssh-dss
        aes128-ctr,aes192-ctr,aes256-ctr
        hmac-sha1,hmac-ripemd160
        ssh-dss,ssh-rsa,rsa-sha2-256,rsa-sha2-512
        using aes128-ctr, aes128-ctr
        using hmac-sha1, hmac-sha1
        using diffie-hellman-group14-sha1
        2020-09-07 16:52:32
        validating host key using algorithm ssh-dss
        trying ssh-dss
        signature OK
        host fingerprint: SHA256:DiG/qZM2OZYTZuTwc2vygAxR0tOKMB4ZNznvpHjGQRI=

        Error starting at line : 82 in command –
        begin
        as_sftp.open_connection( i_host => ‘myhost’, i_port => 22 );
        as_sftp.close_connection;
        end;
        Error report –
        ORA-20017: Host fingerprint not OK.
        ORA-06512: at “HR.AS_SFTP”, line 2128
        ORA-06512: at “HR.AS_SFTP”, line 3228
        ORA-06512: at “HR.AS_SFTP”, line 3244
        ORA-06512: at line 2

          1. Thankyou, it worked.
            Does it support password less connectivity towards SFTP server?
            Does it support passphrase? I created a passphrase to connect to sftp server but this is giving me
            as_sftp.login( i_user => ‘oracle’, i_passphrase => ‘thisismypassphrase’);
            ORA-20030: Could not login.

          2. If you mean login with a private key, yes. But in that case you need to use the i_priv_key parameter of procedure login. And, depending on your key, you might need the i_passphrase parameter. And of course you need the user and public key stored at the sftp-server.

  1. I am trying with this as_sftp library

    begin
    as_sftp.open_connection( i_host => ‘myhost’, i_trust_server => true );
    as_sftp.close_connection;
    end;

    And I get below error. Can you please suggest how to resolve this?

    everything closed
    try to connect to myhost, port 22
    SSH-2.0-OpenSSH_5.3
    diffie-hellman-group-exchange-sha256,diffie-hellman-group-exchange-sha1,diffie-h
    ellman-group14-sha1,diffie-hellman-group1-sha1
    ssh-rsa,ssh-dss
    aes128-ctr,aes192-ctr,aes256-ctr
    hmac-sha1,hmac-ripemd160
    ssh-dss,ssh-rsa,rsa-sha2-256,rsa-sha2-512
    begin
    *
    ERROR at line 1:
    ORA-20002: Could not find matching encryption algorithm client to server
    ORA-06512: at “HR.AS_SFTP”, line 1805
    ORA-06512: at “HR.AS_SFTP”, line 3227
    ORA-06512: at “HR.AS_SFTP”, line 3270
    ORA-06512: at line 2

  2. Anton, thank you very much for this!
    We were pretty excited to find and test this.
    Such great work.
    I was able to figure out the naming directory thing before I read your post.
    While testing we noticed that the put command takes double the time, that WinScp or Filezilla takes.
    For example on a 9 Mb file it takes Winscp or Filezilla about 14 seconds almost consistenly, but with as_sftp the speed was closer to 36 seconds, sometimes 39 seconds.
    We were wondering if there was a way to speed things up.
    We believe the code in question is the loop located between lines 2973 and 2987 in https://github.com/antonscheffer/as_sftp/blob/master/src/as_sftp.pkb.
    Any pointers will be appreciated.
    Thanks again!

  3. Thanks for this Anton.
    However, what should I do if the file has been created by another process already and it is just sitting at a directory.
    How do I call as_sftp.put_file, so that it will transfer it?
    Thanks

    1. I you want to handle a file sitting in a directory with Oracle PL/SQL you need a “directory object” pointing to that directory. You can use that directory object using the overloaded function put_file( i_path varchar2, i_directory varchar2, i_filename varchar2 )

  4. Hi Anton,

    This is excellent work. However, I guess there is a bug in read_dir function. When I try to read a directory with more than 250 read only files, read_fxp_message( l_buf ); gives an error ORA-06502: PL/SQL: raw variable length too long. Do you have any idea what cause this error ?

    1. Actually the problem is in the read_packet function. If return value l_buf exceeds the limit of 32767 then it generates above error.

    2. Hhm, I can’t reproduce this error. I can read a directory listing with more than 11000 files without a problem.

      1. In read_fxp_message function there is a code snippet which starts from line 2576 like below. When my call to this funcion produces the error, length of p_buf, length of l_buf and l_len variables are as follows:

        p_buf length : 32396
        l_buf length: 32400
        l_len: 70107

        line 2576: while utl_raw.length( p_buf ) < l_len
        line 2577: loop
        line 2578: read_fxp_message( l_buf, false );
        *length of p_buf, l_buf and l_len are calculated here*
        line 2579: p_buf := utl_raw.concat( p_buf, l_buf );
        line 2580: end loop;

  5. 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.

  6. 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.

  7. 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’ );

    1. 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.

  8. 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.

Next Post

Override Date.now() based on OS time when Node returns incorrect current date

Facebook 0 Twitter Linkedin I was facing a weird issue. In my Node application – version 10.6.3 – running on Ubuntu – 18.04 – I got the wrong value for the current date and time. A call to new Date() and to Date.now() resulted in a wrong value, off by […]