Friday, July 31, 2020

Redirect to another page using a button in Interactive Grid

function( options )
{
   var $ = apex.jQuery,
       toolbarData = $.apex.interactiveGrid.copyDefaultToolbar(), // Make a copy of the default toolbar
       actionsMenuGroup = toolbarData.toolbarFind( "actions1" );  // Locate the actions menu group

   // Array position denotes displayed position in the toolbar, so let's add the new download button directly
   // after the actions menu group in the array, such that it displays directly after the actions menu in the
   // toolbar.
   // Note: The toolbar is action-driven, so integrates easily with the Interactive Grid. To show the dialog, we
   // just define the appropriate action for showing the download dialog (show-download-dialog).
   actionsMenuGroup.controls.push( {
       type: "BUTTON",
       action: "show-download-dialog",
       iconBeforeLabel: true
   } );
   actionsMenuGroup.controls.push( {
       type: "BUTTON",
       action: "show-sort-dialog",
       iconBeforeLabel: true
   } );
 
 toolbarGroup = toolbarData[toolbarData.length - 1]; // this is the last group with reset button
   // add our own button
    toolbarGroup.controls.push( {
        type: "BUTTON",
        action: "my-action",
         hot:true 
    });
options.initActions = function( actions ) {
        // can modify state of existing actions or add your own
        // can also pass in an array of actions to add
        actions.add( {
            name: "my-action",
            label: "New",
            action: function(event, focusElement) {
         apex.navigation.redirect( "f?p=" + $v( "pFlowId" ) + ":6:" + $v( "pInstance" ) );

            }
        } );
    }
   // Assign new toolbar data back to toolbarData configuration property
   options.toolbarData = toolbarData;
 
   // Return the options
   return options;  
   
}



--------------------------

function(config) {  
    let $ = apex.jQuery,  
        toolbarData = $.apex.interactiveGrid.copyDefaultToolbar(), // copy the whole toolbar  
        toolbarGroup = toolbarData.toolbarFind("actions2"); // this is the group with the save button  
          
        ///  
       config.initActions = function( actions ) {  
        // Add new actions, either singularly passing in an actions object as shown here, or in  
        // multiple by passing an array of action objects  
        actions.add( {  
            name: "my-action",  
            action: function( event, focusElement ) {  
          //redirect to page in this application  set your URL accordingly  
            apex.navigation.redirect ( "f?p=&APP_ID.:1:&SESSION.::&DEBUG.:RP::" );  
                /***In case the Url above  is giving you some issues use this one***/  
//            apex.navigation.redirect( "f?p=" + $v( "pFlowId" ) + ":1:" + $v( "pInstance" ) );  
        } );  
    };     
    // add a new "redirect" button  
    toolbarGroup.controls.push({type: "BUTTON",  
                                iconBeforeLabel: true,  
                                hot: true,  
                                label: 'redirect',  
                                action: "my-action"  
                               });  
  
    //store the config  
    config.toolbarData = toolbarData;  
    return config;  
}  

How to split column data into rows in Oracle

SELECT REGEXP_SUBSTR (:P2_SELECTED_NODES,
                          '[^:]+',
                          1,
                          ROWNUM)
              ID
      FROM DUAL
CONNECT BY LEVEL <= LENGTH (REGEXP_REPLACE (:P2_SELECTED_NODES, '[^:]+')) + 1;

Input: 1:2:3:4
Output: 
1
2
3
4

Thursday, July 30, 2020

Files Upload in Oracle APEX

Item -->  :P9_FILE_BROWSE  (File Browse)

-- Validation (V_FileFormat) --> Type: PL/SQL Function (returning error text)
DECLARE
   v_file   VARCHAR2 (1000);
BEGIN
   v_file := UPPER (TRIM ( :P9_FILE_BROWSE));

   IF LOWER (SUBSTR (v_file, INSTR (v_file, '.', -4) + 1)) IN ('jpeg',
                                                               'jpg',
                                                               'png',
                                                               'bmp',
                                                               'xls',
                                                               'xlsx',
                                                               'pdf')
   THEN
      NULL;
   ELSE
      RETURN 'Please upload jpeg/jpg/png/xls/xlsx/pdf format files only.';
   END IF;
END;

Buttons with icons in Oracle APEX

SAVE : <span aria-hidden="true" class="fa fa-save">&nbsp;Save</span>
CANCEL: <span aria-hidden="true" class="fa fa-times">&nbsp;Cancel</span>

Exclude columns while downloading the Interactive report data in Oracle APEX


SELECT T.CITYID,
         T.CITYID AS CITYID_DISPLAY,
         T.CITYNAME,
         COUNTRYID,
         (SELECT C.COUNTRYNAME
            FROM COUNTRY C
           WHERE C.COUNTRYID = T.COUNTRYID)
            AS COUNTRYNAME,
         'Delete' Del
    FROM CITIES T
ORDER BY UPPER (T.CITYNAME) ASC

To exclude CITYID while export/download from Interactive Report:

Select the column -->Server-Side Condition -->  Type (PL/SQL Expression) --> Paste below script

nvl(:request, 'EMPTY') not in ('CSV','XLS','PDF','RTF','HTMLD')
and nvl(wwv_flow.g_widget_action, 'EMPTY') <> 'SEND_EMAIL'

Monday, July 27, 2020

Client Side Error Messages in Oracle APEX

SAVE (button) --> Page Message (Dynamic Action) --> On Click (Event) --> TRUE event

/* General page error message */
apex.message.showErrors([
{
type: "error",
location: "page",
message: "Page error has occurred!",
unsafe: false
}
]);

/* Item error message */
apex.message.showErrors([
{
type: "error",
location: [ "page", "inline" ],
pageItem: "P7_CITYNAME",
message: "Value is required!",
unsafe: false
}
]);

/* Multi Item error message */
apex.message.clearErrors();
var chkErr = 0;
var arr = [
   'P7_CITYNAME',
   'P7_COUNTRYNAME'
];

for (var i in arr) {
  if ($v(arr[i]).length == 0) {
    apex.message.showErrors([
      {
        type: apex.message.TYPE.ERROR,
        location: ["inline"],
        pageItem: arr[i],
        message: "Value is required!",
        unsafe: false
      }
    ]);
    chkErr = 1;
  }
}

if ( chkErr == 0 ) {
  /* Custom dynamic action call when no error occurred */
  apex.event.trigger(document, 'customDA', [{customAttribute:'1'}]);
}

Saturday, July 25, 2020

Display Validations using Dynamic Actions in Oracle APEX

Button --> Dynamic Actions --> Event: Click --> True --> Execute Javascript Code

NOT NULL Check
*****************************************

if ($v('P7_CITYNAME')=== undefined || $v('P7_CITYNAME')== null || $v('P7_CITYNAME').length <= 0)
 {
    apex.message.alert('Please Enter City Name.');
}
if ($v('P7_COUNTRYNAME')=== undefined || $v('P7_COUNTRYNAME')== null || $v('P7_COUNTRYNAME').length <= 0)
 {
    apex.message.alert('Please Enter Country Name.');
}

or


if ($v('P7_CITYNAME') == "")
{
alert("Please Enter City Name.");
}
if ($v('P7_COUNTRYNAME') == "")
{
alert("Please Enter Country Name.");
}

Duplicate check
*************************************************
Execute PL/SQL Code

DECLARE
   v_dup   NUMBER (1) := 0;
BEGIN
   SELECT COUNT (*)
     INTO v_dup
     FROM CITIES
    WHERE CITYNAME = :P7_CITYNAME;

   IF v_dup > 0
   THEN
      :P7_CITYDUP := 'Y';
   ELSE
      :P7_CITYDUP := 'N';
   END IF;
END;


Items to Submit: P7_CITYNAME
Items to Return: P7_CITYNAME

Execute Javascript Code

var nameExists = $v('P7_CITYDUP') === 'Y';
var cityNull = $v('P7_CITYNAME') === '';

if (nameExists) {
  if (!cityNull) {
  alert('This City Name already exists. Please enter a different City Name');
}
}

Custom CSV Download button for Interactive Report


 While creating a button, 
 specify the option "Page in this application". 
 The page number should be same as the one where the Interactive report is created.
 In the Request text field, mention the "CSV" option. 
 
 That is all you need.

Delete Row Button for Interactive Report in Oracle Apex


1. Add One More Dummy Column to Interactive Report (City Details)

SELECT T.CITYID,
       T.CITYID AS CITYID_DISPLAY,
       T.CITYNAME,
       (SELECT C.COUNTRYNAME
          FROM COUNTRY C
         WHERE C.COUNTRYID = T.COUNTRYID)
          AS COUNTRYID,
       'Delete' Del
  FROM CITIES T
 
2. Set the Following Properties for the DEL Column

Type: Link
Heading: Delete
Target > Type: URL
URL: javascript:void(null);
Link Text: <span class="t-Icon fa fa-trash delete-irrow" aria-hidden="true"></span>
Link Attributes: data-id=#CITYID#

3. Create a Page Item to Hold the Primary Key Column Value

Now create a hidden page item to hold the primary key column P2_CITYID_H value. Do the right-click on the interactive report region and select Create Page Item option and set the following properties:

Name: P2_CITYID_H (set the name according to your page)
Type: Hidden
Value Protected: No

4. Create a Dynamic Action for the Interactive Report’s Delete Row Button

In Oracle Apex page designer, click on the Dynamic Actions Tab and do the right-click on the Click node and select Create Dynamic Action option and set the following properties:

Name: DA_DELETEROW
Event: Click
Selection Type: jQuery Selector
jQuery Selector: .delete-irrow
Event Scope: Dynamic

5. Create 4 True Actions for the Above Dynamic Action DA_DELETEROW

1st True action Confirm.
---------------------------------------
Do the right-click on the Dynamic Action DA_DELETEROW and select Create True Action option and set the following properties:
Action: Confirm
Text: Are you sure to delete this customer?

2nd True Action Set Value.
---------------------------------------
Create another True action Set Value below the Confirm action and set the following properties:
Action: Set Value
Set Type: JavaScript Expression
JavaScript Expression: $(this.triggeringElement).parent().data('id')
Selection Type: Item(s)
Item(s): P2_CITYID_H

3rd True Action Execute PL/SQL Code.
---------------------------------------
Create another True action Execute PL/SQL Code below the Set Value action and set the following properties:
Action: Execute PL/SQL Code
PL/SQL Code: Delete from CITIES where CITYID = :P2_CITYID_H;
Items to Submit: P2_CITYID_H

4th True Action Refresh.
--------------------------------
Create the last True action Refresh below the Execute PL/SQL Code action and set the following properties:
Action: Refresh
Selection Type: Region
Region: City Details (this is the interactive report region on my page)


The task is complete now, you have created the delete row button for the interactive report. Save the changes and run the page to test.


How to set the column width of Interactive reports in Oracle Apex


Region(Type Interactive Report) --> Report Attributes --> Column --> Static ID (Ex:scityid_display)


Page Inline:

th#scityid_display,
td[headers="scityid_display"]
{
   width: 150px;
}

th#scityname,
td[headers="scityname"]
{
   width: 150px;
}

th#scityid,
td[headers="scityid"]
{
   width: 150px;
}

th#scountryid,
td[headers="scountryid"]
{
   width: 150px;
}

Friday, July 24, 2020

Configuring Oracle Application Express to Send Email

Manage Instance --> Instance Settings --> Email

Application Express Instance URL : https://apex.oracle.com/pls/apex/
Application Express Images URL: https://apex.oracle.com/i/
SMTP Host Address :localhost
SMTP Host Port : 25
SMTP Authentication Username : ADMIN
SMTP Authentication Password : abcdefghijk
Use SSL/TLS : No
Default Email From Address :
Maximum Emails per Workspace : 1000

Customizing Interactive Grid Action Menu



add below script in  js path: Interactive Grid (Region Type) --> Attributes --> JavaScript Initialization Code

function(config)
{
    config.initActions = function( actions )
{
actions.remove("show-columns-dialog"); // Hides Columns
        actions.remove("show-filter-dialog"); // Hides Filter


//actions.hide("show-sort-dialog"); // Hides Sort inside "Data"
actions.hide("show-aggregate-dialog"); // Hides Aggregate inside "Data"
actions.hide("refresh"); // Hides Refresh inside "Data"

actions.hide("chart-view"); // Hides Chart.

actions.hide("show-save-report-as-dialog"); // Hides save as inside "Report"
actions.hide("show-edit-report-dialog"); // Hides edit inside "Report"

actions.remove("show-help-dialog"); // Hides Help
    }

config.features.flashback = false; // Hides Refresh inside "Flashback"

   var $ = apex.jQuery;
   var toolbarData = $.apex.interactiveGrid.copyDefaultToolbar();
   config.toolbarData = toolbarData;
   toolbarData[3].controls[0].menu.items[4]['hide'] = true; // Hides Format
   toolbarData[3].controls[0].menu.items[5]['hide'] = true; // Hides Selection
   toolbarData[3].controls[0].menu.items[8]['hide'] = true; // Hides Report

   return config;
}



--- to move options (Download and Sort) to right side of title bar
Region --> Attributes --> JavaScript Initialization Code

function( options )
{
   var $ = apex.jQuery,
       toolbarData = $.apex.interactiveGrid.copyDefaultToolbar(), // Make a copy of the default toolbar
       actionsMenuGroup = toolbarData.toolbarFind( "actions1" );  // Locate the actions menu group

   // Array position denotes displayed position in the toolbar, so let's add the new download button directly
   // after the actions menu group in the array, such that it displays directly after the actions menu in the
   // toolbar.
   // Note: The toolbar is action-driven, so integrates easily with the Interactive Grid. To show the dialog, we
   // just define the appropriate action for showing the download dialog (show-download-dialog).
   actionsMenuGroup.controls.push( {
       type: "BUTTON",
       action: "show-download-dialog",
       iconBeforeLabel: true
   } );
   actionsMenuGroup.controls.push( {
       type: "BUTTON",
       action: "show-sort-dialog",
       iconBeforeLabel: true
   } );
 
   // Assign new toolbar data back to toolbarData configuration property
   options.toolbarData = toolbarData;
 
   // Return the options
   return options;
}


-- adding hello button to beside Reset button

function(config) {
    var $ = apex.jQuery,
        toolbarData = $.apex.interactiveGrid.copyDefaultToolbar(),
        toolbarGroup = toolbarData[toolbarData.length - 1]; // this is the last group with reset button

    // add our own button
    toolbarGroup.controls.push( {
        type: "BUTTON",
        action: "my-action"
    });
    config.toolbarData = toolbarData;

    config.initActions = function( actions ) {
        // can modify state of existing actions or add your own
        // can also pass in an array of actions to add
        actions.add( {
            name: "my-action",
            label: "Hello",
            action: function(event, focusElement) {
                alert("Hello World!");
            }
        } );
    }
    return config;
}

-- combining action menu and tool bar

function( options )
{
   var $ = apex.jQuery,
       toolbarData = $.apex.interactiveGrid.copyDefaultToolbar(), // Make a copy of the default toolbar
       actionsMenuGroup = toolbarData.toolbarFind( "actions1" );  // Locate the actions menu group

   // Array position denotes displayed position in the toolbar, so let's add the new download button directly
   // after the actions menu group in the array, such that it displays directly after the actions menu in the
   // toolbar.
   // Note: The toolbar is action-driven, so integrates easily with the Interactive Grid. To show the dialog, we
   // just define the appropriate action for showing the download dialog (show-download-dialog).
   actionsMenuGroup.controls.push( {
       type: "BUTTON",
       action: "show-download-dialog",
       iconBeforeLabel: true
   } );
   actionsMenuGroup.controls.push( {
       type: "BUTTON",
       action: "show-sort-dialog",
       iconBeforeLabel: true
   } );
 
 toolbarGroup = toolbarData[toolbarData.length - 1]; // this is the last group with reset button
   // add our own button
    toolbarGroup.controls.push( {
        type: "BUTTON",
        action: "my-action"
    });
options.initActions = function( actions ) {
        // can modify state of existing actions or add your own
        // can also pass in an array of actions to add
        actions.add( {
            name: "my-action",
            label: "Hello",
            action: function(event, focusElement) {
                alert("Hello World!");
            }
        } );
    }
   // Assign new toolbar data back to toolbarData configuration property
   options.toolbarData = toolbarData;
 
   // Return the options
   return options;  
   
}

Delete Row Button to all records of Interactive Report in Oracle Apex

 1. add 'Delete' Del column to Report Query 2. Set the Following Properties for the DEL Column Type: Link Heading: Delete Targ...