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), …