Posted inORACLE
ORA-06533: Subscript beyond count
ORA-06533: Subscript beyond count Cause: An in-limit subscript was greater than the count of a varray or too large for a nested table. Action: Check the program logic and explicitly…
ORA-06531: Reference to uninitialized collection PLSQL
ORA-06531: Reference to uninitialized collection Error Description:Reference to uninitialized collection Error Cause:An element or member function of a nested table or varray was referenced (where an initialized collection is needed) without…
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…
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…
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…
Posted inORACLE Oracle Tutorials
Oracle Functions
NVL2 Function COALESCE Function DECODE Function NULLIF Function
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…
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)…
Posted inORACLE
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. Copy CodeCopiedUse…
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…