APEX shortcut variables

APEX shortcut variables are best in dynamic use in Javascript code. We can fetch data from database or can use any hard coded text. Under the shared components we have pretty awesome feature of APEX shortcut. We have several source type available to populate the shortcut variables. * PL/SQl Function body * HTML Text * …

Get Current Schema and DB name

Some times we do not know about our schema exist in which db and which is our schema hare is the method to find db and schema name. DECLARE CURSOR ashish_db_name IS SELECT NAME FROM v$database; My_db_name VARCHAR2 (4000); BEGIN OPEN ashish_db_name; FETCH ashish_db_name INTO My_db_name ; CLOSE ashish_db_name; …

Refresh a materialized view

To refresh a Materialized view we need DBMS_SNAPSHOT.REFRESH procedure. Steps. CREATE TABLE demo_m ( id NUMBER (10) primary key, name VARCHAR2 (400) ); / INSERT INTO DEMO_M ( ID, NAME) VALUES (1001, 'VIKAS' ); / SELECT * FROM demo_m; One record is available in the table. Create Materialized view. CREATE …

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;

Handle trigger Mutating Error

Many times we have faced the problem of trigger Mutating error. errorORA-04091: table TEST1.TEST_MUTATING is mutating, trigger/function may not see it let see how it comes and resolve Create Table CREATE TABLE test_mutating ( id NUMBER, name VARCHAR2(400 byte) ); Create trigger CREATE OR replace TRIGGER t1 …

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 …