Home » SQL & PL/SQL » SQL & PL/SQL » recompiling objects after droping and recreating tables
recompiling objects after droping and recreating tables [message #37441] Thu, 07 February 2002 13:56 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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;
/
Re: recompiling objects after droping and recreating tables [message #37472 is a reply to message #37441] Mon, 11 February 2002 04:55 Go to previous message
jagjeet singh
Messages: 16
Registered: October 2001
Junior Member
try this .. its compile the all objects ..except
views

exec dbms_utility.compile_scheema('SCHEEMA_NAME');
Previous Topic: Re: triggers
Next Topic: Select/Order By Question
Goto Forum:
  


Current Time: Sat Aug 15 06:06:50 CDT 2020