Working With SYS_CONTEXT

How to create Oracle SYS_CONTEXT ? Oracle SYS_CONTEXT holds the value of the parameter associated with context namespace. Prerequisite Database user needs grant to create context. GRANT CREATE ANY CONTEXT TO c##ontoor_dev; Package to set parameter value. CREATE OR REPLACE PACKAGE sys_context_utility IS /* Version 1.0.1 …

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 …

Blob to Clob Using DBMS_LOB

Create function to convert the blob content to clob CREATE OR REPLACE FUNCTION blob_to_clob (i_blob_content IN BLOB) RETURN CLOB AS l_chunk VARCHAR2 (32767); l_position PLS_INTEGER := 1; l_buffer_size PLS_INTEGER := 32767; l_clob_text CLOB; BEGIN -- Create temprary DBMS_LOB.createtemporary (l_clob_text, TRUE); FOR i …

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 …