pl/sql中ftp处理代码,PLSQL代码库,德仔网

代码语言
代码分类
【PLSQL】 pl/sql中ftp处理
作者:Dezai.CN / 发布于2011/3/29/ 1435
<div>create or replace package FTP is
-- --------------------------------------------------------------------------
-- Name : <a href="http://www.oracle-base.com/dba/miscellaneous/ftp.pks">http://www.oracle-base.com/dba/miscellaneous/ftp.pks</a>
-- Author : DR Timothy S Hall
-- Description : Basic FTP API. For usage notes see: <a href="http://www.oracle-base.com/articles/misc/FTPFromPLSQL.php">http://www.oracle-base.com/articles/misc/FTPFromPLSQL.php</a>
-- Requirements : UTL_TCP
-- --------------------------------------------------------------------------
Type t_string_table IS TABLE OF VARCHAR2(32767);</div>
<div>Function Login(p_Host In Varchar2,
 p_Port In Varchar2,
 p_User In Varchar2,
 p_Pass In Varchar2,
 p_Timeout In Number := Null) Return Utl_Tcp.Connection;

Procedure Logout(p_Conn In Out Nocopy Utl_Tcp.Connection,
 p_Reply In Boolean := True);</div>
<div>
Function Get_Passive(p_Conn In Out Nocopy Utl_Tcp.Connection)
 Return Utl_Tcp.Connection;</div>
<div>Procedure Get_Reply(p_Conn In Out Nocopy Utl_Tcp.Connection);</div>
<div>Procedure Send_Command(p_Conn In Out Nocopy Utl_Tcp.Connection,
 p_Command In Varchar2,
 p_Reply In Boolean := True);</div>
<div>
Function Get_Local_Ascii_Data(p_Dir In Varchar2, p_File In Varchar2)
 Return Clob;</div>
<div>Function Get_Local_Binary_Data(p_Dir In Varchar2, p_File In Varchar2)
 Return Blob;</div>
<div>Function Get_Remote_Ascii_Data(p_Conn In Out Nocopy Utl_Tcp.Connection,
 p_File In Varchar2) Return Clob;</div>
<div>Function Get_Remote_Binary_Data(p_Conn In Out Nocopy Utl_Tcp.Connection,
 p_File In Varchar2) Return Blob;</div>
<div>Procedure Put_Local_Ascii_Data(p_Data In Clob,
 p_Dir In Varchar2,
 p_File In Varchar2);</div>
<div>Procedure Put_Local_Binary_Data(p_Data In Blob,
 p_Dir In Varchar2,
 p_File In Varchar2);</div>
<div>Procedure Put_Remote_Ascii_Data(p_Conn In Out Nocopy Utl_Tcp.Connection,
 p_File In Varchar2,
 p_Data In Clob);</div>
<div>Procedure Put_Remote_Binary_Data(p_Conn In Out Nocopy Utl_Tcp.Connection,
 p_File In Varchar2,
 p_Data In Blob);</div>
<div>
Procedure Get(p_Conn In Out Nocopy Utl_Tcp.Connection,
 p_From_File In Varchar2,
 p_To_Dir In Varchar2,
 p_To_File In Varchar2);</div>
<div>Procedure Put(p_Conn In Out Nocopy Utl_Tcp.Connection,
 p_From_Dir In Varchar2,
 p_From_File In Varchar2,
 p_To_File In Varchar2);</div>
<div>Procedure Get_Direct(p_Conn In Out Nocopy Utl_Tcp.Connection,
 p_From_File In Varchar2,
 p_To_Dir In Varchar2,
 p_To_File In Varchar2);</div>
<div>Procedure Put_Direct(p_Conn In Out Nocopy Utl_Tcp.Connection,
 p_From_Dir In Varchar2,
 p_From_File In Varchar2,
 p_To_File In Varchar2);</div>
<div>Procedure Help(p_Conn In Out Nocopy Utl_Tcp.Connection);</div>
<div>Procedure Ascii(p_Conn In Out Nocopy Utl_Tcp.Connection);</div>
<div>Procedure Binary(p_Conn In Out Nocopy Utl_Tcp.Connection);</div>
<div>Procedure List(p_Conn In Out Nocopy Utl_Tcp.Connection,
 p_Dir In Varchar2,
 p_List Out t_String_Table);</div>
<div>Procedure Nlst(p_Conn In Out Nocopy Utl_Tcp.Connection,
 p_Dir In Varchar2,
 p_List Out t_String_Table);</div>
<div>Procedure Rename(p_Conn In Out Nocopy Utl_Tcp.Connection,
 p_From In Varchar2,
 p_To In Varchar2);</div>
<div>Procedure Delete(p_Conn In Out Nocopy Utl_Tcp.Connection,
 p_File In Varchar2);</div>
<div>Procedure Mkdir(p_Conn In Out Nocopy Utl_Tcp.Connection, p_Dir In Varchar2);</div>
<div>Procedure Rmdir(p_Conn In Out Nocopy Utl_Tcp.Connection, p_Dir In Varchar2);</div>
<div>Procedure Convert_Crlf(p_Status In Boolean);</div>
<div>end FTP;
/
create or replace package body FTP is
-- --------------------------------------------------------------------------
-- Name : <a href="http://www.oracle-base.com/dba/miscellaneous/ftp.pkb">http://www.oracle-base.com/dba/miscellaneous/ftp.pkb</a>
-- Author : DR Timothy S Hall
-- Description : Basic FTP API. For usage notes see: <a href="http://www.oracle-base.com/articles/misc/FTPFromPLSQL.php">http://www.oracle-base.com/articles/misc/FTPFromPLSQL.php</a>
-- Requirements : <a href="http://www.oracle-base.com/dba/miscellaneous/ftp.pks">http://www.oracle-base.com/dba/miscellaneous/ftp.pks</a>
-- --------------------------------------------------------------------------
g_reply t_string_table := t_string_table();
g_binary BOOLEAN := TRUE;
g_debug BOOLEAN := False;
g_convert_crlf BOOLEAN := TRUE;</div>
<div>Procedure Debug(p_Text In Varchar2);</div>
<div>-- =====================================
Function Login(p_Host In Varchar2,
 p_Port In Varchar2,
 p_User In Varchar2,
 p_Pass In Varchar2,
 p_Timeout In Number := Null) Return Utl_Tcp.Connection Is
 l_Conn Utl_Tcp.Connection;
Begin
 g_Reply.Delete;</div>
<div> l_Conn := Utl_Tcp.Open_Connection(p_Host, p_Port);
 Get_Reply(l_Conn);
 Send_Command(l_Conn, 'USER ' || p_User);
 Send_Command(l_Conn, 'PASS ' || p_Pass);
 Return l_Conn;
End;</div>
<div>-- --------------------------------------------------------------------------
Procedure Logout(p_Conn In Out Nocopy Utl_Tcp.Connection,
 p_Reply In Boolean := True) As
Begin
 Send_Command(p_Conn, 'QUIT', p_Reply);
 Utl_Tcp.Close_Connection(p_Conn);
End;</div>
<div>
-- =====================================
Function Get_Passive(p_Conn In Out Nocopy Utl_Tcp.Connection)
 Return Utl_Tcp.Connection Is
 l_Conn Utl_Tcp.Connection;
 l_Reply Varchar2(32767);
 l_Host Varchar(100);
 l_Port1 Number(10);
 l_Port2 Number(10);
Begin
 Send_Command(p_Conn, 'PASV');
 l_Reply := g_Reply(g_Reply.Last);</div>
<div> l_Reply := Replace(Substr(l_Reply,
 Instr(l_Reply, '(') + 1,
 (Instr(l_Reply, ')')) - (Instr(l_Reply, '(')) - 1),
 ',',
 '.');
 l_Host := Substr(l_Reply, 1, Instr(l_Reply, '.', 1, 4) - 1);</div>
<div> l_Port1 := To_Number(Substr(l_Reply,
 Instr(l_Reply, '.', 1, 4) + 1,
 (Instr(l_Reply, '.', 1, 5) - 1) -
 (Instr(l_Reply, '.', 1, 4))));
 l_Port2 := To_Number(Substr(l_Reply, Instr(l_Reply, '.', 1, 5) + 1));</div>
<div> l_Conn := Utl_Tcp.Open_Connection(l_Host, 256 * l_Port1 + l_Port2);
 Return l_Conn;
End;</div>
<div>-- --------------------------------------------------------------------------
Procedure Get_Reply(p_Conn In Out Nocopy Utl_Tcp.Connection) Is
 l_Reply_Code Varchar2(3) := Null;
Begin
 Loop
 g_Reply.Extend;
 g_Reply(g_Reply.Last) := Utl_Tcp.Get_Line(p_Conn, True);
 Debug(g_Reply(g_Reply.Last));
 If l_Reply_Code Is Null Then
 l_Reply_Code := Substr(g_Reply(g_Reply.Last), 1, 3);
 End If;
 
 /*FTP服务器返回错误代码时，不弹出警告框*/
 If Substr(l_Reply_Code, 1, 3) = '550' Then 
 --Raise_Application_Error(-20000, g_Reply(g_Reply.Last));
 Exit;
 ElsIf Substr(l_Reply_Code, 1, 1) In ('4', '5') Then 
 Raise_Application_Error(-20000, g_Reply(g_Reply.Last));
 Elsif (Substr(g_Reply(g_Reply.Last), 1, 3) = l_Reply_Code And Substr(g_Reply(g_Reply.Last), 4, 1) = ' ') Then
 Exit;
 End If;
 End Loop;
Exception
 When Utl_Tcp.End_Of_Input Then
 Null;
End;</div>
<div>-- --------------------------------------------------------------------------
Procedure Send_Command(p_Conn In Out Nocopy Utl_Tcp.Connection,
 p_Command In Varchar2,
 p_Reply In Boolean := True) Is
 l_Result Pls_Integer;
Begin
 l_Result := Utl_Tcp.Write_Line(p_Conn, p_Command);
 -- If you get ORA-29260 after the PASV call, replace the above line with the following line.
 -- l_result := UTL_TCP.write_text(p_conn, p_command || utl_tcp.crlf, length(p_command || utl_tcp.crlf));</div>
<div> If p_Reply Then
 Get_Reply(p_Conn);
 End If;
End;</div>
<div></div>
<div>-- =====================================
Function Get_Local_Ascii_Data(p_Dir In Varchar2, p_File In Varchar2)
 Return Clob Is
 l_Bfile Bfile;
 l_Data Clob;
Begin
 Dbms_Lob.Createtemporary(Lob_Loc => l_Data,
 Cache => True,
 Dur => Dbms_Lob.Call);</div>
<div> l_Bfile := Bfilename(p_Dir, p_File);
 Dbms_Lob.Fileopen(l_Bfile, Dbms_Lob.File_Readonly);</div>
<div> If Dbms_Lob.Getlength(l_Bfile) > 0 Then
 Dbms_Lob.Loadfromfile(l_Data, l_Bfile, Dbms_Lob.Getlength(l_Bfile));
 End If;</div>
<div> Dbms_Lob.Fileclose(l_Bfile);</div>
<div> Return l_Data;
End;</div>
<div>-- --------------------------------------------------------------------------
Function Get_Local_Binary_Data(p_Dir In Varchar2, p_File In Varchar2)
 Return Blob Is
 l_Bfile Bfile;
 l_Data Blob;
Begin
 Dbms_Lob.Createtemporary(Lob_Loc => l_Data,
 Cache => True,
 Dur => Dbms_Lob.Call);</div>
<div> l_Bfile := Bfilename(p_Dir, p_File);
 Dbms_Lob.Fileopen(l_Bfile, Dbms_Lob.File_Readonly);
 If Dbms_Lob.Getlength(l_Bfile) > 0 Then
 Dbms_Lob.Loadfromfile(l_Data, l_Bfile, Dbms_Lob.Getlength(l_Bfile));
 End If;
 Dbms_Lob.Fileclose(l_Bfile);</div>
<div> Return l_Data;
End;</div>
<div>-- --------------------------------------------------------------------------
Function Get_Remote_Ascii_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);</div>
<div> l_Conn := Get_Passive(p_Conn);
 Send_Command(p_Conn, 'RETR ' || p_File, True);
 --logout(l_conn, FALSE);</div>
<div> 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);
 Get_Reply(p_Conn);</div>
<div> Return l_Data;</div>
<div>Exception
 When Others Then
 Utl_Tcp.Close_Connection(l_Conn);
 Raise;
End;</div>
<div>-- --------------------------------------------------------------------------
Function Get_Remote_Binary_Data(p_Conn In Out Nocopy Utl_Tcp.Connection,
 p_File In Varchar2) Return Blob Is
 l_Conn Utl_Tcp.Connection;
 l_Amount Pls_Integer;
 l_Buffer Raw(32767);
 l_Data Blob;
Begin
 Dbms_Lob.Createtemporary(l_Data, True, Dbms_Lob.Call);</div>
<div> l_Conn := Get_Passive(p_Conn);
 Send_Command(p_Conn, 'RETR ' || p_File, True);</div>
<div> Begin
 Loop
 l_Amount := Utl_Tcp.Read_Raw(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
 Utl_Tcp.Close_Connection(l_Conn); --
 Return Empty_Blob(); --Null;
 End;
 
 Utl_Tcp.Close_Connection(l_Conn);
 Get_Reply(p_Conn);
 
 Return l_Data;
 
Exception
 When Others Then
 Utl_Tcp.Close_Connection(l_Conn);
 Raise;
End;</div>
<div>-- --------------------------------------------------------------------------
Procedure Put_Local_Ascii_Data(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_Data);</div>
<div> l_Out_File := Utl_File.Fopen(p_Dir, p_File, 'w', 32767);</div>
<div> While l_Pos <= l_Clob_Len Loop
 Dbms_Lob.Read(p_Data, l_Amount, l_Pos, l_Buffer);
 If g_Convert_Crlf Then
 l_Buffer := Replace(l_Buffer, Chr(13), Null);
 End If;
 
 Utl_File.Put(l_Out_File, l_Buffer);
 Utl_File.Fflush(l_Out_File);
 l_Pos := l_Pos + l_Amount;
 End Loop;</div>
<div> Utl_File.Fclose(l_Out_File);
Exception
 When Others Then
 If Utl_File.Is_Open(l_Out_File) Then
 Utl_File.Fclose(l_Out_File);
 End If;
 Raise;
End;</div>
<div>-- --------------------------------------------------------------------------
PROCEDURE put_local_binary_data (p_data IN BLOB,
 p_dir IN VARCHAR2,
 p_file IN VARCHAR2) IS
 l_out_file UTL_FILE.file_type;
 l_buffer RAW(32767);
 l_amount BINARY_INTEGER := 32767;
 l_pos INTEGER := 1;
 l_blob_len INTEGER;
BEGIN
 l_blob_len := DBMS_LOB.getlength(p_data);</div>
<div> l_out_file := UTL_FILE.fopen(p_dir, p_file, 'wb', 32767);
 
 WHILE l_pos <= l_blob_len LOOP
 DBMS_LOB.read (p_data, l_amount, l_pos, l_buffer);
 UTL_FILE.put_raw(l_out_file, l_buffer, TRUE);
 UTL_FILE.fflush(l_out_file);
 l_pos := l_pos + l_amount;
 END LOOP;
 
 UTL_FILE.fclose(l_out_file);
EXCEPTION
 WHEN OTHERS THEN
 IF UTL_FILE.is_open(l_out_file) THEN
 UTL_FILE.fclose(l_out_file);
 END IF;
 RAISE;
END;</div>
<div>-- --------------------------------------------------------------------------
PROCEDURE put_remote_ascii_data (p_conn IN OUT NOCOPY UTL_TCP.connection,
 p_file IN VARCHAR2,
 p_data IN CLOB) IS
 l_conn UTL_TCP.connection;
 l_result PLS_INTEGER;
 l_buffer VARCHAR2(32767);
 l_amount BINARY_INTEGER := 32767;
 l_pos INTEGER := 1;
 l_clob_len INTEGER;
BEGIN
 l_conn := get_passive(p_conn);
 send_command(p_conn, 'STOR ' || p_file, TRUE);
 
 l_clob_len := DBMS_LOB.getlength(p_data);</div>
<div> WHILE l_pos <= l_clob_len LOOP
 DBMS_LOB.READ (p_data, l_amount, l_pos, l_buffer);
 IF g_convert_crlf THEN
 l_buffer := REPLACE(l_buffer, CHR(13), NULL);
 END IF;
 l_result := UTL_TCP.write_text(l_conn, l_buffer, LENGTH(l_buffer));
 UTL_TCP.flush(l_conn);
 l_pos := l_pos + l_amount;
 END LOOP;</div>
<div> UTL_TCP.close_connection(l_conn);
 -- The following line allows some people to make multiple calls from one connection.
 -- It causes the operation to hang for me, hence it is commented out by default.
 -- get_reply(p_conn);</div>
<div>EXCEPTION
 WHEN OTHERS THEN
 UTL_TCP.close_connection(l_conn);
 RAISE;
END;</div>
<div>-- --------------------------------------------------------------------------
PROCEDURE put_remote_binary_data (p_conn IN OUT NOCOPY UTL_TCP.connection,
 p_file IN VARCHAR2,
 p_data IN BLOB) IS
 l_conn UTL_TCP.connection;
 l_result PLS_INTEGER;
 l_buffer RAW(32767);
 l_amount BINARY_INTEGER := 32767;
 l_pos INTEGER := 1;
 l_blob_len INTEGER;
BEGIN
 l_conn := get_passive(p_conn);
 send_command(p_conn, 'STOR ' || p_file, TRUE);
 
 l_blob_len := DBMS_LOB.getlength(p_data);</div>
<div> WHILE l_pos <= l_blob_len LOOP
 DBMS_LOB.READ (p_data, l_amount, l_pos, l_buffer);
 l_result := UTL_TCP.write_raw(l_conn, l_buffer, l_amount);
 UTL_TCP.flush(l_conn);
 l_pos := l_pos + l_amount;
 END LOOP;</div>
<div> UTL_TCP.close_connection(l_conn);
 -- The following line allows some people to make multiple calls from one connection.
 -- It causes the operation to hang for me, hence it is commented out by default.
 -- get_reply(p_conn);</div>
<div>EXCEPTION
 WHEN OTHERS THEN
 UTL_TCP.close_connection(l_conn);
 RAISE;
END;</div>
<div>
-- =====================================
Procedure Get(p_Conn In Out Nocopy Utl_Tcp.Connection,
 p_From_File In Varchar2,
 p_To_Dir In Varchar2,
 p_To_File In Varchar2) As
Begin
 If g_Binary Then
 Put_Local_Binary_Data(Get_Remote_Binary_Data(p_Conn,
 p_From_File),
 p_To_Dir,
 p_To_File);
 Else
 Put_Local_Ascii_Data(Get_Remote_Ascii_Data(p_Conn,
 p_From_File),
 p_To_Dir,
 p_To_File);
 End If;
End;</div>
<div>-- --------------------------------------------------------------------------
Procedure Put(p_Conn In Out Nocopy Utl_Tcp.Connection,
 p_From_Dir In Varchar2,
 p_From_File In Varchar2,
 p_To_File In Varchar2) As
Begin
 If g_Binary Then
 Put_Remote_Binary_Data(p_Conn => p_Conn,
 p_File => p_To_File,
 p_Data => Get_Local_Binary_Data(p_From_Dir,
 p_From_File));
 Else
 Put_Remote_Ascii_Data(p_Conn => p_Conn,
 p_File => p_To_File,
 p_Data => Get_Local_Ascii_Data(p_From_Dir,
 p_From_File));
 End If;
 Get_Reply(p_Conn);
End;</div>
<div>-- --------------------------------------------------------------------------
Procedure Get_Direct(p_Conn In Out Nocopy Utl_Tcp.Connection,
 p_From_File In Varchar2,
 p_To_Dir In Varchar2,
 p_To_File In Varchar2) Is
 l_Conn Utl_Tcp.Connection;
 l_Out_File Utl_File.File_Type;
 l_Amount Pls_Integer;
 l_Buffer Varchar2(32767);
 l_Raw_Buffer Raw(32767);
Begin
 l_Conn := Get_Passive(p_Conn);
 Send_Command(p_Conn, 'RETR ' || p_From_File, True);
 If g_Binary Then
 l_Out_File := Utl_File.Fopen(p_To_Dir, p_To_File, 'wb', 32767);
 Else
 l_Out_File := Utl_File.Fopen(p_To_Dir, p_To_File, 'w', 32767);
 End If;</div>
<div> Begin
 Loop
 If g_Binary Then
 l_Amount := Utl_Tcp.Read_Raw(l_Conn, l_Raw_Buffer, 32767);
 Utl_File.Put_Raw(l_Out_File, l_Raw_Buffer, True);
 Else
 l_Amount := Utl_Tcp.Read_Text(l_Conn, l_Buffer, 32767);
 If g_Convert_Crlf Then
 l_Buffer := Replace(l_Buffer, Chr(13), Null);
 End If;
 Utl_File.Put(l_Out_File, l_Buffer);
 End If;
 Utl_File.Fflush(l_Out_File);
 End Loop;
 Exception
 When Utl_Tcp.End_Of_Input Then
 Null;
 When Others Then
 Null;
 End;
 Utl_File.Fclose(l_Out_File);
 Utl_Tcp.Close_Connection(l_Conn);
Exception
 When Others Then
 If Utl_File.Is_Open(l_Out_File) Then
 Utl_File.Fclose(l_Out_File);
 End If;
 Raise;
End;</div>
<div>-- --------------------------------------------------------------------------
Procedure Put_Direct(p_Conn In Out Nocopy Utl_Tcp.Connection,
 p_From_Dir In Varchar2,
 p_From_File In Varchar2,
 p_To_File In Varchar2) Is
 l_Conn Utl_Tcp.Connection;
 l_Bfile Bfile;
 l_Result Pls_Integer;
 l_Amount Pls_Integer := 32767;
 l_Raw_Buffer Raw(32767);
 l_Len Number;
 l_Pos Number := 1;
 Ex_Ascii Exception;
Begin
 If Not g_Binary Then
 Raise Ex_Ascii;
 End If;</div>
<div> l_Conn := Get_Passive(p_Conn);
 Send_Command(p_Conn, 'STOR ' || p_To_File, True);</div>
<div> l_Bfile := Bfilename(p_From_Dir, p_From_File);</div>
<div> Dbms_Lob.Fileopen(l_Bfile, Dbms_Lob.File_Readonly);
 l_Len := Dbms_Lob.Getlength(l_Bfile);</div>
<div> While l_Pos <= l_Len Loop
 Dbms_Lob.Read(l_Bfile, l_Amount, l_Pos, l_Raw_Buffer);
 Debug(l_Amount);
 l_Result := Utl_Tcp.Write_Raw(l_Conn, l_Raw_Buffer, l_Amount);
 l_Pos := l_Pos + l_Amount;
 End Loop;</div>
<div> Dbms_Lob.Fileclose(l_Bfile);
 Utl_Tcp.Close_Connection(l_Conn);
Exception
 When Ex_Ascii Then
 Raise_Application_Error(-20000,
 'PUT_DIRECT not available in ASCII mode.');
 When Others Then
 If Dbms_Lob.Fileisopen(l_Bfile) = 1 Then
 Dbms_Lob.Fileclose(l_Bfile);
 End If;
 Raise;
End;</div>
<div>-- --------------------------------------------------------------------------
Procedure Help(p_Conn In Out Nocopy Utl_Tcp.Connection) As
Begin
 Send_Command(p_Conn, 'HELP', True);
End;</div>
<div>
-- ===================================
Procedure Binary(p_Conn In Out Nocopy Utl_Tcp.Connection) As
Begin
 Send_Command(p_Conn, 'TYPE I', True);
 g_Binary := True;
End;</div>
<div>-- --------------------------------------------------------------------------
Procedure Ascii(p_Conn In Out Nocopy Utl_Tcp.Connection) As
Begin
 Send_Command(p_Conn, 'TYPE A', True);
 g_Binary := False;
End;</div>
<div>-- --------------------------------------------------------------------------
Procedure List(p_Conn In Out Nocopy Utl_Tcp.Connection,
 p_Dir In Varchar2,
 p_List Out t_String_Table) As
 l_Conn Utl_Tcp.Connection;
 l_List t_String_Table := t_String_Table();
 l_Reply_Code Varchar2(3) := Null;
Begin
 l_Conn := Get_Passive(p_Conn);
 Send_Command(p_Conn, 'LIST ' || p_Dir, True);</div>
<div> Begin
 Loop
 l_List.Extend;
 l_List(l_List.Last) := Utl_Tcp.Get_Line(l_Conn, True);
 Debug(l_List(l_List.Last));
 If l_Reply_Code Is Null Then
 l_Reply_Code := Substr(l_List(l_List.Last), 1, 3);
 End If;
 If Substr(l_Reply_Code, 1, 1) In ('4', '5') Then
 Raise_Application_Error(-20000, l_List(l_List.Last));
 Elsif (Substr(g_Reply(g_Reply.Last), 1, 3) = l_Reply_Code And
 Substr(g_Reply(g_Reply.Last), 4, 1) = ' ') Then
 Exit;
 End If;
 End Loop;
 Exception
 When Utl_Tcp.End_Of_Input Then
 Null;
 End;</div>
<div> l_List.Delete(l_List.Last);
 p_List := l_List;</div>
<div> Utl_Tcp.Close_Connection(l_Conn);
 Get_Reply(p_Conn);
End;</div>
<div>-- --------------------------------------------------------------------------
Procedure Nlst(p_Conn In Out Nocopy Utl_Tcp.Connection,
 p_Dir In Varchar2,
 p_List Out t_String_Table) As
 l_Conn Utl_Tcp.Connection;
 l_List t_String_Table := t_String_Table();
 l_Reply_Code Varchar2(3) := Null;
Begin
 l_Conn := Get_Passive(p_Conn);
 Send_Command(p_Conn, 'NLST ' || p_Dir, True);</div>
<div> Begin
 Loop
 l_List.Extend;
 l_List(l_List.Last) := Utl_Tcp.Get_Line(l_Conn, True);
 Debug(l_List(l_List.Last));
 If l_Reply_Code Is Null Then
 l_Reply_Code := Substr(l_List(l_List.Last), 1, 3);
 End If;
 If Substr(l_Reply_Code, 1, 1) In ('4', '5') Then
 Raise_Application_Error(-20000, l_List(l_List.Last));
 Elsif (Substr(g_Reply(g_Reply.Last), 1, 3) = l_Reply_Code And
 Substr(g_Reply(g_Reply.Last), 4, 1) = ' ') Then
 Exit;
 End If;
 End Loop;
 Exception
 When Utl_Tcp.End_Of_Input Then
 Null;
 End;</div>
<div> l_List.Delete(l_List.Last);
 p_List := l_List;</div>
<div> Utl_Tcp.Close_Connection(l_Conn);
 Get_Reply(p_Conn);
End;</div>
<div>-- --------------------------------------------------------------------------
Procedure Rename(p_Conn In Out Nocopy Utl_Tcp.Connection,
 p_From In Varchar2,
 p_To In Varchar2) As
 l_Conn Utl_Tcp.Connection;
Begin
 l_Conn := Get_Passive(p_Conn);
 Send_Command(p_Conn, 'RNFR ' || p_From, True);
 Send_Command(p_Conn, 'RNTO ' || p_To, True);
 Logout(l_Conn, False);
End Rename;</div>
<div>-- --------------------------------------------------------------------------
Procedure Delete(p_Conn In Out Nocopy Utl_Tcp.Connection,
 p_File In Varchar2) As
 l_Conn Utl_Tcp.Connection;
Begin
 l_Conn := Get_Passive(p_Conn);
 Send_Command(p_Conn, 'DELE ' || p_File, True);
 Logout(l_Conn, False);
End Delete;</div>
<div>-- --------------------------------------------------------------------------
Procedure Mkdir(p_Conn In Out Nocopy Utl_Tcp.Connection, p_Dir In Varchar2) As
 l_Conn Utl_Tcp.Connection;
Begin
 l_Conn := Get_Passive(p_Conn);
 Send_Command(p_Conn, 'MKD ' || p_Dir, True);
 Logout(l_Conn, False);
End Mkdir;</div>
<div>-- --------------------------------------------------------------------------
Procedure Rmdir(p_Conn In Out Nocopy Utl_Tcp.Connection, p_Dir In Varchar2) As
 l_Conn Utl_Tcp.Connection;
Begin
 l_Conn := Get_Passive(p_Conn);
 Send_Command(p_Conn, 'RMD ' || p_Dir, True);
 Logout(l_Conn, False);
End Rmdir;</div>
<div>-- --------------------------------------------------------------------------
Procedure Convert_Crlf(p_Status In Boolean) As
Begin
 g_Convert_Crlf := p_Status;
End;</div>
<div>-- --------------------------------------------------------------------------
Procedure Debug(p_Text In Varchar2) Is
Begin
 /*大数据量循环操作时DBMS输出缓冲区溢出，故置g_Debug:=false;*/
 If g_Debug Then
 Dbms_Output.Put_Line(Substr(p_Text, 1, 255));
 End If;
End;</div>
<div>Begin
 -- Initialization
 Null;
End Ftp;
/
</div>
试试其它关键字
　ftp处理　
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)