Скачать с ютуб | Compilation & Recompilation of Invalid Objects in Oracle | Use of DBMS_UTILITY | Use of DBMS_DDL в хорошем качестве

| 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