| recompiling objects after droping and recreating tables [message #37441] |
Thu, 07 February 2002 13:56  |
bhasker
Messages: 5
Registered: February 2002
|
Junior Member |
|
|
I need to drop and recreate tables as part of the
stored procedure. do you guys no any way to recompile
objects as part of stored procedure, because after
i drop the tables, the objects(in my case stored procedures) that depend on these tables get invalid.
I am looking for something which i can use to
automatically compile stored procedures that depend on these tables as part of a stored procedure.
do you guys know any comands or dynamic pl/sql that automatically compiles the objects that depend on these
stored procedures
|
|
|
|
| Re: recompiling objects after droping and recreating tables [message #37444 is a reply to message #37441] |
Thu, 07 February 2002 17:06  |
Suresh Vemulapalli
Messages: 624
Registered: August 2000
|
Senior Member |
|
|
save this script in a file and run from sqlplus
set define off
set scan off
set heading off
set feedback off
set term off
set newpage none
set pagesize 0
spool compile_invalid.sql
SELECT
'alter '||decode(trim(object_type),'PACKAGE BODY','PACKAGE',object_type)||' '||object_name ||' '
||decode(object_type,'PACKAGE BODY','COMPILE BODY','COMPILE')||';'
FROM user_objects
WHERE object_type IN
('PACKAGE','PACKAGE BODY','VIEW','PROCEDURE','TRIGGER','FUNCTION')
AND status='INVALID';
set term on
set feed on
@compile_invalid.sql
spool off
|
|
|
|
| Re: recompiling objects after droping and recreating tables [message #37450 is a reply to message #37441] |
Fri, 08 February 2002 01:52 |
pratap kumar tripathy
Messages: 660
Registered: January 2002
|
Senior Member |
|
|
try this
SET SERVEROUTPUT ON SIZE 100000;
--
DECLARE
--
CURSOR_ID INTEGER;
NUMROWS INTEGER;
--
CURSOR COMPILE_CUR IS
SELECT 'ALTER '|| OBJECT_TYPE||' '||OBJECT_NAME ||' COMPILE' COMMAND_STRING,
OBJECT_TYPE,
SUBSTR(OBJECT_NAME,1,40) OBJECT_NAME
FROM USER_OBJECTS
WHERE STATUS = 'INVALID'
AND OBJECT_TYPE IN ('FUNCTION','PACKAGE','PROCEDURE','TRIGGER','VIEW')
UNION
SELECT 'ALTER PACKAGE '||OBJECT_NAME ||' COMPILE BODY' COMMAND_STRING,
OBJECT_TYPE,
SUBSTR(OBJECT_NAME,1,40) OBJECT_NAME
FROM USER_OBJECTS
WHERE STATUS = 'INVALID'
AND OBJECT_TYPE='PACKAGE BODY'
ORDER BY 2;
--
V_COMMAND VARCHAR2(800);
V_ERR_STR VARCHAR2(200);
--
BEGIN
--
CURSOR_ID := DBMS_SQL.OPEN_CURSOR;
--
FOR TMP_CUR IN COMPILE_CUR
LOOP
BEGIN
V_COMMAND :=TMP_CUR.COMMAND_STRING;
DBMS_SQL.PARSE(CURSOR_ID,V_COMMAND,DBMS_SQL.V7);
NUMROWS:=DBMS_SQL.EXECUTE(CURSOR_ID);
EXCEPTION
WHEN OTHERS THEN
V_ERR_STR:=RPAD(TMP_CUR.OBJECT_TYPE,18)||' '
||RPAD(TMP_CUR.OBJECT_NAME,40)||
' Altered With Compilation Errors.';
DBMS_OUTPUT.PUT_LINE(V_ERR_STR);
END;
END LOOP;
--
DBMS_SQL.CLOSE_CURSOR(CURSOR_ID);
--
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
DBMS_SQL.CLOSE_CURSOR(CURSOR_ID);
--
END;
/
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
