Oracle: ваш первый шаг к web-службам

<cover> <title>Oracle SQL*Loader</title> <author>Jonathan Gennick</author> <author>Sanjay Mishra</author> <pages>269</pages> </cover>

SET SERVEROUTPUT ON SIZE 40000
DECLARE req utl_http.req; resp utl_http.resp; name VARCHAR2(256); value VARCHAR2(1024);

BEGIN req := utl_http.begin_request

('http://localhost:7778/mydoc.xml'); resp := utl_http.get_response(req);
 dbms_output.put_line('HTTP response status code:
 ' || resp.status_code); dbms_output.put_line('HTTP response reason: '
 || resp.reason_phrase);
 dbms_output.put_line('-');
 FOR i IN 1 .. utl_http.get_header_count(resp) LOOP utl_http.get_header(resp, i, name, value); dbms_output.put_line(name || ': ' || value); END LOOP;
 dbms_output.put_line('-');
 LOOP utl_http.read_line(resp, value, TRUE); dbms_output.put_line(value); END LOOP;

EXCEPTION WHEN utl_http.end_of_body THEN
 utl_http.end_response(resp);
END;
/

HTTP response status code: 200
HTTP response reason: OK
-Date: Fri, 28 May 2004 13:01:49 GMT
Server: Oracle HTTP Server Powered by Apache/1.3.22


(Win32) mod_plsql/3.0.9.8.3b PHP/4.3.0 mod_ssl/2.8.5


OpenSSL/0.9.6b mod_fastcgi/2.2.12 mod_oprocmgr/1.0 mod_perl/1.25
Last-Modified: Thu, 13 May 2004 15:15:47 GMT
ETag: "0-98-40a39123"
Accept-Ranges: bytes
Content-Length: 152
Connection: close
Content-Type: text/xml
-<cover>
<title>Oracle SQL*Loader</title>
<author>Jonathan Gennick</author>
<author>Sanjay Mishra</author>
<pages>269</pages>
</cover>

DECLARE
xmldoc VARCHAR2(4000) := '';
ok BOOLEAN := FALSE;

PROCEDURE readxml(url IN VARCHAR2,
 doc OUT VARCHAR2, success OUT BOOLEAN)
IS req utl_http.req; resp utl_http.resp; name VARCHAR2(256); value VARCHAR2(1024);

BEGIN doc := ''; req := utl_http.begin_request(url); resp := utl_http.get_response(req);
 success := resp.status_code = 200; IF success THEN FOR i IN 1 .. utl_http.get_header_count(resp) LOOP utl_http.get_header(resp, i, name, value); success := name = 'Content-Type' AND
 value = 'text/xml'; EXIT WHEN success; END LOOP;
 IF success THEN LOOP utl_http.read_line(resp, value, TRUE); doc := doc || value; END LOOP; END IF; END IF;

EXCEPTION WHEN utl_http.end_of_body THEN 

utl_http.end_response(resp);
END;

BEGIN readxml('http://localhost:7778/mydoc.xml',
 xmldoc, ok); IF ok THEN dbms_output.put_line(xmldoc); ELSE dbms_output.put_line('Not XML
 or bad URL'); END IF;
END;
/

import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class ServletForXML extends HttpServlet {
 public void service (HttpServletRequest request,
HttpServletResponse response)
throws ServletException, IOException {

response.setContentType("text/xml");

PrintWriter out = response.getWriter();

out.println("<cover>");
out.println("<title>Oracle SQL*Loader</title>");
out.println("<author>Jonathan Gennick</author>");
out.println("<author>Sanjay Mishra</author>");
out.println("<pages>269</pages>");
out.println("<</cover>"); }
}

<?xml version="1.0"?>
<page connection="demo" xmlns:xsql="urn:oracle-xsql">
<xsql:include-request-params/>
<xsql:query tab="EMP" null-indicator="yes" >
 SELECT * FROM {@tab}

</xsql:query>
</page>

CREATE TYPE typ_va IS VARRAY(10) OF VARCHAR(20);
/

CREATE TYPE typ_obj IS OBJECT (city VARCHAR2(20), street VARCHAR2(20));
/

ALTER TABLE emp ADD (xmlc XMLTYPE, vac typ_va, addr typ_obj);

UPDATE emp 
SET xmlc = XMLTYPE(<a>bbb</a>) , vac = typ_va('Alice', 'Bob') , addr = typ_obj('Ленинград', 'Невский')
WHERE ename='SMITH';

COMMIT;