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.

Original Article: Roel Hartman – APEX 5 New Package Features


No comments

Geef een reactie

Het e-mailadres wordt niet gepubliceerd. Verplichte velden zijn gemarkeerd met *

De volgende HTML tags en attributen zijn toegestaan: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>