Finally, sftp from an Oracle database using plain plsql

11

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

11 Comments

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

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

    • Anton Scheffer on

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

      • 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;

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

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

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

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