1 2 Previous Next 28 Replies Latest reply: Aug 3, 2012 7:56 AM by Charlie (ME) Go to original post RSS
      • 15. Re: Call to procedure returns result above <head> tag..
        Charlie (ME)
        Here is the 'display_line' procedure from the 'common' package
        PROCEDURE display_line (
          i_display_line IN LONG
          ) IS
        BEGIN
          IF v_display_for_web THEN
            htp.print(i_display_line);
          ELSE
            dbms_output.put_line(i_display_line);
          END IF;
        END ;
        As you can see if :P30_interface field on the APEX form passed the value of "WEB" (which is also the default if nothing is passed) to the DATABASE_USAGE_PKG.MONTHLY_BILLING proc then the output is returned as a HTP.PRINT otherwise it is displayed using DBMS_OUTPUT.PUT_LINE

        Edited by: Charlie (ME) on Jul 31, 2012 4:10 PM
        • 16. Re: Call to procedure returns result above <head> tag..
          fac586
          And the <tt>run_application_billing</tt> code?

          Switch the debugging off, it's not helping here.
          • 17. Re: Call to procedure returns result above <head> tag..
            Charlie (ME)
            It's long so hope you can follow it ok on here
               PROCEDURE run_application_billing (i_month                    IN VARCHAR2,
                                                  i_year                     IN VARCHAR2,
                                                  i_display_full_report      IN CHAR,
                                                  i_populate_bill_hist_tab   IN CHAR)
               IS
               BEGIN
                  init_variables;
                  v_billing_month_year :=
                     TO_CHAR (LPAD (i_year, 4, '20')) || LPAD (i_month, 2, '0');
                  v_display_full_report := i_display_full_report;
                  v_populate_bill_hist_tab := i_populate_bill_hist_tab;
                  --  Purge any previous entries for reporting month
                  purge_previous_run_data;
                  --  Truncate OIT Billing table in generalt/p oracle_push
                  --      maintain_oracle_push.TRUNC@general;
                  v_total_app_storage := 0;
            
                 -------------------------------------------------------------------------------------
                 --  Loop through all of the Active applications
                 -------------------------------------------------------------------------------------
                 <<Active_Applications>>
                  FOR ac IN active_applications_cur
                  LOOP
                     -------------------------------------------------------------------------------------
                     --  Prep Work
                     -------------------------------------------------------------------------------------
                     display_billing_line (c_line_separator);
                     check_for_non_billable_display (ac.billable);
                     display_billing_line (ac.agency_title || ' - ' || ac.app_name);
                     v_state_app_id := ac.state_app_id;
            
                     --  Perform the initial insert into the Billing History Table.  This ensures
                     --  that detail data can be added without running into a foreign key constraint error.
                     IF v_populate_bill_hist_tab = 'Y'
                     THEN
                        INSERT INTO billing_history (billing_date,
                                                     billing_code,
                                                     cost_center,
                                                     agency,
                                                     agency_title,
                                                     app_code,
                                                     app_name,
                                                     billable)
                             VALUES (TO_DATE (v_billing_month_year, 'YYYYMM'),
                                     ac.billing_code,
                                     ac.cost_center,
                                     ac.agency,
                                     ac.agency_title,
                                     ac.app_code,
                                     ac.app_name,
                                     ac.billable);
                     ELSE
                        v_populate_bill_hist_tab := v_populate_bill_hist_tab;
                     END IF;
            
                     --  Init Accumulator variables
                     v_unadj_monthly_charge := 0;
                     v_unadj_db_charge := 0;
                     v_unadj_charge := 0;
                     v_total_app_storage := 0;
                     print_billing_constraints (ac.app_code);
            
                    -------------------------------------------------------------------------------------
                    --  Start the Application Billing Process
                    --  1.  Determine Base Database Charge, based upon OLTP (normal) Storage and
                    --      Concurrent Sessions.
                    -------------------------------------------------------------------------------------
                    --  Loop through all of the databases per application, determine the database charges
                    <<Application_Databases>>
                     FOR dc IN application_databases_cur (ac.app_code)
                     LOOP
                        -- Initialize variables
                        v_base_charge := 0;
                        v_base_session_charge := 0;
                        --  Get storage rate data.
                        v_avg_storage := get_avg_storage (ac.app_code, dc.db_name);
                        v_avg_concurrent_sessions :=
                           get_avg_concurrent_sessions (ac.app_code, dc.db_name);
            
                        IF dc.db_type = 'OLTP'
                        THEN
                           v_storage_type := 'STORAGE - OLTP';
                           v_session_type := 'SESSION - OLTP';
                           v_storage_type_display := 'Storage - OLTP';
                           v_session_type_display := 'Session - OLTP';
                        ELSE
                           v_storage_type := 'STORAGE - DSS';
                           v_session_type := 'SESSION - DSS';
                           v_storage_type_display := 'Storage - DSS ';
                           v_session_type_display := 'Session - DSS ';
                        END IF;
            
                        v_storage_rate := get_rate (v_storage_type, v_avg_storage);
                        v_base_charge :=
                           ROUND (v_avg_storage / c_megabyte * v_storage_rate);
                        v_total_app_storage := v_total_app_storage + v_avg_storage;
            
                        --          common.display_line;
                        --          common.display_line('v_base_charge = ' || v_base_charge || ' v_avg_storage = ' || v_avg_storage);
                        IF v_base_charge > 0
                        THEN
                           display_billing_line (
                                 'Database:  '
                              || RPAD (dc.db_name, 12)
                              || '  '
                              || v_storage_type_display
                              || '      : '
                              || common.format_number (v_avg_storage, 16, 2)
                              || '  Rate: '
                              || common.format_number (v_storage_rate, 8, 4));
                           --  Get session rate data.
                           v_session_rate :=
                              get_rate (v_session_type, v_avg_concurrent_sessions);
                           v_base_session_charge := ROUND (v_base_charge * v_session_rate);
                           --             common.display_line('v_base_session_charge = ' || v_base_session_charge);
                           display_billing_line (
                                 RPAD (dc.server, 23)
                              || '  '
                              || v_session_type_display
                              || '      : '
                              || common.format_number (v_avg_concurrent_sessions, 16, 2)
                              || '  Rate: '
                              || common.format_number (v_session_rate, 8, 4)
                              || '      DBAmt :  '
                              || common.format_number (v_base_session_charge, 8, 2));
                        END IF;
            
                        --          common.display_line('v_base_session_charge = ' || v_base_session_charge);
                        --  Get slow storage rate data.
                        v_avg_slow_storage :=
                           get_avg_slow_storage (ac.app_code, dc.db_name);
                        v_slow_storage_rate :=
                           get_rate ('SLOWSTORAGE', v_avg_slow_storage);
                        v_slow_storage_charge :=
                           ROUND (v_avg_slow_storage / c_megabyte * v_slow_storage_rate);
                        v_total_app_storage := v_total_app_storage + v_avg_slow_storage;
            
                        IF v_slow_storage_charge > 0
                        THEN
                           display_billing_line (
                                 '                       '
                              || '  Ave Slow Storage    : '
                              || common.format_number (v_avg_slow_storage, 16, 2)
                              || '  Rate: '
                              || common.format_number (v_slow_storage_rate, 8, 4)
                              || '  SlowStAmt :  '
                              || common.format_number (v_slow_storage_charge, 8, 2));
                        END IF;
            
                        --  Determine DB Charge
                        v_unadj_db_charge := v_base_session_charge + v_slow_storage_charge;
                        display_billing_line (
                           '                                                                           '
                           || '   Unadj Database Charge : $'
                           || common.format_number (v_unadj_db_charge, 8, 2));
                        display_billing_line;                             -- For asthetics
                        --  Add DB charge to total monthly charge
                        v_unadj_charge := v_unadj_db_charge;
                        v_unadj_monthly_charge := v_unadj_monthly_charge + v_unadj_charge;
            
                        --  Update the Billing History Detail Table
                        IF v_populate_bill_hist_tab = 'Y'
                        THEN
                           INSERT
                             INTO billing_history_detail (billing_date,
                                                          app_code,
                                                          db_name,
                                                          db_type,
                                                          sessions_ave,
                                                          sessions_rate,
                                                          storage_ave,
                                                          storage_rate,
                                                          slow_storage_ave,
                                                          slow_storage_rate,
                                                          bcdr_storage_ave,
                                                          bcdr_level_rate,
                                                          app_server_only_description,
                                                          app_server_only_rate,
                                                          unadj_other_charge,
                                                          unadj_db_charge,
                                                          unadj_charge)
                           VALUES (TO_DATE (v_billing_month_year, 'YYYYMM'),
                                   ac.app_code,
                                   dc.db_name,
                                   dc.db_type,
                                   v_avg_concurrent_sessions,
                                   v_session_rate,
                                   v_avg_storage,
                                   v_storage_rate,
                                   v_avg_slow_storage,
                                   v_slow_storage_rate,
                                   NULL,
                                   NULL,
                                   NULL,
                                   NULL,
                                   0,
                                   v_unadj_db_charge,
                                   v_unadj_charge);
                        ELSE
                           v_populate_bill_hist_tab := v_populate_bill_hist_tab;
                        END IF;
                     END LOOP Application_Databases;
            
                     -------------------------------------------------------------------------------------
                     --  2.  Determine any Application Server Usage Fees
                     -------------------------------------------------------------------------------------
                     display_billing_line;                                -- For asthetics
                     v_other_charge := 0;
                     v_unadj_other_charge := 0;
                     v_app_server_use_count := 0;
            
            
                     FOR abcc IN app_bill_con_cur (ac.app_code, 'APPSERVER')
                     LOOP
                        v_app_server_use_count := v_app_server_use_count + 1;
                     END LOOP;
            
                     IF v_app_server_use_count > 0
                     THEN
                        v_app_server_use_rate :=
                           get_rate ('APPSERVER', v_app_server_use_count);
                     ELSE
                        v_app_server_use_rate := 0;
                     END IF;
            
                     v_app_server_use_charge :=
                        v_app_server_use_count * v_app_server_use_rate;
                     --  Determine App Server Charge
                     v_unadj_other_charge := v_app_server_use_charge;
                     --  Add App Server charge to total monthly charge
                     v_other_charge := v_other_charge + v_app_server_use_charge;
                     v_unadj_monthly_charge :=
                        v_unadj_monthly_charge + v_app_server_use_charge;
                     v_unadj_charge := v_app_server_use_charge;
            
                     --  Only add detail line to history table if there is an actual charge
                     IF v_app_server_use_count > 0
                     THEN
                        display_billing_line (
                              'Other Charges:         '
                           || '  App Server Usage    : '
                           || common.format_number (v_app_server_use_count, 16, 2)
                           || '  Rate: '
                           || common.format_number (v_app_server_use_rate, 8, 4)
                           || '  AppSrvAmt :  '
                           || common.format_number (v_app_server_use_charge, 8, 2));
            
                        SELECT description
                          INTO v_app_server_only_desc
                          FROM billing_rates
                         WHERE rate_type = 'APPSERVER' AND rate_level = 1;
            
                        --  Update the Billing History Detail Table
                        IF v_populate_bill_hist_tab = 'Y'
                        THEN
                           INSERT
                             INTO billing_history_detail (billing_date,
                                                          app_code,
                                                          db_name,
                                                          sessions_ave,
                                                          sessions_rate,
                                                          storage_ave,
                                                          storage_rate,
                                                          slow_storage_ave,
                                                          slow_storage_rate,
                                                          bcdr_storage_ave,
                                                          bcdr_level_rate,
                                                          app_server_only_description,
                                                          app_server_only_rate,
                                                          unadj_other_charge,
                                                          unadj_db_charge,
                                                          unadj_charge)
                           VALUES (TO_DATE (v_billing_month_year, 'YYYYMM'),
                                   ac.app_code,
                                   'APP-SERVER',
                                   NULL,
                                   NULL,
                                   '123',
                                   NULL,
                                   NULL,
                                   NULL,
                                   NULL,
                                   NULL,
                                   v_app_server_only_desc,
                                   v_app_server_use_rate,
                                   v_unadj_other_charge,
                                   0,
                                   v_unadj_charge);
                        ELSE
                           v_populate_bill_hist_tab := v_populate_bill_hist_tab;
                        END IF;
                     END IF;
            
                     -------------------------------------------------------------------------------------
                     --  3.  Determine any BCDR Offline Usage Fees
                     -------------------------------------------------------------------------------------
                     v_unadj_other_charge := 0;
                     v_avg_bcdr_storage := get_avg_bcdr_storage (ac.app_code);
                     --      v_avg_bcdr_storage := 1048576000;
                     v_bcdr_storage_rate := get_rate ('BCDROFFLINE', v_avg_bcdr_storage);
                     v_bcdr_storage_charge :=
                        ROUND (v_avg_bcdr_storage / c_megabyte * v_bcdr_storage_rate);
                     v_unadj_other_charge := v_bcdr_storage_charge;
                     --  Add BCDR Storage charge to total monthly charge
                     v_unadj_charge := v_unadj_other_charge;
                     v_unadj_monthly_charge := v_unadj_monthly_charge + v_unadj_charge;
                     v_other_charge := v_other_charge + v_unadj_other_charge;
                     display_billing_line (
                           '                       '
                        || '  Ave BCDR Offline Stg: '
                        || common.format_number (v_avg_bcdr_storage, 16, 2)
                        || '  Rate: '
                        || common.format_number (v_bcdr_storage_rate, 8, 4)
                        || '  BCDROffAmt: '
                        || common.format_number (v_bcdr_storage_charge, 8, 2));
            
                     --  Only add detail line to history table if there is an actual charge
                     IF v_avg_bcdr_storage > 0
                     THEN
                        --  Update the Billing History Detail Table
                        IF v_populate_bill_hist_tab = 'Y'
                        THEN
                           INSERT
                             INTO billing_history_detail (billing_date,
                                                          app_code,
                                                          db_name,
                                                          sessions_ave,
                                                          sessions_rate,
                                                          storage_ave,
                                                          storage_rate,
                                                          slow_storage_ave,
                                                          slow_storage_rate,
                                                          bcdr_storage_ave,
                                                          bcdr_level_rate,
                                                          app_server_only_description,
                                                          app_server_only_rate,
                                                          unadj_other_charge,
                                                          unadj_db_charge,
                                                          unadj_charge)
                           VALUES (TO_DATE (v_billing_month_year, 'YYYYMM'),
                                   ac.app_code,
                                   'BCDR-OFFLINE',
                                   NULL,
                                   NULL,
                                   NULL,
                                   NULL,
                                   NULL,
                                   NULL,
                                   v_avg_bcdr_storage,
                                   v_bcdr_storage_rate,
                                   NULL,
                                   NULL,
                                   v_unadj_other_charge,
                                   0,
                                   v_unadj_charge);
                        ELSE
                           v_populate_bill_hist_tab := v_populate_bill_hist_tab;
                        END IF;
                     END IF;
            
                     -------------------------------------------------------------------------------------
                     --  4.  Determine totals, print them out, etc.
                     --      Finish calculations for the application
                     -------------------------------------------------------------------------------------
                     --  Display summary for unadj other total if not zero
                     IF v_other_charge > 0
                     THEN
                        display_billing_line (
                           '                                                                           '
                           || '     Unadj Other Charges : $'
                           || common.format_number (v_other_charge, 8, 2));
                        display_billing_line;
                     END IF;
            
                     v_discount := ROUND (v_unadj_monthly_charge * (ac.discount / 100), 0);
                     v_total_amount_discounted := v_total_amount_discounted + v_discount;
                     --  Apply discount
                     v_monthly_charge := v_unadj_monthly_charge - v_discount;
                     display_billing_line (
                           'UnAdj Monthly Charge : $'
                        || common.format_number (v_unadj_monthly_charge, 8, 2)
                        || '  Disc Amount: $'
                        || common.format_number (v_discount, 8, 2)
                        || '  Disc %: '
                        || common.format_number (ac.discount, 6, 2));
            
                     IF ac.billable = 'Y'
                     THEN
                        display_billing_line (
                           'Final Monthly Charge : $'
                           || common.format_number (v_monthly_charge, 8, 2));
                     ELSE
                        display_billing_line (
                           'NoBll Monthly Charge : $'
                           || common.format_number (v_monthly_charge, 8, 2));
                     END IF;
            
                     --  Print the Billing Line-Item
                     IF ac.billable = 'Y'
                     THEN
                        display_billing_line;
            
                        IF ac.cost_center IS NULL
                        THEN
                           v_cost_center := '';
                        ELSE
                           v_cost_center := ac.cost_center || ' ';
                        END IF;
            
                        common.display_line (
                           RPAD (ac.billing_code, 11) || '&lt;font color=red>'
                           || common.
                               format_number ( (v_total_app_storage / c_megabyte), 15, 2)
                           || '&lt;/font>'
                           || ' '
                           || RPAD (c_billing_code, 8)
                           || '   $ '
                           || TO_CHAR (v_monthly_charge, '9,999,999.99')
                           || '   '
                           || RPAD (
                                    v_cost_center
                                 || c_billing_text
                                 || RPAD (ac.app_name, 31),
                                 50)
                           || '   &lt;font color=red>'
                           || v_state_app_id
                           || '&lt;/font>');
                        v_total_amount_billed := v_total_amount_billed + v_monthly_charge;
                     ELSE
                        v_total_amount_notbilled :=
                           v_total_amount_notbilled + v_monthly_charge;
                     END IF;
            
                     display_billing_line;
            
                     --  Update the Billing History Table monthly information
                     IF v_populate_bill_hist_tab = 'Y'
                     THEN
                        UPDATE billing_history
                           SET unadj_monthly_charge = v_unadj_monthly_charge,
                               discount = ac.discount,
                               discount_amount = v_discount,
                               final_monthly_charge = v_monthly_charge
                         WHERE billing_date = TO_DATE (v_billing_month_year, 'YYYYMM')
                               AND app_code = ac.app_code;
            
                        COMMIT;
                     ELSE
                        v_populate_bill_hist_tab := v_populate_bill_hist_tab;
                     END IF;
            
                     -------------------------------------------------------------------------------------
                     --  Wrap-up
                     -------------------------------------------------------------------------------------
                     check_for_non_billable_display (ac.billable);
                     display_billing_line;
                  END LOOP Active_Applications;
            
                  display_billing_report_summary (i_month,
                                                  i_year,
                                                  i_populate_bill_hist_tab);
               END;
            Edited by: Charlie (ME) on Jul 31, 2012 4:03 PM
            • 18. Re: Call to procedure returns result above <head> tag..
              fac586
              Seem to be pursuing an ever receding target here...what about <tt>init_variables</tt>?

              Can I also ask that you edit your posts and post code using <tt>\
              ...\
              </tt> tags as described in the FAQ rather than the <tt>&lt;pre&gt;</tt> element you used, just to ensure that the forum software isn't mangling anything?
              • 19. Re: Call to procedure returns result above <head> tag..
                Charlie (ME)
                   PROCEDURE init_variables
                   IS
                   BEGIN
                      SELECT shared_application_tablespace,
                             shared_application_bytes,
                             check_storage_time_of_day_hh24
                        INTO v_shared_app_db_tsname,
                             v_shared_app_db_bytes,
                             v_check_storage_time_of_day_24
                        FROM boe_billing_config
                       WHERE code = 'PROD';
                
                      v_total_amount_billed := 0;
                      v_total_amount_notbilled := 0;
                      v_total_amount_discounted := 0;
                      common.display_line;
                      common.display_line ('Billing Run General Information / Settings');
                      common.display_line ('------------------------------------------');
                      common.display_line (
                         '  Shared Application Bytes - '
                         || common.format_number (v_shared_app_db_bytes, 12, 0));
                      common.display_line;
                      common.display_line;
                   END;
                You really think it is something deep in the proc's?
                If I run this outside of APEX it returns the complete report as a text file wrapped in &lt;PRE> tags.

                I really appreciate the time you are giving this FAC! Thanks
                • 20. Re: Call to procedure returns result above <head> tag..
                  fac586
                  Does <tt>common.set_interface</tt> do anything other than setting the value of <tt>v_display_for_web</tt>?

                  Run the page in Debug Mode and use the trace to determine exactly where the invalid number error occurs.

                  Can you call <tt>run_application_billing</tt> directly rather than through the <tt>monthly_billing</tt> wrapper procedure? It looks like your "framework" code is causing the issues: it wasn't designed to run within another framework&mdash;APEX.
                  • 21. Re: Call to procedure returns result above <head> tag..
                    Charlie (ME)
                    Here is the error section of the debug output
                    0.13640 0.00088 Processes - point: AFTER_SUBMIT 4  
                     0.13728 0.00304 ...Process "Run Stored Procedure" - Type: PLSQL 4  
                     0.14032 0.01939 ...Execute Statement: begin BISDBA.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); end; 
                    0.15971 0.00164 Add error onto error stack 4  
                     0.16135 0.00147 ...Error data: 4  
                     0.16282 0.00149 ......message: ORA-01722: invalid number 4  
                     0.16431 0.00139 ......additional_info: ORA-01722: invalid number 4  
                     0.16570 0.00138 ......display_location: INLINE_IN_NOTIFICATION 4  
                     0.16708 0.00134 ......is_internal_error: false 4  
                     0.16842 0.00141 ......ora_sqlcode: -1722 4  
                    
                     0.16983 0.00135 ......ora_sqlerrm: ORA-01722: invalid number 4  
                     0.17118 0.00141 ......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_PROCESS", line 128  4  
                     0.17259 0.00152 ......component.type: APEX_APPLICATION_PAGE_PROCESS 4  
                     0.17411 0.00143 ......component.id: 49699423483819971 4  
                     0.17554 0.00246 ......component.name: Run Stored Procedure
                    And yes common.set_interface does just that
                    PROCEDURE set_interface(
                       i_interface IN VARCHAR2
                      ) IS
                    BEGIN
                      IF i_interface = 'WEB' THEN
                        v_display_for_web := TRUE;
                      ELSE
                        dbms_output.enable(1000000);
                        v_display_for_web := FALSE;
                      END IF;
                    END ;
                    I was told not to modify the packages; management expected the UI to be a direct transplant and it seems to be proving otherwise.



                    ::EDIT

                    Could this be due to the exstensive use boolean parameters throuough the code? Just a thought.

                    Edited by: Charlie (ME) on Aug 2, 2012 9:40 AM
                    • 22. Re: Call to procedure returns result above <head> tag..
                      fac586
                      >
                      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
                      Here is the error section of the debug output
                      0.13640 0.00088 Processes - point: AFTER_SUBMIT 4  
                      0.13728 0.00304 ...Process "Run Stored Procedure" - Type: PLSQL 4  
                      0.14032 0.01939 ...Execute Statement: begin BISDBA.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); end; 
                      Are you running <tt>BISDBA.DATABASE_USAGE_PKG.MONTHLY_BILLING</tt> as both a post-submit page process ('Form on a procedure') and as a Dynamic PL/SQL region on page show?

                      If so that would explain the weird stuff appearing before the page proper...

                      Try disabling the "Run Stored Procedure" After-Submit process&mdash;and any reset/clear cache processes or branch directives that clear the <tt>P30_MONTH</tt> etc parameter items&mdash;so that <tt>BISDBA.DATABASE_USAGE_PKG.MONTHLY_BILLING</tt> is only called from the Dynamic PL/SQL region.
                      I was told not to modify the packages; management expected the UI to be a direct transplant and it seems to be proving otherwise.
                      Quite likely. If you've got an existing "framework" that produces entire HTML pages (i.e. with <tt>html</tt>, <tt>head</tt> and <tt>body</tt> elements); generates HTTP headers; or lacks complete separation of processing and display concerns then you should expect problems.

                      However, although I still think the various "framework" aspects of your packages will cause rendering problems when used from APEX, the fact this modular framework exists should make it possible to minimize the changes required. Start by replacing the debug code with calls to the <tt>APEX_DEBUG_MESSAGE</tt> API, which will provide debug information without disrupting page rendering.
                      Could this be due to the exstensive use boolean parameters throuough the code? Just a thought.
                      No.
                      • 23. Re: Call to procedure returns result above <head> tag..
                        Charlie (ME)
                        Made the suggested change and called "run_application_billing" directly - I had to add the following line however to set the BOOLEAN for htp.print output to TRUE
                        v_display_for_web := TRUE;
                        and this is the error:
                        0.10850 0.00162 Processes - point: AFTER_SUBMIT 4  
                         0.11012 0.00266 ...Process "Run Stored Procedure" - Type: PLSQL 4  
                         0.11278 0.01857 ...Execute Statement: begin BISDBA.DATABASE_USAGE_PKG.run_application_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); end; 4  
                         0.13137 0.00179 Add error onto error stack 4  
                         0.13313 0.00141 ...Error data: 4  
                         0.13454 0.00135 ......message: ORA-01722: invalid number 4  
                         0.13592 0.00133 ......additional_info: ORA-01722: invalid number 4  
                         0.13722 0.00136 ......display_location: INLINE_IN_NOTIFICATION 4  
                         0.13858 0.00131 ......is_internal_error: false 4  
                         0.13992 0.00136 ......ora_sqlcode: -1722 4  
                         0.14125 0.00137 ......ora_sqlerrm: ORA-01722: invalid number 4  
                         0.14262 0.00138 ......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_PROCESS", line 128  4  
                         0.14400 0.00154 ......component.type: APEX_APPLICATION_PAGE_PROCESS 4  
                         0.14554 0.00131 ......component.id: 53139226781811289 4  
                         0.14684 0.00312 ......component.name: Run Stored Procedure 4  
                         0.14997 0.00123 Show current page with inline errors
                        • 24. Re: Call to procedure returns result above <head> tag..
                          Charlie (ME)
                          Ok we had crossed message posts; in response to your last post...

                          I have set conditions to "NEVER" (disabled) on the page branching and run stored procedure commands under Page processing

                          In the source field of the Dynamic PL/SQL region I have
                          bisdba.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);
                          and in the conditions field I 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
                          Now when I submit I get the following error
                          Error during rendering of region "Billing Report".
                          
                          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: Billing Report
                          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
                          fac586 wrote:Are you running <tt>BISDBA.DATABASE_USAGE_PKG.MONTHLY_BILLING</tt> as both a post-submit page process ('Form on a procedure') and as a Dynamic PL/SQL region on page show?
                          I built the page (P30) using the 'Form on a Stored Procedure' wizard when creating the new page, I then added the Dynamic PL/SQL region after and named this "Billing Report" ...this is where I want the returned data to be rendered.

                          ::EDIT
                          No HTML tags are produced in the resulting output from the procedures other than the &lt;PRE> tag, the rest of the return set is simple text.



                          When run outside of APEX this is what the proc's produce for output
                          <PRE>
                          Execution Timestamp: 08/02/2012-12:26:27
                          
                          
                          Billing Run General Information / Settings
                          
                          Shared Application Bytes - 250,000,000
                          
                          
                          18B29CAR 1,786.84 MEGABYTE BIS ORA ENV - ARCHIVES DEATH / MARRIAGE DB
                          ...I've truncated the report content...
                          18B10AHW 198,604.83 MEGABYTE BIS ORA ENV - DHHS DATA HUB2
                          </PRE>
                          Edited by: Charlie (ME) on Aug 2, 2012 12:26 PM
                          • 25. Re: Call to procedure returns result above <head> tag..
                            Charlie (ME)
                            fac586,
                            I just want to thank you for your time and assistance. I was finally able to get it to work and as you pointed out the issue was in the procedure(s), not APEX.
                            I've not got the full thing to work yet but I am now able to get a return from the procedures to render in the correct location.

                            regards,
                            Charlie
                            • 26. Re: Call to procedure returns result above <head> tag..
                              fac586
                              Charlie (ME) wrote:

                              I have set conditions to "NEVER" (disabled) on the page branching and run stored procedure commands under Page processing
                              So we've got rid of the junk HTML output?
                              Now when I submit I get the following error
                              Error during rendering of region "Billing Report".
                              
                              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: Billing Report
                              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 built the page (P30) using the 'Form on a Stored Procedure' wizard when creating the new page, I then added the Dynamic PL/SQL region after and named this "Billing Report" ...this is where I want the returned data to be rendered.

                              ::EDIT
                              No HTML tags are produced in the resulting output from the procedures other than the &lt;PRE&gt; tag, the rest of the return set is simple text.
                              Which suggests that it's not getting much further. It looks strange that <tt>MONTHLY_BILLING</tt> isn't appearing in the error trace...

                              At this point you just apply standard debugging techniques to isolate where the problem is occurring. These can be more or less sophisticated: remote debugging with SQL Developer if you're permitted to set it up in your environment; otherwise just commenting stuff out and adding APEX debug messages.

                              One thing I don't like the look of is the <tt>LONG</tt> parameter to <tt>display_line</tt>. Very 20th century, very obsolete, and it doesn't provide any benefit there over a <tt>VARCHAR2</tt>.
                              • 27. Re: Call to procedure returns result above <head> tag..
                                fac586
                                Charlie (ME) wrote:
                                fac586,
                                I just want to thank you for your time and assistance. I was finally able to get it to work and as you pointed out the issue was in the procedure(s), not APEX.
                                I've not got the full thing to work yet but I am now able to get a return from the procedures to render in the correct location.
                                Posts crossed again. Glad to hear it. To satisfy my curiosity can you provide a bit more detail on the nature of the problem?
                                • 28. Re: Call to procedure returns result above <head> tag..
                                  Charlie (ME)
                                  I agree about the 'LONG' being antiquated, a lot of the code is, as it was first written in the late 90's to early turn of the century; anyway I have not got the full report showing yet, I just disabled the call to the RUN_MONTHLY_BILLING proc and the rest came up so I know that there is an error somehow in there, though none of that code should have been touched so I don't know why it would be producing an error now unless inadvertently modified by someone.

                                  And to be totally honest, I think the real issue resides with me...I have never used WebDB or APEX before this project and there are no knowledge resources on site beyond the three APRESS books on APEX that I have here, so your patience and perseverance proved invaluable to troubleshooting this issue.

                                  -- Charlie
                                  1 2 Previous Next