Use of Merge Statement

Sometimes whenever we want to insert records into table with the conditions For existing records (if found then) Update, For Non Existing records (If not found) then insert Step 1 : Create table a_merge as select empno , ename from emp; Now the PL/SQL Block or SQL query: DECLARE l_start NUMBER; BEGIN l_start := …

RANK and DENSE_RANK

RANK() : function always returns sequential order for each record. DENSE_RANK() : It returns continuous order for each record. SELECT empno, deptno, sal, mgr, RANK () OVER (PARTITION BY deptno ORDER BY sal) "rank", DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal) "dense rank" FROM emp;

Constraints in Nested table

In my previous blog “Working With Nested Table Oracle ” i have explained about how to work with nested tables. Here we can also define Constraints to the nested table. According to the previous blog “Working With Nested Table Oracle ” we have Table := dept_details nested table := employees with …

Working With Nested Table Oracle

Nested table is awesome feature of Oracle. You can create table inside a table, Which means one column can be treated as a table. Nested tables are faster than the parent child table relationship. Steps : Create Object TYPE CREATE OR REPLACE TYPE emp_rec_type AS OBJECT ( EMPNO NUMBER (4), ENAME VARCHAR2 (10 BYTE), …

Browse blob file on button click

Hey Guys, Some times user does no want to click on browse button, provided by oracle apex by default. By clicking on button browse image is better experience. How we can achieve this, let us see Here we have one browse file browse type item. By default we know if we click on item it gives file browse window. We can use …

Working with Multi Select List

As we know select list is an item type available in Oracle apex in each of the version, for the single selection you don’t need to bother, it is just below We have a select list in page region Here i have disabled Multi Selection . ITEM Settings Allow Multi Selection - NO Report query is just simple as where …

Set item value in Session using PL/SQL

Some times I feel that the value of item is not setting to it’s session state. we can not save the value to session state until unless we use submit the page with the item or submitting the page items as well. Now try to set it without loading the page let say we have an Item as “P1_DEPT”. Create a …

Pass Item to item Values Using Javascript

Pass Item to item Values Using Javascript in Oracle APEX Here you have two page items, and a button eg.- P3_EMAIL P3_EMAILVIEW Show_BTN Problem - Pass value of P3_EMAIL to P3_EMAILVIEW Step 1 - disable item P3_EMAILVIEW using disable property Step 2 - Add dynamic Action for the button Show_BTN Create dynamic Action …

Region Display Selector

You just need to choose a region named as region display selector in the breadcrumb of the apex page it will show all the regions , those are enabled for display in region display selector. And highlight that one which is on display. must select scroll window option in region display selector attributes.