Dynamic Navigation Menu With Sub Menu Manually In Oracle Apex

Here is the SQL to do the Dynamic Navigation Menu With Sub Menu. The details watch the video.

SELECT LEVEL,
                  ENAME label,
                  'f?p=&OBNK.:' || '1' || ':' ||:APP_SESSION||':'||EMPNO target,
  SAL is_current,
 'fa-angle-double-right' image,                  
                  MGR 
             FROM EMP                 
       START WITH MGR IS NULL
       CONNECT BY PRIOR EMPNO = MGR
ORDER SIBLINGS BY EMPNO

Delete Row From Classic/ IR Report in Oracle Apex

Due to time limitation here I just share the code. For details you can see the given Video under the post.

SELECT ROWID,   
EMPNO ID,   
EMPNO,   
ENAME,   
JOB,   
ename MGR,   
HIREDATE,   
SAL,   
COMM,
DEPTNO
FROM EMP_T

=============================
<span class="t-Icon fa fa-trash-o" aria-hidden="true"></span>
=============================
id='#ID#' class="delete t-Button t-Button--danger t-Button--simple t-Button--small" title="Delete Employee: #ENAME#"
=============================
this.triggeringElement.id
=============================
begin
delete emp_t where empno=:P10_NEW ;
end;
=============================
alert("Employee #"+$v("P10_New")+"removed.");
=============================

Oracle Application Express Keyboard Shortcuts

Where Keys Action
Page Designer Grid Layout CTRL+ALT+D Display from here
Page Designer Grid Layout CTRL+ALT+T Display from page
Page Designer Alt+2 Go to Dynamic Actions
Page Designer Alt+9 Go to Gallery Buttons
Page Designer Alt+8 Go to Gallery Items
Page Designer Alt+7 Go to Gallery Regions
Page Designer Alt+5 Go to Grid Layout
Page Designer Alt+F1 Go to Help
Page Designer Ctrl+F1 Go to Messages
Page Designer Alt+4 Go to Page Shared Components
Page Designer Alt+3 Go to Processing
Page Designer Alt+6 Go to Property Editor
Page Designer Alt+1 Go to Rendering
Page Designer Alt+Shift+F1 List Keyboard Shortcuts
Page Designer Ctrl+Alt+F Page Search
Page Designer Ctrl+Y Redo
Page Designer Alt+F11 Restore/Expand
Page Designer Ctrl+Alt+S Save
Page Designer Ctrl+Alt+R Save and Run Page
Page Designer Ctrl+Alt+E Toggle Hide Empty Positions
Page Designer Ctrl+Z Undo
Page Designer with focus anywhere that has a context menu Shift+F10 or Context menu key Open context menu
Page Designer Grid Layout Gallery and Icon List in wizards Arrow keys Move selection
Page Designer Property Editor with focus on a group heading Up/Down Arrow keys Move to previous/next group
Page Designer Property Editor with focus on a group heading Home/End keys Move to first/last group
Page Designer Property Editor with focus on a group heading Left/Right Arrow keys or Enter or Space Expand or collapse the group
Page Designer With focus on any tab Arrow keys Select previous/next tab
Focus on a splitter handle Arrow keys Move splitter
Focus on a splitter handle Enter Expand or collapse splitter if supported by splitter
On pages that have a splitter Ctrl+F6 Move to next splitter
On pages that have a splitter Ctrl+Shift+F6 Move to previous splitter
Focus on any field with a (?) help icon Alt+F1 Open field help dialog
Focus on a field help dialog Alt+F6 Move focus back to field without closing dialog
Focus in any dialog Escape Close dialog
Any page, focus on region display selector "tabs" Arrow keys Select the previous/next tab
SQL Commands Ctrl+Enter Run the current command
Code Editor Ctrl+Z Undo
Code Editor Ctrl+Shift+Z Redo
Code Editor Ctrl+F Find
Code Editor Ctrl+Shift+F Replace
Code Editor with focus in find field Up/Down Arrows Find previous/next instance
Code Editor Ctrl+Space Auto complete when available
Code Editor Alt+F6 Leave code editor and go to next tab stop
Code Editor Alt+Shift+F6 Leave code editor and go to previous tab stop

Auto Reload / Refresh A Page After a Certain Time In Oracle Apex

Auto Reload / Refresh A Page In Oracle Apex is very simple.

Just copy-pest the code on Page Properties >> HTML Header 

<meta http-equiv="refresh" content="3" >

This 3 is the number of second  between every Reload / Refresh of the Page.

Check Or Click and Get Details Report

Step 1. Create a Interactive Grid Editable Form by you query.

Step 2. Create another item to keep details report query parameter from Master Table. \

Step 3. Create a Dynamic Action on Master region with  Selection Change [Interactive Grid]  Event
           and Execute JavaScript Action.
           Copy Pest the code:

    var i, selectedIds=":" ,
    model = this.data.model;

for ( i = 0; i < this.data.selectedRecords.length; i++ ) {
    if (i>=0) { 
    selectedIds += model.getValue( this.data.selectedRecords[i], "EMPNO") + ":"; }  // Matching column (parameter) 
    else 
        {
    selectedIds += model.getValue( this.data.selectedRecords[i], "EMPNO");   // Matching column (parameter) 
       }
}
$s("P8_NEW", selectedIds);   //Item to keep parameter Value from master Table
apex.region("ID").refresh();
   
Step 4. Finally create another report to show details report with you query and using master table matching column. And do the condition like this.

select EMPNO,
       ENAME,
       JOB,
       MGR,
       HIREDATE,
       SAL,
       COMM,
       DEPTNO
  from EMP
 where instr(:P8_NEW, ':' ||EMPNO|| ':' ) > 0 ;

--------------------------------That's All------------------------Thank You ---------------------------

Get Summation Doing Checkbox Checked In Oracle Apex Via JavaScript

To Get Summation By Doing Checkbox Checked In Oracle Apex Via JavaScript.

Step 1. Create a CheckBox item and with Some  Numbers by Query or Static. Like :

     select 100,100 a from dual
     union
     select 200,200 a from dual
     union
     select 300,300 c from dual

Step 2. Create another item with  Textfield type to show summation result .

Step 3. Now Copy-Pest the function to

Page Properties  > Function and Global Variable Declaration 

/* P43_SUM = Textfield item ID where we show the result.
P43_NEW = Checkbox item from where we get value. 
P43_NEW_ = First portion of every Checkbox ID . */

 function findTotal() {
        var arr = document.getElementsByName('P43_NEW');
        var tot = 0;
        var count= 0 ;
    for (var i = 0; i<arr.length; i++) {  
        if ($('#P43_NEW_'+i).prop('checked') == true) {      
            count = count + 1 ;
            tot += parseInt(arr[i].value);
        document.getElementById('P43_SUM').value =  tot;
         }
         else  if (count == 0 ){
               document.getElementById('P43_SUM').value =  0;
         }         
      }
    } 

Step 4. Finally  Call the JavaScript Function Like.
Checkbox item properties > Advanced  > Custom Attributes

     onchange="findTotal();"

-------------------------------------Thank You----------------------------------

Apex them API to Change Color of Them

apex_theme.set_session_style( p_theme_number =>   42,
                                                  p_name =>     'Vita - Red'  );

Login Time Apex Them Color Mention.

Hide/Wrap/Alias Application Number and Page Number From URL of Oracle Apex Manually

In Oracle Apex URL have shown Application Number and Page Number. But it is not secure. So you can hide or wrap this Numbers. But you have to face little bit of difficulty or extra afford. Let’s see how we can wrap this Numbers step by step.

1. In Application level. Edit Application Properties >>Application Alias>> Wright A Alias  

    Example: F_139139

    In Page level. Go to Page Alias>> Wright A Alias     

    Example: PAGE_TEST

Now you can try the URL writing manually like this.

http://10.11.1.162:8080/apex/f?p=F_139139:PAGE_TEST:15674490024092:::::

 2.  Now you have to wright the URL manually with Alias where’s from the page called. Like Navigation Menu/ Other pages.

f?p=F_139139:PAGE_TEST:&SESSION.:::::

Finally save the page and click on the menu. 

Oracle Apex Extend Session Idle Timeout and Session Length

1. Goto Application Share Component >> Security Attributes>> Session Management

Than Change What You Want...

Access Control List (ACL) Setup

An access control list (ACL), with respect to a computer file system, is a list of permissions attached to an object. An ACL specifies which users or system processes are granted access to objects, as well as what operations are allowed on given objects. Each entry in a typical ACLspecifies a subject and an operation.

https://en.wikipedia.org/wiki/Access_control_list

begin

  dbms_network_acl_admin.create_acl (
  acl          => 'networkacl.xml',
  description  => 'Allow Network Connectivity',
  principal    => 'PUBLIC',
  is_grant     => TRUE,
  privilege    => 'connect',
  start_date   => SYSTIMESTAMP,
  end_date     => NULL);

 dbms_network_acl_admin.assign_acl (
  acl         => 'networkacl.xml',
  host        => '127.0.0.1', --(web-service)
  lower_port  => '77', --lower port
  upper_port  => '9999'); --upper port
  commit;

end;

Enter Key Press Change Focus to Next Item like a Tab

Enter Key Press Change Focus to Next Item like a Tab in JavaScript

Just Copy and Pest the JavaScript Code to Page>>JavaScript>>Function and Global Variable Declaration

$('body').on('keydown', 'input, select, textarea', function(e) {
    var self = $(this)
      , form = self.parents('form:eq(0)')
      , focusable
      , next
      ;
    if (e.keyCode == 13) {
        focusable = form.find('input,a,select,button,textarea').filter(':visible');
        next = focusable.eq(focusable.index(this)+1);
        if (next.length) {
            next.focus();
        } else {
            form.submit();
        }
        return false;
    }
});

There's no place like 127.0.0.1

Phillip Remaker, void f()

There's no place like 127.0.0.1 is a t-shirt slogan which is a play on the common American English saying, There’s no place like home, popularized by the 1939 movie The Wizard of Oz.

In computer networking the Localhost reserved IP address is guaranteed to be in every networked computer. In IPv4, we use 127.0.0.1; in IPv6 we use ::1. This is why the phrase sometimes appears as There’s no place like ::1

Since that address is on every machine, it is just like “home.” Always there, always safe, and local to you. It’s an inside joke for networking nerds.

Since that IP address is the same on every computer, you can double your nerd points by quoting Buckaroo Banzai and say, No matter where you go, there you are.

JavaScript Events

Events

You can write JavaScript code that executes when an event occurs, such as when a user clicks an HTML element, moves the mouse, or submits a form.
When an event occurs on a target element, a handler function is executed.
Common HTML events include:

Working with DOM (Document Object Method) JavsScript

Each element in the DOM has a set of properties and methods that provide information about their relationships in the DOM:

element.childNodes returns an array of an element's child nodes.
element.firstChild returns the first child node of an element.
element.lastChild returns the last child node of an element.
element.hasChildNodes returns true if an element has any child nodes, otherwise false.
element.nextSibling returns the next node at the same tree level.
element.previousSibling returns the previous node at the same tree level.
element.parentNode returns the parent node of an element.

Date Methods JavaScript

Date Methods

When a Date object is created, a number of methods make it possible to perform operations on it.

JavaScript Math Object Methods

Math Object Methods

The Math object contains a number of methods that are used for calculations:

Growth Rates and Dominance Relations (Algorithms Time Complexity Sequence )

http://bigocheatsheet.com/

Oracle Apex Button CSS

Lint attributese  =>   class="t-Button t-Button--hot  "

Password Strength Check In Oracle Apex

Step 1:- Post Text

<span id="password_strength"></span>

Step 2:- Custom Attributes

onkeyup="CheckPasswordStrength(this.value)" 

Step 3:- Function and Global Variable Declaration

function CheckPasswordStrength(password) {
        var password_strength = document.getElementById("password_strength");

        //TextBox left blank.
        if (password.length == 0) {
            password_strength.innerHTML = "";
            return;
        }

        //Regular Expressions.
        var regex = new Array();
        regex.push("[A-Z]"); //Uppercase Alphabet.
        regex.push("[a-z]"); //Lowercase Alphabet.
        regex.push("[0-9]"); //Digit.
        regex.push("[$@$!%*#?&]"); //Special Character.

        var passed = 0;

        //Validate for each Regular Expression.
        for (var i = 0; i < regex.length; i++) {
            if (new RegExp(regex[i]).test(password)) {
                passed++;
            }
        }

        //Validate for length of Password.
        if (passed > 2 && password.length > 8) {
            passed++;
        }

        //Display status.
        var color = "";
        var strength = "";
        switch (passed) {
            case 0:
            case 1:
                strength = "Weak";
                color = "red";
                break;
            case 2:
                strength = "Good";
                color = "darkorange";
                break;
            case 3:
            case 4:
                strength = "Strong";
                color = "green";
                break;
            case 5:
                strength = "Very Strong";
                color = "darkgreen";
                break;
        }
        password_strength.innerHTML = strength;
        password_strength.style.color = color;
    }

Create PDB , Alter/Open , TNSNAMES Configer, Drop PDB

CREATE PDB(Plaggable Database)
================================
create pluggable database NAME
admin user 
   app_admin identified by Password
file_name_convert = ('/pdbseed/', '/NAME/');



alter pluggable database NAME open;

TNSNAMES.ORA
================================
Path:
cd /u01/app/oracle/product/12.1.0/db_1/network/admin/

NAME =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.022)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = NAME)
    )
  )


DROP PDB
================================

alter pluggable database NAME close;

drop pluggable database NAME including datafiles;

Source: http://www.dba-oracle.com/t_pluggable_database.htm

Text Generation by PL/SQL

-- Create directory 
create or replace directory EXPIMP_DIR  as 'E:\hira';

--PL/SQL Block

DECLARE
v_file  UTL_FILE.FILE_TYPE;
BEGIN
  v_file := UTL_FILE.FOPEN(location     => 'EXPIMP_DIR',
                           filename     => 'New_Card_Test.txt',
                           open_mode    => 'w',
                           max_linesize => 32767);
                           
  FOR cur_rec IN (select sl,data_desc from tt order by sl) LOOP
  
    UTL_FILE.PUT_LINE(v_file,cur_rec.data_desc);
  
  END LOOP;
  UTL_FILE.FCLOSE(v_file);
    
EXCEPTION
  WHEN OTHERS THEN UTL_FILE.FCLOSE(v_file); RAISE;
END;

Global Back Button in Oracle Apex Using JavaScript

Back Button in Oracle Apex by doing this you can go back your immediate previous page.

1. Create a button with name "Back"

2. Button Properties >> Action When Button Clicked >>Action>> Redirect to URL >> URL Target>>    javascript:history.go(-1) 

And Enjoy 

Call Process From Dynamic Action in Oracle Apex 5.1

Some time we have need long PL/SQL block or raise_applicaiton_error and many other things which we can do in Apex Process level but can not do in Dynamic Action.

Now here I am show how to call a Process from item  Dynamic Action.

1. Create a Item and create Dynamic Action on this Item. 

2. This Dynamic Action Event can be anything but Action will Execute JavaScript Code and right this JavaScript Code >> Settings.

apex.submit({  request:"ID", showWait:true});

3.  Here "ID" is a reference to call a process. 

4.  Now a Create One or multiple Process with what ever as your wish.

5.  Go to Process >>Properties >> Conditions>> Condition Type >>Select :- Request= Expression 1 >> Expression 1 >> ID (reference)

That's It. Now enjoy Process form Dynamic Action.

Make a Oracle Apex 5.1 Item Readonly

Make a Oracle Apex 5.1 Item Readonly. It's not mean Display Only item type. By doing this you can just read a input text field but can not edit or remove.

1. Make a Item in a Region.

2. Go to Item properties >> Element >>HTML Form Element Attributes >> readonly

and Save.   

RESTful Get API Example in Oracle Apex

DECLARE
   l_param_list      VARCHAR2 (512);
   l_http_request    UTL_HTTP.req;
   l_http_response   UTL_HTTP.resp;
   l_response_text   VARCHAR2 (32767);
   l_response        VARCHAR2 (32767);

   v_url             VARCHAR (500);
   url_param_list    VARCHAR2 (512);

   p_userid          VARCHAR2 (500) := '100140';
   p_sessionid       VARCHAR2 (500) := '123456';
   p_companyid       VARCHAR2 (500) := '101';
   p_executionmode   VARCHAR2 (500) := 'S';
   p_studentid       VARCHAR2 (500) := '9005';
   p_authkey         VARCHAR2 (500) := 123456;
   ts                VARCHAR2 (32000)
      := '{"std_info_node":[{"errormessage":"Invalid ID . Please input valid ID","studentname":"Qaium","accountno":"0487878778","mobileno":"01234","errorcode":"1"}]}';

   l_data_count      NUMBER := 0;
   l_values          apex_json.t_values;

   t_timeout         INTEGER;
BEGIN
   v_url := 'http://10.11.150.12/PmsWebService/StudentInfo?';
   url_param_list :=
         'userid='
      || p_userid
      || '&sessionid='
      || p_sessionid
      || '&companyid='
      || p_companyid
      || '&executionmode='
      || p_executionmode
      || '&studentid='
      || :P180_STUDENT_ID                                        
      || '&authkey='
      || p_authkey;

   /*begin
   l_http_request :=  UTL_HTTP.begin_request (v_url, 'GET', 'HTTP/1.1');
   UTL_HTTP.write_text (l_http_request, l_param_list);
   --  raise_application_error(-20007,'1');
   l_http_response := UTL_HTTP.get_response (l_http_request);
   raise_application_error(-20007,'2 : '||l_http_response.status_code);
   end ; */

   UTL_HTTP.get_transfer_timeout (t_timeout);
   UTL_HTTP.set_transfer_timeout (10);
   UTL_HTTP.get_transfer_timeout (t_timeout);

   l_param_list := NULL;
   l_http_request :=
      UTL_HTTP.begin_request (v_url || url_param_list, 'GET', 'HTTP/1.1');
   UTL_HTTP.write_text (l_http_request, l_param_list);
   l_http_response := UTL_HTTP.get_response (l_http_request);

   UTL_HTTP.read_text (l_http_response, l_response_text);

   IF l_response_text IS NULL
   THEN
      raise_application_error (-20003,
                               'Request Does Not Get Any Response !!!');
   END IF;

   apex_json.parse (l_response_text);

   SELECT    'DCMC'
          || REPLACE (TO_CHAR (SYSDATE, 'DD-MM-YY'), '-', '')
          || DCMC_PAYMENT.NEXTVAL
     INTO :P180_REF_NO
     FROM DUAL;

   :P180_NAME :=
      apex_json.get_varchar2 (p_path => 'std_info_node[1].studentname');
   :P180_MOBILE :=
      apex_json.get_varchar2 (p_path => 'std_info_node[1].mobileno');
   :P180_AC_NUMBER :=
      apex_json.get_varchar2 (p_path => 'std_info_node[1].accountno');
   --raise_application_error(-20003,:P180_MOBILE);

   /*
    FOR i IN 1 ..3

    LOOP
   :P180_NAME:= apex_json.get_varchar2 (p_path => 'std_info_node[' || i || '].studentname') ;
   :P180_MOBILE:= apex_json.get_varchar2 (p_path => 'std_info_node[' || i || '].mobileno') ;
   :P180_AC_NUMBER :=apex_json.get_varchar2 (p_path => 'std_info_node[' || i || '].accountno') ;

    -- DBMS_OUTPUT.put_line ('a is '|| apex_json.get_varchar2 (p_path => 'std_info_node[' || i || '].studentname')|| l_data_count);

    END LOOP; */

   UTL_HTTP.end_response (l_http_response);
EXCEPTION
   WHEN UTL_HTTP.end_of_body
   THEN
      UTL_HTTP.end_response (l_http_response);
END;