This discussion is archived
7 Replies Latest reply: Apr 3, 2013 5:22 AM by Jaffee2 RSS

compiling packages or procedures

Jaffee2 Newbie
Currently Being Moderated
Hello,

I am compiling a package , procedures etc. The query comes back with invalids. I have show error. How do I retrieve more detail information about why the package/procedure/view are invalid?
 

DECLARE 
                 
                v_status       VARCHAR2(10); 
                v_cmd          VARCHAR2(150); 
             
                Cursor rw_objects 
                 IS 
                  Select object_name,object_type,status,owner 
                  From dba_objects 
          WHERE UPPER(object_type) IN ('VIEW','PROCEDURE','FUNCTION','PACKAGE', 'PACKAGE BODY','TRIGGER','SYNONYM') 
          AND owner = UPPER('&&DW_SCHEMA') 
                  AND status = 'INVALID'; 

         BEGIN 
             FOR  t IN rw_objects 
                        
                       LOOP 
                               
                              IF  t.object_type ; IN ('SYNONYM','VIEW','TRIGGER','PROCEDURE','FUNCTION') THEN 
                                  v_cmd  := 'ALTER ' || t.object_type || ' ' || t.owner||'.'||t.object_name || ' COMPILE ';   
                                 execute immediate v_cmd; 
                     ELSIF 
                                 t.object_type ; = ('PACKAGE') THEN                                  
                                 v_cmd  := 'ALTER  PACKAGE ' || ' ' || t.owner||'.'||t.object_name || ' COMPILE PACKAGE';   
                                 execute immediate v_cmd;   

                              ELSIF                                
                                  t.object_type ; = ('PACKAGE BODY') THEN                                  
                                  v_cmd  := 'ALTER PACKAGE ' || ' ' || t.owner||'.'||t.object_name || ' COMPILE BODY';   
                                 execute immediate v_cmd;   
                                         
                                dbms_output.put_line( rpad( t.object_type || ' ' || t.object_name, 50, ' ' ) || ' VALID '); 

                              END IF; 
                END LOOP; 

END; 
/ 

SELECT   object_name, object_type, status                     
               FROM  dba_objects 
               WHERE STATUS = 'INVALID'      
               AND UPPER(object_type) IN ('VIEW','PROCEDURE','FUNCTION','PACKAGE', 'PACKAGE BODY','TRIGGER','SYNONYM') 
               AND owner = UPPER('&&DW_SCHEMA') 
           ORDER by object_type; 

show errors; 
Here are the results.
OBJECT_NAME                                                                OBJECT_TYPE         STATUS                             
--------------------------------------------------------------------------------     ------------------- -------
PM206                                                                           PACKAGE BODY        INVALID
                                                                                
DW_BE_DFACT_TB_REFRESH_HANDLER                            PACKAGE BODY        INVALID
                                                                                
DW_BE_DFACT_TB_INITIAL_HANDLER                               PACKAGE BODY        INVALID
                                                                                
CHART_HANDLER                                                            PACKAGE BODY        INVALID
                                                                                
DW_CRS_HANDLER                                                          PACKAGE BODY        INVALID
                                                                                
DELETE_UPDATE_PROC                                                   PROCEDURE           INVALID
                                                                                
DW_CBS_PROC                                                              PROCEDURE           INVALID
                                                                                
DW_GL_DFACT_VW                                                           VIEW                INVALID
                                                                                

8 rows selected.
No errors.
  • 1. Re: compiling packages or procedures
    sb92075 Guru
    Currently Being Moderated
    do SHOW ERROR after the the compile & BEFORE the SELECT!
  • 2. Re: compiling packages or procedures
    Jaffee2 Newbie
    Currently Being Moderated
    I tried putting show error after the compile and before the select, however, I did not retrieve more detail.

    Thanks.
  • 3. Re: compiling packages or procedures
    sb92075 Guru
    Currently Being Moderated
    too bad COPY & PASTE are broken for you!
      1  DECLARE
      2                  v_status       VARCHAR2(10);
      3                  v_cmd          VARCHAR2(150);
      4                  Cursor rw_objects
      5                   IS
      6                    Select object_name,object_type,status,owner
      7                    From dba_objects
      8            WHERE UPPER(object_type) IN ('VIEW','PROCEDURE','FUNCTION','PACKAGE', 'PACKAGE BODY','TRIGGER','SYNONYM')
      9            AND owner = UPPER('&&DW_SCHEMA')
     10                    AND status = 'INVALID';
     11           BEGIN
     12               FOR  t IN rw_objects
     13                         LOOP
     14                                IF  t.object_type ; IN ('SYNONYM','VIEW','TRIGGER','PROCEDURE','FUNCTION') THEN
     15                                    v_cmd  := 'ALTER ' || t.object_type || ' ' || t.owner||'.'||t.object_name || ' COMPILE ';
     16                                   execute immediate v_cmd;
     17                       ELSIF
     18                                   t.object_type ; = ('PACKAGE') THEN
     19                                   v_cmd  := 'ALTER  PACKAGE ' || ' ' || t.owner||'.'||t.object_name || ' COMPILE PACKAGE';
     20                                   execute immediate v_cmd;
     21                                ELSIF
     22                                    t.object_type ; = ('PACKAGE BODY') THEN
     23                                    v_cmd  := 'ALTER PACKAGE ' || ' ' || t.owner||'.'||t.object_name || ' COMPILE BODY';
     24                                   execute immediate v_cmd;
     25                                  dbms_output.put_line( rpad( t.object_type || ' ' || t.object_name, 50, ' ' ) || ' VALID ');
     26                                END IF;
     27                  END LOOP;
     28* END;
     29  /
    old   9:           AND owner = UPPER('&&DW_SCHEMA')
    new   9:           AND owner = UPPER(' USER1')
                                  IF  t.object_type ; IN ('SYNONYM','VIEW','TRIGGER','PROCEDURE','FUNCTION') THEN
                                                    *
    ERROR at line 14:
    ORA-06550: line 14, column 49:
    PLS-00103: Encountered the symbol ";" when expecting one of the following:
    . ( * @ % & = - + < / > at in is mod remainder not rem then
    <an exponent (**)> <> or != or ~= >= <= <> and or like like2
    like4 likec between || multiset member submultiset
    The symbol ";" was ignored.
    ORA-06550: line 18, column 48:
    PLS-00103: Encountered the symbol ";" when expecting one of the following:
    . ( * @ % & = - + < / > at in is mod remainder not rem then
    <an exponent (**)> <> or != or ~= >= <= <> and or like like2
    like4 likec between || multiset
    ORA-06550: line 22, column 49:
    PLS-00103: Encountered the symbol ";" when expecting one of the following:
    . ( * @ % & = - + < / > at in is mod remainder not rem then
    <an exponent (**)> <> or != or ~= >= <= <> and or like like2
    like4 likec between || multiset
    
    
    SQL> 
  • 4. Re: compiling packages or procedures
    surendra4y Newbie
    Currently Being Moderated
    try now

    DECLARE

    v_status VARCHAR2(10);
    v_cmd VARCHAR2(150);

    Cursor rw_objects
    IS
    Select object_name,object_type,status,owner
    From dba_objects
    WHERE UPPER(object_type) IN ('VIEW','PROCEDURE','FUNCTION','PACKAGE', 'PACKAGE BODY','TRIGGER','SYNONYM')
    AND owner = UPPER('&&DW_SCHEMA')
    AND status = 'INVALID';

    BEGIN
    FOR t IN rw_objects

    LOOP

    IF t.object_type IN ('SYNONYM','VIEW','TRIGGER','PROCEDURE','FUNCTION') THEN
    v_cmd := 'ALTER ' || t.object_type || ' ' || t.owner||'.'||t.object_name || ' COMPILE ';
    execute immediate v_cmd;
    ELSIF
    t.object_type = ('PACKAGE') THEN
    v_cmd := 'ALTER PACKAGE ' || ' ' || t.owner||'.'||t.object_name || ' COMPILE PACKAGE';
    execute immediate v_cmd;

    ELSIF
    t.object_type = ('PACKAGE BODY') THEN
    v_cmd := 'ALTER PACKAGE ' || ' ' || t.owner||'.'||t.object_name || ' COMPILE BODY';
    execute immediate v_cmd;

    dbms_output.put_line( rpad( t.object_type || ' ' || t.object_name, 50, ' ' ) || ' VALID ');

    END IF;
    END LOOP;

    END;
    /
  • 5. Re: compiling packages or procedures
    Jaffee2 Newbie
    Currently Being Moderated
    Thanks everyone for your help.
  • 6. Re: compiling packages or procedures
    rp0428 Guru
    Currently Being Moderated
    What help?

    If you got it working you need to post what the problem was and how you fixed it and give credit to those that were helping you.

    Then mark the thread ANSWERED.
  • 7. Re: compiling packages or procedures
    Jaffee2 Newbie
    Currently Being Moderated
    The solution worked after using surendra4y code. There were also invlalid packages and/or procedures that were deleted.

    Thanks surendra4y

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points