NoReplyRule的博客

Oracle P/L SQL实现PDF报表内容生成，包体经过打包处理plb，内付包头说明和测试案例（调用例子），感兴趣用户可以下载下来。

Oracle P/L SQL生成XML格式的Excel内容，包体经过打包处理plb，内付包头说明和测试案例（调用例子），感兴趣用户可以下载下来。

Oracle P/L SQL实现文件压缩、解压功能，包体经过打包处理plb，内付包头说明和测试案例（调用例子），感兴趣用户可以下载下来。

Oracle P/L SQL实现FTP上传、下载功能，包体经过打包处理plb，内付包头说明和测试案例（调用例子），感兴趣用户可以下载下来。

Oracle P/L SQL实现FTP上传、下载功能，包体经过打包处理plb，感兴趣用户可以下载下来。 新版更新了几个补丁。

Oracle P/L SQL实现发送Email、浏览网页等网络操作功能 Oracle P/L SQL实现发送Email、浏览网页等网络操作功能，以下是此过程包的头部，包体经常打包处理plb，感兴趣用户可以下载下来。 --.使用聚合函数实现 多行合并 Drop Type Strcat_type; Drop Function f_StrCat; Drop Package UTL_INet; Variable ls_ObjectName VarChar2(128); Begin Select Sequence_Name Into :ls_ObjectName From User_Sequences Where Sequence_Name = 'SYS_RAND_ID'; DBMS_Output.Put_Line( :ls_ObjectName ); Exception When No_Data_Found Then Execute Immediate 'Create Sequence SYS_RAND_ID minvalue 1 maxValue 99999999999999999 Start With 1 increment by 1 cache 5 cycle order'; End; / --1、创建类型 Create Or Replace Type Strcat_type As Object ( cat_string varchar2(4000), Static Function ODCIAggregateInitialize(cs_ctx In Out strcat_type) Return Number, Member Function ODCIAggregateIterate(self In Out strcat_type,value in varchar2) Return Number, Member Function ODCIAggregateMerge(self In Out strcat_type,ctx2 In Out strcat_type) Return Number, Member Function ODCIAggregateTerminate(self In Out strcat_type,ReturnValue Out varchar2,flags in Number) Return Number ); / --2. 创建类型体 Create Or Replace Type Body Strcat_type Is Static Function ODCIAggregateInitialize( cs_ctx In Out strcat_type )Return Number is Begin cs_ctx := strcat_type( Null ); Return ODCIConst.Success; End; Member Function ODCIAggregateIterate( self In Out strcat_type, value In varchar2 ) Return Number is Begin if self.cat_string is Null or Instr( self.cat_string, value ) = 0 Then self.cat_string := self.cat_string || ','|| value; End if; Return ODCIConst.Success; End; Member Function ODCIAggregateTerminate( self In Out strcat_type, ReturnValue Out varchar2, flags In Number) Return Number is Begin ReturnValue := ltrim(rtrim( self.cat_string,','),',' ); Return ODCIConst.Success; End; Member Function ODCIAggregateMerge( self In Out strcat_type, ctx2 In Out strcat_type) Return Number is Begin if self.cat_string is Null or Instr( self.cat_string, ctx2.cat_string ) = 0 Then self.cat_string := self.cat_string || ',' || ctx2.cat_string; End if; Return ODCIConst.Success; End; End; / --3.创建函数: 使用聚合函数实现 多行合并 Create or Replace Function f_StrCat( as_input Varchar2 ) Return Varchar2 PARALLEL_ENABLE AGGREGATE USING strcat_type; / Grant Execute on f_StrCat To Public; --End of 使用聚合函数实现 多行合并 Create Or Replace Package UTL_INet AS Type VarChar_Type is Table of VarChar2(400) Index By Binary_Integer; Type Number_Type is Table of Number(12,4) Index By Binary_Integer; Type DynamicCursor is ref Cursor; --动态游标 --Purpose : 获得汉字拼音编码 Type ut_PYIndex_191_List is Varray( 191 ) OF Number; Type ut_PYIndex_List is Varray( 10 ) OF ut_PYIndex_191_List; is_OracleDirectory Constant VarChar2(20) := 'ATTACH_DIR'; --内部附件生成目录（Oracle的目录） --Clob叠加比较慢，先用VarChar2叠加到4000个字符后才叠加到Clob字段 --UTL_INet.p_ClobCAT( Procedure p_ClobCAT( ac_HTMLText in Out Clob, as_CatText in Out VarChar2, as_Str in VarChar2 Default Null ); --字符串根据特定分隔符分来 --Select UTL_INet.f_SplitString( 'A,B,C', xx, ',' ) From dual; Function f_SplitString( as_SourStr in out Clob, --输入字符串A,B,C as_Separator in VarChar2 Default '/' --分拆依据的分隔符, )Return VarChar2; --分拆结果A --将Clob内容写入物理文件 --Exec UTL_INet.p_PutClob2File( as_FileName => 'aa.sql', ac_Text => 'test sql' ); Procedure p_PutClob2File( as_SubDir in VarChar2, --目录名 as_FileName in VarChar2, --文件名 ac_Text in Clob, --文件内容 as_Overwrite in VarChar default 'Y', --标志位：Y:覆盖文件内容，N:追加 as_OraVersion in VarChar default 'N' --标志位：Y:写入Oracle版本信息 ); --序号自动递增计算 --e.g.: 输入：HLXU99349021，返回：HLXU99349022 --范例： Select UTL_INet.f_AutoNum( 'HLXU99349021' ), UTL_INet.f_AutoNum( 'ABA', -1 ), UTL_INet.f_AutoNum( 'ABZ' ) from Dual; Function f_AutoNum( as_OldNum in VarChar2, --原字符串 ai_Step in Number Default 1, --步长，默认是递增加1， ai_DigitXXX in Number Default Null --累计序号位数 XXX )Return VarChar2; --字符串加解密，返回一串32位长的字符串 --Select UTL_INet.f_MD5( 'TestPassword' ) From Dual; Function f_MD5( as_SourceStr in Varchar2 --需要加密的字符串 ) Return Varchar2; --将Email地址去头去尾，剩下最简单的Email地址，如"TSI Customer Service" <[email protected]>变成[email protected] Function f_GetNakedEmailAddr( as_DisplayEmail In VarChar2, as_Including in Char Default 'N' --Y: 返回<[email protected]> )Return VarChar2; --测试发送Email的邮箱是否正确 Function f_TestEmailAccount( ac_Connection out Nocopy UTL_SMTP.Connection, as_SMTPHost in VarChar2, --邮件服务器 mail.csdn.com ai_SMTPPort in PLS_Integer Default 25, --邮件服务器端口 as_SMTPAuth in VarChar2 Default 'Y', --发送密码验证 as_Username in VarChar2 Default Null, --邮件用户 as_Password in VarChar2 Default Null, --邮件口令 as_WalletPath in VarChar2 Default Null, as_WalletPwd in VarChar2 Default Null )Return Boolean; ------------------------------------------------ 写邮件头和邮件内容------------------------- Procedure p_WriteRawData( ac_Conn in Out Nocopy UTL_SMTP.Connection, as_Partname in VarChar2, as_Value in VarChar2, as_Splite in VarChar2 Default ':', as_CRLF in VarChar2 Default UTL_TCP.CRLF ); ----------------------------------------------发送附件------------------------------------- Procedure p_MailAttachment( ac_Conn in Out Nocopy UTL_SMTP.Connection, as_Filename in VarChar2, as_Boundary in VarChar2, as_Encode in VarChar2 Default 'base64', as_MimeType in VarChar2 Default 'text/plain', as_ContentID in VarChar2 Default Null, -- ab_Inline in Boolean Default False --True将文本内容直接在邮件内容显示出来，并出现在附件中，False不显示只出现在附件中 ); -----------------自动签名的生成，签名生成显示后还出现此签名文件为附件，尚未解决------------------------------- Procedure p_GetMailSignature( ac_Conn in Out Nocopy UTL_SMTP.Connection, as_Boundary in VarChar2, as_Encode in VarChar2 Default 'base64', as_SignatureLogo in VarChar2 Default Null, as_SignatureText in VarChar2 Default Null ); --发送Email前必须将ewallet.p12拷贝到C:\OracleAttachDir目录 --发送电子邮件 --Exec UTL_INet.p_SendEmail( '[email protected]', 'Test 主题Subject', 'Mail body(邮件内容)' ); Procedure p_SendEmail( as_Sender in VarChar2, as_Recipient in VarChar2, as_CC in VarChar2 Default Null, as_BCC in VarChar2 Default Null, as_Subject in VarChar2, ac_Message in Clob, as_AttachLists in VarChar2 Default Null, --多个用逗号，分开 as_SMTPHost in VarChar2, --邮件服务器 ai_SMTPPort in PLS_Integer Default 25, --邮件服务器端口 as_SMTPAuth in VarChar2 Default 'Y', --发送密码验证 as_WalletPath in VarChar2 Default Null, as_WalletPwd in VarChar2 Default Null, as_Username in VarChar2 Default Null, as_Password in VarChar2 Default Null, as_RunResult out VarChar2, --返回信息，OK成功，其他返回错误 as_SignatureLogo in VarChar2 Default Null, as_SignatureText in VarChar2 Default Null, as_Encode in VarChar2 Default 'base64', ai_Priority In Pls_Integer Default Null ); Function f_LoadHTMLFromURL( as_URL in VarChar2, as_CharSet in VarChar2 Default 'UTF-8' )Return Clob; --生成HTML报表表头 --Exec f_HTMLTableHead( 'Tab1', '20:Table Name;20:Records' ); Function f_HTMLTableHead( as_TableID in VarChar2, --表ID as_WidthColumns in VarChar2, --表头内容，用分号;隔开，宽度与标题用冒号:隔开 as_BgColor in VarChar2 Default 'CCCCCC' --标题背景色 )Return VarChar2; --生成HTML报表主体内容 --Exec UTL_INet.f_HTMLTableBody( '' ); Function f_HTMLTableBody( as_BodyText in VarChar2, --主体内容，多个用分号;隔开 as_Align in VarChar2 Default Null --格式（居中，靠左，靠右） )Return VarChar2; --获取汉字拼音字母表 --Select UTL_INet.f_getChineseSpell( '获取汉字拼音' ) from dual; Function f_getChineseSpell( as_CNStr in VarChar2, --中文 as_First in VarChar2 Default Null --空返回完整拼音，其他返回拼音首字母 )Return VarChar2; --二进制转换成十进制函数 --Select UTL_INet.f_Bin2Dec( '10111011' ) From dual; Function f_Bin2Dec( as_Bin in VarChar2 )Return Number; --十进制转换成二进制函数 --Select UTL_INet.f_Dec2Bin( 187 ) From dual; Function f_Dec2Bin( an_Dec in Number )Return VarChar2; --十进制转换成三十二进制函数 --Select UTL_INet.f_Dec2Hex( 187 ) From dual; Function f_Dec2Hex( an_Dec in Number )Return VarChar2; --三十二进制转换成十进制函数 --Select UTL_INet.f_Hex2Dec( '5R' ) From dual; Function f_Hex2Dec( as_Hex in VarChar2 )Return Number; --生成12位随机数 --Select UTL_INet.f_Rand() From dual; Function f_Rand( as_PreFix in VarChar2 Default '00', --未满个数字符补充串 an_Nums in Number Default 12 )Return VarChar2; --繁体字转化成简体字(传入的汉字，若有繁体自动转化为简体) Function f_ft2jt( as_Text in VarChar2 --传入的汉字 )Return VarChar2; --简体字转化成繁体字(传入的汉字，若有简体自动转化为繁体) Function f_jt2ft( as_Text in VarChar2 --传入的汉字 )Return VarChar2; -- -------------------------------------------------------------------------- -- Description : SOAP related Functions for consuming web services. Type t_Request is Record ( Method VarChar2(256), Namespace VarChar2(256), Body VarChar2(32767), EnvelopeTag VarChar2(30) ); Type t_Response is Record ( Doc XMLType, EnvelopeTag VarChar2(30) ); Function f_NewRequest( as_Method in VarChar2, as_Namespace in VarChar2, as_EnvelopeTag in VarChar2 Default 'SOAP-ENV' )Return t_Request; Procedure p_AddParameter( as_Request in out Nocopy t_Request, as_Name in VarChar2, as_Type in VarChar2, as_Value in VarChar2 ); Function f_Invoke( as_Request in out Nocopy t_Request, as_URL in VarChar2, as_Action in VarChar2 )Return t_Response; Function f_GetReturnValue( as_Response in out Nocopy t_Response, as_Name in VarChar2, as_Namespace in VarChar2 )Return VarChar2; --列出当前目录下所有文件清单，可以指定扩展名，是否包含子目录，返回的文件名用|分割开来 Function f_ListDirectory( as_SubDir in VarChar2, as_Ext in VarChar2 Default Null, as_IncludingSubDir in Char Default 'N' )Return Clob; --将数值翻译成中文大写、英文大写 --Select f_Digit2Char( 24822.80, 'EN_Amount' ) From dual; Function f_Digit2Char( an_Amount in Number, --要被翻译的数值 as_Option in VarChar2 --翻译选择项 )Return VarChar2; --发送短信 Function f_SendSMS( as_MobilePhone in VarChar2, --手机号码 as_SMSText in VarChar2, --短信内容 as_SMSURL in VarChar2, as_SMSUserID in VarChar2, as_SMSPasswd in VarChar2 )Return VarChar2; --删除某个指定文件 Procedure p_RemoveFile( as_SubDir in VarChar2, --路径 as_FileName in VarChar2 --多个用逗号，分开 ); --从文件中读内容 Function f_GetTextFromFile( as_SubDir in VarChar2, --目录名 as_FileName in VarChar2, --文件名 as_NewLine in VarChar2 Default UTL_TCP.CRLF --换行符 )Return Clob; END UTL_INet; /

Oracle P/L SQL实现文件压缩、解压功能，以下是此过程包的头部，包体经常打包处理plb，感兴趣用户可以下载下来。 Create or Replace Package UTL_ZIP AUTHID CURRENT_USER as Type File_List is Table of Clob; --文件变成流 Function f_File2Blob( as_SubDir in VarChar2, as_FileName in VarChar2 )Return Blob; --获取压缩流的文件清单 Function f_GetFileList( as_ZippedBlob in Blob, as_Encoding in VarChar2 Default Null )Return File_List; --获取压缩文件的文件清单 Function f_GetFileList( as_SubDir in VarChar2, as_ZIPFile in VarChar2, as_Encoding in VarChar2 Default Null )Return File_List; --获取文件，返回流 Function f_GetFile( as_ZippedBlob in Blob, as_FileName in VarChar2, as_Encoding in VarChar2 Default Null )Return Blob; --获取文件，返回流 Function f_GetFile( as_SubDir in VarChar2, as_ZIPFile in VarChar2, as_FileName in VarChar2, as_Encoding in VarChar2 Default Null )Return Blob; --往压缩流添加文件 Procedure p_AddFile( as_ZippedBlob in out Blob, as_Filename in VarChar2, ab_Content in Blob ); --完成压缩 Procedure p_FinishZIP( as_ZippedBlob in out Blob ); --保存到压缩文件 Procedure p_SaveZIP( as_ZippedBlob in Blob, as_SubDir in VarChar2 Default 'FTP_DIR', as_FileName in VarChar2 Default 'my.zip' ); End UTL_ZIP; /

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; /