Script to enable all Constraints

declare
Cursor cCons is select table_name,constraint_name
from user_constraints
where status = 'DISABLED';
--order by decode(constraint_type,'R','A',constraint_type);
type t is table of cCons%rowtype index by binary_integer;
all_constraints t;

nLimit int := 100;
begin

open cCons;
loop
Fetch cCons Bulk Collect into all_constraints limit nLimit;

for i in 1..all_constraints.count loop
begin
execute immediate 'Alter table '||all_constraints(i).table_name||' modify constraint '|| all_constraints(i).constraint_name ||' enable validate';
--Exception
-- WHEN Others then
-- null; -- to ignore globalmedi_intermedia system generated constriants
end;
end loop;

exit when all_constraints.count < nLimit;
end loop;
Close cCons;

end;
/

Script to Enable All Triggers

Declare
Cursor cTriggers is Select table_name from user_tables;
type t is table of cTriggers%rowtype index by binary_integer;
all_triggers t;
nLimit int := 100;
begin

Open cTriggers;
loop
Fetch cTriggers Bulk Collect into all_triggers limit nLimit;

for i in 1..all_triggers.count loop
Execute Immediate ' Alter table '||all_triggers(i).table_name||' enable all triggers';
end loop;

Exit when all_triggers.count < nLimit;
end loop;
Close cTriggers;
end;
/

Script to disable All Constraints

declare
Cursor cCons is select table_name,constraint_name
from user_constraints
where status = 'ENABLED'
order by decode(constraint_type,'R','A',constraint_type);
type t is table of cCons%rowtype index by binary_integer;
all_constraints t;

nLimit int := 100;
begin

open cCons;
loop
Fetch cCons Bulk Collect into all_constraints limit nLimit;

for i in 1..all_constraints.count loop
begin
execute immediate 'Alter table '||all_constraints(i).table_name||' disable constraint '|| all_constraints(i).constraint_name;
Exception
WHEN Others then
null; -- to ignore any system generated constriants for BLOB/CLOB columns
end;
end loop;

exit when all_constraints.count < nLimit;
end loop;
Close cCons;

end;
/

Script to disable All Triggers

Declare
Cursor cTriggers is Select table_name from user_tables;
type t is table of cTriggers%rowtype index by binary_integer;
all_triggers t;
nLimit int := 100;
begin

Open cTriggers;
loop
Fetch cTriggers Bulk Collect into all_triggers limit nLimit;

for i in 1..all_triggers.count loop
Execute Immediate ' Alter table '||all_triggers(i).table_name||' disable all triggers';
end loop;

Exit when all_triggers.count < nLimit;
end loop;
Close cTriggers;
end;
/

Amruta Patki

Amruta Patki

Gauhar Khan

New face

Gauhar Khan

New face

Windows: Task Manager won't open

You may have virus/Malware installed on your computer.

Click Start- > Run -> TaskMgr.exe

It it doesn't open

Start ->Run->Regedit

Navigate to

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Image File Execution Options\taskmgr.exe

Remove the value for the Field Debugger.

No Read Access to System Tables. Modify Access db before retrying

You may get this message, if you use Oracle SQL Developer to migrate Access data.
To fix this:

Open Access database, go to Tools->Security->User Groups & Permissions

Select each system tables (They all start with MSys), Check all the Check boxes under Permissions.

If the system tables are not visible, go to Tools-> Options -> View tab, and check System Tables

Save.

Now retry from SQL Developer.

Minissha Lamba

Minissha Lamba

Oracle: How to convert CLOB to BLOB

FUNCTION clob_to_blob(p_clob IN CLOB) RETURN BLOB IS
v_blob BLOB;
v_offset NUMBER DEFAULT 1;
v_amount NUMBER DEFAULT 4096;
v_offsetwrite NUMBER DEFAULT 1;
v_amountwrite NUMBER;
v_buffer VARCHAR2(4096 CHAR);
BEGIN
dbms_lob.createtemporary(v_blob, TRUE);

Begin
LOOP
dbms_lob.READ(p_clob, v_amount, v_offset, v_buffer);

v_amountwrite := utl_raw.length(utl_raw.cast_to_raw(v_buffer));
dbms_lob.WRITE(v_blob, v_amountwrite, v_offsetwrite, utl_raw.cast_to_raw(v_buffer));

v_offsetwrite := v_offsetwrite + v_amountwrite;

v_offset := v_offset + v_amount;
v_amount := 4096;
END LOOP;
EXCEPTION
WHEN no_data_found THEN
NULL;
End;
RETURN v_blob;
END clob_to_blob;

Oracle: How to convert CLOB to BLOB

FUNCTION clob_to_blob(p_clob IN CLOB) RETURN BLOB IS
v_blob BLOB;
v_offset NUMBER DEFAULT 1;
v_amount NUMBER DEFAULT 4096;
v_offsetwrite NUMBER DEFAULT 1;
v_amountwrite NUMBER;
v_buffer VARCHAR2(4096 CHAR);
BEGIN
dbms_lob.createtemporary(v_blob, TRUE);

Begin
LOOP
dbms_lob.READ(p_clob, v_amount, v_offset, v_buffer);

v_amountwrite := utl_raw.length(utl_raw.cast_to_raw(v_buffer));
dbms_lob.WRITE(v_blob, v_amountwrite, v_offsetwrite, utl_raw.cast_to_raw(v_buffer));

v_offsetwrite := v_offsetwrite + v_amountwrite;

v_offset := v_offset + v_amount;
v_amount := 4096;
END LOOP;
EXCEPTION
WHEN no_data_found THEN
NULL;
End;
RETURN v_blob;
END clob_to_blob;

Oracle: How to convert BLOB to CLOB

FUNCTION blob2clob(p_blob IN BLOB) RETURN CLOB IS
v_clob CLOB;
v_amount NUMBER DEFAULT 2000;
v_offset NUMBER DEFAULT 1;
v_buffer VARCHAR2(32767);
v_length PLS_INTEGER := dbms_lob.getlength(p_blob);

BEGIN
dbms_lob.createtemporary(v_clob, TRUE);

dbms_lob.OPEN(v_clob, dbms_lob.lob_readwrite);

WHILE v_offset <= v_length LOOP
v_buffer := utl_raw.cast_to_varchar2(dbms_lob.substr(p_blob, v_amount, v_offset));

IF length(v_buffer) > 0 THEN
dbms_lob.writeappend(v_clob, length(v_buffer), v_buffer);
END IF;

v_offset := v_offset + v_amount;
EXIT WHEN v_offset > v_length;
END LOOP;

RETURN v_clob;
END blob2clob;


----------------------------------------------------------------
FUNCTION blob_to_clob (blob_in IN BLOB) RETURN CLOB
AS
v_clob CLOB;
v_varchar VARCHAR2(32767);
v_start PLS_INTEGER := 1;
v_buffer PLS_INTEGER := 32767;
BEGIN
DBMS_LOB.CREATETEMPORARY(v_clob, TRUE);

FOR i IN 1..CEIL(DBMS_LOB.GETLENGTH(blob_in) / v_buffer)
LOOP

v_varchar := UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(blob_in, v_buffer, v_start));

DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_varchar), v_varchar);

v_start := v_start + v_buffer;
END LOOP;

RETURN v_clob;

END blob_to_clob;