Script that uses DBMS_UTILITY.COMPILE_SCHEMA works in 10g, but crashes in 9i

[Dave Hart]

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

[ddf]

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

[David Hart]

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

;

/

[ddf]

> > 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

[David Hart]

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