Oracle UTL_HTTP（收集汇总有用资料）

Non-protocol interfaces that manipulate cookies

• 
Constants

  Name	Data Type	Value
  HTTP protocol versions that can be used in the function begin_request
  HTTP_VERSION_1_0	VARCHAR2(64)	HTTP/1.0
  HTTP_VERSION_1_1	VARCHAR2(64)	HTTP/1.1
  Default TCP/IP port numbers that a HTTP server listens
  DEFAULT_HTTP_PORT	PLS_INTEGER	80
  DEFAULT_HTTPS_PORT	PLS_INTEGER	443
  Status codes of a HTTP response as defined in HTTP 1.1
  HTTP_CONTINUE	PLS_INTEGER	100
  HTTP_SWITCHING_PROTOCOLS	PLS_INTEGER	101
  HTTP_OK	PLS_INTEGER	200
  HTTP_CREATED	PLS_INTEGER	201
  HTTP_ACCEPTED	PLS_INTEGER	202
  HTTP_NON_AUTHORITATIVE_INFO	PLS_INTEGER	203
  HTTP_NO_CONTENT	PLS_INTEGER	204
  HTTP_RESET_CONTENT	PLS_INTEGER	205
  HTTP_PARTIAL_CONTENT	PLS_INTEGER	206
  HTTP_MULTIPLE_CHOICES	PLS_INTEGER	300
  HTTP_MOVED_PERMANENTLY	PLS_INTEGER	301
  HTTP_FOUND	PLS_INTEGER	302
  HTTP_SEE_OTHER	PLS_INTEGER	303
  HTTP_NOT_MODIFIED	PLS_INTEGER	304
  HTTP_USE_PROXY	PLS_INTEGER	305
  HTTP_TEMPORARY_REDIRECT	PLS_INTEGER	307
  HTTP_BAD_REQUEST	PLS_INTEGER	400
  HTTP_UNAUTHORIZED	PLS_INTEGER	401
  HTTP_PAYMENT_REQUIRED	PLS_INTEGER	402
  HTTP_FORBIDDEN	PLS_INTEGER	403
  HTTP_NOT_FOUND	PLS_INTEGER	404
  HTTP_NOT_ACCEPTABLE	PLS_INTEGER	406
  HTTP_PROXY_AUTH_REQUIRED	PLS_INTEGER	407
  HTTP_REQUEST_TIME_OUT	PLS_INTEGER	408
  HTTP_CONFLICT	PLS_INTEGER	409
  HTTP_GONE	PLS_INTEGER	410
  HTTP_LENGTH_REQUIRED	PLS_INTEGER	411
  HTTP_preCONDITION_FAILED	PLS_INTEGER	412
  HTTP_REQUEST_ENTITY_TOO_LARGE	PLS_INTEGER	413
  HTTP_REQUEST_URI_TOO_LARGE	PLS_INTEGER	414
  HTTP_UNSUPPORTED_MEDIA_TYPE	PLS_INTEGER	415
  HTTP_REQ_RANGE_NOT_SATISFIABLE	PLS_INTEGER	416
  HTTP_EXPECTATION_FAILED	PLS_INTEGER	417
  HTTP_NOT_IMPLEMENTED	PLS_INTEGER	501
  HTTP_BAD_GATEWAY	PLS_INTEGER	502
  HTTP_SERVICE_UNAVAILABLE	PLS_INTEGER	503
  HTTP_GATEWAY_TIME_OUT	PLS_INTEGER	504
  HTTP_VERSION_NOT_SUPPORTED	PLS_INTEGER	505

  
  Data Types -- represent the remote hosts and TCP/IP ports of a network connection that is kept persistent after an HTTP request is completed, according to the HTTP 1.1 protocol specification.TYPE connection IS RECORD (
  host VARCHAR2(256),
  port PLS_INTEGER,
  proxy_host VARCHAR2(256),
  proxy_port PLS_INTEGER,
  ssl BOOLEAN);
  
  TYPE connection_table IS TABLE OF connection INDEX BY BINARY_INTEGER;
  
  -- A PL/SQL record type that represents a HTTP cookie
  TYPE cookie IS RECORD (
  name VARCHAR2(256), -- Cookie name
  value VARCHAR2(1024), -- Cookie value
  domain VARCHAR2(256), -- Domain for which the cookie applies
  expire TIMESTAMP WITH TIME ZONE -- When should the cookie expire?
  path VARCHAR2(1024), -- Virtual path for which the cookie applies
  secure BOOLEAN, -- Transfer cookies by HTTPS only
  version PLS_INTEGER, -- Cookie specification version
  comment VARCHAR2(1024)); -- Comments about this cookie
  
  -- PL/SQL table of cookiesTYPE cookie_table IS TABLE OF cookie INDEX BY BINARY_INTEGER;
  
  -- VARCHAR2 table for returning HTML from request_pieces
  TYPE html_pieces IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
  
  -- A PL/SQL record type that represents a HTTP request
  TYPE req IS RECORD (
  url VARCHAR2(32767 byte), -- Requested URL
  method VARCHAR2(64), -- Requested method
  http_version VARCHAR2(64), -- Requested HTTP version
  private_hndl PLS_INTEGER); -- For internal use only
  
  -- PL/SQL record type that represents a HTTP response
  TYPE resp IS RECORD (
  status_code PLS_INTEGER, -- Response status code
  reason_phrase VARCHAR2(256), -- Response reason phrase
  http_version VARCHAR2(64), -- Response HTTP version
  private_hndl PLS_INTEGER); -- For internal use only
  
  /* Note:
  * - the "private_xxxx" field(s) in the req and resp record types are for
  * internal use only and users should not try to modify them.
  * - the HTTP information returned in the req and resp from the API
  * begin_request and get_response are for read only. Changing the
  * field values in the records has no effect to request or reesponse
  * when making calls to the API in this package.
  */ Dependencies

  DBMS_AQADM_SYS	ORDX_HTTP_SOURCE	UTL_HTT_LIB
  DBMS_AQELM	PLITBLM	UTL_RAW
  DBMS_PRVTAQIP	URIFACTORY	UTL_URL
  HTTPURITYPE

  Constants

  Exception Name	Error Code	Reason
  init_failed	-29272	The UTL_HTTP pkg initialization failed
  request_failed	-29273	The HTTP request failed
  bad_argument	-29261	A bad argument was passed to an API
  bad_url	-29262	The URL is bad
  protocol_error	-29263	A HTTP protocol error occurred
  unknown_scheme	-29264	The scheme of the URL is unknown
  header_not_found	-29265	The HTTP header is not found
  end_of_body	-29266	The end of response body is reached
  illegal_call	-29267	The API call is illegal at this stage
  http_client_error	-29268	A 4xx response code is returned
  http_server_error	-29269	A 5xx response code is returned
  too_many_requests	-29270	Too many open requests or responses
  partial_multibyte_char	-29275	A partial multi-byte character found
  transfer_timeout	-29276	Transfer time-out occurred

  Security Model ?   ADD_COOKIES
  Adds the cookies maintained by UTL_HTTP utl_http.add_cookies(cookies IN cookie_table); CREATE OR REPLACE PROCEDURE restore_cookies(this_session_id IN BINARY_INTEGER) AS
  cookies utl_http.cookie_table;
  cookie utl_http.cookie;
  i PLS_INTEGER := 0;
  
  CURSOR c (c_session_id BINARY_INTEGER) IS
  SELECT *
  FROM my_cookies
  WHERE session_id = c_session_id;
  BEGIN
  FOR r IN c(this_session_id)
  LOOP
  i := i + 1;
  cookie.name := r.name;
  cookie.value := r.value;
  cookie.domain := r.domain;
  cookie.expire := r.expire;
  cookie.path := r.path;
  
  IF (r.secure = ‘Y‘) THEN
  cookie.secure := TRUE;
  ELSE
  cookie.secure := FALSE;
  END IF;
  
  cookie.version := r.version;
  cookies(i) := cookie;
  END LOOP;
  
  utl_http.clear_cookies;
  utl_http.add_cookies(cookies);
  END;
  /   BEGIN_REQUEST Begins a new HTTP request. When the function returns, the UTL_HTTP
  package has established the network connection to the target Web server, or the proxy server if a proxy server is to be used, and has sent the HTTP request line. The PL/SQL program should continue the request by calling some other API to complete the request. utl_http.begin_request(
  url IN VARCHAR2,
  method IN VARCHAR2 DEFAULT ‘GET‘,
  http_version IN VARCHAR2 DEFAULT NULL) RETURN req; set serveroutput on
  
  DECLARE
  req utl_http.req;
  resp utl_http.resp;
  value VARCHAR2(1024);
  BEGIN
  req := utl_http.begin_request(‘http://www.psoug.org‘);
  utl_http.set_header(req, ‘User-Agent‘, ‘Mozilla/4.0‘);
  resp := utl_http.get_response(req);
  LOOP
  utl_http.read_line(resp, value, TRUE);
  dbms_output.put_line(value);
  END LOOP;
  utl_http.end_response(resp);
  EXCEPTION
  WHEN utl_http.end_of_body THEN
  utl_http.end_response(resp);
  END;
  /   CLEAR_COOKIES Clears all the cookies currently maintained by the UTL_HTTP package utl_http.clear_cookies See ADD_COOKIES Demo   CLOSE_PERSISTENT_CONN Closes a HTTP persistent connection in the current session utl_http.close_persistent_conn(conn IN connection); TBD   CLOSE_PERSISTENT_CONNS Closes a group of HTTP persistent connections maintained by the UTL_HTTP package in the current database session. This procedure uses a pattern-match approach to decide which persistent connections to close. utl_http.close_persistent_conns(
  host IN VARCHAR2 DEFAULT NULL,
  port IN PLS_INTEGER DEFAULT NULL,
  proxy_host IN VARCHAR2 DEFAULT NULL,
  proxy_port IN PLS_INTEGER DEFAULT NULL,
  ssl IN BOOLEAN DEFAULT NULL); exec utl_http.close_persistent_conns(host => ‘washington.edu‘, proxy_port => 80);   END_REQUEST Ends the HTTP request utl_http.end_request(r IN OUT NOCOPY req); TBD   END_RESPONSE Ends the HTTP response. This completes the HTTP request and response. Unless HTTP 1.1 persistent connection is used in this request, the network con