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;
/