Interesting... thanks for looking into this... I only have a 10g version of Oracle, but one of our QA'ers is having problems running the script... maybe it's an issue with her install.
Thanks again, Dave On Fri, May 15, 2009 at 11:01 AM, ddf <orat...@msn.com> wrote: > > > > On May 15, 9:36 am, David Hart <david.john.h...@gmail.com> wrote: > > 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- Hide quoted text - > > > > - Show quoted text - > > I cannot duplicate your 'results' as the same script, as you've posted > it, functions without error on both 9.2.0.8 and 10.2.0.3.0. > > You'll need to post exactly what you're doing and what error messages > Oracle reports because I can't get Oracle to misbehave. > > > 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 -~----------~----~----~----~------~----~------~--~---
