2 Replies Latest reply on Jul 26, 2012 12:09 AM by cbeaufort

    How do I return a XML document from an APEX Listener resource template?

      We have the APEX Listener 1.1.3 deployed in Glassfish 3.1.2 (build23).

      We've implemented a REST web service via a resource template with a method of
      "POST" and a type of "PL/SQL Block". We POST a XML document in the request to
      the web service and expect a XML document in return.

      In our testing, we can see that our POST data is getting through to our resource
      template's PL/SQL block, which in turn passes the data to a function in one of our
      PL/SQL packages. We're succesfully receiving the POST data in our PL/SQL function,
      where we parse it and return a XML document (the return type of the function is
      VARCHAR2) to the resource template's PL/SQL block.

      My question is, how do we configure/code the resource template to return the XML document
      to the caller of the web service? I have seen the documentation that describes how to configure
      the APEX Listener to return a JSON response, but we need to return XML.

      Here's our resource template's PL/SQL block:

      -- TBD: This should be a CLOB for production use
      l_xml_response varchar2(1024);
      l_xml_response := famlab1.fz_tcast.handle_request(:contentType, :body);

      -- TBD: Proper setting of ":status" and exception handling
      :status := case when l_xml_response is not null then 200 else 500 end;

      So on the "return path" to the web service caller, we have the desired XML response
      document in the "l_xml_response" variable in the resource template's PL/SQL block. How
      do we coerce the APEX Listener to send that response back (in XML format, not JSON)
      to the web service caller?

      - Chris
        • 1. Re: How do I return a XML document from an APEX Listener resource template?
          Frank Nimphius-Oracle

          I assume you would need to file this as an ER for the listener as the documentation explicitly says that only JSON and CSV is supported. Instead of hacking your way to XML, which would put you out of support and probably locks you in in a future version, I suggest to go for a product enhancement

          • 2. Re: How do I return a XML document from an APEX Listener resource template?
            Hi Frank,

            Thanks for your reply and my apologies for not responding sooner.

            I have to confess that I probably don't have the right/most current documentation, but I'm using what I could dig up beyond the "Installation Guide":


            This "Application Express Listener Developer's Guide" is more of a tutorial, but after my initial post, we were directed to this line tucked away in it:

            "This Resource Handler type [Media Resource] is very useful for generating XML and HTML representations, ..."

            So after my opening post in this thread, we ended up getting our REST web service (implemented as a APEX Listener resource template) to return the desired XML. It turns out, to achieve this result, you have to define 2 resource templates and I'll attempt to "depict" them below in a text format. At the end of the post, I'll describe briefly how the 2 resource templates work together.

            For the purposes of this example, I've contrived some object names and a XML response schema. Assume that I have an Oracle RDBMS schema named "MYSCHEMA", a PLSQL package in the schema named "MYPACKAGE", and a function in the package named "HANDLE_REQUEST()" that writes to a table named "REQUEST_LOG" and returns the id of the record written into the table. In addition to a "REQUEST_ID" column, the "REQUEST_LOG" table has a "REQUEST_TIME" column which "HANDLE_REQUEST()" also populates:

            Template 1:
            URI Template: requestHandler
            Priority: 0
            Entity Tag: Secure Hash
            Method: POST
            Type: PL/SQL Block
            Security Constraint: None
            Acceptable: NULL
            Name Aliasing Source Access Type
            status X-APEX-STATUS-CODE Header OUT Integer
            location X-APEX-FORWARD Header OUT String

            l_request_id number;
            l_request_id := myschema.mypackage.handle_request(:contentType, :body);
            if l_request_id is null then
            :status := 500;
            :status := 201;
            :location := 'responder/' || l_request_id;
            end if;

            Template 2:
            URI Template: responder/{rid}
            Priority: 0
            Entity Tag: Secure Hash
            Method: GET
            Type: Media Resource
            Security Constraint: None
            Acceptable: NULL
            Name Aliasing Source Access Type
            rid URI IN Integer

            select 'text/xml', xmlquery('
            { for $k in ora:view("myschema","request_log")/ROW where $k/REQUEST_ID = $rid
            (<Request requestTime="{$k/REQUEST_TIME/text()}" success="true"></Request>)
            ' passing :rid as "rid" returning content) from dual

            Here's how it all plays out:

            1. The web service client calls the "requestHandler" on the APEX Listener, making a POST request.
            2. The "requestHandler" resource template calls the PLSQL function named, "MYSCHEMA.MYPACKAGE.HANDLE_REQUEST, passing the content of the POST request (which is presumably XML.) The "HANDLE_REQUEST()" function has to know how the parse the XML (use "apex_web_service.parse_xml()") and then it inserts a record into the "REQUEST_LOG" table. This is important because, later, the "responder" resource template is going to query this table in order to generate the web service response.
            3.The PLSQL "HANDLE_REQUEST()" function returns the "REQUEST_ID" of the inserted row to the "requestHandler" resource template.
            4. The "requestHandler" resource template sets 2 APEX Listener parameters, ":status" and ":location". Here's where the "magic" happens: because the parameters were defined as aliases of "X-APEX-STATUS-CODE" and "X-APEX-FORWARD" respectively, the APEX Listener doesn't immediately return a response to the web service client.
            5. Instead, the APEX Listener calls the URI defined by the value of ":location", which is our "responder" resource template. Note that the "REQUEST_ID" of the row inserted into the REQUEST_LOG table is passed on the URI.
            6. The "responder" resource template uses "xmlquery" to access the REQUEST_LOG table and then generates an XML response and returns it to the web service client.

            To be fair, the "Application Express Listener Developer's Guide" document basically describes how to do all of this, it was just hard for me to get my head wrapped around it in the context of our REST web service.