Posted inORACLE PLSQL

PLSQL Collection Data Deletion

PLSQL collection data deletion CREATE OR REPLACE TYPE l_rec_type IS OBJECT (id NUMBER, name VARCHAR2 (1111)); / CREATE OR REPLACE TYPE l_tab_type IS TABLE OF l_rec_type; / Copy CodeCopiedUse a…
February 25, 2020
Posted inORACLE

Row level, statement level Trigger

Create two tables based on the query: Copy CodeCopiedUse a different BrowserCREATE TABLE product ( MESSAGE VARCHAR2 (50), CURRENT_DATE DATE ); CREATE TABLE product_log ( MESSAGE VARCHAR2 (4000), CURRENT_DATE DATE…
February 24, 2020
Posted inDBA ORACLE

Create user in Oracle 12 C

Create user 12C: connect system/manager as sysdba alter session set "_ORACLE_SCRIPT"=true; / create user lime identified by limeoracle; / grant dba to lime; / connect lime/limeoracle / create table test(lime…
February 24, 2020
Use of Merge Statement
Posted inORACLE SQL

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…
July 25, 2017
Refresh a materialized view
Posted inORACLE

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)…
July 25, 2017
RANK and DENSE_RANK
Posted inORACLE

RANK and DENSE_RANK

RANK() : function always returns sequential order for each record.DENSE_RANK() : It returns continuous order for each record. Copy CodeCopiedUse a different BrowserSELECT empno, deptno, sal, mgr, RANK () OVER (PARTITION BY…
April 26, 2017
Handle trigger Mutating Error
Posted inORACLE

Handle trigger Mutating Error

Many times we have faced the problem of trigger Mutating error. error ORA-04091: table TEST1.TEST_MUTATING is mutating, trigger/function may not see it let see how it comes and resolve Create…
April 25, 2017