| Compilation & Recompilation of Invalid Objects in Oracle | Use of DBMS_UTILITY | Use of DBMS_DDL

Описание: Please subscribe my channel and don't forget to comment & share the videos

Scripts Are given below
---------------------------------------
Compilation/Recompilation of Invalid Objects in Oracle
-----------------------------------------------------------------------------------------
CREATE TABLE TAB1(COL1 NUMBER(4));

CREATE OR REPLACE PROCEDURE PRC_INSERT_RECORD(P_VALUE IN NUMBER)AS
BEGIN
INSERT INTO TAB1(COL1) VALUES (P_VALUE);
END;
/
CREATE OR REPLACE FUNCTION FN_SUM_OF_SQUARE RETURN NUMBER AS
V_SUM_OF_SQUARE NUMBER DEFAULT 0;
BEGIN
FOR I IN (SELECT COL1 FROM TAB1) LOOP
V_SUM_OF_SQUARE := V_SUM_OF_SQUARE + POWER(I.COL1,2);
END LOOP;
RETURN V_SUM_OF_SQUARE ;
EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END;
/
CREATE OR REPLACE PROCEDURE PRC_DELETE_RECORD(P_VALUE IN NUMBER)AS
BEGIN
DELETE FROM TAB1 WHERE COL1=P_VALUE;
END;
/
SET SERVEROUTPUT ON;
DECLARE
V_SUM_OF_SQUARE NUMBER;
BEGIN
PRC_INSERT_RECORD(1);
PRC_INSERT_RECORD(2);
PRC_INSERT_RECORD(3);

V_SUM_OF_SQUARE:=FN_SUM_OF_SQUARE;
DBMS_OUTPUT.PUT_LINE('Sum of Square of each row is :'||V_SUM_OF_SQUARE);

PRC_DELETE_RECORD(3);

V_SUM_OF_SQUARE:=FN_SUM_OF_SQUARE;
DBMS_OUTPUT.PUT_LINE('Sum of Square of each row is :'||V_SUM_OF_SQUARE);

END;
/
SELECT OBJECT_NAME,object_type,STATUS FROM USER_OBJECTS WHERE OBJECT_TYPE NOT IN ('TABLE');
DROP TABLE TAB1;

CREATE TABLE TAB1(COL1 NUMBER(4));

Method-1
----------
ALTER PROCEDURE PRC_INSERT_RECORD COMPILE;
ALTER FUNCTION FN_SUM_OF_SQUARE COMPILE;
ALTER PROCEDURE PRC_DELETE_RECORD COMPILE;

Method-2
----------
SELECT 'ALTER '|| OBJECT_TYPE ||' '|| OBJECT_NAME ||' COMPILE;'Invalid_Objects
FROM USER_OBJECTS
WHERE OBJECT_TYPE NOT IN ('TABLE')
AND STATUS='INVALID';

ALTER PROCEDURE PRC_INSERT_RECORD COMPILE;
ALTER FUNCTION FN_SUM_OF_SQUARE COMPILE;
ALTER PROCEDURE PRC_DELETE_RECORD COMPILE;

Method-3
----------
EXEC DBMS_UTILITY.compile_schema('USER1');

Method-4
----------
Syntax
--------
EXEC DBMS_DDL.alter_compile("OBJECT TYPE", "SCHEMA NAME", "OBJECT NAME" );

Ex-1
-------
EXEC DBMS_DDL.alter_compile('PROCEDURE', 'USER1', 'PRC_INSERT_RECORD');


SELECT 'EXEC DBMS_DDL.alter_compile('||CHR(39)||OBJECT_TYPE
||CHR(39)||','||CHR(39)||USER
||CHR(39)||','||CHR(39)||OBJECT_NAME
||CHR(39)||');' Invalid_Objects
FROM USER_OBJECTS
WHERE OBJECT_TYPE NOT IN ('TABLE')
AND STATUS='INVALID';

EXEC DBMS_DDL.alter_compile('PROCEDURE','USER1','PRC_INSERT_RECORD');
EXEC DBMS_DDL.alter_compile('FUNCTION','USER1','FN_SUM_OF_SQUARE');
EXEC DBMS_DDL.alter_compile('PROCEDURE','USER1','PRC_DELETE_RECORD');

LIKE THE FACEBOOK PAGE -   / oracle-tutorials-108483330680405  
FOLLOW ON Instagram -   / nihar8308  
FOLLOW ON LinkedIn - https://www.linkedin.com/company/orac...
FOLLOW ON TWITTER -   / niharra59094760  

Email Id: [email protected]
Phone No- 8018319781

#Compilation_Invalid_Objects
#Oracle_SQL_PLSQL_Concept
#DBMS_UTIL
#DBMS_DDL