CREATE PROCEDURE [dbo].[sp_aaa] AS
Declare @adc int
Declare @hr int
DECLARE @dcnt int
select @adc=1IF @adc=1
BEGIN
DECLARE @sql varchar (8000) select @sql='Open("GET","http://..........",False)' EXEC @hr=sp_OACreate 'Microsoft.XMLHTTP', @adc OUT
EXEC @hr=sp_OAMethod @adc,@sql
EXEC @hr=sp_OAMethod @adc,'Send()',nullEND
GO
Declare @adc int
Declare @hr int
DECLARE @dcnt int
select @adc=1IF @adc=1
BEGIN
DECLARE @sql varchar (8000) select @sql='Open("GET","http://..........",False)' EXEC @hr=sp_OACreate 'Microsoft.XMLHTTP', @adc OUT
EXEC @hr=sp_OAMethod @adc,@sql
EXEC @hr=sp_OAMethod @adc,'Send()',nullEND
GO
declare
req Utl_Http.Req;
resp Utl_Http.Resp;
name varchar2(255);
value varchar2(1023);
v_msg varchar2(80); v_url varchar2(32767) := '/';
begin
/* request that exceptions are raised for error Status Codes */
Utl_Http.Set_Response_Error_Check ( enable => true ); /* allow testing for exceptions like Utl_Http.Http_Server_Error */
Utl_Http.Set_Detailed_Excp_Support ( enable => true ); Utl_Http.Set_Proxy (
proxy => 'www-proxy.us.oracle.com',
no_proxy_domains => 'us.oracle.com' ); req := Utl_Http.Begin_Request (
url => v_url,
method => 'GET' );
/*
Alternatively use method => 'POST' and Utl_Http.Write_Text to
build an arbitrarily long message
*/ Utl_Http.Set_Authentication (
r => req,
username => 'SomeUser', password => 'SomePassword',
scheme => 'Basic',
for_proxy => false /* this info is for the target web server */ ); Utl_Http.Set_Header (
r => req,
name => 'User-Agent',
value => 'Mozilla/4.0' ); resp := Utl_Http.Get_Response ( r => req ); Dbms_Output.Put_Line ( 'Status code: ' || resp.status_code );
Dbms_Output.Put_Line ( 'Reason phrase: ' || resp.reason_phrase ); for i in 1..Utl_Http.Get_Header_Count ( r => resp ) loop
Utl_Http.Get_Header (
r => resp,
n => i,
name => name,
value => value );
Dbms_Output.Put_Line ( name || ': ' || value);
end loop; begin
loop
Utl_Http.Read_Text (
r => resp, data => v_msg );
Dbms_Output.Put_Line ( v_msg );
end loop;
exception when Utl_Http.End_Of_Body then null;
end; Utl_Http.End_Response ( r => resp );
exception
/*
The exception handling illustrates the use of "pragma-ed" exceptions
like Utl_Http.Http_Client_Error. In a realistic example, the program
would use these when it coded explicit recovery actions. Request_Failed is raised for all exceptions after calling
Utl_Http.Set_Detailed_Excp_Support ( enable=>false ) And it is NEVER raised after calling with enable=>true
*/
when Utl_Http.Request_Failed then
Dbms_Output.Put_Line ( 'Request_Failed: ' || Utl_Http.Get_Detailed_Sqlerrm ); /* raised by URL http://xxx.oracle.com/ */
when Utl_Http.Http_Server_Error then
Dbms_Output.Put_Line ( 'Http_Server_Error: ' || Utl_Http.Get_Detailed_Sqlerrm ); /* raised by URL /xxx */
when Utl_Http.Http_Client_Error then
Dbms_Output.Put_Line ( 'Http_Client_Error: ' || Utl_Http.Get_Detailed_Sqlerrm ); /* code for all the other defined exceptions you can recover from */ when others then
Dbms_Output.Put_Line (SQLERRM);
end;/
这段代码好多地方看不懂,还要请教。
刚才我将源码全部复制出来做了一个存储过程执行,
结果是:
ORA-12535: TNS: 操作超时
PL/SQL 过程已成功完成。因为我的目的就是要用存储过程调用一个页面。于是我将其中一个函数作了如下修改:
Utl_Http.Set_Proxy(
proxy=>'http://192.168.1.62/webui/Jobs.aspx',
no_proxy_domains=>'192.168.1.62' );
在执行时提示:
ORA-29261: 参数无效
PL/SQL 过程已成功完成。不知道为什么?
url应该在
Utl_Http.Begin_Request(
url => v_url ; -- 'http://....