oracle中实现ftp文件上传

1、敲好最近有机会用到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.a

2、ppend()函数，但是在一般的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.i

3、o.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 cla

4、ss 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

5、 = 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

6、(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 (clien

7、t != 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

8、,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"); /重新命名文件名称 /Tel

9、netOutputStream 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) /cli

10、ent.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)

11、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文

12、件内容 */ 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(fg

13、et,"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 =

14、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;impo

15、rt 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

16、 /实现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(fileN

17、ame, 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(

18、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 p

19、ublic 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.clo

20、seConnection(); /关闭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_c

21、onn 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.conne

22、ction, 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.connecti

23、on; 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_b

24、uffer, 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 WHEN OTHERS THEN UTL_TCP.close_connection(l_conn); RAISE;END;ftp上传PROCEDURE send_xml (p_data IN CLOB, p_dir IN VARCHAR2, p_file IN VARCHAR2) IS l_out_file UTL_FILE.file_type; l_buffer VARCHAR2(32767); l_amount BINARY_INTEGER := 32767; l_pos INTEGER := 1; l_clob_len INTEGER;BEGIN l_clob_len := DBMS_LOB.getlength(p