PL/SQL Web Services Made EASY - (Part 2)

Finally...I have a moment to post an example of a PL/SQL web service! So, I went into the nitty-gritty details of doing so last week...not much to it. Just create a stored procedure which utilizes the OWA htp packages to expose PL/SQL to the web. If you have any questions regarding further detail, please give me a shout. Otherwise, here is a stored procedure example.

This stored procedure is actually a member of a PL/SQL package which contains many web services. I believe it is a good practice to package similar code together when possible. If you include all of your PL/SQL web services in one package, then maintainablility will be easier. I've altered the code slightly to remove any proprietary information which cannot be exposed, however, you should be able to get the general idea.

--*********************************************
-- FUNCTION: EMPLOYEE_SEARCH_SERVICE--***********************************************
PROCEDURE EMPLOYEE_SEARCH_SERVICE(search_text varchar2) IS
CURSOR EMP_SEARCH_CUR IS
SELECT ID, FIRST_NAME, LAST_NAME
FROM MY_TABLE WHERE EMP_STATUS = 'A'
AND ID LIKE '%' search_text '%'
OR FIRST_NAME LIKE upper(search_text) '%'
OR LAST_NAME LIKE upper(search_text) '%'
ORDER BY ID, LAST_NAME, FIRST_NAME;

return_prefix constant varchar(10) := 'OUTER XML TAG (would not post correctly on blog)';
return_elem_pre constant varchar(5) := 'INNER XML TAG';
return_elem_post constant varchar(7) := 'CLOSE INNER XML TAG';
return_suffix constant varchar(11) := 'CLOSE OUTER XML TAG';
return_string varchar2(4000) DEFAULT NULL;
counter number := 0;

BEGIN
return_string := return_prefix;
owa_util.mime_header('text/xml');
htp.htmlopen;
htp.headopen;
htp.headclose;
htp.bodyopen;
htp.print(return_prefix);

FOR emp_search_rec IN emp_search_cur LOOP
htp.print(return_elem_pre ' id="' counter '">');
htp.print('' emp_search_rec.id '');
htp.print('' emp_search_rec.FIRST_NAME '');
htp.print('' emp_search_rec.LAST_NAME '');
htp.print(return_elem_post);
counter := counter + 1;
end LOOP;
htp.print(return_suffix);
htp.bodyclose;
htp.htmlclose;
END EMPLOYEE_SEARCH_SERVICE;
-- *********************************************

You then place the procedure definition in the package header:
procedure employee_search_service(search_text varchar2);

That's all there is to it! At this point, you can load your package into the appropriate database schema, and as long as there are no compile errors you should be able to access the service via a URL. You'll need to use the following syntax for your URL:

http: //your-server-name:port_number/DAD_NAME/package_name.procedure_name?search_text=your_search_criteria

The result should be an XML page which can then be parsed using JavaScript (good for Ajax) or another XML parsing technology.

Now...that was easy!