oracle中实现ftp文件上传下载.doc

敲好最近有机会用到ftp发送接收文件，总结一下oracle里面ftp发送接收的方法。Java中可以使用.ftp.FtpClient实现简单的ftp操作，这是在oracle数据库中有提供api的。我个人觉得对于ftp的操作用java方式比较方便，代码简单容易懂而且广泛。不过oracle中也有提供ftp访问的包 UTL_TCP,通过这个包也可以很方便的实现ftp操作。Java方式实现ftp客户端操作在oracle数据库中有提供 .ftp.FtpClient ，所以可以直接使用该包完成简单的ftp操作在oracle数据库端。这里有个问题没有解决：ftp文件追加时候，发现没有提供FtpClient.append()函数，但是在一般的java程序中式可以使用，测试都是在jdk1.4版本上进行的。还是在oracle里面这个功能不允许还是我没有找到append替代方法呢？create or replace and compile java source named remoteFtpClient ASpackage oracle.apps.zz.zzmes;import java.io.BufferedReader;import java.io.DataInputStream;import java.io.File;import java.io.IOException;import java.io.InputStreamReader;import java.io.RandomAccessFile;import java.io.FileInputStream;import java.util.StringTokenizer;import .TelnetInputStream;import .TelnetOutputStream;import .ftp.FtpClient;import .*; import .ftp.*;/import org.apache.*;/import .ftp.*;/import .ftp.FTP.*;/import .ftp.FTP.*;public class remoteFtpClient private String host; private String username; private String password; private FtpClient client; public remoteFtpClient() public remoteFtpClient(String Host, String userName,String passWord) this.host= Host;/ ip 地址 this.username=userName;/用户名 this.password=passWord;/密码 this.client = new FtpClient(); public void setHost(String Host) host=Host; public void setUserName(String userName) username=userName; public void setPassword(String pwd) password=pwd; /* * 获取和远程ftp服务器的连接 */ public boolean getFtpConnection( ) /client = new FtpClient(); try client.openServer(host); client.login(username,password); /client.ascii(); client.binary(); / client.setConnectTimeout(6000); /设置超时 System.out.println(login sucess); catch (IOException e) / TODO Auto-generated catch block e.printStackTrace(); return false; return true; /* * 关闭ftp连接 */ public void closeConnection() if (client != null) try client.closeServer(); catch (IOException e) / TODO Auto-generated catch block e.printStackTrace(); client = null; /* * 实现ftp文件上传 * parameters * fileName: 文件名 * sendcontent：要发送的内容 * path：上传的路径,路径为空则取登录ftp后的默认路径 * return: 成功返回 true，失败返回false */ public boolean sendXml2Ftp(String fileName,String sendContent,String path) try if(path !=null) client.cd(path); /发现在数据库中不能使用client.append(path); String lineSeparator =System.getProperty(line.separator); TelnetOutputStream os = client.put(fileName); /创建一个新的文件或者覆盖 / client.rename(index, wip); /重新命名文件名称 /TelnetOutputStream os = client.append(fileName); / os.write(sendContent.getBytes(UTF-8); /os.write(sendContent.getBytes(GB2312); String encoding = System.getProperty(file.encoding); os.write(sendContent.getBytes(encoding); os.close(); / client.rename(arg0, arg1) /client.sendServer(DELE wip.xml ); /删除文件 catch (IOException e) System.out.println(insert a record to error table!); / TODO Auto-generated catch block e.printStackTrace(); return false; return true; public static String replaceAllString(String str, String pattern,String replaces) StringBuffer result = new StringBuffer(); StringTokenizer strToken = new StringTokenizer(str,pattern); while(strToken.hasMoreTokens() result.append(String)strToken.nextToken(); result.append(replaces); return result.toString(); /* * 实现ftp下载 * parameters: * fileName: 文件名称 * path: 文件路径 * return : ftp文件内容 */ public String loadXml2Temp(String fileName,String path) TelnetInputStream fget; String xmlString =null ; String encoding = System.getProperty(file.encoding); String temp; try if(path !=null) client.cd(path); fget=client.get(fileName); / InputStreamReader s=new InputStreamReader(fget,GB2312);/接收的字符集需要和要下载的文件最好字符集相同，否则在中文时候会有乱码 InputStreamReader s=new InputStreamReader(fget,UTF-8); BufferedReader in=new BufferedReader(s); StringBuffer sb = new StringBuffer(); while( (temp=in.readLine() !=null) sb.append(temp.trim(); sb.append(n); xmlString =sb.toString(); s.close(); catch (IOException e) / TODO Auto-generated catch block e.printStackTrace(); return null; return xmlString; 通过在另一个java存储过程调用以上的以上ftp存储过程，可以实现ftp文件的上传下载例如： create or replace and compile java source named ftpSendGetTest ASpackage oracle.apps.zz.zzmes;import java.sql.Clob;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.Hashtable;import oracle.xml.sql.query.OracleXMLQuery;import java.io.*;import .ftp.FtpClient;import oracle.sql.*;import oracle.jdbc.OracleDriver;import java.sql.Connection;public class ftpSendGetTest /实现ftp下载，并转换为xml文件格式并返回clob public static CLOB getXml(String host,String username,String pwd,String fileName ,String path) String xml= ; CLOB tempClob=null; /ftp类构造 remoteFtpClient fc = new remoteFtpClient( host, username,pwd); if(fc.getFtpConnection() /连接ftp xml = fc.loadXml2Temp(fileName, path); /调用下载函数 if(xml !=null) try /实现String转换为clob Connection conn = new OracleDriver().defaultConnection(); tempClob = CLOB.createTemporary(conn, true, CLOB.DURATION_SESSION); tempClob.open(CLOB.MODE_READWRITE); Writer tempClobWriter = tempClob.getCharacterOutputStream(); tempClobWriter.write(xml); tempClobWriter.flush(); tempClobWriter.close(); tempClob.close(); conn.close(); catch (SQLException e) / TODO Auto-generated catch block e.printStackTrace(); catch (IOException e) / TODO Auto-generated catch block e.printStackTrace(); fc.closeConnection(); /关闭ftp连接 return tempClob; /发送数据到ftp public static String sendxml(String host,String username,String pwd,String fileName ,String path,String content) String b=N; remoteFtpClient fc = new remoteFtpClient( host, username,pwd); if(fc.getFtpConnection() /连接ftp if(fc.sendXml2Ftp(fileName, content,path) /发送文件 b=Y; fc.closeConnection(); /关闭ftp连接 return b; Pl/sql中实现ftp客户端操作在oracle中提供了数据库包 SYS.utl_tcp 实现客户端操作。ftp操作详细的可以查看该package. 举例一个简单的ftp上传下载程序。ftp连接FUNCTION login (p_host IN VARCHAR2, p_port IN VARCHAR2, /ftp 端口 21 p_user IN VARCHAR2, p_pass IN VARCHAR2, p_timeout IN NUMBER := NULL) RETURN UTL_TCP.connection IS l_conn UTL_TCP.connection;BEGIN l_conn := UTL_TCP.open_connection(p_host, p_port, tx_timeout = p_timeout); get_reply (l_conn); send_command(l_conn, USER | p_user); send_command(l_conn, PASS | p_pass); RETURN l_conn;END;ftp关闭连接PROCEDURE logout(p_conn IN OUT NOCOPY UTL_TCP.connection, p_reply IN BOOLEAN := TRUE) ASl_reply t_string_table := t_string_table();BEGIN send_command(p_conn, QUIT, l_reply); UTL_TCP.close_connection(p_conn);END;ftp下载FUNCTION get_remote_data (p_conn IN OUT NOCOPY UTL_TCP.connection, p_file IN VARCHAR2) RETURN CLOB IS l_conn UTL_TCP.connection; l_amount PLS_INTEGER; l_buffer VARCHAR2(32767); l_data CLOB;BEGIN DBMS_LOB.createtemporary (lob_loc = l_data, cache = TRUE, dur = DBMS_LOB.call); l_conn := get_passive(p_conn); send_command(p_conn, RETR | p_file, TRUE); BEGIN LOOP l_amount := UTL_TCP.read_text (l_conn, l_buffer, 32767); DBMS_LOB.writeappend(l_data, l_amount, l_buffer); END LOOP; EXCEPTION WHEN UTL_TCP.END_OF_INPUT THEN NULL; WHEN OTHERS THEN NULL; END; UTL_TCP.close_connection(l_conn); RETURN l_data;EXCEPTION W