Posts

Showing posts from September, 2020

Send an Oracle Database Table Data in Excel File as Mail Attachment using a Simple PL/SQL Excel & APEX Mail APIs

Image
    Here I am giving an example to send an oracle database table data in excel sheet as mail attachment using a simple PL/SQL & APEX Mail APIs. What is  ORA_EXCEL ? Oracle PL/SQL to Excel XSLX API. ORA_EXCEL  (Oracle Excel) is a PL/SQL package for Oracle database that produces Excel XLSX documents. ORA_EXCEL  enables developers to create Excel reports or export data from an Oracle database to Excel files using a simple PL/SQL Excel API. Step 1: Download ORA_EXCEL pkg (wrapped pkg) and Install it in your schema. Click here to Download ORA_EXCEL Step 2:  Create a new procedure as below, which should take care of following actions,  Convert table data into excel file.  Save generated excel file to PL/SQL BLOB variable type or Oracle Directory.  Send generated excel file as an attachment. CREATE OR REPLACE  PROCEDURE  table_data_to_excel_file (x_return_status out VARCHAR2,                                     x_return_message out VARCHAR2) AS   CURSOR cur_emp_data   IS     SELECT emp.em

Return a Validation Message using JavaScript if the Oracle Application Express Item is Empty

Image
Here I am giving an example to Return a custom validation message using JavaScript if the Oracle Application Express Item is Empty. Step 1:  Create a new blank page. Note:  Mine was page 15. You will need to update reference to "P15" with your page number if it's different. Step 2: Create a new HTML region to the page. Step 3:  Create a new item to the region. Set name to P15_ATTRIBUTE_1 and leave the default of Text Field Step 4:  Create a new item to the region. Set name to  P15_ATTRIBUTE_2  and leave the default of  Text Field Step 5:  Create a new button to the region. Set name to  SUBMIT  and action to Defined Dynamic Action Step 6:  Create a new dynamic action that fires when the button is clicked. The easiest way to do this is to right click the button and select  Create Dynamic Action  Set name to  Not Null Check Validation Step 7:  Set action to Execute JavaScript Code and copy and paste the following code into the JavaScript Code section. var attribute1 = $(&

Search Results with Highlighted Searched Text using PL/SQL in Oracle APEX Classical Report

Image
Here I am giving an example to how to do search results with highlighted searched text using PL/SQL in Oracle APEX classical report. Step 1:  Create a page. Step 2:  Create a region (Type: PL/SQL Dynamic Content ). Step 3:  Create a text item. Step 4:  Create a select list to define no of rows that needs to be displayed in search results. (Optional) Step 5:  Change a UI of text field item as like search item (Optional) Appearance: Template:  Hidden Icon:  fa-search Value Placeholder:  Enter country to search....  (Your own text) Advanced: CSS Classes:  t-Form-searchField Custom Attributes:  style="border-radius:20px;" Source: Type:  Null Used:  Only when current value in session state is null Step 6: Populate sample data  (if required) Step 7:  Just copy and paste below code in region (Ref Step: 2) DECLARE    c             pls_integer := 0;    l_detail      VARCHAR2(32767);    i             pls_integer;    l             pls_integer;    l_max_rows    INTEGER; BEGIN l_max_rows

Submit a Page when Enter Key Pressed on Autocomplete Field using JavaScript

Image
Here I am giving an example to submit a page when enter key pressed on text with autocomplete field in Oracle APEX. In Oracle Apex, there is an option for the Text Field item, where you can define to submit the page when enter key pressed, but for the item "Text with Autocomplete", there is no such option, we need to write dynamic action to submit a page when user presses enter key on autocomplete field. Step 1:  Create a page. Step 2:  Create a region. Step 3:  Create a item (text with autocomplete). Step 4: Change a UI of text field with autocomplete item as like search item (Optional) Appearance: Template: Hidden Icon: fa-search Value Placeholder: Enter country to search.... (Your own text) Advanced: CSS Classes:  t-Form-searchField Custom Attributes:  style="border-radius:20px;" Source: Type: Null Used: Only when current value in session state is null Step 5:  Create a dynamic action to submit a page when enter key pressed on autocomplete field as below, Event