ORA-00972: identifier is too long

ORA-00972: identifier is too long. Till Oracle 12 C, the identifier length is 30 characters. Starting from 18 C Oracle increased the length of identifier to 128 character. So now we can define our identifiers till 128 characters if you have upgraded to 18 C. Let see one example. Database version 18 C. Select ename as …

APEX page types | Universal Theme

APEX provides three types of pages till APEX 20. To create these pages Select Page Mode. Page Mode Select the page mode for this page. Available options include: Normal The page is presented as a normal Application Express application page. Modal Dialog The page is presented as a modal dialog. A modal dialog is …

Create ZIP File using APEX_ZIP

Data Types The data types used by the APEX_ZIP package. t_files type t_files is table of varchar2(32767) index by binary_integer; declare l_zip_file blob; begin for l_file in ( select file_name, file_content from my_files ) loop apex_zip.add_file ( p_zipped_blob => l_zip_file, p_file_name => l_file.file_name, …

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; / DECLARE l_tab l_tab_type := l_tab_type (); l_check NUMBER; BEGIN l_tab := l_tab_type (NULL, NULL); l_tab (1) := l_rec_type (1000, 'ashish'); …

Ref cursor switch based on condition

A ref cursor is a datatype that holds a cursor value in the same way that a varchar2 variable will hold a string value. Ref cursor will allow us to open difference sets of query where is the explicit cursor will not allow us to open a query other then the one which is associated to that cursor pointer. Using ref …

Row level, statement level Trigger

Create two table based on query: CREATE TABLE product (Message varchar2(50), Current_Date date ); CREATE TABLE product_log (Message varchar2(4000), Current_Date date ); Statement level trigger CREATE or REPLACE TRIGGER Before_Update_Stat_product BEFORE UPDATE ON product Begin INSERT INTO product_log …

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 …