Thursday, September 04, 2014

APEX 5 New Package Features

To get a proper list of the new packages and API's APEX 5 provides us, we have to wait for the documentation of course. But if you're impatient and want to now more, you can get a list of all package procedures and functions by running this SQL on the APEX 5 Early Adopter instance:

select distinct s.synonym_name, p.procedure_name
from all_procedures p join all_synonyms s on p.object_name = s.table_name
where p.owner like 'APEX%'
  and s.synonym_name like 'APEX%'
order by 1, 2;

You'll get a list of over 700 procedures/functions. Most should look familiar. But there are some new kids on the block like : APEX_JSON, APEX_SPATIAL and APEX_ZIP.
You can examine the ALL_SOURCE view to get more insight by looking at the parameters and the examples in the comments.

The APEX_ZIP package is - according to the comments in the package - based on the work of Anton Scheffer (see this blog post). The functions / procedures can be used to zip and upload a file or to download and unzip a file. 

The APEX_SPATIAL package enables you to use the Oracle Locator and Spatial Option within APEX. I am not sure whether one of the functions in that package actually require a Spatial Option license - and if you can easily violate the (absence of that) license by calling such a function "by accident". Maybe one of the Oracle people can shine a light on this - always tricky - subject!

The APEX_JSON package finally, is used for generating and parsing JSON with PL/SQL. As an example - more or less "borrowed" from Morten Braten's post

Generating JSON
   apex_json.initialize_output(p_http_header => false);
   for i in 1..3 loop
     apex_json.write('Attr'||i, i);
   end loop;
   apex_json.write('ExtraData','More to come');

results in :

{ "Item1":"value1" 
,"Attributes":[ { "Attr1":1 ,"Attr2":2 ,"Attr3":3 } ] 
,"ExtraData":"More to come" }

Parsing JSON
   l_json varchar2(32767) := '{"empno":123, "empname":"King", "empsal":3000}';
   apex_json.parse( l_json );
   htp.prn( apex_json.get_number( p_path => 'empno' ));
   htp.prn( apex_json.get_varchar2( p_path => 'empname' ));

Results in : 123 King

This feature can be very handy when you want to generate JSON as a source for one of the many Javascript charting libraries that usually need data in this format. Or - the other way around - when a call to an ORDS Web Service returns JSON and you need to display the results somewhere on a page.
Post a Comment