Group Report In Oracle Apex

Just Create A Region with PL/SQL Dynamic Content Type.
Then copy-pest the code.
This report is just a demo. Do your report yourself. Just you can take the concept .

declare
v_did varchar2(50) :=1000;
v_cnt number;
v_rowspan number;
begin

htp.p('<style>
    tr,th,td{
        border: 1px solid black;
    }
    table{
        width: 100%;
    }

</style>');
htp.p('<table><tr><th>did</th><th>name</th><th>sal</th></tr>');
for i in (select department_id did,  department_name dname from departments where department_id in (select department_id from employees) order by 1 asc) loop


 select count(*) into v_cnt from employees where department_id=i.did;
 v_rowspan :=v_cnt+1;
    htp.p('<tr><td rowspan="'||v_rowspan||'">'||i.did||'</td></tr>');

for a in (select last_name lname, salary sal from employees where department_id =i.did) loop
    if v_did<> i.did then
     htp.p('<tr><td>'||a.lname||'</td><td>'||a.sal||'</td></tr>'); 
    end if;

end loop;

 v_did :=i.did;
end loop;
    htp.p('</table>');
end; 


--Thank You 

Instant Search in Oracle Apex by Java Script

1. Create a classic report by your query
2. Take a item for input search content
3. Goto Shared Components >> Templates >>Copy Report (Type)/Standard (Name) with diffrent       name like  Standard2
4. Goto Standard2 >> After Column Heading >> </thead> <tbody id="id" >  and save
5. Goto Report Attributes >> Appearance >>Template >> Standard2
6. Copy the code into Page >> Execute when Page Loads

(Change item name and id)

   $(document).ready(function(){
  $("#P9_SEARCH").on("keyup", function() {
    var value = $(this).val().toLowerCase();
    $("#id tr").filter(function() {
   $(this).toggle($(this).text().toLowerCase().indexOf(value) > -1)
    });
  });
});

Then Enjoy

Redirect To Another Page by Java Script

You can use this location JavaScript in dynamic action >> Execute JavaScript Code Action

// Goto new location with a new tab

window.open('https://qaiumer.blogspot.com', '_blank');

// Sets the new location of the current window.

window.location = "https://qaiumer.blogspot.com";

// Sets the new href (URL) for the current window.

window.location.href = "https://qaiumer.blogspot.com";

// Assigns a new URL to the current window.

window.location.assign("https://qaiumer.blogspot.com");

// Replaces the location of the current window with the new one.

window.location.replace("https://qaiumer.blogspot.com");

// Sets the location of the current window itself.

self.location = "https://qaiumer.blogspot.com";

// Sets the location of the topmost window of the current window.

top.location = "https://qaiumer.blogspot.com";

Oracle Apex URL Descriptions

http://apex.oracle.com/pls/apex/f?p=AppId :PageId :Session :Request :Debug :ClearCache :Params :ParamValues :PrinterFriendly

§  http:// – the protocol, can be http or https

§  apex.oracle.com – your domain/host/server, whatever you want to call it. Can also be localhost.

§  /pls – indicates that you are using Oracle HTTP Server with mod_plsql. If you are using APEX Listener or Embedded PL/SQL Gateway this part is obsolete/missing.

§  /apex – the entry from your dads.conf file (this a file on your application-server or EPG where the target database is configured) – in case of EPG its just one entry pointing to localhost, in case of an OAS you can have multiple entries, each pointing to an other database

§  /f?p= – procedure “f” is called and parameter “p” is set to the complete rest of the string. Remember: APEX uses mod_plsql. “f” is a public procedure, this is the main entrypoint for APEX. Or you could say: “f” is APEX.

§  AppId – the number or the Alias of the Application

§  :PageId – the number or the Alias of the Page

§  :Session – unique Session ID, can be 0 for Public Pages or empty (then APEX creates a new Session)

§  :Request – a Request Keyword. This is basically free text, just a string you can specify to react in a process or region condition on. e.g. you could pass the keyword “CREATE” and have a condition on the delete button of your page saying “dont’t display this button if request is CREATE”.
In other words: use the REQUEST to control the behaviour of your page.
When pressing a button, the button sets the REQUEST to the button-value (e.g. SAVE), so that you can control the processes in the page processing (Submit) phase.

§  :Debug – set to YES (uppercase!) switches on the Debug-Mode which renders debug-messages and timestamps in your Browser window. This helps to detect wrong behaviour of your page or performance issues and everything else. Every other value then YES turns the Debug-Mode off

§  :ClearCache – you can put a page id or a list of page ids here (comma-separated) to clear the cache for these pages (set session state to null, …). But there is more: RP resets the pagination of reports on the page(s), a collection name deletes the collection, APP clears all pages and application-items, SESSION does the same as APP but for all applications the session-id has been used in.

§  :Parameters – comma seperated list of page-item names. Good practice is to set only those page-items which are on the page you are going to. Accepts page-items as well as application-items.

§  :ParamValues – comma separated list of values. Each value is assigned to the corresponding Parameter provided in ParamNameList (first value assigned to first parameter, second value assigned to second parameter, and so on…).
The trick here is not having values which contain either a comma “,” or a colon “:”. Both would lead to side-effects and errors, as APEX gets confused when parsing the URL. Using a comma works, if enclosed by slashes: e.g. \123,89\.

§  :PrinterFriendly – set to YES (uppercase!) switches the page into PrinterFriendly-Mode, uses the Printerfriendly template to render the Page. You can also hide regions or other elements in PrinterFriendly-Mode using the PRINTER_FRIENDLY variable in a condition.

Download PDF

Play With JSON . Generating JSON ,Temporary CLOB, REF CURSOR to JSON ,Manual JSON Build ,XML to JSON ,JSON to XML, Parsing JSON

From This PDF you can learn more about JSON and play in PL/SQLusing  APEX_JSON Package . 

PDF On APEX_JSON


This PDF data is collected from different sources.

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 ---------------------------