Finally, sftp from an Oracle database using plain plsql

Anton Scheffer 68

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

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

  1. Thank you for the fantastic job.
    Would it be possible to add a DELETE_FILE function to the package?

  2. I was experiencing the error “ORA-20002: Could not find matching encryption algorithm client to server”.

    Then I saw the algorithm used was aes128-ctr,aes192-ctr,aes256-ctr, the mode was CTR.

    So I’ve added some three more lines to include them on my_encr_algo_client_to_server variable:

    add2name_list( my_encr_algo_client_to_server, ‘aes128-ctr’ );
    add2name_list( my_encr_algo_client_to_server, ‘aes192-ctr’ );
    add2name_list( my_encr_algo_client_to_server, ‘aes256-ctr’ );

    And then it worked.

    Is that correct? Shouldn’t these values had been already there?

    Thank you very much for this package, by the way, it’s REALLY awesome!

    1. Some people have added these lines too, but for them it results in ORA-06502: PL/SQL: numeric or value error. See for instance the replies from Tirupati S Vidap.
      I want to look into this in the near future.

  3. Anton, if you ever get to the point of adding support for additional encryption methods, AES-256 SDCTR is one we see commonly in our environment and it would be great to be able to use it with your package.

    We use the package regularly with AES-256 CBC and continue to appreciate your work on this.

    -Todd

      1. Unfortunately, if a server is configured for AES-256 SDCTR (and not AES-256 CBC), we get “ORA-20002: Could not find matching encryption algorithm client to server”. Only if they add AES-256 CBC to the SSH config (which requires a security exception and comments about not wanting to use a “lower cypher”) does the connection work.

  4. Is our AS_SFTP PLSQL Program will work with SSH Key provided by bank. if so kindly share some sample script.

      1. Thank You For your Quick Response. We have tried file transferring using as_sftp package with unix user name and password its working fine. we are asking as_sftp PLSQL package will work for SSH Key? if so kindly share some sample script. Through Unix we tried transferring file to bank and its working fine after the ssh key shared between the parties. The same functionality trying to use as_sftp package to perform the same. It will be great helpful for your support

        1. You have to share the key with as_sftp too by using the parameter i_priv_key of the login procedure. And depending on the key you also might need to use the i_passphrase parameter to supply the password/phrase for that key.

          1. Thank You For Your Speedy Response. Can i give like this in our PLSQL Wrapper
            as_sftp.login(i_user=>’NA351_19702′, i_priv_key=>’/home/orahotst/.ssh/id_rsa.ppk’,i_passphrase=>null);

            NA351_19702 – Bank FTP User Name
            /home/orahotst/.ssh/id_rsa.ppk – Our Private Key

            getting below error
            ORA-20030: Could not login.
            ORA-06512: at “APPS.AS_SFTP”, line 3186
            ORA-06512: at line 3

            Kindly correct us where we did mistake

          2. Hmm, ppk means Putty Private Key. That is a format as_sftp doesn’t support. But anyway, you need the content of a private key (file), not the location of that file.

          3. Your parameter needs to look something like:
            —–BEGIN RSA PRIVATE KEY—–
            MIICXAIBAAKBgQCqGKukO1De7zhZj6+H0qtjTkVxwTCpvKe4eCZ0FPqri0cb2JZfXJ/DgYSF6vUp
            wmJG8wVQZKjeGcjDOL5UlsuusFncCzWBQ7RKNUSesmQRMSGkVb1/3j+skZ6UtW+5u09lHNsj6tQ5
            1s1SPrCBkedbNf0Tp0GbMJDyR4e9T04ZZwIDAQABAoGAFijko56+qGyN8M0RVyaRAXz++xTqHBLh
            3tx4VgMtrQ+WEgCjhoTwo23KMBAuJGSYnRmoBZM3lMfTKevIkAidPExvYCdm5dYq3XToLkkLv5L2
            pIIVOFMDG+KESnAFV7l2c+cnzRMW0+b6f8mR1CJzZuxVLL6Q02fvLi55/mbSYxECQQDeAw6fiIQX
            GukBI4eMZZt4nscy2o12KyYner3VpoeE+Np2q+Z3pvAMd/aNzQ/W9WaI+NRfcxUJrmfPwIGm63il
            AkEAxCL5HQb2bQr4ByorcMWm/hEP2MZzROV73yF41hPsRC9m66KrheO9HPTJuo3/9s5p+sqGxOlF
            L0NDt4SkosjgGwJAFklyR1uZ/wPJjj611cdBcztlPdqoxssQGnh85BzCj/u3WqBpE2vjvyyvyI5k
            X6zk7S0ljKtt2jny2+00VsBerQJBAJGC1Mg5Oydo5NwD6BiROrPxGo2bpTbu/fhrT8ebHkTz2epl
            U9VQQSQzY1oZMVX8i1m5WUTLPz2yLJIBQVdXqhMCQBGoiuSoSjafUhV7i1cEGpb88h5NBYZzWXGZ
            37sJ5QsW+sJyoNde3xH8vdXhzU7eT82D6X/scw9RZz+/6rCJ4p0=
            —–END RSA PRIVATE KEY—–

            If it looks like this you also need the i_passphrase parameter
            —–BEGIN RSA PRIVATE KEY—–
            Proc-Type: 4,ENCRYPTED
            DEK-Info: AES-128-CBC,D54228DB5838E32589695E83A22595C7

            3+Mz0A4wqbMuyzrvBIHx1HNc2ZUZU2cPPRagDc3M+rv+XnGJ6PpThbOeMawz4Cbu
            lQX/Ahbx+UadJZOFrTx8aEWyZoI0ltBh9O5+ODov+vc25Hia3jtayE51McVWwSXg
            wYeg2L6U7iZBk78yg+sIKFVijxiWnpA7W2dj2B9QV0X3ILQPxbU/cRAVTd7AVrKT
            … etc …
            —–END RSA PRIVATE KEY—–

          4. Our Format similar to this format which you provided

            —–BEGIN RSA PRIVATE KEY—–
            MIICXAIBAAKBgQCqGKukO1De7zhZj6 H0qtjTkVxwTCpvKe4eCZ0FPqri0cb2JZfXJ/DgYSF6vUp
            wmJG8wVQZKjeGcjDOL5UlsuusFncCzWBQ7RKNUSesmQRMSGkVb1/3j skZ6UtW 5u09lHNsj6tQ5
            1s1SPrCBkedbNf0Tp0GbMJDyR4e9T04ZZwIDAQABAoGAFijko56 qGyN8M0RVyaRAXz xTqHBLh
            3tx4VgMtrQ WEgCjhoTwo23KMBAuJGSYnRmoBZM3lMfTKevIkAidPExvYCdm5dYq3XToLkkLv5L2
            pIIVOFMDG KESnAFV7l2c cnzRMW0 b6f8mR1CJzZuxVLL6Q02fvLi55/mbSYxECQQDeAw6fiIQX
            GukBI4eMZZt4nscy2o12KyYner3VpoeE Np2q Z3pvAMd/aNzQ/W9WaI NRfcxUJrmfPwIGm63il
            AkEAxCL5HQb2bQr4ByorcMWm/hEP2MZzROV73yF41hPsRC9m66KrheO9HPTJuo3/9s5p sqGxOlF
            L0NDt4SkosjgGwJAFklyR1uZ/wPJjj611cdBcztlPdqoxssQGnh85BzCj/u3WqBpE2vjvyyvyI5k
            X6zk7S0ljKtt2jny2 00VsBerQJBAJGC1Mg5Oydo5NwD6BiROrPxGo2bpTbu/fhrT8ebHkTz2epl
            U9VQQSQzY1oZMVX8i1m5WUTLPz2yLJIBQVdXqhMCQBGoiuSoSjafUhV7i1cEGpb88h5NBYZzWXGZ
            37sJ5QsW sJyoNde3xH8vdXhzU7eT82D6X/scw9RZz /6rCJ4p0=
            —–END RSA PRIVATE KEY—–
            This type of private key supports?

          1. No, as_sftp is meant to move files to and from a SFTP-server. It has no functionality to delete or move files, neither on the database nor on the SFTP server.

  5. Hi Anton, I got your blog and code reference from Connor. I am looking for this but have a doubt, what types of file it can handle? Only text files in txt, csv or any executable files too?

    1. It can move any type of file, but it doesn’t set or copies any file attributes. So it doesn’t set an executable attribute flag.

  6. Hi,

    I have been given a task of moving the csv files from oracle directory to SFTP location using PL/SQL stored procedure in my project. I have tried using as_sftp as below

    BEGIN
    as_sftp.open_connection( i_host => ‘hostname’, i_port => 22 );
    as_sftp.login(‘username’, ‘password’);
    as_sftp.put_file(i_path =>’SFTP location path’, i_directory =>’Oracle directory’, i_filename => ‘csv name’);
    as_sftp.close_connection;
    end;

    and getting the error as below:

    Error report –
    ORA-29260: network error: not connected
    ORA-06512: at “OBI.AS_SFTP”, line 3186
    ORA-06512: at line 3
    29260. 00000 – “network error: %s”
    *Cause: A network error occurred.
    *Action: Fix the network error and retry the operation.

    Can you please help me to resolve this?

    Bhumi

      1. Yes, I have set the ACL privilege to the database user. Do i need to provide it to the user which I am using to connect to SFTP location? I am using Oracle 12c currently.

        1. ACL for the database user is OK. Then it looks like “hostname” is not reachable from your database server. I leave it up to you to find out if that is really true, caused by a firewall on either side of the connection or something else.

          1. I have checked that the inbound and outbound is allowed to this location through the DB. Also, after backtracking, i found that the line on which the error is displayed is:
            l_dummy := utl_tcp.write_raw( c => g_con, data => l_buf );

            I think it means that I am unable to write anything at this SFTP location. Although the user is having both read/write privileges.

          2. enable dbms_output logging, set serveroutput on or something simular depending or your SQL-client program

            And use as_sftp.login( i_user => ‘username’, i_password => ‘password’, i_log_level => 1);

      2. The database in our project will be migrated to Oracle 18c by end of this year. Since it is a cloud database, do you mean to say that this package as_sftp will not work there?

        1. I don’t know. Oracle says in mentioned link that it removed, for whatever reason, utl_tcp package from the “Autonomous Transaction Processing database”. It doesn’t mention a database version. But as_sftp does need to package to be functional.

          1. The above error is resolved now and I am able to login successfully at the SFTP server location. But now, I am starting to get the below error while executing this line

            as_sftp.put_file(i_path =>’SFTP location path’, i_directory =>’Oracle directory’, i_filename => ‘csv file name’);

            Error:
            ORA-22288: file or LOB operation FILEOPEN failed
            Permission denied

            My database user for accessing oracle directory is different than the user which I have used to login the SFTP server location.

            Do they both need to be same?

          2. No, the two users are not related at all.
            You need a database user, which needs access some database packages (ie. as_sftp, dbms_crypto, utl_tcp), a database ACL for the sftp server, and when needed read or write access to a database directory object. And the database itself (ie. the OS user which is used to run Oracle) needs access to the filesystem when needed.
            And you need a user to access the sftp server.

    1. Updating the list of algorithms alone is not enough. You need to implement that algorithm too, as I did not.

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

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

      1. I’m getting the same error, “Could not find matching encryption algorithm client to server”. And as you said where I can I find the upgraded version of code? please let me know the link.

        Thank you

          1. Anton, thank you very much for your reply, but Still I’m getting the same error “Could not find matching encryption algorithm client to server”.

            And your upgrade source code I took it from below link: https://github.com/antonscheffer/as_sftp/blob/9c3386ad778875c43cc29eb969bba0d6ced5e68b/src/as_sftp.pkb

            Error message:
            ORA-20002: Could not find matching encryption algorithm client to server
            ORA-06512: at “AS_SFTP”, line 1805
            ORA-06512: at “AS_SFTP”, line 3227
            ORA-06512: at “AS_SFTP”, line 3270
            ORA-06512: at line 2

            Thank you.

          2. If the sftp server only allows protocols which as_sftp does not supports, there’s nothing I can do. Unless you mention the list of allowed protocols. Maybe I can add a protocol when I get a lot of requests for it.

          3. Thank you Anton for your response :

            Error message:
            ORA-20002: Could not find matching encryption algorithm client to server
            ORA-06512: at “AS_SFTP”, line 1805
            ORA-06512: at “AS_SFTP”, line 3227
            ORA-06512: at “AS_SFTP”, line 3270
            ORA-06512: at line 2

            And here is list of allowed protocols in our sftp server.

            “SecurityPolicy”: {
            “Fips”: false,
            “SecurityPolicyName”: “TransferSecurityPolicy-2018-11”,
            “SshCiphers”: [
            “chacha20-poly1305@openssh.com”,
            “aes128-ctr”,
            “aes192-ctr”,
            “aes256-ctr”,
            “aes128-gcm@openssh.com”,
            “aes256-gcm@openssh.com”
            ],
            “SshKexs”: [
            “curve25519-sha256”,
            “curve25519-sha256@libssh.org”,
            “ecdh-sha2-nistp256”,
            “ecdh-sha2-nistp384”,
            “ecdh-sha2-nistp521”,
            “diffie-hellman-group-exchange-sha256”,
            “diffie-hellman-group16-sha512”,
            “diffie-hellman-group18-sha512”,
            “diffie-hellman-group14-sha256”,
            “diffie-hellman-group14-sha1”
            ],
            “SshMacs”: [
            “umac-64-etm@openssh.com”,
            “umac-128-etm@openssh.com”,
            “hmac-sha2-256-etm@openssh.com”,
            “hmac-sha2-512-etm@openssh.com”,
            “hmac-sha1-etm@openssh.com”,
            “umac-64@openssh.com”,
            “umac-128@openssh.com”,
            “hmac-sha2-256”,
            “hmac-sha2-512”,
            “hmac-sha1”
            ]
            }
            }

          4. See an earlier reply:
            so adding
            add2name_list( my_encr_algo_client_to_server, ‘aes128-ctr’ );
            after this line
            my_encr_algo_client_to_server := tp_name_list( ‘aes128-cbc’, ‘3des-cbc’ );
            might solve it.

          5. Anton, thank you for your response and as per you comment I have added below line and I get below error. Can you please suggest how to resolve this?

            Added
            add2name_list( my_encr_algo_client_to_server, ‘aes128-ctr’ );
            after this line
            my_encr_algo_client_to_server := tp_name_list( ‘aes128-cbc’, ‘3des-cbc’ );

            And now getting the new errors:

            ORA-06502: PL/SQL: numeric or value error
            ORA-06512: at “SYS.UTL_RAW”, line 380
            ORA-06512: at “AS_SFTP”, line 2663
            ORA-06512: at “AS_SFTP”, line 2975
            ORA-06512: at “AS_SFTP”, line 3036
            ORA-06512: at “AS_SFTP”, line 3046
            ORA-06512: at line 6

        1. Hello Anton, Did you got chance to look into the below error and as per your response I have added the line and I got the another error.

          Added
          add2name_list( my_encr_algo_client_to_server, ‘aes128-ctr’ );
          after this line
          my_encr_algo_client_to_server := tp_name_list( ‘aes128-cbc’, ‘3des-cbc’ );

          Geetting the new errors:

          ORA-06502: PL/SQL: numeric or value error
          ORA-06512: at “SYS.UTL_RAW”, line 380
          ORA-06512: at “AS_SFTP”, line 2663
          ORA-06512: at “AS_SFTP”, line 2975
          ORA-06512: at “AS_SFTP”, line 3036
          ORA-06512: at “AS_SFTP”, line 3046
          ORA-06512: at line 6

          Thank you.

          1. No, I had a quick look at it, but at this moment I don’t have a SFTP-server which supports aes128-ctr available at my development machine. So I can’t really check it myself. In 2 weeks I have more time to look into this.

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

  9. 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 )

  10. 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;

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

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

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

        1. Hmmm, yes and no. With the source found on github not, but McMogan has raised on issuein which he has added that.

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

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 over two months: The […]