ORDS tips


XML over ORDS

Turns out you can emit XML via ORDS. I'll assume you have a working ORDS install and the schema is already enabled

BEGIN
  ORDS.DEFINE_MODULE(
      p_module_name    => 'reporting',
      p_base_path      => '/reporting/',
      p_items_per_page =>  0,
      p_status         => 'PUBLISHED',
      p_comments       => NULL);      
  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'reporting',
      p_pattern        => 'hello_xml',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);
  ORDS.DEFINE_HANDLER(
      p_module_name    => 'reporting',
      p_pattern        => 'hello_xml',
      p_method         => 'GET',
      p_source_type    => 'plsql/block',
      p_items_per_page =>  0,
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
'DECLARE
    l_clob   CLOB;
BEGIN
    SELECT
        ''<xml>hello world</xml>''
    INTO
        l_clob
    FROM
        dual;

    owa_util.mime_header(
        ccontent_type   => ''text/xml'',
        bclose_header   => true,
        ccharset        => ''ISO-8859-4''
    );

    htp.print(l_clob);
END;'
      );
  COMMIT; 
END;

$ curl -v http://oracle.rocks:8080/ords/scott/reporting/mime_works
* About to connect() to oracle.rocks port 8080 (#0)
*   Trying 1.2.3.4... connected
* Connected to oracle.rocks (1.2.3.4) port 8080 (#0)
> GET /ords/scott/reporting/mime_works HTTP/1.1
> User-Agent: curl/7.19.7 (x86_64-redhat-linux-gnu) libcurl/7.19.7 NSS/3.27.1 zlib/1.2.3 libidn/1.18 libssh2/1.4.2
> Host: oracle.rocks:8080
> Accept: */*
>
< HTTP/1.1 200 OK
< Content-Type: text/xml; charset=ISO-8859-4
< ETag: "WaloI8WDL3PY2G0ZN6+I8C+c0FxVaUBuDc/v7LKXTpE6dTuJR1s2bLF/0hqW2fVzaXNYpr9TFXqucyoq6dO2Xw=="
< Transfer-Encoding: chunked
<
<xml>hello world</xml>
* Connection #0 to host oracle.rocks left intact
* Closing connection #0

Important item of note is the mime header, it's important that bclose_header is always true because this is literally the last HTTP header before the response body. Setting this to false will result in no data.

References

Emitting JSON via refcursor

Earlier this year Tim Hall did an excellent talk called, "Make the RDBMS Relevant Again with RESTful Web Services and JSON" (youtube) at Oracle Code 2017. The big takeaway for me was to not throw away your existing investment and rewrite everything to fit the new technology (ORDS). Instead you should use ORDS to wrap around the existing objects.

At the day job we use strongly typed reference cursor as an out parameter (this helps interoperability with Microsoft's SSRS). Here's the example "reporting" package.

CREATE OR REPLACE PACKAGE foo_pkg AS 
    /* strongly typed reference cursors or gtfo */
    TYPE bar_reftype IS RECORD (
        object_name user_objects.object_name%TYPE,
        object_type user_objects.object_type%TYPE
    );
    TYPE bar_refcur IS REF CURSOR RETURN bar_reftype;
    
    PROCEDURE bar_get (p_recordset IN OUT bar_refcur);
END foo_pkg;
/

CREATE OR REPLACE PACKAGE BODY foo_pkg AS

    PROCEDURE bar_get ( p_recordset IN OUT bar_refcur )
        AS
    BEGIN
        OPEN p_recordset FOR
            SELECT
                uo.object_name,
                uo.object_type
            FROM
                user_objects uo
            WHERE
                ROWNUM < 5;

    EXCEPTION
        WHEN OTHERS THEN
            IF
                p_recordset%isopen
            THEN
                CLOSE p_recordset;
            END IF;
            RAISE;
    END bar_get;

END foo_pkg;
/

Here's the ORDS wrapper, yes unfortunately you're going to need APEX_JSON, but the example should be easy enough to understand. It would be great if one day we didn't need this boilerplate, but for now this is a handy way of reusing code.

BEGIN
  ORDS.DEFINE_MODULE(
      p_module_name    => 'foo',
      p_base_path      => '/foo/',
      p_items_per_page =>  0,
      p_status         => 'PUBLISHED',
      p_comments       => NULL);      
  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'foo',
      p_pattern        => 'bar/',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);
  ORDS.DEFINE_HANDLER(
      p_module_name    => 'foo',
      p_pattern        => 'bar/',
      p_method         => 'GET',
      p_source_type    => 'plsql/block',
      p_items_per_page =>  0,
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
'DECLARE
l_cursor SYS_REFCURSOR;
BEGIN    
    foo_pkg.bar_get(l_cursor);
    apex_json.open_object;
    apex_json.write(''bar'', l_cursor);
    apex_json.close_object;
END;'
      );


  COMMIT; 
END;
/


Tweet