1. 安装jar 包

[oracle@XAG143 javaclass]$ ls
ora-sftp-1.3.jar

[oracle@XAG143 javaclass]$ loadjava -r -f -o -user admin/xag123@192.168.40.143:1521/MYPDB1 ora-sftp-1.3.jar

发布 OraSFTP_Package.sql 文件

2. 检查安装情况

SQL> select ORA_SFTP.VERSION from dual;

VERSION                                                                                                                                                                                                                                                                       
----------------------------------------------------------
1.3.2.1 

#returns TRUE if this is an evaluation version
SQL> select ORA_SFTP.IS_TRIAL_VERSION from dual;
IS_TRIAL_VERSION
----------------
               1

3. grant permissions

SQL> call dbms_java.grant_permission('ADMIN', 'SYS:java.net.SocketPermission', '192.168.40.143:22', 'connect,resolve');

4 测试(显示当前目录)

DECLARE 
 connection_id NUMBER;
 remote_ssh_port NUMBER := 22;
 username VARCHAR2(200);
 password VARCHAR2(200); 
BEGIN
 username:= 'oracle'; 
 password:= '123456';
 connection_id := ORA_SFTP.CONNECT_HOST('192.168.40.143', remote_ssh_port, username, password);
 dbms_output.put_line('Current folder is ' || ORA_SFTP.CURRENT_DIRECTORY(connection_id));
 dbms_output.put_line('Current folder is ' || ORA_SFTP.PWD(connection_id));
END;

#输出
Current folder is /home/oracle
Current folder is /home/oracle

4.1 测试(LIST)

DECLARE 
 connection_id NUMBER;
 remote_ssh_port NUMBER := 22;
 username VARCHAR2(200);
 password VARCHAR2(200); 
 
 files ORA_SFTP_FILES_LIST;
BEGIN
 username:= 'oracle'; 
 password:= '123456';
 connection_id := ORA_SFTP.CONNECT_HOST('192.168.40.143', remote_ssh_port, username, password);
 dbms_output.put_line('Current folder is ' || ORA_SFTP.CURRENT_DIRECTORY(connection_id));
 dbms_output.put_line('Current folder is ' || ORA_SFTP.PWD(connection_id));
 
 files :=ORA_SFTP.LIST(connection_id);  -- retrieves in current folder
 
 --print the listed file names 
 FOR i IN files.first .. files.last loop
 dbms_output.put_line('file(' || i || ') = ' || files(i));
 END loop;
 
 ORA_SFTP.DISCONNECT_HOST(connection_id); 
EXCEPTION
 WHEN OTHERS THEN
 DBMS_OUTPUT.PUT_LINE('General error : ' || SQLERRM );
END;

#输出
Current folder is /home/oracle
Current folder is /home/oracle
file(1) = .
file(2) = ..
file(3) = .bash_history
file(4) = .bash_logout
file(5) = .bash_profile
file(6) = .bashrc
file(7) = .oracle_jre_usage
file(8) = .sqlcl
file(9) = .viminfo
file(10) = javaclass
file(11) = my_dir
file(12) = setup

参考
https://www.didisoft.com/ora-sftp/