Minifying APEX_JSON CLOB Output

Minifying APEX_JSON CLOB Output

Matt Mulvaney's photo
Matt Mulvaney
·

3 min read

Firstly a walk-through of the different modes of apex_json.initialize_clob_output

For example, I have this code in a Dynamic Content Region.

DECLARE
  l_result CLOB;
  c        sys_refcursor;
BEGIN
  apex_json.initialize_clob_output;
  open c for 
   select id, Name from snes_games where lower ( name ) like '%fighter%';
  apex_json.open_object;
  apex_json. write('games', c);
  apex_json.close_object;
  l_result := apex_json.get_clob_output;
  apex_json.free_output; 
  RETURN TO_CLOB(
    apex_string.format( '<h1>%s Characters</h1><pre>%s</pre>', 
    length(l_result), 
    l_result )
    );
END;

In APEX, Dynamic Content Regions require I return a CLOB, so I'm explicitly converting to a CLOB before returning the content.

Here is what the page looks like when I run it.

Note that the output is pre-formatted with indentations, therefore I want the browser to preserve that too. Therefore I use the <PRE></PRE> tags to achieve this.

Now, without changing a thing, lets run that page in Debug Mode.

So be aware that apex_json.initialize_clob_output, by default, decides to indent according to if the page is in Debug Mode or not. You get the same result, but just formatted different. You always get new lines regardless of the debug mode.

Ideally, it shouldn't matter, as long as the JSON is valid and you are using a parser to unpack the JSON when you need it.

However there is a behavioural difference which is dependent on debug mode.

But what happens if you always want consistency regardless of the Debug Mode?

In this case, you can use a parameter.

apex_json.initialize_clob_output( p_indent => 0 );

Lets see the full code

DECLARE
  l_result CLOB;
  c        sys_refcursor;
BEGIN
  apex_json.initialize_clob_output( p_indent => 0 );
  open c for 
   select id, Name from snes_games where lower ( name ) like '%fighter%';
  apex_json.open_object;
  apex_json. write('games', c);
  apex_json.close_object;
  l_result := apex_json.get_clob_output;
  apex_json.free_output; 
  RETURN TO_CLOB(
    apex_string.format( '<h1>%s Characters</h1><pre>%s</pre>', 
    length(l_result), 
    l_result )
    );
END;

The outputs in both Debug and Non-Debug Modes are now identical

The p_indent parameter is bonkers:

  • You can supply negative numbers, e.g -3. It doesn't error, however in these cases it treats as null or as if no parameter is passed.

  • Floating point numbers round! i.e 2.1 rounds to an indentation of 2. 2.5 rounds to an indentation of 3.

  • You can supply huge numbers! it started to freak out at indentations of around 1300 with a strange error of ORA-12705: Cannot access NLS data files or invalid environment specified

Now for the challenge: What do we do if we want to use as few characters as possible?

Well we can crunch it down a little more using by replacing the new-lines

l_result := REPLACE( l_result, CHR(10) );

and the code in full

DECLARE
  l_result CLOB;
  c        sys_refcursor;
BEGIN
  apex_json.initialize_clob_output( p_indent => 0 );
  open c for 
   select id, Name from snes_games where lower ( name ) like '%fighter%';
  apex_json.open_object;
  apex_json. write('games', c);
  apex_json.close_object;
  l_result := apex_json.get_clob_output;
  apex_json.free_output; 
  l_result := REPLACE( l_result, CHR(10) );
  RETURN TO_CLOB(
    apex_string.format( '<h1>%s Characters</h1><pre>%s</pre>', 
    length(l_result), 
    l_result )
    );
END;

and the result is

That's a 7% saving on character usage!

If you want to crunch this down even further, you'll need to evaluate renaming the JSON tags and/or use a compression algorithm.

Bonus: If you are going to use apex_string.format in this way, i.e to format large strings, be aware that there is a 1000 character limit resulting in a chopping of strings, causing invalid JSON objects.

You can boost this by using

apex_string.format( p_max_length => 32767 );

Here is the code in full

DECLARE
  l_result CLOB;
  c        sys_refcursor;
BEGIN
  apex_json.initialize_clob_output( p_indent => 0 );
  open c for 
   select id, Name from snes_games where lower ( name ) like '%fighter%';
  apex_json.open_object;
  apex_json. write('games', c);
  apex_json.close_object;
  l_result := apex_json.get_clob_output;
  apex_json.free_output; 
  l_result := REPLACE( l_result, CHR(10) );
  RETURN TO_CLOB(
    apex_string.format( '<h1>%s Characters</h1><pre>%s</pre>', 
    length(l_result), 
    l_result,
    p_max_length => 32767 )
    );
END;

Alternatively, avoid using apex_string.format when using CLOBs. Or avoid using APEX_JSON altogether

What's the picture? Its Trearddur Bay on Holy Island, Wales, during Storm Kathleen.

jsonorclapexOracle#oracle-apexOCIDatabases