Overwriting a stored procedure that disables or enables the constraints of an Oracle database

Rewritten a hero on the Internet (which was not remembered at the beginning). Writes a stored procedure that disables or enables all constraints on an Oracle database, adding exception control so that an exception can be executed.

– Call procedure:

set Serveroutputon before execution; ----To print out the output
Disable constraint checking for exec manage_user_constraints (' Disable ', true,true,true);
Enable constrained exec manage_user_constraints (' Enable ', true,true,true);

CREATE OR REPLACE PROCEDURE manage_user_constraints (Operation VARCHAR2,
FK BOOLEAN DEFAULT TRUE,
PK BOOLEAN DEFAULT TRUE,
UK BOOLEAN DEFAULT TRUE) is
ST VARCHAR2 (255);
ErrorCode number; --Exception code
ErrorMsg VARCHAR2 (1000); --Exception information
Out_return VARCHAR2 (1000);
Flag VARCHAR2 (10);

CURSOR R is
SELECT table_name, constraint_name
From User_constraints
WHERE constraint_type = ' R ';

CURSOR P is
SELECT table_name, constraint_name
From User_constraints
WHERE constraint_type = ' P ';

CURSOR U is
SELECT table_name, constraint_name
From User_constraints
WHERE constraint_type = ' U ';

BEGIN

Dbms_output.  ENABLE (buffer_size = null); ---do not limit caching
IF UPPER (operation) in (' DROP ', ' DISABLE ') then
IF FK Then
BEGIN
For E in R LOOP
Begin
ST: = ' ALTER TABLE ' | | E.table_name | | "| | Operation | | ‘
CONSTRAINT ' | | UPPER (E.constraint_name);
EXECUTE IMMEDIATE (ST);
Dbms_output. Put_Line (ST);
EXCEPTION
When others then
ErrorCode: = SQLCODE;
ErrorMsg: = SUBSTR (SQLERRM, 1, 200);
Flag: = ' false ';
Out_return: = ' flag= ' | | Flag | | ', errorcode= ' | | ErrorCode | | ', errormsg= ' | | ErrorMsg;
Dbms_output.put_line (Out_return);
Null
End
END LOOP;
END;
END IF;
IF PK Then
BEGIN
For E in R LOOP
Begin
ST: = ' ALTER TABLE ' | | E.table_name | | "| | Operation | | ‘
CONSTRAINT ' | | UPPER (E.constraint_name);
EXECUTE IMMEDIATE (ST);
Dbms_output. Put_Line (ST);
EXCEPTION
When others then
ErrorCode: = SQLCODE;
ErrorMsg: = SUBSTR (SQLERRM, 1, 200);
Flag: = ' false ';
Out_return: = ' flag= ' | | Flag | | ', errorcode= ' | | ErrorCode | | ', errormsg= ' | | ErrorMsg;
Dbms_output.put_line (Out_return);
Null
End
END LOOP;
END;
BEGIN
For E in P LOOP
Begin
ST: = ' ALTER TABLE ' | | E.table_name | | "| | Operation | | ‘
CONSTRAINT ' | | UPPER (E.constraint_name);
Dbms_output. Put_Line (ST);
EXECUTE IMMEDIATE (ST);
EXCEPTION
When others then
ErrorCode: = SQLCODE;
ErrorMsg: = SUBSTR (SQLERRM, 1, 200);
Flag: = ' false ';
Out_return: = ' flag= ' | | Flag | | ', errorcode= ' | | ErrorCode | | ', errormsg= ' | | ErrorMsg;
Dbms_output.put_line (Out_return);
Null
End
END LOOP;
END;
END IF;
IF UK Then
BEGIN
For-E in U LOOP
Begin
ST: = ' ALTER TABLE ' | | E.table_name | | "| | Operation | | ‘
CONSTRAINT ' | | UPPER (E.constraint_name);
EXECUTE IMMEDIATE (ST);
Dbms_output. Put_Line (ST);
EXCEPTION
When others then
ErrorCode: = SQLCODE;
ErrorMsg: = SUBSTR (SQLERRM, 1, 200);
Flag: = ' false ';
Out_return: = ' flag= ' | | Flag | | ', errorcode= ' | | ErrorCode | | ', errormsg= ' | | ErrorMsg;
Dbms_output.put_line (Out_return);
Null
End
END LOOP;
END;
END IF;
elsif UPPER (operation) in (' ENABLE ') then
IF PK Then
BEGIN
For E in P LOOP
Begin
ST: = ' ALTER TABLE ' | | E.table_name | | "| | Operation | | ‘
CONSTRAINT ' | | UPPER (E.constraint_name);
Dbms_output. Put_Line (ST);
EXECUTE IMMEDIATE (ST);
EXCEPTION
When others then
ErrorCode: = SQLCODE;
ErrorMsg: = SUBSTR (SQLERRM, 1, 200);
Flag: = ' false ';
Out_return: = ' flag= ' | | Flag | | ', errorcode= ' | | ErrorCode | | ', errormsg= ' | | ErrorMsg;
Dbms_output.put_line (Out_return);
Null
End
END LOOP;
END;
END IF;
IF FK Then
BEGIN
For E in P LOOP
Begin
ST: = ' ALTER TABLE ' | | E.table_name | | "| | Operation | | ‘
CONSTRAINT ' | | UPPER (E.constraint_name);
Dbms_output. Put_Line (ST);
EXECUTE IMMEDIATE (ST);
EXCEPTION
When others then
ErrorCode: = SQLCODE;
ErrorMsg: = SUBSTR (SQLERRM, 1, 200);
Flag: = ' false ';
Out_return: = ' flag= ' | | Flag | | ', errorcode= ' | | ErrorCode | | ', errormsg= ' | | ErrorMsg;
Dbms_output.put_line (Out_return);
Null
End
END LOOP;
END;
BEGIN
For E in R LOOP
Begin
ST: = ' ALTER TABLE ' | | E.table_name | | "| | Operation | | ‘
CONSTRAINT ' | | UPPER (E.constraint_name);
Dbms_output. Put_Line (ST);
EXECUTE IMMEDIATE (ST);
EXCEPTION
When others then
ErrorCode: = SQLCODE;
ErrorMsg: = SUBSTR (SQLERRM, 1, 200);
Flag: = ' false ';
Out_return: = ' flag= ' | | Flag | | ', errorcode= ' | | ErrorCode | | ', errormsg= ' | | ErrorMsg;
Dbms_output.put_line (Out_return);
Null
End
END LOOP;
END;
END IF;
IF UK Then
BEGIN
For-E in U LOOP
Begin
ST: = ' ALTER TABLE ' | | E.table_name | | "| | Operation | | ‘
CONSTRAINT ' | | UPPER (E.constraint_name);
Dbms_output. Put_Line (ST);
EXECUTE IMMEDIATE (ST);
EXCEPTION
When others then
ErrorCode: = SQLCODE;
ErrorMsg: = SUBSTR (SQLERRM, 1, 200);
Flag: = ' false ';
Out_return: = ' flag= ' | | Flag | | ', errorcode= ' | | ErrorCode | | ', errormsg= ' | | ErrorMsg;
Dbms_output.put_line (Out_return);
Null
End
END LOOP;
END;
END IF;
ELSE
Dbms_output. Put_Line (' The first PARAMETER of the PROCEDURE must be
DROP or ENABLE or DISABLE ');
END IF;
END;
/

Overwriting a stored procedure that disables or enables the constraints of an Oracle database