-- In-place upgrade for bug 9836012.

CREATE OR REPLACE PROCEDURE WB_Compile_PLSQL         ( 
                                  p_name              VARCHAR2
                                , p_type              VARCHAR2 := 'PACKAGE' )
AUTHID CURRENT_USER
AS
   p_schema VARCHAR2(32);
   sql_stmt VARCHAR2(2000) := NULL;
   p_security_name VARCHAR2(2000);

BEGIN
   p_security_name := SYS.DBMS_ASSERT.ENQUOTE_NAME(p_name,false);
   p_schema := SYS.DBMS_ASSERT.ENQUOTE_NAME(USER,false);
   IF UPPER( p_type ) NOT IN( 'PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'FUNCTION', 'TRIGGER' ) THEN
      RAISE_APPLICATION_ERROR( -20010,
                              ' Invalid parameter ' || p_type || ' for procedure WB_Compile_PLSQL' ||
                              ' Valid parameters are: PACKAGE, ' ||
                                                     'PACKAGE BODY, ' ||
                                                     'PROCEDURE, ' ||
                                                     'FUNCTION, ' ||
                                                     'TRIGGER' );
   END IF;

   IF UPPER( p_type ) = 'PACKAGE' THEN
      sql_stmt := 'alter package ' || p_schema || '.' || p_security_name || ' compile';

   ELSIF UPPER( p_type ) = 'PACKAGE BODY' THEN
      sql_stmt := 'alter package ' || p_schema || '.' || p_security_name || ' compile BODY';

   ELSIF UPPER( p_type ) = 'PROCEDURE' THEN
      sql_stmt := 'alter procedure ' || p_schema || '.' || p_security_name || ' compile';

   ELSIF UPPER( p_type ) = 'FUNCTION' THEN
      sql_stmt := 'alter function ' || p_schema || '.' || p_security_name || ' compile';

   ELSIF UPPER( p_type ) = 'TRIGGER' THEN
      sql_stmt := 'alter trigger ' || p_schema || '.' || p_security_name || ' compile';
   ELSE
     RAISE_APPLICATION_ERROR( -20010, ' Invalid parameter for procedure Compile_Mapping' );
   END IF;

   EXECUTE IMMEDIATE sql_stmt;
 
END WB_Compile_PLSQL;
/