mod_plsql XML example
To add a mod_plsql procedure to an Oracle application server is pretty straightforward. The first step is to add the DAD to the server using the console.
After logging into the application server console (http://app:1156) select HTTP_server > Administration > PLSQL > Create DAD.
Add the database connectivity information and restart the server. Then create the stored procedures to access the database and return XML. e.g.
CREATE OR REPLACE PROCEDURE web_getservices (p_account_no varchar2) AS
BEGIN
DECLARE
ctx dbms_xmlgen.ctxHandle;
xml CLOB;
amount integer:= 5000;
position integer := 1;
charString varchar2(5000); --set to varchar2 max limit
BEGIN
owa_util.mime_header ('text/xml');
dbms_lob.createtemporary(
lob_loc => xml,
cache => true, -- this is the line to watch
dur => dbms_lob.session
);
ctx := dbms_xmlgen.newContext('SELECT * FROM access_no_details WHERE account_no = '|| p_account_no);
dbms_xmlgen.setRowTag(ctx, 'row');
xml := dbms_xmlgen.getXML(ctx);
dbms_xmlgen.closeContext(ctx);
if length(xml) > 0 then
dbms_lob.open(xml,dbms_lob.lob_readonly); -- Open the file in READ_ONLY mode
loop
dbms_lob.read(xml,amount,position,charString); --Read until end
-- DEBUG dbms_output.put_line (charString);
htp.prn(charString); -- generates the specified string,
--not terminated with a newline.
position := position + amount;
end loop;
else
htp.prn('<ROWSET/>');
end if;
exception
when no_data_found then
dbms_lob.close(xml);
dbms_lob.freetemporary(xml);
when others then
htp.prn('<ROWSET><ERROR>'||SQLERRM||'</ERROR></ROWSET>');
END;
END web_getservices;
/