Oracle P/L SQL实现FTP上传、下载功能，以下是此过程包的头部，包体经常打包处理plb，感兴趣用户可以下载下来。
--Oracle上的FTP功能
Create or Replace Package UTL_FTP AUTHID CURRENT_USER as
Type Connection is Record(
Connection UTL_TCP.Connection,
AccountInfo VarChar2(1000),
TransferMethod Char(1), --A: ASCII, E: EBCDIC, I: IMAGE
TransferOption Char(1),
LocalDirectory VarChar2(30),
LastReply VarChar2(32767 )
);
Type File_List is Table of VarChar2(32767) Index by Binary_Integer;
is_FTPStatus VarChar2(800) := 'disconnect';
is_FTPPort Constant Integer := 21;
is_TransferMethod Constant VarChar2(10) := 'ASCII';
ii_OutputLog Constant Integer := 1;
ii_RollBufferLog Constant Integer := 2;
ii_ClientInfoLog Constant Integer := 4;
-- Per RFC 959, if account info ( ACCT ) is requested Then a 332 code
-- should be Returned from the PASS command instead of a Positive Completion
ii_FTPRequestAcct Constant Integer := 332;
gb_Verbose Boolean := False; --是否记录冗长、累赘的日志
gi_LogOptions Integer := ii_OutputLog;
gs_LogText VarChar2(32767) := Null;
Procedure p_SetVerbose(
ab_Verbose in Boolean
);
Procedure p_SetLogOptions(
ai_LogOptions in Integer
);
Procedure p_ClearLog;
--登录到远程FTP服务器
Function f_Login(
as_RemoteHost in VarChar2,
as_Username in VarChar2,
as_Password in VarChar2,
as_LocalDirectory in VarChar2 Default Null,
as_RemoteDir in VarChar2 Default Null,
as_TransferMethod in VarChar2 Default is_TransferMethod,
ai_Timeout in Integer Default Null,
ai_FTPPort in Integer Default is_FTPPort,
as_AccountInfo in VarChar2 Default Null
)Return Connection;
Procedure p_Logout(
ac_Connection in out Nocopy Connection
);
Procedure p_SendFTPCmd(
ac_Connection in out Nocopy Connection,
as_Command in VarChar2,
as_Argument in VarChar2 Default Null,
as_AccountInfo in VarChar2 Default Null
);
Procedure p_ReadReply(
ac_Connection in out Nocopy Connection
);
Procedure p_Rename(
ac_Connection in out Nocopy Connection,
as_OldFilename in VarChar2,
as_NewFilename in VarChar2
);
Procedure p_DeleteFile(
ac_Connection in out Nocopy Connection,
as_Filename in VarChar2
);
Function f_isDirectory(
ac_Connection in out Nocopy Connection,
as_Directory in VarChar2,
ab_CDToo in Boolean Default True
)Return Boolean;
Procedure p_CreateDirectory(
ac_Connection in out Nocopy Connection,
as_Directory in VarChar2
);
Procedure p_DeleteDirectory(
ac_Connection in out Nocopy Connection,
as_Directory in VarChar2
);
Procedure p_SetTransferMethod(
ac_Connection in out Nocopy Connection,
as_TransferMethod in VarChar2,
as_Option in VarChar2 Default Null
);
Procedure p_RemoteCD(
ac_Connection in out Nocopy Connection,
as_Directory in VarChar2,
ab_CreateDir in Boolean Default True
);
Procedure p_RemoteCDup(
ac_Connection in out Nocopy Connection
);
Function f_RemotePWD(
ac_Connection in out Nocopy Connection
)Return VarChar2;
Procedure p_PutClob(
ac_Connection in out Nocopy Connection,
ac_LocalClob in Clob,
as_RemoteFilename in VarChar2,
as_TransferMethod in VarChar2 Default Null
);
Function f_PutClob(
ac_Connection in out Nocopy Connection,
ac_LocalClob in Clob,
as_RemoteFilename in VarChar2,
as_TransferMethod in VarChar2 Default Null
)Return VarChar2;
Procedure p_PutBlob(
ac_Connection in out Nocopy Connection,
ab_LocalBlob in BLOB,
as_RemoteFilename in VarChar2,
ab_ForceBinary in Boolean Default True --强制为二进制
);
Procedure p_GetClob(
ac_Connection in out Nocopy Connection,
as_RemoteFilename in VarChar2,
ac_LocalClob in out Nocopy Clob,
as_TransferMethod in VarChar2 Default Null
);
Function f_GetClob(
ac_Connection in out Nocopy Connection,
as_RemoteFilename in VarChar2,
as_TransferMethod in VarChar2 Default Null
)Return Clob;
Procedure p_GetBlob(
ac_Connection in out Nocopy Connection,
as_RemoteFilename in VarChar2,
ab_LocalBlob in out Nocopy BLOB,
ab_ForceBinary in Boolean Default True
);
Function f_GetBlob(
ac_Connection in out Nocopy Connection,
as_RemoteFilename in VarChar2,
ab_ForceBinary in Boolean Default True
)Return BLOB;
Procedure p_PutFile(
ac_Connection in out Nocopy Connection,
ai_LocalFilename in UTL_File.File_Type,
as_RemoteFilename in VarChar2,
as_TransferMethod in VarChar2 Default Null
);
Procedure p_PutFile(
ac_Connection in out Nocopy Connection,
as_LocalDirectory in VarChar2,
as_LocalFilename in VarChar2,
as_RemoteFilename in VarChar2,
as_TransferMethod in VarChar2 Default Null
);
Function f_PutFile(
ac_Connection in out Nocopy Connection,
as_LocalDirectory in VarChar2,
as_LocalFilename in VarChar2,
as_RemoteFilename in VarChar2,
as_TransferMethod in VarChar2 Default Null
)Return VarChar2;
Procedure p_PutFile(
ac_Connection in out Nocopy Connection,
as_LocalDirectory in VarChar2,
as_LocalFilename in VarChar2
);
Procedure p_GetFile(
ac_Connection in out Nocopy Connection,
as_RemoteFilename in VarChar2,
as_LocalDirectory in VarChar2,
as_LocalFilename in VarChar2,
as_TransferMethod in VarChar2 Default Null
);
Procedure p_GetFile(
ac_Connection in out Nocopy Connection,
as_RemoteFilename in VarChar2,
as_TransferMethod in VarChar2 Default Null
);
Procedure p_GetFile(
ac_Connection in out Nocopy Connection,
as_RemoteFilename in VarChar2,
ai_LocalFilename in out Nocopy UTL_File.File_Type,
as_TransferMethod in VarChar2 Default Null
);
Procedure p_GetFileList(
ac_Connection in out Nocopy Connection,
afl_List out File_List,
as_RemotePath in VarChar2 Default Null,
ab_FilenameOnly in Boolean Default True,
as_FilenamePrefix in VarChar2 Default Null,
as_FilenameExt in VarChar2 Default Null,
as_TransferMethod in VarChar2 Default is_TransferMethod
);
Function f_GetFileList(
ac_Connection in out Nocopy Connection,
as_RemotePath in VarChar2 Default Null,
ab_FilenameOnly in Boolean Default True,
as_FilenamePrefix in VarChar2 Default Null,
as_FilenameExt in VarChar2 Default Null,
as_TransferMethod in VarChar2 Default is_TransferMethod
)Return File_List;
--根据FTP参数或系统事先设定好的IP登录到FTP服务器
--Select UTL_FTP.f_ConnectFTP() From dual;
Function f_ConnectFTP(
as_RemoteSubDir in VarChar2 Default Null, --Remote Subdirectory
as_RemoteFileWildcard in VarChar2 Default Null, --Remote File Wildcard --删除之前生成的文件 如I02-UB*.xls
as_FTPServer in VarChar2, --FTP Server
as_FTPUserID in VarChar2, --FTP User ID
as_FTPPasswd in VarChar2 --FTP Password
)Return UTL_FTP.Connection;
END UTL_FTP;
/
扫一扫
1342
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
