Problems with compilation of a large view

[Lukasz Indeka]

Hi,

What's the difference between:
1. opening the view code in the SQL Window and pressing F8 button
vs. opening the Compile Invalid Objects window and either
2. pressing F8 (let's ignore other invalid objects for now) or
3. selecting the invalid object and pressing Recompile from the dropdown menu?

Why I'm asking?
I have a large (1900 lines and 150 kB of code) view with complex dependency structure. When I choose the 2nd option, the object is still marked as invalid but without any error message in the bottom part of the window. With 3rd option, I'm getting ORA-00600 [KGL-heap-size-exceeded], [0x08A2C3830], [6], [603940752], [], [], [], [], [], [], [], [] on the popup window. Only the 1st option works, and the view is eventually compiled.

 

[Marco Kalter]

Opening the View code in the SQL Window and pressing F8 will send the "create or replace view " code from the SQL Window editor to the server.

The Invalid Objects tool will send a simple "alter view compile" command to the server.

By definition the ORA-00600 error seems to be an Oracle Bug, because it typically indicates that the server process for your session has died.

 

[CoffeeK1d]

I have run into multiple compile bugs and ultimately settled on calling DBMS_UTILITY.COMPILE_SCHEMA and then loop through invalid objects to execute the alter compile. This process addressed our main issue with being unable to recompile procs using macros even when they didn't change and were completely valid.

CREATE OR REPLACE PROCEDURE FOO.SP_RECOMPILE_OBJECTS
AS
BEGIN
    BEGIN
        DBMS_UTILITY.COMPILE_SCHEMA(schema => 'VAL', compile_all => FALSE);
        DBMS_UTILITY.COMPILE_SCHEMA(schema => 'APP', compile_all => TRUE);
        DBMS_UTILITY.COMPILE_SCHEMA(schema => 'CC', compile_all => TRUE);
    END;

    BEGIN
        FOR cur_rec IN (
            SELECT
                owner
               ,object_name
               ,object_type
            FROM dba_objects
            WHERE status = 'INVALID'
              AND owner IN ('CC')
              AND object_type = 'PROCEDURE'
            ORDER BY object_type
                ,owner
                ,object_name
        )
        LOOP
            BEGIN
                EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type || ' ' || cur_rec.owner || '.' || cur_rec.object_name || ' COMPILE';
            EXCEPTION
                WHEN OTHERS THEN
                    DBMS_OUTPUT.put_line('EXCEPTION : ' || cur_rec.owner || ' : ' || cur_rec.object_name);
            END;
        END LOOP;
    END;

END SP_RECOMPILE_OBJECTS;
/