Thanks for the reply :) Sorry for the confusion the DBMS_UTILTIY.COMPILE_SCHEMA call is wrapped in a BEGIN / END Block. Here's the full script:
DECLARE v_sql VARCHAR2(32000); createstmt VARCHAR2(500); target VARCHAR2(50); BEGIN --COMPILE ALL OBJECTS DBMS_UTILITY.COMPILE_SCHEMA(USER); --RECREATE THE EXISTING VIEWS INCASE THE UNDERLYING TABLE SIGNATURES HAVE CHANGED FOR CUR_REC IN (SELECT v.VIEW_NAME FROM USER_VIEWS v) LOOP BEGIN target := CUR_REC.VIEW_NAME; createstmt := 'CREATE OR REPLACE FORCE VIEW ' || target || ' AS '; EXECUTE IMMEDIATE 'SELECT TEXT FROM USER_VIEWS WHERE VIEW_NAME = '|| '''' || target || '''' INTO v_sql; EXECUTE IMMEDIATE createstmt || v_sql; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,1,255)); END; END LOOP; --COMPILE ONLY THE INVALID OBJECTS --SEVERAL OBJECTS COULD HAVE BECOME --INVALID WHILE RECREATING THE VIEWS. DBMS_UTILITY.COMPILE_SCHEMA(USER,FALSE); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,1,255)); END; / On Fri, May 15, 2009 at 9:05 AM, ddf <orat...@msn.com> wrote: > > > > On May 14, 11:31 am, Dave Hart <david.john.h...@gmail.com> wrote: > > Hi, > > I have a script that has to run in 9i and 10g. At the end of the > > script the DBMS_UTILITY.COMPILE_SCHEMA procedure gets called to > > recompile the schema. > > > > This works in 10g but not in 9i: > > DBMS_UTILITY.COMPILE_SCHEMA(USER); > > > > In 9i I have to use this syntax (add the EXEC) to get it to run and > > then the script won't compile in 10g: > > EXEC DBMS_UTILITY.COMPILE_SCHEMA(USER); > > > > WHERE: > > USER is the current logged in user. > > > > Is there a way to write this write script so that it will work in both > > databases? > > > > Thanks, > > Dave > > I'm lost, apparently, as I cannot get anything to execute in 10g using > the syntax you describe: > > SQL*Plus: Release 10.2.0.3.0 - Production on Fri May 15 08:00:49 2009 > > Copyright (c) 1982, 2006, Oracle. All Rights Reserved. > > idle> connect *****/&&&&&&&&&& > Connected. > SQL> dbms_utility.compile_schema(USER) > SP2-0734: unknown command beginning "dbms_utili..." - rest of line > ignored. > SQL> > > The EXEC instruction is absolutely necessary in 10g to run any package > or procedure outside of PL/SQL. > > You need to post this script so we can see what you've written before > anyone can provide insight into your issue. > > > David Fitzjarrell > > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to Oracle-PLSQL@googlegroups.com To unsubscribe from this group, send email to oracle-plsql-unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en -~----------~----~----~----~------~----~------~--~---
