![Finally, sftp from an Oracle database using plain plsql 20188367001]({"aperture":"0","credit":"","camera":"","caption":"","created_timestamp":"0","copyright":"http:\/\/seattletimes.nwsource.com\/ABPub\/2012\/08\/02\/2018836700.jpg","focal_length":"0","iso":"0","shutter_speed":"0","title":"","orientation":"0"} "Finally, sftp from an Oracle database using plain plsql 1")
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
Hi anton
I think I mange to understand how to get files from the SFTP server and keep the files under oracle directory I am using the following code:
declare
l_file blob;
l_dir_listing as_sftp.tp_dir_listing;
l_rv boolean;
begin
as_sftp.open_connection( i_host => ‘82.80.249.52’ );
as_sftp.login( i_user => ‘BI-Yenot’, i_password => ‘Bp8NsFlF’ );
as_sftp.set_log_level( 0 );
dbms_output.put_line( as_sftp.pwd );
l_dir_listing := as_sftp.read_dir( i_path => ‘/324402’ );
l_rv :=as_sftp.file_exists(i_path => ‘KupaDoc_Header_24.03.23.txt’);
IF l_rv = TRUE THEN
DBMS_OUTPUT.PUT_LINE(‘File Exists : True’ );
l_file := utl_raw.cast_to_raw( ‘KupaDoc_Header_24.04.23.txt’ );
l_rv:=as_sftp.get_file(i_path =>’KupaDoc_Header_24.04.23.txt’,i_directory => ‘MY_DOCS’,i_filename => ‘KupaDoc_Header_24.04.23.txt’ );
dbms_lob.freetemporary( l_file);
else
DBMS_OUTPUT.PUT_LINE(‘File Exists : False’ );
END IF;
as_sftp.close_connection;
end;
but it look like if the size of the file is bigger then 10M it returns false as if the file doesn’t exists in the SFTP folder.
Can you please help me?
Thanks Eli
hi
i am using the following code to read from an sftp server but it looks like for files bigger the 10M it fails.
declare
l_file blob;
l_dir_listing as_sftp.tp_dir_listing;
l_rv boolean;
i integer;
begin
as_sftp.open_connection( i_host => ‘server_ip’ );
as_sftp.login( i_user => ‘user’, i_password => ‘password’ );
as_sftp.set_log_level( 0 );
dbms_output.put_line( as_sftp.pwd );
l_dir_listing := as_sftp.read_dir( i_path => ‘/folder_name’ );
l_rv :=as_sftp.file_exists(i_path => ‘file_name.txt’);
IF l_rv = TRUE THEN
DBMS_OUTPUT.PUT_LINE(‘File Exists : True’ );
else
DBMS_OUTPUT.PUT_LINE(‘File Exists : False’ );
END IF;
as_sftp.close_connection;
end;
thanks
Hello Anton,
I can’t use the package. I have an error
begin
as_sftp.open_connection( i_host => ‘myhost’, i_port => 2222 , i_trust_server => true);
dbms_output.put_line(‘login’);
as_sftp.login( i_user => ‘mysuser’, i_password => ‘mypass’ );
as_sftp.close_connection;
end;
Rapport d’erreur –
ORA-29259: fin d’entrée atteinte
ORA-06512: à “SYS.UTL_TCP”, ligne 94
ORA-06512: à “SYS.UTL_TCP”, ligne 322
ORA-06512: à “SYS.UTL_TCP”, ligne 385
ORA-06512: à “HR.AS_SFTP”, ligne 1462
ORA-06512: à “HR.AS_SFTP”, ligne 1710
ORA-06512: à “HR.AS_SFTP”, ligne 2413
ORA-06512: à “HR.AS_SFTP”, ligne 4468
ORA-06512: à “HR.AS_SFTP”, ligne 4511
ORA-06512: à ligne 3
29259. 00000 – “end-of-input reached”
*Cause: The end of the input was reached.
*Action: If the end of the input is reached prematurely, check if the input
source terminates prematurely. Otherwise, close the connection
to the input.
everything closed
try to connect to xxxxxx (myhost) , port 2222
SSH-2.0-mod_sftp/0.9.9
using aes128-cbc, aes128-cbc
using hmac-sha1, hmac-sha1
using ssh-dss
using diffie-hellman-group14-sha1
Thank you
Hello Anton,
Thanks a lot for this, it worked for a SFTP host but not for another one, i get the below error :
“everything closed
try to connect to 196.XX.XXX.XXX, port 22
ORA-06512: à “SYS.UTL_TCP”, ligne 19
ORA-06512: à “SYS.UTL_TCP”, ligne 280
ORA-06512: à “APPS.as_SFTP_PKG”, ligne 1733
everything closed
Login
Erreur=ORA-29260: erreur réseau : non connecté”
Instruction getting error is “as_SFTP_PKG.open_connection( i_host =>’196.XX.XXX.XXX’, i_trust_server => true, i_port =>22 );
would you know how i can resolve this please ?
Did you add the required ACL for the second server? Is the sftp server reachable from the database server, no firewalls in between?
It just works. Awesome. Given the amount of trouble I usually face when trying to set up a new SSH connection, this worked right out of the box, at least for RSA. Ed25519 failed for me, but that isn’t working from the command line either, so must be something about my server configuration. It might work for my customer. It is on their list of current connections whereas I was trying to create one from scratch.
The team I’m supporting wants to get away from the Unix ETL server because they do not have that expertise. This would put them a step closer, but they are afraid of unsupported complexity. Given the level of support they would get from a commercial vendor if they had sftp issues, that is laughable.
Not sure if anything will come of it, but this is a nice tool to have available.
Thanks Anton.
Will this work on an Oracle 10 database?
I don’t know. Oracle 10 is so long ago that I don’t have a database available to test it.
I got your code running on an Oracle 19 “sandbox”. Worked great, the first time! Great work, I really appreciate it!
Good to hear, thanks
Hi
Thanks for the effort 🙂
Trying to get it working.
So I get “ssh-dss not OK” error.
What might be the problem?
This is in the logs:
everything closed
try to connect to 81.198.79.214, port 22
SSH-2.0-CrushFTPSSHD
using aes128-cbc, aes128-cbc
using hmac-sha1, hmac-sha1
using ssh-dss
using diffie-hellman-group14-sha1
validating host key using algorithm ssh-dss
trying ssh-dss
trying signature ssh-dss
I can’t tell right now. I assume it’s is error 20012. In that case it would help if you could mail me the public key of the server, and if possible also the private key. scheffer@amis.nl
Do I need to fill the known hosts with the site info?
The “usage” has an easier method for that:
as_sftp.open_connection( i_host => ‘localhost’, i_trust_server => true );
But that will not solve a “ssh-dss not OK” error.
If you still get that error you might help solving it be adding these debug messages before the error is thrown:
debug_msg( p_host_key );
debug_msg( p_signature );
debug_msg( l_H );
That will be around line 1949 in the package, so
if l_v != l_dss_r
then
debug_msg( p_host_key );
debug_msg( p_signature );
debug_msg( l_H );
raise_application_error( -20012, ‘ssh-dss not OK’ );
end if;
That will produce some hex strings in the output logging. If you can mail those to me I will take a look.
Thank you for the fantastic job.
Would it be possible to use pattern or wildcard in dir listing? eg, as_sftp.read_dir(i_path => ‘/’, i_pattern => ‘read*.*’)
Probably yes, I will look into that on my next update. Which is not yet planned by the way.
Thanks again.
Could you give me a hint how to do that? is it as simple as adding ” append_string( l_buf, utl_i18n.string_to_raw( i_pattern, ‘AL32UTF8’ ) );” or there’s other things?
I don’t think that the SFTP protocol itself supports patterns or wildcards, so just filtering the results of the current read_dir function whould be enough.
That can be done. but might be a network traffic if the folder contains lots of files. I might try that way and hope there will be any update with more efficient way (real soon)
Thanks again for the fantastic job and real quick replies
Thank you for the fantastic job.
Would it be possible to add a DELETE_FILE function to the package?
Yes, that is possible. McMogan has created an issuein which he has added that.
Thank you Athon. It just works, Nice job!
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!
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.
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
As far as I know, see for instance https://www.ietf.org/rfc/rfc4344.txt, the name for method AES-256 SDCTR is “aes256-ctr”. And that method is supported.
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.
Is our AS_SFTP PLSQL Program will work with SSH Key provided by bank. if so kindly share some sample script.
How should I know? I don’t know what your bank is providing.
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
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.
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
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.
Our Key is RSA Private Key
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—–
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?
Yes
It Worked. Thank You So Much Sir
Dear Anton, Is there any option in AS_SFTP PLSQL Procedure to move the file current to backup folder?
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.
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?
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.
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
Did you set a ACL privilege? See https://github.com/antonscheffer/as_sftp
Are you running on a Autonomous Transaction Processing Cloud database? That won’t work, see https://docs.oracle.com/en/cloud/paas/atp-cloud/atpug/experienced-database-users.html#GUID-829A7D07-1EA4-4F59-AA60-F780FABAFDEC
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.
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.
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.
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);
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?
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.
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?
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.
Hi, i’m having this Error: ORA-20002: Could not find matching encryption algorithm client to server; The encryption algorithm is aes256-gcm@openssh.com. I think i need to update the list of algorithms. How can i do this?
Updating the list of algorithms alone is not enough. You need to implement that algorithm too, as I did not.
I tried it on Sep3 taking latest from https://github.com/antonscheffer/as_sftp. What is the upgrade you are suggesting here?
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.
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
Use as_sftp.open_connection( i_host => ‘myhost’, i_trust_server => true );
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.
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.
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
I would say, upgrade 🙂 That bug is fixed 8 months ago.
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
The latest, and greatest, version can be found on GitHub. For a link see the blog post above.
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.
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.
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”
]
}
}
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.
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
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.
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.
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!
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
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 )
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.
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;
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.
A person called Morgan created an issue on the github repository, https://github.com/antonscheffer/as_sftp/issues/4 , with a procedure which can delete a file. I didn’t had time to check out that code. Maybe you can take a look at that code.
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.
Pass (=ftp) it to windows in binary mode
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’ );
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.
Hallo Anton!
Is it possible to remove a remote file via as_sftp?
Regards
Herbert Broich
PRODATIC
Hmmm, yes and no. With the source found on github not, but McMogan has raised on issuein which he has added that.
Hi Anton,
Appears to be getting the numeric value error. Might be derive_key function. Not sure what is causing. Will be great if you can help me with that.
g_encr_algo_s = 3des-cbc
l_rv in function DERIVE_KEY = 3A4D391A001B6BE6F6A5FE0CD570EC3A7615EE70
p_byte in function DERIVE_KEY = 42
p_len in function DERIVE_KEY = 8
G_IV_CYPHER_S2C = 3A4D391A001B6BE6
BEGIN
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
Anton, this is fantastic. Thank you so much! I’ve already successfully tested this against one of our SFTP servers.