This discussion is archived
1 2 Previous Next 28 Replies Latest reply: Aug 3, 2012 5:56 AM by 405008 RSS

Call to procedure returns result above <head> tag..

405008 Newbie
Currently Being Moderated
Hi, We have an APEX 4.1 app that makes a call to a package in the same #OWNER# schema. When we submit the form it is placing the result before the <html> tag like so...

=== START OF EXAMPLE ===
-----
&lt;PRE>
Execution Timestamp: 07/19/2012-11:45:17



Billing Run General Information / Settings

Shared Application Bytes - 250,000,000



Content-type: text/html; charset=utf-8

<html lang="en-us" xmlns:htmldb="http://htmldb.oracle.com">
<head>
-----
=== END OF EXAMPLE ===

As you can see the page starts to render before the HTML and HEAD tags and then cuts off without showing the rest of the report. Below is an example of what the return looks like it we call the proc from outside of APEX. It is simply returning plain text wrapped in &lt;PRE&gt; tags.

=== START OF SUCCESSFUL RUN OUTSIDE OF APEX ===
-----
Execution Timestamp: 07/19/2012-11:46:27



Billing Run General Information / Settings

Shared Application Bytes - 250,000,000


18B29CAR 1,786.84 MEGABYTE BIS ORA ENV - ARCHIVES DEATH / MARRIAGE DB
18B29CAR 3,750.00 MEGABYTE BIS ORA ENV - ARCHIVES--GIS PORTION
18B14AMH 9,950.00 MEGABYTE BIS ORA ENV - AS-ONE REPOSITORY
18B14AMH 408,349.78 MEGABYTE BIS ORA ENV - ENTERPRISE INFORMATION SYSTEM
18B18BBIS 86,914.07 MEGABYTE BIS ORA ENV - GIS SDE ENTERPRISE DB
18B18BBIS 13,873.26 MEGABYTE BIS ORA ENV - OIT BILLING
18B10AHW 1,891,328.54 MEGABYTE BIS ORA ENV - ACES ON LINE
18B10AHW 1,604,209.76 MEGABYTE BIS ORA ENV - CHILD SUPPORT ENFORCEMENT-ME
18B10AHW 198,604.83 MEGABYTE BIS ORA ENV - DHHS DATA HUB2
...
-----
=== END OF SUCCESSFUL RUN OUTSIDE OF APEX ===


I suspect the truncated return is because it is rendering before the header tags, so my question is, how do I specify where this return is to be displayed on an APEX page? Additionally, if you know that the trucation is happening for some other reason please advise.

-- Charlie
  • 1. Re: Call to procedure returns result above <head> tag..
    Jeff E Expert
    Currently Being Moderated
    Create a "PL/SQL Dynamic Content" region and place the call to your procedure in the source. Arrange this region as you would any other region.
  • 2. Re: Call to procedure returns result above <head> tag..
    WannaRock Explorer
    Currently Being Moderated
    Hi Charlie.
    What are you using to place this proc call into the page? Is it in an application or page process? and how are you trying to dump the output to the screen? With a htp.p?

    I need to know more about how you have placed this call in the app and page, and how you are trying to return it.

    Also, did you say that the proc simply returns that text within <pre></pre> tags? Or is the report return the plain text that you are wrapping in <pre></pre> using htp.p?
  • 3. Re: Call to procedure returns result above <head> tag..
    405008 Newbie
    Currently Being Moderated
    Thank you guys for answering so quickly, it is a process fired by clicking the submit button after completing the form. I should point out that I am still learning APEX, converting an application that was written in WebDB.

    I tried what Jeff suggested but got the following error:

    Error during rendering of region "Result".

    ORA-01722: invalid number


    Technical Info (only visible for developers)
    is_internal_error: true
    apex_error_code: APEX.REGION.UNHANDLED_ERROR
    ora_sqlcode: -1722
    ora_sqlerrm: ORA-01722: invalid number
    component.type: APEX_APPLICATION_PAGE_REGIONS
    component.id: 3
    component.name: Result
    error_backtrace:
    ORA-06512: at "SYS.WWV_DBMS_SQL", line 904
    ORA-06512: at "APEX_040100.WWV_FLOW_DYNAMIC_EXEC", line 618
    ORA-06512: at "APEX_040100.WWV_FLOW_DISP_PAGE_PLUGS", line 3432
    ORA-06512: at "APEX_040100.WWV_FLOW_DISP_PAGE_PLUGS", line 4204

    I am guess this is because it is trying to call the proc but no params have been passed as it is fireing before I press the submit button.


    WannaRock: Yes if I indicate the output inferface is 'WEB' on the form before submitting then it uses HTP.PRINT otherwise it will use DBMS_OUTPUT.PUT_LINE

    --Charlie                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
  • 4. Re: Call to procedure returns result above <head> tag..
    405008 Newbie
    Currently Being Moderated
    duplicate post

    Edited by: Charlie (ME) on Jul 19, 2012 1:15 PM
  • 5. Re: Call to procedure returns result above <head> tag..
    405008 Newbie
    Currently Being Moderated
    duplicate post

    Edited by: Charlie (ME) on Jul 19, 2012 1:15 PM
  • 6. Re: Call to procedure returns result above <head> tag..
    WannaRock Explorer
    Currently Being Moderated
    Okay. So having the proc call as a process that fires on submit is good. So you hit submit and the process writes to the same page.

    To get this to work, create a page item and in the process, assign the value of the page item to the return of the procedure call:
    :P1_SAMPLE_ITEM = pkg.proc(params);

    Then in an HTML region in one of the BODY_REGION regions or one of the PAGE_TEMPLATE_REGION regions.

    Inside that HTML regions output the item in the source: &P1_SAMPLE_ITEM.

    If I understand your setup correctly, that should work. The reason it is displaying above the HTML is because the ORACLE server-side processes are firing before the DOM is rendered. So the output is there before the HTML tags are generated.
  • 7. Re: Call to procedure returns result above <head> tag..
    405008 Newbie
    Currently Being Moderated
    Ok, I created a new item (hidden text field) that I've named 'P30_SUBMITTED'.

    On the P30_SUBMITTED item that I just created I put the following into the 'Item Source Value':

    :P30_SUBMITTED = #OWNER#.DATABASE_USAGE_PKG.MONTHLY_BILLING(I_MONTH => :P30_MONTH, I_YEAR => :P30_YEAR, I_DISPLAY_FULL_REPORT => :P30_DISPLAY_FULL_REPORT, I_POPULATE_BILL_HIST_TAB => :P30_POPULATE_BILL_HIST_TAB, I_INTERFACE => :P30_INTERFACE);

    I then created the new HTML region on the same page, named it 'Report Output' and put &P30_SUBMITTED in the 'Region Source' field.

    When I run the page it shows the text '&P30_SUBMITTED' in the new region and if I fill in the fields of the form above and click the submit button it still is putting the return at the very top of the page.

    What am I doing wrong?

    Do you maybe have a very simple 'Hello World' example you could show me?

    --Charlie                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
  • 8. Re: Call to procedure returns result above <head> tag..
    WannaRock Explorer
    Currently Being Moderated
    Two things.

    #1 - You must put the trailing period on the output: "&P30_Submitted."
    #2 - if the output is still dumping in the top of the page you are running the proc somewhere with an htp.p instead of just assigning the return to your page item.

    Also, where is the region where you placed the page item located? If you check the session (click session in the developer toolbar) and look at page item values do you see your report output?

    As far as I hello world, if you want to set up a similar app in apex.oracle.com and set me up with a temporary password, I can take a look at it. You can request a workspace.

    Edited by: WannaRock on Jul 19, 2012 1:06 PM
  • 9. Re: Call to procedure returns result above <head> tag..
    fac586 Guru
    Currently Being Moderated
    Charlie (ME) wrote:
    Ok, I created a new item (hidden text field) that I've named 'P30_SUBMITTED'.

    On the P30_SUBMITTED item that I just created I put the following into the 'Item Source Value':

    :P30_SUBMITTED = #OWNER#.DATABASE_USAGE_PKG.MONTHLY_BILLING(I_MONTH => :P30_MONTH, I_YEAR => :P30_YEAR, I_DISPLAY_FULL_REPORT => :P30_DISPLAY_FULL_REPORT, I_POPULATE_BILL_HIST_TAB => :P30_POPULATE_BILL_HIST_TAB, I_INTERFACE => :P30_INTERFACE);
    If <tt>DATABASE_USAGE_PKG.MONTHLY_BILLING</tt> is a procedure producing OWA output then it's impossible for this to work. A procedure can't be used as an expression in an assignment. (It would have to be a function returning a string/CLOB instead.)
    I then created the new HTML region on the same page, named it 'Report Output' and put &P30_SUBMITTED in the 'Region Source' field.

    When I run the page it shows the text '&P30_SUBMITTED' in the new region and if I fill in the fields of the form above and click the submit button it still is putting the return at the very top of the page.
    The static text exact substitution method of referencing the value of an APEX item requires a terminating dot ("."):
    &P30_SUBMITTED.
    However, as pointed out, that approach is fatally flawed. The correct approach is that suggested by Jeff: use a Dynamic PL/SQL Content region. To prevent the errors described above, the region needs to be conditionally displayed only when the required parameters have been submitted. Use a PL/SQL Expression condition like:
        :P30_MONTH is not null
    and :P30_YEAR is not null
    and :P30_DISPLAY_FULL_REPORT is not null
    and :P30_POPULATE_BILL_HIST_TAB is not null
    and :P30_INTERFACE is not null
    or whatever rules apply.
  • 10. Re: Call to procedure returns result above <head> tag..
    WannaRock Explorer
    Currently Being Moderated
    I see what I did. My apologies. Thanks for keeping me honest fac. I read and was even typing Proc and thinking function. And I was thinking you should do something like :P30_submit := pkg.function_name(params).

    Yes the PL/SQL region is the way to go. But you will want to set a condition on the region so that it does not run the PROCEDURE (: unless triggered.
  • 11. Re: Call to procedure returns result above <head> tag..
    405008 Newbie
    Currently Being Moderated
    Thank you guys, I will try out your suggestions tomorrow once I am back in the office.

    One other point that I neglected to mention...does it matter if the proc I am calling is a wrapper around yet another procedure?

    Thanks again
    --Charlie                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
  • 12. Re: Call to procedure returns result above <head> tag..
    405008 Newbie
    Currently Being Moderated
    Hi Guys, sorry for the long absence. I have been doing some reading in the meantime but I am still unable to get this to work!

    I built a form using 'Form on a procedure' and select the procedure I want to model the form on.
    I then added a Dynamic PL/SQL region to the same page and placed the following code in the 'source' field
    BEGIN
    IF :P30_month is not null
    AND :P30_year is not null
    AND :P30_display_full_report is not null
    AND :P30_pupulate_bill_hist_tab is not null
    THEN
    database_usage_pkg.monthly_billing (
    i_month => :P30_month,
    i_year => :P30_year,
    i_display_full_report => :P30_pisplay_full_report,
    i_populate_bill_hist_tab => :P30_populate_bill_hist_tab);
    END IF;
    END;
    I then run the page, click 'submit' and I get
    == DEBUG VALUES ===
    Month: 06
    Year: 2011
    Detail Report: N
    Populate Table: N
    Interface: WEB
    === DEBUG VALUES ===

    Execution Timestamp: 07/31/2012-12:56:23



    Billing Run General Information / Settings
    ------------------------------------------
    Shared Application Bytes - 250,000,000


    Content-type: text/html; charset=utf-8


    Just like before, and then the page loads below it but with an error message and nothing displayed in the dynamic region I added
    1 error has occurred
    ORA-01722: invalid number
    but if I set the condition for 'Run Stored Procedure' under 'Page Processing' to NEVER and I click on the 'submit' button nothing happens but no error either.

    What am I doing wrong?
    Does anyone have a live example I can take a look at?

    I've got many more screens to convert from WebDB that rely on calls to procedures returning 'htp.print' output so any and all help is appreciated.

    Regards,

    --Charlie                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
  • 13. Re: Call to procedure returns result above <head> tag..
    fac586 Guru
    Currently Being Moderated
    Charlie (ME) wrote:
    Hi Guys, sorry for the long absence. I have been doing some reading in the meantime but I am still unable to get this to work!

    I built a form using 'Form on a procedure' and select the procedure I want to model the form on.
    I then added a Dynamic PL/SQL region to the same page and placed the following code in the 'source' field
    BEGIN
    IF :P30_month is not null
    AND :P30_year is not null
    AND :P30_display_full_report is not null
    AND :P30_pupulate_bill_hist_tab is not null
    THEN
    That is not what was meant by "conditional". APEX Conditions allow you to control whether the region is rendered or not.
    I then run the page, click 'submit' and I get
    == DEBUG VALUES ===
    Month: 06
    Year: 2011
    Detail Report: N
    Populate Table: N
    Interface: WEB
    === DEBUG VALUES ===

    Execution Timestamp: 07/31/2012-12:56:23



    Billing Run General Information / Settings
    ------------------------------------------
    Shared Application Bytes - 250,000,000


    Content-type: text/html; charset=utf-8
    Can you post the source code for the procedure? It appears that the procedure may generate an HTTP header + a complete HTML document. This will conflict with the generation of the APEX page.
  • 14. Re: Call to procedure returns result above <head> tag..
    405008 Newbie
    Currently Being Moderated
    Hi fac586,
    I got that example from this link...
    Re: Execute procedure on oracle apex online
    and placed it in the Dynamic PL/SQL regions 'source' field.

    I also have
        :P30_MONTH is not null
    and :P30_YEAR is not null
    and :P30_DISPLAY_FULL_REPORT is not null
    and :P30_POPULATE_BILL_HIST_TAB is not null
    and :P30_INTERFACE is not null
    in the 'conditions' field of the Dynamic PL/SQL region.




    Here is the procedure, it just does a
       PROCEDURE monthly_billing (
          i_month                    IN VARCHAR2,
          i_year                     IN VARCHAR2,
          i_display_full_report      IN CHAR,
          i_populate_bill_hist_tab   IN CHAR,
          i_interface                IN VARCHAR2 DEFAULT 'WEB')
       IS
       BEGIN
          common.set_interface (i_interface);
    
          IF common.v_display_for_web
          THEN
             common.display_line ('&lt;PRE>');
    
             IF debugging
             THEN
                common.display_line ('');
                common.display_line ('=== DEBUG VALUES ===');
                common.display_line ('Month: ' || i_month);
                common.display_line ('Year: ' || i_year);
                common.display_line ('Detail Report: ' || i_display_full_report);
                common.display_line ('Populate Table: ' || i_populate_bill_hist_tab);
                common.display_line ('Interface: ' || i_interface);
                common.display_line ('=== DEBUG VALUES ===');
             END IF;
          END IF;
    
          common.capture_time;
          common.display_runtime;
          common.display_line;
          run_application_billing (LPAD (i_month, 2, '0'),
                                   LPAD (i_year, 4, '20'),
                                   i_display_full_report,
                                   i_populate_bill_hist_tab);
          common.capture_time;
          common.display_elapsed_time;
          common.display_line;
    
          IF common.v_display_for_web
          THEN
             common.display_line ('</PRE>');
          END IF;
       END;
    As you can see it is passing character strings that get converted to numbers later so I have no clue where the invalid number error is coming from.

    Edited by: Charlie (ME) on Jul 31, 2012 4:08 PM

    Edited by: Charlie (ME) on Jul 31, 2012 4:09 PM
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points