01 February 2019

Solving an issue regarding escaping substitution strings in Oracle APEX

In a recent dashboard-type application, I was using Google Charts for one of my dashboard regions.  This meant that I needed to place some javascript inside the html page header.

The JavaScript was broken into two types

  1. static sections that would never change, and
  2. a dynamic section that was generated by assigning the result of PL/SQL package function to a hidden item named P1_DRAWMAP_JS.
This is the code that was run "before header" to initialise the item.


:P1_DRAWMAP_JS := centutils.genDrawMap (p_language => v(:FSP_LANGUAGE_PREFERENCE));


Once initialised, item P1_DRAWMAP_JS contained a part of a javascript function with about 200 lines of code.  This code also contained some html tags.  A snippet of the generated code is shown here,  although it's not important to understand it for this article, just to note its general structure:

function drawMap() {
var data = new google.visualization.DataTable();
data.addColumn('string', 'Country'); // Implicit domain label col.
data.addColumn('number', 'Value'); // Implicit series 1 data col.
data.addColumn({type:'string', role:'tooltip', p: {html: 'true'}}); //
data.addRows([ [{v:"BA",f:"Bosnia and Herzegovina"},1,"<strong>P029</strong> - entry into force - 09 Aug 2019"],
[{v:"BE",f:"Belgium"},1,"<strong>C187</strong> - entry into force - 31 May 2019"],
[{v:"BG",f:"Bulgaria"},1,"<strong>C131</strong> - entry into force - 20 Mar 2019"],
[{v:"BR",f:"Brazil"},1,"<strong>C189</strong> - entry into force - 31 Jan 2019"],
....
....



The page header looked something like this (below) with the dynamically generated P1_DRAWMAP_JS substitution variable placed between a <script> tag and a </script> tag along with a few other bits of static javascript code.


So everything should work then, right?   Unfortunately not - my region was blank.  When viewing the html source, what I actually saw was that the javascript code contained in my substitution variable had been escaped by APEX.





This is when I decided to search the APEX documentation to see how to get around this problem.  After a few minutes, I came across this documentation on escaping substitution strings.

From the documentation we can see that there are 5 different ways of escaping APEX substitution variables.
They all start with an exclamation mark and immediately follow the name and can also be used in report and interactive grid columns.

!HTML escapes reserved HTML characters
!ATTR escapes reserved characters in a HTML attribute context
!JS   escapes reserved characters in a JavaScript context
!RAW  preserves the original item value and does not escape characters
!STRIPHTML removes HTML tags from the output and escapes reserved HTML characters


The solution to my issue was very simple.  I disabled the escaping of my substitution variable by adding !RAW to the name of the substitution string, so it now became &P1_DRAWMAP_JS!RAW. 

Everything worked!  The result was exactly what I expected and my region looked now looked as it should, like this.


Sometimes the solutions to our problems are very simple. 
And in this case, the APEX development team at Oracle had already anticipated what was needed and provided a mechanism for implementing it.  Another reason that Oracle APEX is one of the best low-code development tools out there!

10 December 2018

Computing an SHA 256-bit checksum/hash on a BLOB in an Oracle 11g database

A client of mine recently had the need to generate SHA 256-bit checksums on BLOB columns stored in an Oracle 11g database.  My first reaction was to say "No Problem, we'll just use either the standard_hash function or the dbms_crypto.hash function".

I soon discovered that Oracle 11g doesn't have SHA 256-bit capabilities.  I then embarked on an extensive Google search and had a few false starts - mostly finding solutions for VARCHAR2 fields but not for BLOBs.

However, after a while I came across this stackoverflow entry from August 2013.
https://stackoverflow.com/questions/18522525/compute-blob-hash-in-trigger-on-blobs-table

So I ran the code below to create the java stored procedure and two wrapper functions, tested with a couple of BLOB columns containing files, compared the result with the checksum computed for the same files on the file system and everything worked perfectly!
Problem solved, client happy!

Here's the code, and a shout-out to the original developers of this solution: Sean Stuber (https://twitter.com/sdstuber) and AdiM.  Thanks guys!

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED sha2 AS
import java.security.MessageDigest;
import oracle.sql.*;
public class sha2
{
    public static oracle.sql.RAW get_digest_string( String p_string, int p_bits ) throws Exception
    {
        MessageDigest v_md = MessageDigest.getInstance( "SHA-" + p_bits );
        byte[] v_digest;
        v_digest = v_md.digest( p_string.getBytes( "UTF-8" ) );
        return RAW.newRAW(v_digest);
    }

    public static oracle.sql.RAW get_digest_blob( oracle.sql.BLOB p_blob, int p_bits ) throws Exception
    {
        byte[] allBytesInBlob;
        allBytesInBlob = p_blob.getBytes(1, (int) p_blob.length());
        MessageDigest v_md = MessageDigest.getInstance( "SHA-" + p_bits );
        byte[] v_digest = v_md.digest( allBytesInBlob );
        return RAW.newRAW(v_digest);
    }
}
/

CREATE OR REPLACE FUNCTION sha2_string(p_string in VARCHAR2, p_bits in number)
RETURN RAW AS LANGUAGE JAVA
NAME 'sha2.get_digest_string( java.lang.String, int ) return oracle.sql.RAW';
/

CREATE OR REPLACE FUNCTION sha2_blob(p_byte in BLOB, p_bits in number)
RETURN RAW AS LANGUAGE JAVA
NAME 'sha2.get_digest_blob( oracle.sql.BLOB, int ) return oracle.sql.RAW';
/

SELECT sha2_string('0123456789',256) FROM DUAL;

SELECT sha2_blob(utl_raw.cast_to_raw('0123456789'),256) FROM DUAL;

14 November 2018

An updated and better method for selecting values from a string using the APEX_STRING package

My previous post about selecting from a comma-separated string has been made redundant by the updated APEX_STRING package.

Nowadays, to select values from a comma-separated string you can simply use the SPLIT function of the APEX_STRING package.  This method has the advantage of also working with other delimiters apart from commas and of working with non-numeric values

The APEX_STRING.SPLIT function accepts the following three arguments

  • p_str  - the string to be split
  • p_sep - the separator (optional parameter)
    the default is a line feed
  • p_limit - the maximum number of splits (optional parameter)
    the default is null i.e. ignore this parameter



-- comma separated list
select column_value as id 
  from table(apex_string.split('123,456,789,012,234,567,890',','));

ID 
---------
123
456
789
012
234
567
890

7 rows selected.

-- hashtag separated list
select column_value as my_value 
  from table(apex_string.split('cat#dog#cow#horse#dolphin#elephant#bigfoot','#'));

MY_VALUE 
---------
cat
dog
cow
horse
dolphin
elephant
bigfoot

7 rows selected. 

03 July 2015

A simple way of SELECTing values from a comma-separated string

See my later blog post "An updated and better method for selecting values from a string using the APEX_STRING packagefor an updated and better way of doing this 

Want a quick and easy way to select from a comma-separated list? Just use xmltable. 


This can be used for a FOR loop too! 



Note: You probably shouldn't use this in an environment that requires high-performance though.  As Mathias Rogel and Carsten Czarski have pointed out here, it's not as efficient as a PL/SQL-pipelined function or a pure SQL-approach - but it does have the merit of being simple.

11 March 2015

Recently, I decided to automate the regular backup of APEX applications on my servers.  To achieve this I scheduled a regular PL/SQL job using crontab.

Basically I just loop through all of the applications, then export them to a clob and write this clob to a directory.   I use the application ID, its name and the date as the output file name.

I have an optional part at the end where I push the directory to Amazon's S3 as off-site storage.  

 #!/bin/bash   
 export ORACLE_BASE=/u01/oracle  
 export ORACLE_HOME=${ORACLE_BASE}/product/11.2.0/dbhome_1  
 export PATH=${PATH}:${ORACLE_HOME}/bin  
 export ORACLE_SID=MYSID  -- replace this with your SID
 export ORAENV_ASK=NO  
 export OUTPUT_DIR=/u01/exports  -- same as oracle dir DATA_PUMP_DIR
 . ${ORACLE_HOME}/bin/oraenv  
   
 date  
 echo "Oracle Home is " ${ORACLE_HOME}  
   
 ${ORACLE_HOME}/bin/sqlplus system/mysystempassword << EOC  
 SET ECHO ON  
 SET FEEDBACK ON  
 SET TIMING ON  
 SET TIME ON  
 SET SERVEROUTPUT ON  
   
 DECLARE  
  v_clob CLOB;  
 BEGIN  
  FOR rec IN (SELECT application_id, application_name   
                FROM apex_applications   
               ORDER BY application_id)  
  LOOP  
    v_clob := wwv_flow_utilities.export_application_to_clob(trim(to_char(rec.application_id)));  
    dbms_xslprocessor.clob2file  
     (cl => v_clob,   
      flocation => 'DATA_PUMP_DIR',   -- oracle dir points to the same as $OUTPUT_DIR
      fname => trim(to_char(rec.application_id,'99900000'))||'_'  
               ||replace(rec.application_name,' ','_')  
               ||'.'||to_char(sysdate,'YYYYMMDD')||'.sql');  
    dbms_output.put_line(rec.application_name||':'||length(v_clob));  
  END LOOP;  
 END;  
 /  
   
 EOC  
   
 date  
   
 #optional copy to Amazon S3 - you don't necessarily need this   
 TODAY=`date "+%Y%m%d"`  
 echo $TODAY  
 s3cmd put $OUTPUT_DIR/*$TODAY* s3://DBExports/APEXapplications/$TODAY/  
   
 date