Oracle procedure make invalid and valid

 

CREATE OR REPLACE PROCEDURE greetings1
AS
BEGIN
   dbms_output.put_line('Hello World!');

END;
/


col OBJECT_NAME for a20
SELECT object_name, status
FROM dba_objects
WHERE object_name = 'GREETINGS1'; 


OBJECT_NAME          STATUS
-------------------- -------
GREETINGS1           VALID




ALTER SESSION SET PLSQL_WARNINGS = 'ENABLE:ALL','ERROR:5018';


ALTER PROCEDURE greetings1 COMPILE;



col OBJECT_NAME for a20
SELECT object_name, status
FROM dba_objects
WHERE object_name = 'GREETINGS1'; 



SQL> exec greetings1;
BEGIN greetings1; END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00905: object SYS.GREETINGS1 is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


set linesize 300 pagesize 300
column param_name  format a32
column param_value format a32
 
select object_name, param_name, param_value,object_type
from    user_stored_settings
where   object_name = 'GREETINGS1'
-- and     object_type = 'FUNCTION'
/

OBJECT_NAME          PARAM_NAME                       PARAM_VALUE                      OBJECT_TYPE
-------------------- -------------------------------- -------------------------------- ------------
GREETINGS1           plsql_optimize_level             2                                PROCEDURE
GREETINGS1           plsql_code_type                  INTERPRETED                      PROCEDURE
GREETINGS1           plsql_debug                      FALSE                            PROCEDURE
GREETINGS1           nls_length_semantics             BYTE                             PROCEDURE
GREETINGS1           plsql_warnings                   ENABLE:ALL,ERROR:  5018          PROCEDURE
GREETINGS1           plsql_ccflags                                                     PROCEDURE
GREETINGS1           plscope_settings                 IDENTIFIERS:NONE                 PROCEDURE
GREETINGS1           plsql_compiler_flags             INTERPRETED,NON_DEBUG            PROCEDURE

8 rows selected.



To make GREETINGS1 VALID again
============================================
Run:

ALTER SESSION SET PLSQL_WARNINGS = 'ENABLE:ALL';

ALTER PROCEDURE greetings1 COMPILE;

Then:

SELECT object_name, status
FROM dba_objects
WHERE object_name = 'GREETINGS1';

You should get:

GREETINGS1    VALID


SET SERVEROUTPUT ON
EXEC greetings1;

Expected:

Hello World!

PL/SQL procedure successfully completed.






SQL> ALTER PROCEDURE greetings COMPILE;

Warning: Procedure altered with compilation errors.

SQL> 

SELECT object_name, status
FROM user_objects
WHERE object_name = 'GREETINGS'; 

OBJECT_NAME               STATUS
------------------------- -------
GREETINGS                 INVALID



ALTER PROCEDURE greetings1 COMPILE;



set linesize 300 pagesize 300 
column param_name  format a27
column param_value format a27
col OBJECT_NAME for a20
 col owner for a20
select owner,object_name, param_name, param_value,object_type
from    dba_stored_settings
where   object_name = 'GREETINGS1'
-- and     object_type = 'FUNCTION'
/