About Oracle and Oracle ā'pěks (#orclapex)

Objective : To Scroll the top of the page/report in Oracle APEX application. Scenario : We  have encountered an issue that you always stay  on  the bottom  of  the page even after the refresh OF the report. One of my customers marked this  as  a bug and meant that after the refresh the display position should be at the beginning OF the report. Solution : Add a dynamic action: After refresh of your report execute this JavaScript statement. Step 1:  Create dynamic action Fig 1: Dynamic Action I      Fig 2: Dynamic Action II   Fig 3: J Query to scroll up the page

Objective : To stop the user to click a APEX page submit button more than once in Oracle APEX 4.2 Scenario : The standard way to push the page data back to the APEX server or table is to use an HTML button. One issue with HTML buttons is that they can be clicked more than once, and under certain circumstances this can cause issues.  Solution : To ensure that the button can only be pressed once you can do the following Step 1:  Crete APEX Page Step 2:  Crete Page Region Step 3:  Crete Page Button Alter button settings so that inside the URL redirect section the URL target value is set to javascript:this.disabled=true;doSubmit(‘SUBMIT’) Note:  The value inside doSubmit(‘’) is the same as the button name. The button now looks like this when it has been clicked Output: Conclusion:  The button is now coded so that the user can only click it once to submit the page. Any further clicks are ignored.

Objective : To create template for generating multiple sheet excel in Oracle BI Publisher 10g. Solution : Template Creation - EXCEL Step 1:  Excel contains the below template with Sheet 1 and 2 as follows Step 2:  Go to Formulas => Name Manager – See the XDO call Step 3:  Go to Sheet2 (XDO_METADATA) – See the XSLT code Step 4:  Come to Sheet1 – See how the groupings were done Note:   Ø    Workout example screenshot shared below.   Ø    Template has been attached separately with this component. Output:  Output will be multiple sheets.

Objective : To generate multiple excel sheet report from OBIP using data template SQL queries. Scenario : Customer needs multiple sheets excel report from OBIP. While developing it, developer came to know multiple data-set of SQL queries required to complete the report. Developer has to develop the report with single data-set of SQL query, Instead of using multiple data-set of SQL queries. Solution : To develop multiple sheets excel OBIP report using Data template SQL Queries.  Step 1:  Create OBIP report Step 2:   Create data template SQL Queries and place it into data model in OBIP. Step 3:  Data Template SQL Query. S tep 3:  XML data generation. S tep 3:  XML data generation. Output: (Sheet I) Output: (Sheet III)

Objective : To show dynamic help text in radio group item in Oracle APEX 4.2 using CSS. Solution: Step 1:  Create Radio group item. Step 2:  Use below query in radio group LOV Source. <<SQL Begins>> SELECT   ('<span style="cursor:help" title="'   || PRODUCT_DESCRIPTION   || '">'   || PRODUCT_NAME   || '</span>') AS SHOW_VALUE,   PRODUCT_ID RETURN_VALUE FROM   DEMO_PRODUCT_INFO_TBL ORDER BY   1 ); <<End>> Output:

Objective : To read and import XLSX file in Oracle APEX 4.2. Scenario : Customer wants to see their investor details in APEX (in the form of IR). Though investor details are in Oracle Directory (XLSX file format), But what we have done is, we read the XLSX file from Oracle directory and import data in APEX (Interactive Report).  Solution : Step 1:  Create the process in APEX to Call the PL/SQL procedure to generate excel file in oracle directory. Step 2:  Install the  AS_READ_XLSX  Package. << Package Specification <> CREATE OR REPLACE PACKAGE as_read_xlsx IS TYPE tp_one_cell IS   record   (     sheet_nr   NUMBER(2),     sheet_name VARCHAR(4000),     row_nr     NUMBER(10),     col_nr     NUMBER(10),     cell       VARCHAR2(100),     cell_type  VARCHAR2(1),     string_val VARCHAR2(4000),     number_val NUMBER,     date_val   DATE,     formula    VARCHAR2(4000)   ); TYPE tp_all_cells IS   TABLE OF t