Re: Recompiling invalid objects in PL/SQL

hpuxrac wrote:
> Recommending people run utlrp.sql to fix invalid application objects is
> not just bad advice, it is terrible advice.
>
> It demonstrates a poor understanding of how to administer oracle based
> applications in my opinion. Posting this kind of advice by someone who
> probably knows better is sad.
>
> Many organizations monitor invalid objects in important application
> schema's and recompile them as needed ... just within the schema
> affected ... not all schema's.
>
> It is possible to design many if not most oracle based applications so
> that no object goes invalid in an application schema. In production I
> don't expect any in my environment to go invalid and I will report and
> investigate any that do go invalid.
>
> TUSC provided many years ago a procedure that I still have in my
> organizations application schemas.
>
> CREATE OR REPLACE PROCEDURE RECOMPILE_ALL_INVALID IS
> --
> --
> ***************************************************************************
> -- File: 5_20.sql
> --
> -- Developed By TUSC
> --
> -- Disclaimer: Neither Osborne/McGraw-Hill, TUSC, nor the author
> warrant
> -- that this source code is error-free. If any errors are
> -- found in this source code, please report them to TUSC at
> -- (630)960-2909 ext 1011 or trezzoj_at_tusc.com.
> --
> ***************************************************************************
> -- Compiles all objects under the current schema by executing this
> -- procedure once.
> -- The schema creating this procedure must be granted SELECT privilege
> -- directly to the USER_OBJECTS view (not through a role).
> --
> -- Fetches INVALID stored PL/SQL program units
> CURSOR cur_objects_invalid IS
> SELECT object_id, object_name, object_type
> FROM user_objects
> WHERE status = 'INVALID'
> AND object_type IN ('PACKAGE', 'PACKAGE BODY',
> 'FUNCTION', 'PROCEDURE', 'TRIGGER','VIEW')
> ORDER BY object_type, object_name;
> -- Queries PL/SQL program unit compiled to ensure it was successful
> CURSOR cur_objects_valid (p_object_id_num NUMBER) IS
> SELECT 'FOUND'
> FROM user_objects
> WHERE STATUS = 'VALID'
> AND object_id = p_object_id_num;
> -- Stores PL/SQL program units that failed compiled
> TYPE lv_invalid_tab IS TABLE OF cur_objects_invalid%ROWTYPE
> INDEX BY BINARY_INTEGER;
> lv_invalid_tab_rec lv_invalid_tab;
> lv_count_compiled_num PLS_INTEGER; -- compiled counter
> lv_column_valid_txt VARCHAR2(5);
> lv_exec_cursor_num PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
> lv_sql_statement_txt VARCHAR2(200);
> lv_object_count_num PLS_INTEGER := 0; -- VALID counter
> BEGIN
> DBMS_OUTPUT.PUT_LINE('Starting Re-Compilation of Objects');
> DBMS_OUTPUT.PUT_LINE('------------------------------------------');
> LOOP
> -- set to 0 to determine if any program units compiled this loop
> lv_count_compiled_num := 0;
> FOR cur_objects_invalid_rec IN cur_objects_invalid LOOP
> -- Make sure PL/SQL program unit was not already
> -- unsuccessfully compiled. If it is in the PL/SQL table,
> -- the program unit previously failed and is skipped.
> IF NOT lv_invalid_tab_rec.
> EXISTS(cur_objects_invalid_rec.object_id) THEN
> -- Builds COMPILE DDL statements
> IF cur_objects_invalid_rec.object_type =
> 'PACKAGE BODY' THEN
> lv_sql_statement_txt := 'ALTER PACKAGE ' ||
> cur_objects_invalid_rec.object_name ||
> ' COMPILE BODY';
> ELSE
> lv_sql_statement_txt := 'ALTER ' ||
> cur_objects_invalid_rec.object_type ||
> ' ' || cur_objects_invalid_rec.object_name ||
> ' COMPILE';
> END IF;
> -- The DBMS_SQL.PARSE executes a DDL statement, therefore
> -- DBMS_SQL.EXECUTE is not required. Prior to Oracle8, if
> -- the DBMS_SQL.PARSE command resulted in an Oracle error,
> -- no error was returned to the PL/SQL calling code. After
> -- Oracle8, an error is passed back, therefore, this call
> -- is in a nested PL/SQL block to bypass the error, the
> -- query to USER_OBJECTS for the object being VALID tells
> -- this program if the compiled succeeded or failed.
> BEGIN
> lv_object_count_num := lv_object_count_num + 1;
> DBMS_SQL.PARSE(lv_exec_cursor_num,
> lv_sql_statement_txt, DBMS_SQL.NATIVE);
> EXCEPTION
> WHEN OTHERS THEN
> NULL;
> END;
> -- If object VALID, compile successful, otherwise failed.
> OPEN cur_objects_valid(cur_objects_invalid_rec.object_id);
> FETCH cur_objects_valid INTO lv_column_valid_txt;
> IF CUR_OBJECTS_VALID%ROWCOUNT > 0 THEN
> -- Display Success and exit loop
> DBMS_OUTPUT.PUT_LINE('Object Compilation: ' ||
> cur_objects_invalid_rec.object_type ||
> ' - ' || cur_objects_invalid_rec.object_name ||
> ' SUCCEEDED');
> lv_count_compiled_num := lv_count_compiled_num + 1;
> CLOSE cur_objects_valid;
> EXIT;
> ELSE
> -- Display failure and add to PL/SQL table
> DBMS_OUTPUT.PUT_LINE('Object Compilation: ' ||
> cur_objects_invalid_rec.object_type ||
> ' - ' || cur_objects_invalid_rec.object_name ||
> ' FAILED');
> lv_invalid_tab_rec(cur_objects_invalid_rec.object_id).
> object_name := cur_objects_invalid_rec.object_name;
> lv_invalid_tab_rec(cur_objects_invalid_rec.object_id).
> object_type := cur_objects_invalid_rec.object_type;
> CLOSE cur_objects_valid;
> END IF;
> END IF;
> END LOOP;
> -- When no more INVALID PL/SQL program units exist that have not
> -- been attempted to be compiled in this program unit, exit.
> IF lv_count_compiled_num = 0 THEN
> EXIT;
> END IF;
> END LOOP;
> -- Displays the fact that no INVALID PL/SQL program units found
> IF lv_object_count_num = 0 THEN
> DBMS_OUTPUT.PUT_LINE('No Objects to Re-Compile - All VALID.');
> END IF;
> DBMS_OUTPUT.PUT_LINE('------------------------------------------');
> DBMS_OUTPUT.PUT_LINE('Re-Compilation of Objects Complete');
> DBMS_SQL.CLOSE_CURSOR(lv_exec_cursor_num);
> EXCEPTION
> WHEN OTHERS THEN
> DBMS_OUTPUT.PUT_LINE('---------------------------------------');
> DBMS_OUTPUT.PUT_LINE('Re-Compilation Aborted.');
> DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM, 1, 100));
> -- Closes any cursor left open upon an error
> IF DBMS_SQL.IS_OPEN(lv_exec_cursor_num) THEN
> DBMS_SQL.CLOSE_CURSOR(lv_exec_cursor_num);
> END IF;
> IF cur_objects_valid%ISOPEN THEN
> CLOSE cur_objects_valid;
> END IF;
> END RECOMPILE_ALL_INVALID;
> /
>
> But I would recommend monitoring and investigating application invalid
> objects as well.

You might want to reread the OP's actual question. It did not relate to a single schema or an ongoing process.

You might also noted the reference to UTL_RECOMP.

Why reinvent the wheel unless you are being paid by the hour?

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)