DBA Hub

📋Steps in this guide1/7

XML-Over-HTTP (REST Web Services) From PL/SQL

XML-over-HTTP was the precursor to web services allowing easy access to XML via HTTP GETs and POSTs.

oracle miscconfigurationintermediate
by OracleDba
10 views
1

Test Schema

First we create a test schema.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
CONN / AS SYSDBA

CREATE USER test IDENTIFIED BY test DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;
GRANT CONNECT, CREATE TABLE, CREATE PROCEDURE TO test;

CREATE TABLE test.emp AS SELECT * FROM scott.emp;
CREATE TABLE test.dept AS SELECT * FROM scott.dept;
2

Database Access Descriptor Configuration (DAD)

For this article I will be using the Embedded PL/SQL Gateway to provide access to PL/SQL from the browser. The following code creates a DAD called "xml_demo" which points at an Oracle test user. We must also make sure the HTTP port for XML DB is set. If the value was not set, we could set it with the following procedure call.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
CONN / AS SYSDBA

-- Create a Database Access Descriptor
BEGIN
  DBMS_EPG.create_dad (
    dad_name => 'xml_demo',
    path     => '/xml_demo/*');
END;
/

BEGIN
  DBMS_EPG.authorize_dad (
    dad_name => 'xml_demo',
    user     => 'TEST');
END;
/

SELECT DBMS_XDB.gethttpport FROM dual;

GETHTTPPORT
-----------
       8080

SQL>

EXEC DBMS_XDB.sethttpport(8080);

PL/SQL procedure successfully completed.

SQL>
3

Volatile Data

For volatile data, we need to make sure the XML is produced each time a request is made for it. This is simple using the PL/SQL Web Toolkit. The following package contains four different procedures that produce the same data as XML. The first two hand-craft the XML in tag-based and attribute-based form. The next two generate the XML directly using built in SQL functions ( SQL/XML ) in the same two formats. The procedures retrieve the record specified by the parameter, or all records if the parameter is NULL. With the DAD configured and the PL/SQL code in place the XML documents can be retrieved by entering the correct URL into a browser.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
CONN test/test

CREATE OR REPLACE PACKAGE xml_api AS

PROCEDURE get_emp_1 (p_empno  IN  emp.empno%TYPE DEFAULT NULL);
PROCEDURE get_emp_2 (p_empno  IN  emp.empno%TYPE DEFAULT NULL);
PROCEDURE get_emp_3 (p_empno  IN  emp.empno%TYPE DEFAULT NULL);
PROCEDURE get_emp_4 (p_empno  IN  emp.empno%TYPE DEFAULT NULL);

END xml_api;
/
SHOW ERRORS


CREATE OR REPLACE PACKAGE BODY xml_api AS

PROCEDURE error_page (p_message IN VARCHAR2);

PROCEDURE get_emp_1 (p_empno  IN  emp.empno%TYPE DEFAULT NULL) IS
BEGIN
  OWA_UTIL.mime_header('text/xml');  
  HTP.print('<emp_list>');

  FOR cur_rec IN (SELECT empno, ename, job FROM emp WHERE p_empno IS NULL OR empno = p_empno) LOOP
  HTP.print(
    '<emp>'
    || '<empno>' || cur_rec.empno || '</empno>'
    || '<ename>' || DBMS_XMLGEN.convert(cur_rec.ename) || '</ename>'
    || '<job>' || DBMS_XMLGEN.convert(cur_rec.job) || '</job>'
    || '</emp>');
  END LOOP;
  HTP.print('</emp_list>');
EXCEPTION
  WHEN OTHERS THEN
     error_page(SQLERRM);
END get_emp_1;


PROCEDURE get_emp_2 (p_empno  IN  emp.empno%TYPE DEFAULT NULL) IS
BEGIN
  OWA_UTIL.mime_header('text/xml');  
  HTP.print('<emp_list>');

  FOR cur_rec IN (SELECT empno, ename, job FROM emp WHERE p_empno IS NULL OR empno = p_empno) LOOP
  HTP.print(
    '<emp'
    || ' empno="' || cur_rec.empno || '"'
    || ' ename="' || DBMS_XMLGEN.convert(cur_rec.ename) || '"'
    || ' job="' || DBMS_XMLGEN.convert(cur_rec.job) || '"'
    || ' />');
  END LOOP;
  HTP.print('</emp_list>');
EXCEPTION
  WHEN OTHERS THEN
     error_page(SQLERRM);
END get_emp_2;


PROCEDURE get_emp_3 (p_empno  IN  emp.empno%TYPE DEFAULT NULL) IS
  l_clob  CLOB;
BEGIN
  SELECT XMLELEMENT("emp_list",
           XMLAGG(
             XMLELEMENT("emp",
               XMLFOREST(empno AS "empno",
                         ename AS "ename",
                         job AS "job"
               )
             )
           )
         ).getClobVal()
  INTO   l_clob
  FROM   emp
  WHERE  p_empno IS NULL OR empno = p_empno;

  OWA_UTIL.mime_header('text/xml');  
  HTP.print(l_clob);
EXCEPTION
  WHEN OTHERS THEN
     error_page(SQLERRM);
END get_emp_3;


PROCEDURE get_emp_4 (p_empno  IN  emp.empno%TYPE DEFAULT NULL) IS
  l_clob  CLOB;
BEGIN
  SELECT XMLELEMENT("emp_list",
           XMLAGG(
             XMLELEMENT("emp",
               XMLATTRIBUTES(empno AS "empno",
                             ename AS "ename",
                             job AS "job"
               )
             )
           )        
         ).getClobVal()
  INTO   l_clob
  FROM   emp
  WHERE  p_empno IS NULL OR empno = p_empno;

  OWA_UTIL.mime_header('text/xml');  
  HTP.print(l_clob);
EXCEPTION
  WHEN OTHERS THEN
     error_page(SQLERRM);
END get_emp_4;


PROCEDURE error_page (p_message IN VARCHAR2) AS
BEGIN
  OWA_UTIL.mime_header('text/xml');
  HTP.print(
       '<rowset>'
    || '  <error>' || DBMS_XMLGEN.convert(p_message) || '</error>'
    || '</rowset>');
END error_page;

END xml_api;
/
SHOW ERRORS

http://test:test@myserver:8080/xml_demo/xml_api.get_emp_1
http://test:test@myserver:8080/xml_demo/xml_api.get_emp_1?p_empno=7369
http://test:test@myserver:8080/xml_demo/xml_api.get_emp_2
http://test:test@myserver:8080/xml_demo/xml_api.get_emp_2?p_empno=7369
http://test:test@myserver:8080/xml_demo/xml_api.get_emp_3
http://test:test@myserver:8080/xml_demo/xml_api.get_emp_3?p_empno=7369
http://test:test@myserver:8080/xml_demo/xml_api.get_emp_4
http://test:test@myserver:8080/xml_demo/xml_api.get_emp_4?p_empno=7369
4

Static Data

In the previous section we saw how volatile data could be presented as XML. We could use this method for static and semi-static data, but we would be needlessly regenerating XML that had not changed since the last request. It would be far better to generate the XML once, then keep presenting the original document until such as time as the data changes and the document is regenerated. Fortunately XML DB allows us to do this by creating resources in its internal file system. These resources can then be accessed via HTTP, FTP and WebDAV. We have already turned on HTTP access to the XML DB repository using the procedure. If we want FTP access we must open a port for it as follows. Next we create a folder in the XML DB file system to hold our file. We expect the deletion of the folder to fail if it is the first time you've run the script, so don't worry out it. Next we write a procedure to create the XML data as an XML DB resource in the folder. Every time the procedure is called it will replace the XML document with a fresh copy of the data. This could be called from a scheduled job, or a trigger. The first call will produce an error message associated with the failure to delete the file. This message can be ignored. The file is now present in the XML DB repository, so we can access it using HTTP, FTP and WebDAV. For WebDAV, map a drive using the HTTP URL and you will see the file in the mapped directory.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
CONN / AS SYSDBA

EXEC DBMS_XDB.setftpport(9021);

PL/SQL procedure successfully completed.

SQL>

SET SERVEROUTPUT ON
DECLARE
  l_return BOOLEAN;
  
  ex_delete_failure EXCEPTION;
  PRAGMA EXCEPTION_INIT(ex_delete_failure, -31001);
BEGIN
  -- Delete the folder if it already exists.
  BEGIN
    DBMS_XDB.deleteresource('/public/mydocs', DBMS_XDB.DELETE_RECURSIVE);
  EXCEPTION
    WHEN ex_delete_failure THEN
      DBMS_OUTPUT.put_line(SQLERRM);
  END;

  -- Create the folder.
  l_return := DBMS_XDB.createfolder('/public/mydocs');
  COMMIT;
END;
/

CONN test/test

CREATE OR REPLACE PROCEDURE semi_static AS
  l_return BOOLEAN;
  l_clob   CLOB;
  
  ex_delete_failure EXCEPTION;
  PRAGMA EXCEPTION_INIT(ex_delete_failure, -31001);
BEGIN
  -- Generate some XML and place it in a CLOB.
  SELECT XMLELEMENT("emp_list",
           XMLAGG(
             XMLELEMENT("emp",
               XMLATTRIBUTES(empno AS "empno",
                             ename AS "ename",
                             job AS "job"
               )
             )
           )        
         ).getClobVal()
  INTO   l_clob
  FROM   emp;
   
  -- Delete the resource it if is already present.
  BEGIN
    DBMS_XDB.deleteresource('/public/mydocs/semi_static.xml', DBMS_XDB.DELETE_FORCE);
  EXCEPTION
    WHEN ex_delete_failure THEN
      DBMS_OUTPUT.put_line('Delete of file failed. Probably first time call.');
      DBMS_OUTPUT.put_line(SQLERRM);
  END;
  
  -- Create the resource using the data in the CLOB.
  l_return := DBMS_XDB.createresource('/public/mydocs/semi_static.xml', l_clob);
  
  DBMS_XDB.setACL('/public/mydocs/semi_static.xml',
                  '/sys/acls/all_owner_acl.xml');
  COMMIT;
END semi_static;
/
SHOW ERRORS

SET SERVEROUTPUT ON
EXEC semi_static;

http://test:test@myserver:8080/public/mydocs/semi_static.xml
ftp://test:test@myserver:9021/public/mydocs/semi_static.xml
5

REST-Style URLs

You will have noticed that XML over HTTP looks very similar to REST web services, with the exception of the URL. We can mimic REST style URLs using the and DAD attributes. The attribute identifies an expression to look out for in a URL. The attribute indicates what procedure should be called when the previous expression is found. Using our existing DAD, we will associate the procedure with the expression "rest-ws". Next we code the to intercept the requests and do something with them. The procedure uses some APEX functionality to split the URL, but this could easily be done using string_api.split_test instead. These procedures can be accessed using the original style URLs. But they can also now be accessed using a REST-Style URL.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
CONN / AS SYSDBA

BEGIN
  DBMS_EPG.set_dad_attribute(
    dad_name   => 'xml_demo',
    attr_name  => 'path-alias',
    attr_value => 'rest-ws');
    
  DBMS_EPG.set_dad_attribute(
    dad_name   => 'xml_demo',
    attr_name  => 'path-alias-procedure',
    attr_value => 'rest_api.handle_request');
END;
/

CONN test/test

CREATE OR REPLACE PACKAGE rest_api AS?

  PROCEDURE get_emps (p_empno  IN  emp.empno%TYPE DEFAULT NULL);
  PROCEDURE get_depts (p_deptno  IN  dept.deptno%TYPE DEFAULT NULL);
  
  PROCEDURE handle_request(p_path IN VARCHAR2);
  
END rest_api;
/
SHOW ERRORS


CREATE OR REPLACE PACKAGE BODY rest_api AS

PROCEDURE error_page (p_message IN VARCHAR2);

PROCEDURE get_emps (p_empno IN emp.empno%TYPE DEFAULT NULL) IS
  l_clob  CLOB;
BEGIN
  SELECT XMLELEMENT("emp_list",
           XMLAGG(
             XMLELEMENT("emp",
               XMLATTRIBUTES(empno AS "empno",
                             ename AS "ename",
                             job AS "job"
               )
             )
           )        
         ).getClobVal()
  INTO   l_clob
  FROM   emp
  WHERE  p_empno IS NULL OR empno = p_empno;

  OWA_UTIL.mime_header('text/xml');  
  HTP.print(l_clob);
EXCEPTION
  WHEN OTHERS THEN
    error_page(SQLERRM);
END get_emps;


PROCEDURE get_depts (p_deptno IN dept.deptno%TYPE DEFAULT NULL) IS
  l_clob  CLOB;
BEGIN
  SELECT XMLELEMENT("dept_list",
           XMLAGG(
             XMLELEMENT("dept",
               XMLATTRIBUTES(deptno AS "deptno",
                             dname AS "dname",
                             loc AS "loc"
               )
             )
           )        
         ).getClobVal()
  INTO   l_clob
  FROM   dept
  WHERE  p_deptno IS NULL OR deptno = p_deptno;

  OWA_UTIL.mime_header('text/xml');  
  HTP.print(l_clob);
EXCEPTION
  WHEN OTHERS THEN
    error_page(SQLERRM);
END get_depts;


PROCEDURE handle_request(p_path IN VARCHAR2) IS
  l_path_arr  apex_application_global.vc_arr2;
  l_subject   VARCHAR2(32767);
  l_id        VARCHAR2(32767) := NULL;
BEGIN
  l_path_arr := apex_util.string_to_table(p_path || '/', '/');
  l_subject  := l_path_arr(1);
  l_id       := l_path_arr(2);
  
  CASE LOWER(l_subject)
  WHEN 'emp' THEN
    get_emps(p_empno => TO_NUMBER(l_id));
  WHEN 'dept' THEN
    get_depts(p_deptno => TO_NUMBER(l_id));
  ELSE
    HTP.Print('Page not found.');
  END CASE;
END handle_request;


PROCEDURE error_page (p_message IN VARCHAR2) AS
BEGIN
  OWA_UTIL.mime_header('text/xml');
  HTP.print(
       '<rowset>'
    || '  <error>' || DBMS_XMLGEN.convert(p_message) || '</error>'
    || '</rowset>');
END error_page;

END rest_api;
/
SHOW ERRORS

http://test:test@myserver:8080/xml_demo/rest_api.get_emps
http://test:test@myserver:8080/xml_demo/rest_api.get_emps?p_empno=7900
http://test:test@myserver:8080/xml_demo/rest_api.get_depts
http://test:test@myserver:8080/xml_demo/rest_api.get_depts?p_deptno=10

http://test:test@myserver:8080/xml_demo/rest-ws/emp
http://test:test@myserver:8080/xml_demo/rest-ws/emp/7900
http://test:test@myserver:8080/xml_demo/rest-ws/dept
http://test:test@myserver:8080/xml_demo/rest-ws/dept/10
6

Performance Considerations

Performance problems associated with this method are almost always caused by badly performing SQL. If you are generating XML using complex and slow SQL, the web service is never going to respond quickly. Possible solutions to this are as follows. - Use the static data method described above to switch to a "write once, read many" approach. - Tune your SQL, or hide the hard work behind a materialized view, which is refreshed intermittently. This will make the data access quicker. - If your data is fronted by a webserver or middleware layer, cache the results in that layer, so the actual web service doesn't get called for each request.
7

JSON

Many of the techniques discussed in this article apply equally well to JSON web services. If you want to know about how to generate JSON from within the database, check out the article here . For more information see: Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!