7 Replies Latest reply: Apr 3, 2013 7:22 AM by Jaffee2 RSS

    compiling packages or procedures

    Jaffee2
      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
          do SHOW ERROR after the the compile & BEFORE the SELECT!
          • 2. Re: compiling packages or procedures
            Jaffee2
            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
              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
                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
                  Thanks everyone for your help.
                  • 6. Re: compiling packages or procedures
                    rp0428
                    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
                      The solution worked after using surendra4y code. There were also invlalid packages and/or procedures that were deleted.

                      Thanks surendra4y