The first blog posting in 2016 will be about APEX without APEX.
What does that mean? Well, APEX installs several PL/SQL packages into the
database (which is obvious since APEX is implemented in PL/SQL). But some
packages of the documented APEX PL/SQL API can be used also outside the
context of APEX applications. And these are what this blog posting is about. I will
not explain all these in the very detail - for some there are already existing
how tos which I will refer to.

JSON parsing: APEX_JSON (APEX 5.0 or higher)

Beginning with release 12.1.0.2, the Oracle database contains SQL/JSON functions
which allow to parse and process JSON documents with SQL functions. These functions
are very powerful and fast, so when it's about parsing JSON in a 12c database, one
should always use the native SQL/JSON functions. But there are also several
applications still running on 11g. Also, the native JSON capabilities only cover JSON parsing,
not JSON generation.

The
APEX_JSON package which was introduced with APEX 5.0, comes to
a rescue here. It's part of APEX, but it can easily be used outside of APEX as
well. In 2015 I already had two blog postings about working with APEX_JSON, so I'll
just reference these here.

ZIP-Archives: APEX_ZIP

The APEX_ZIP
package allows to work with ZIP archives directly in the database and with PL/SQL. Having a ZIP archive
stored as BLOB in the database, the APEX_ZIP package can extract the individual files as BLOBs.
The following code example illustrates how this works.

Building a new ZIP archive from existing BLOB data is also possible. The functions
ADD_FILE and FINISH serve that purpose.

Access an LDAP server the easy way: APEX_LDAP

To access an LDAP server with PL/SQL, we have the
DBMS_LDAP package for a very long time now. But the API is rather cumbersome and not easy to use. Even simple tasks like checking group membership require a lot of (boilerplate) code. So, the APEX development team added the
APEX_LDAP package for the most important task. And since APEX 5.0, the SEARCH function is really interesting.

Beyond the SEARCH function, APEX_LDAP provides other functions for standard LDAP requirements. The
AUTHENTICATE function does exactly what the name indicates - it just logs into the LDAP server. So this function can
be used to validate LDAP username/password combinations. The
MEMBER_OF and MEMBER_OF2 functions return the groups, a given LDAP user belongs to, as PL/SQL array or VARCHAR2 separated list, respectively.

Escaping with APEX_ESCAPE

The APEX_ESCAPE package consolidates several escaping functions. For APEX developers this is a very important package, they need it all the time to escape HTML special characters like <, > and & (important to protect an application against XSS (cross site scripting) attacks).

But APEX_ESCAPE offers further helpful functions. The
JSON
function escapes JSON-specigic characters like " or '. Developers can pass data through that functions when it is to be added to a JSON document.

Calling REST services with APEX_WEB_SERVICE

The APEX_WEB_SERVICE package allows to consume REST or SOAP webservices with PL/SQL calls. For instance, the
MAKE_REST_REQUEST function calls a REST service at the given URL endpoint with the given HTTP method. Additional parameters allow to pass HTTP haeder fiels or authentication data.

But there is one caveat regarding APEX_WEB_SERVICE: Since Oracle11g, network resources
are protected by PL/SQL Network ACLs. In order to connect to a network location,
the database user needs to be granted a network ACL by the DBA. The DBA does this with
the DBMS_NETWORK_ACL_ADMIN package. APEX_WEB_SERVICE performs its HTTP requests as
the APEX engine user (APEX_050000, APEX_040200, ...) - so as soon as the
APEX engine user has been granted a network ACL, all database users can connect to
that network location with APEX_WEB_SERVICE.

Little helpers in APEX_UTIL

APEX_UTIL
is the general store within the APEX PL/SQL packages. It contains many procedures and functions
for various purposes. In the meantime, specialized packages like APEX_ESCAPE or APEX_IR have been introduced; the
APEX_UTIL procedures are still present for backwards compatibility. Some very few functions might
be useful also for the non-APEX PL/SQL developer.

GET_HASH calculates a hash value from a given set of VARCHAR2 items. APEX developers need this often to detect changes
in underlying database tables (Lost update detection). But within a generic stored procedure, the function might also be useful.

Have fun trying these things out - perhaps one or the other function of the APEX PL/SQL API
is useful to you. And for upcoming APEX releases we can expect additions and changes - so
it should be worth the effort to have a look into the Application Express API Reference from time to time.

The views expressed on this blog are my own and do not necessarily reflect the views of Oracle Corporation or Oracle Germany. The views and opinions expressed by visitors to this blog are theirs and do not necessarily reflect mine.