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:

&nbsp;-- Convert a decimal digit into its hex-equiavalent<br />&nbsp; FUNCTION dec2hex(p_dec IN NUMBER) RETURN VARCHAR2 IS<br />&nbsp;&nbsp;&nbsp; hex_char VARCHAR2(1);<br />&nbsp; BEGIN<br />&nbsp;&nbsp;&nbsp; SELECT decode(p_dec,<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 15,<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'F',<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 14,<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'E',<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 13,<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'D',<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 12,<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'C',<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 11,<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'B',<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10,<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'A',<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; to_char(p_dec))<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; INTO hex_char<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM dual;<br />&nbsp;&nbsp;&nbsp; RETURN hex_char;<br />&nbsp; END dec2hex;
&nbsp; --*****************************************<br />&nbsp; -- convert the unsafe characters in an URL string<br />&nbsp; FUNCTION makeurlsafe(p_in_value IN VARCHAR2) RETURN VARCHAR2 IS<br />&nbsp;&nbsp;&nbsp; websafe&nbsp;&nbsp; VARCHAR2(1000);<br />&nbsp;&nbsp;&nbsp; n_counter NUMBER;<br />&nbsp;&nbsp;&nbsp; v_ret&nbsp;&nbsp;&nbsp;&nbsp; VARCHAR2(32000); -- return value<br />&nbsp;&nbsp;&nbsp; v_ok&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VARCHAR2(4000) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890+';&nbsp;&nbsp;&nbsp; -- chars ok for URL<br />&nbsp; BEGIN<br />&nbsp; <br />&nbsp;&nbsp;&nbsp; websafe := REPLACE(p_in_value, ' ', '+'); -- substitute spaces<br />&nbsp; <br />&nbsp;&nbsp;&nbsp; FOR n_counter IN 1 .. length(websafe) -- loop though elements of string<br />&nbsp;&nbsp;&nbsp;&nbsp; LOOP<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; IF instr(v_ok, upper(substr(websafe, n_counter, 1))) = 0 THEN<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- if char is not OK, replace it by %nn value<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; v_ret := v_ret || '%' ||<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; dec2hex(trunc(ascii(substr(websafe, n_counter, 1)) / 16, 0)) ||<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; dec2hex(ascii(substr(websafe, n_counter, 1)) MOD 16);<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ELSE<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- else add char itself<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; v_ret := v_ret || substr(websafe, n_counter, 1);<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; END IF;<br />&nbsp;&nbsp;&nbsp; END LOOP;<br />&nbsp; <br />&nbsp;&nbsp;&nbsp; RETURN v_ret; -- return converted value<br />&nbsp; END makeurlsafe;<br />

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.


About Author


  1. Marcos Claver on

    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