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

Marcos Claver 5

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
    hex_char VARCHAR2(1);
    SELECT decode(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
    websafe := REPLACE(p_in_value, ' ', '+'); -- substitute spaces
    FOR n_counter IN 1 .. length(websafe) -- loop though elements of string
      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 add char itself
        v_ret := v_ret || substr(websafe, n_counter, 1);
      END IF;
    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.

5 thoughts on “Construct a "Save" URL syntax for the Oracle Reports Server

  1. I understand Anton. You could do it like that aswel.

    As for Francois question. The Oracle version was 8174. I do not think URL_URL.ESCAPE is present in that version

  2. My point is, why use your own dec2hex function. The to_char function can do the same.

Comments are closed.

Next Post

Generic Service - HTML Form Post to Email Servlet

This article is about a generic service that takes a simple HTML Form Post and turns it into an Email. It is not revolutionary – but useful nevertheless. Besides, it is the first installment in a series that works towards a SOA implementation. We will implement a Business Process in […]
%d bloggers like this: