Construct a "Save" URL syntax for the Oracle Reports Server

For a customer had to create an Oracle Forms functionality which would need to make several requests to the Oracle Report Server. The customer wanted me to generate a report to the screen,  print the same report to the OS and enter the report as a BLOB type in the database. The entry of the BLOB was required due to regulatory requirements. The customer is a major pharmaceutical company which needs to apply to the 21CFR part 11 regulation enforced by the FDA.

I resolved

the first two distributions simply by using the Oracle standard calling the Oracle Report Server from Oracle Forms. For the database distribution I used the Oracle pluggable destination which you can download from OTN. After properly depoying it to the reports server you can distribute the report to the database by submitting the proper URL call. The biggest challenge in the functionality was to make a safe URL call which would not break. Since I would need to pass several parameters to the report which could contain “strange” characters I had a need to generate a safe URL string which could be submitted withhout causing issues.

For this I used the following two functions:

 -- Convert a decimal digit into its hex-equiavalent
  FUNCTION dec2hex(p_dec IN NUMBER) RETURN VARCHAR2 IS
    hex_char VARCHAR2(1);
  BEGIN
    SELECT decode(p_dec,
                  15,
                  'F',
                  14,
                  'E',
                  13,
                  'D',
                  12,
                  'C',
                  11,
                  'B',
                  10,
                  'A',
                  to_char(p_dec))
      INTO hex_char
      FROM dual;
    RETURN hex_char;
  END dec2hex;

  --*****************************************
  -- convert the unsafe characters in an URL string
  FUNCTION makeurlsafe(p_in_value IN VARCHAR2) RETURN VARCHAR2 IS
    websafe   VARCHAR2(1000);
    n_counter NUMBER;
    v_ret     VARCHAR2(32000); -- return value
    v_ok      VARCHAR2(4000) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890+';    -- chars ok for URL
  BEGIN
  
    websafe := REPLACE(p_in_value, ' ', '+'); -- substitute spaces
  
    FOR n_counter IN 1 .. length(websafe) -- loop though elements of string
     LOOP
      IF instr(v_ok, upper(substr(websafe, n_counter, 1))) = 0 THEN
        -- if char is not OK, replace it by %nn value
        v_ret := v_ret || '%' ||
                 dec2hex(trunc(ascii(substr(websafe, n_counter, 1)) / 16, 0)) ||
                 dec2hex(ascii(substr(websafe, n_counter, 1)) MOD 16);
      ELSE
        -- else add char itself
        v_ret := v_ret || substr(websafe, n_counter, 1);
      END IF;
    END LOOP;
  
    RETURN v_ret; -- return converted value
  END makeurlsafe;

After I had created the proper URL string I pass it to the function MAKEURLSAFE which will convert any ileagal character to its hex-equivalent. In this way a valid URL string is produced and processed by the Oracle Reports Server.