HTP.P no longer working in Application

Question

I'm hoping you could help me

I have a client who I develop an application for, and my development required an upgrade from Oracle 10g to Oracle 11g. As I was doing such a major upgrade, I decided to upgrade everything (i.e. apex, tomcat and listener, as well as 11g), which has unfortunately caused some errors as I'll detail below

My old environment was Oracle Database 10.2g XE, Apex 4.0.1, Tomcat 7.0.5, Apex Listener 1.1.3. My new environment is Oracle DB 11.2g XE, Apex 4.2.4, Tomcat 7.0.50 and Apex Listener 2.0.5.

Everything went smoothly, until I tried to use the HTP package by trying to execute HTP.P. On execution of HTP.P, a window is supposed to open via JavaScript embedded in the code, however nothing happened apart from the page reloading itself. The code I attempted to execute via a button click is shown below

DECLARE
numf NUMBER;
x VARCHAR2(250) := 'window.open("f?p=102:0:&SESSION:PRINT_REPORT=Revised%20Stock%20Req%20Print")';
y VARCHAR2(250) := 'alert("No unprinted stock items found - cannot execute stock print request")';
z VARCHAR2(250) := ' window.location.href= "f?p=102:40:&SESSION"';

BEGIN
select count(*) into numf from transaction_detail where transaction_header_no in (select transaction_code from transaction_header where authority_batch_no = :P40_AUTH_BATCH_NUMBER) and transaction_source_table = 'STOCK_ITEMS' and transaction_total > 0 and Date_Printed is NULL;

if numf > 0 then

update transaction_detail set Req_Printed='Y', Date_Printed=:P40_TIMESTAMP where transaction_header_no in (select transaction_code from transaction_header where authority_batch_no = :P40_AUTH_BATCH_NUMBER and auth_check_back='Y') and transaction_source_table = 'STOCK_ITEMS' and Date_Printed is NULL;

COMMIT WORK;

htp.script (x, 'Javascript');
htp.script  (z, 'Javascript');

ELSE 

htp.script (y,'Javascript');
htp.script (z, 'JavaScript'); 
END IF;
END;

As you can hopefully tell, if the number of transactions (numf) > 0, then it is meant to open a new window with a report, and if not then it is meant to display the message. However it just reloads the page without displaying a message or anything. I've viewed Apex debugs that show the code attempting to be executed (and the debugs have no error), which is leading me to think that there is something I need to set up for the packages to run.

I've loaded the HTP and UTL_HTTP packages (via @utlhttp.sql and @pubht.sql commands)I've granted ALL privileges to the schema and APEX_040200 user on the HTP and UTL_HTTP packages, however nothing seems to be able to open the window. No other config to the Listener (apart from basic config of the port, database sid and so on) has been done.

Any help or advice would be greatly appreciated.

Kind Regards, DazzaRPD

EDIT: The code worked in 10.2g using htp.p instead of htp.script, In 11g I have tested it using htp.p to no avail. The code is executed once a button is pushed, and is in the Processing branch of Apex. The code itself is executed 'On Submit, after Computations and Validations'. The Page Reload isn't done by the window.location.href

Answer 1

I wonder why you would code this way. Is your goal to have no code in apex at all? (why?) Why no dynamic actions or on-demand calls? Why no proper javascript? You understand how htp.script will just add on new script tags each time?
It's always awesome once you need to rummage through plsql code to find HTML or javascript snippets. Imo, there are far more manageable ways to separate concerns.

Anyway, your problem is not in the database or grants: if you get output then that should be proof enough that the required packages can execute.
I spot one error though and that is the substitution of SESSION, which should be &SESSION. (trailing dot) in both x and z Your issue is likely to be with javascript. The page reloads, so that is probably window.location.href doing that.
Have you check the browser's console? Any errors?
Try something simple, like using some alerts or console.log calls so you can understand or follow what's occuring. eg

x VARCHAR2(250) := 'console.log("doing x");';
y VARCHAR2(250) := 'console.log("doing y");';
z VARCHAR2(250) := 'console.log("doing z");';

Also, at which process point is this code running? After Submit? On load before headers? A PLSQL region?

Answer 2

Oracle's OWA html is pretty old (xhtml), I am using "HTP" with ORDS 3.0.1 (formerly apex listener) and both 11g and 12c databases. Works fine in APEX.

What I did though was to stop using any HTML specific functions and sticking to htp.p or htp.prn.

Try modifying your code like this:

declare
    numf   number;
    x      varchar2 (250) := 'window.open("f?p=102:0:&SESSION:PRINT_REPORT=Revised%20Stock%20Req%20Print")';
    y      varchar2 (250) := 'alert("No unprinted stock items found - cannot execute stock print request")';
    z      varchar2 (250) := ' window.location.href= "f?p=102:40:&SESSION"';
begin
    select count (*)
      into numf
      from transaction_detail
     where     transaction_header_no in (select transaction_code
                                           from transaction_header
                                          where authority_batch_no = :p40_auth_batch_number)
           and transaction_source_table = 'STOCK_ITEMS'
           and transaction_total > 0
           and date_printed is null;

    if numf > 0 then
        update transaction_detail
           set req_printed = 'Y', date_printed = :p40_timestamp
         where     transaction_header_no in (select transaction_code
                                               from transaction_header
                                              where authority_batch_no = :p40_auth_batch_number and auth_check_back = 'Y')
               and transaction_source_table = 'STOCK_ITEMS'
               and date_printed is null;

        commit work;

        htp.prn ('<script>' || x || '</script>');
        htp.prn ('<script>' || z || '</script>');
    else
        htp.prn ('<script>' || y || '</script>');
        htp.prn ('<script>' || z || '</script>');
    end if;
end;

You can test this by creating a PL/SQL region and having a simple

begin
    htp.prn ('<span style="font-size:200%">Hello world!!</span>');
end;

as the content

Answer 3

Did you configure ACL permissions for your needs? Check this for a complete reference and examples DBMS_NETWORK_ACL_ADMIN