Identifying Invalid Objects SELECT owner, object_type, object_name, status FROM dba_objects WHERE status = ‘INVALID’ ORDER BY owner, object_type, object_name; Manual Approach ALTER PACKAGE my_package COMPILE; ALTER PACKAGE my_package COMPILE BODY; ALTER PROCEDURE my_procedure COMPILE; ALTER FUNCTION my_function COMPILE; ALTER TRIGGER my_trigger COMPILE; ALTER VIEW my_view COMPILE; DBMS_DDL EXEC DBMS_DDL.alter_compile(‘PACKAGE’, ‘MY_SCHEMA’, ‘MY_PACKAGE’); EXEC DBMS_DDL.alter_compile(‘PACKAGE BODY’,’MY_SCHEMA’,’MY_PACKAGE’); EXEC [...]
Archive for the ‘Procedures’ Category
25 Apr
Oracle Supplied Packages
DBMS_SQL Using DBMS_SQL, one can write stored procedures that use dynamic SQL. It can issue data definition language statements in PLSQL CREATE OR REPLACE PROCEDURE delete_all_rows (tab_name IN VARCHAR2, rows_del OUT NUMBER) IS cursor_name : = DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE ( cursor_name, ‘DELETE FROM ‘ ||tab_name, DBMS_SQL.NATIVE); rows_del := DBMS_SQL.EXECUTE(cursor_name); [...]
