Episode #2 of Feuertips was titled “Bad Lazy – Good Lazy.” This post (and the 46th episode) is all about some very good lazy. Good lazy is mostly, usually about getting someone else to do the “heavy lifting,” implementing functionality you need so you don’t have to write it yourself.
In this episode, we check out some really impressive power lifts from the APEX dev team.
APEX (Oracle Application Express) is one of the most amazing products ever to come out of the Oracle Database group. It supports a low-code approach to application development that lets you leverage your SQL and PL/SQL knowledge, while doing lots of the HTML-CSS-JavaScript heavy lifting. Great stuff!
APEX is also famous for its community of developers and its close ties to the APEX dev team. In fact, that dev team is an active, giving, teaching, learning part of its user community. Their love of their users is clear in many ways, but one of them is their commitment to publishing PL/SQL APIs for lots of the code they have built and use in APEX.
I invited Anton Nielsen of Insum Solutions to join my Feuertip to explore these packages, and get everyone excited about using them.
I suggest you stop reading this post right now and visit https://apex.oracle.com/api. That’s a direct link to the API Reference for the most recent version of APEX (don’t worry – there’s also a Documentation Archive for older versions that is available here).
Browse through the 43 packages and be amazed at all the cool stuff that is offered through these APIs.
And here’s an amazing fact you may never have realized: lots of this code can be (should be!) used by Oracle Database developers even if you are not building applications with APEX!
That’s right. Even if you are building an application with Java or C++ or Python or whatever, you will find lots of goodies in the APEX API that will help you be more productive and improve the quality of your code.
Of course, many of those packages are mostly relevant to APEX development. You can break down the packages into three categories:
- Requires an APEX application session to work properly.
- Requires access to at least one APEX workspace (even if you don’t develop an APEX app in that workspace).
- Requires nothing but having APEX installed in your database instance.
Unfortunately, the documentation is not clear regarding which of these categories a package falls into.
Let’s take a look at some examples.
Likely the most popular and widely used of the APEX packages is apex_string. And almost certainly, the subprogram used most from that package is the split function. Here’s a simple example:
select * from apex_string.split('A:B:C',':')
COLUMN_VALUE
------------
A
B
C
The APEX team uses this same function, so they also realized that sometimes you need to split numbers–and get numbers in return. This allows you to avoid implicit conversions that may raise errors or stop the use of an index. This example returns numbers:
select * from apex_string.split_numbers('1:2:3',':')
COLUMN_VALUE
------------
1
2
3
There’s also a lot going on here. apex_string.split is a function that returns a collection of type apex_t_varchar2 (split_numbers returns apex_t_number). It is, in other words, a table function. Which means that I can query from it as if it were a relational table. This patterns throughout the apex_string package (and others).
This package also offers much more powerful options, such as the grep function. Note: I don’t know how to use grep, but Anton does. Check this out:
declare
l_sqlfiles apex_t_varchar2;
begin
l_sqlfiles := apex_string.grep (
p_table => apex_t_varchar2('a.html','b.sql', 'C.SQL','joe.txt','myPicture.png','f123.sql'),
p_pattern => '(\w+)\.sql',
p_modifier => 'i',
p_subexpression => '1' );
for i in 1..l_sqlfiles.count loop
sys.dbms_output.put_line(l_sqlfiles(i));
end loop;
end;
/
b
C
f123
And you can do the same thing directly in SQL:
select * filename
from apex_string.grep (
p_table => apex_t_varchar2('a.html','b.sql', 'C.SQL','joe.txt','myPicture.png','f123.sql'),
p_pattern => '(\w+)\.sql',
p_modifier => 'i',
p_subexpression => '1' )
;
FILENAME
--------
b
c
f123
And the fun just keeps on coming!
How about issues Web requests directly from inside your PL/SQL block? This returns the HTML for the home page of Insum.
declare
l_clob clob;
begin
l_clob := apex_web_service.make_rest_request(
p_url => 'https://insum.talan.com/',
p_http_method => 'GET');
htp.p(substr(l_clob, 1, 32767));
end;
/
Ever had to work with LDAP? Check out the apex_ldap package!
begin
if apex_ldap.authenticate(
p_username => apex_escape.ldap_dn('ANTON+NIELSEN'),
p_password => 'foo',
p_search_base => 'dn=users',
p_host => '192.168.1.72'
) then
htp.p('true');
else
htp.p('false');
end if;
end;
/
And one of my all-time favorites: apex_mail. Makes it so, so easy to send emails from within APEX – and your PL/SQL backend code. For this package, you will need to have access to at least one workspace from your schema.
declare
l_body clob;
l_body_html clob;
l_id number;
begin
l_body := 'To view the content of this message, please use an HTML enabled mail client.' || utl_tcp.crlf;
l_body_html := 'All the news about Oracle APEX that you can use!'
|| utl_tcp.crlf
|| 'Straight to your Inbox!';
l_id := apex_mail.send(
p_to => 'some_user@somewhere.com', -- change to your email address
p_from => 'some_sender@somewhere.com', -- change to a real sender's email address
p_body => l_body,
p_body_html => l_body_html,
p_subj => 'APEX_MAIL Package - HTML formatted message'
);
end;
/
That should give you a good feel for the usefulness and scope of PL/SQL functionality available to all Oracle Database developers – even if you are not (yet) building APE apps.
