2 Replies Latest reply: Sep 3, 2014 3:15 AM by LANCERIQUE RSS

    A piece of procedure for partition!!

    LANCERIQUE

      Hi Gurus,

       

      We have a PLSQL package which we use for creating partitions. Same code works in one database whereas not in another. Below is only a part of code which re compiles all invalid objects in the database. I am not able to figure out why does it fails in TEST database and runs perfectly in prod database. Please help with your comments. Thanks in advance.

       

      CODE

      -----------------

      CREATE OR REPLACE PACKAGE "SYSTEM"."PTAPI"

      as

      procedure p(p_text varchar2);

      procedure recompile;

      end ptapi;

       

       

       

       

       

       

      CREATE OR REPLACE PACKAGE BODY "SYSTEM"."PTAPI"

      as

      g_part_date_format varchar2(1000) := 'YYYYMMDD';

       

       

      procedure p(p_text varchar2)

      as

      begin

      dbms_output.put_line(p_text);

      end;

       

       

      procedure recompile

      as

      cursor c_invalid

      is

      select 'alter '||

      decode( o.object_type, 'PACKAGE BODY', 'PACKAGE', object_type )

      || ' '

      || o.owner

      || '.'

      || o.object_name

      || ' compile'

      || decode( object_type, 'PACKAGE BODY', ' BODY', '') statement

      from  dba_objects o,( SELECT MAX(LEVEL) dlevel, object_id

      FROM PUBLIC_DEPENDENCY

      CONNECT BY object_id = PRIOR referenced_object_id

      GROUP BY object_id

      ) d

      where o.status = 'INVALID'

      and   o.object_type in ( 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY', 'VIEW', 'TRIGGER' )

      and   d.object_id   = o.object_id

      order by d.dlevel desc,  o.object_type,  o.object_name

      ;

       

       

      begin

      for r_invalid in c_invalid

      loop

      p(r_invalid.statement);

      execute immediate r_invalid.statement;

      end loop;

      end;

      end ptapi;

       

      TEST DB

      -----------

      DB - 10.1.0.5.0

      OS - SunOS ahc529 5.10 Genric_120011-14 sun4v sparc SUNW.Sun-Fire-T200

       

      ERRORS

      ---------------

      35   36   37  p(r_invalid.statement);

      38  execute immediate r_invalid.statement;

      39  end loop;

      end;

      40   41  end ptapi;

      42  /

       

       

      Warning: Package Body created with compilation errors.

       

       

      SQL> show errors

      No errors.

      SQL> set lines 900

      set pages 900

      select * from dba_errors where name='PTAPI' order by line asc;SQL> SQL>

       

       

      OWNER                          NAME                           TYPE           SEQUENCE       LINE   POSITION

      ------------------------------ ------------------------------ ------------ ---------- ---------- ----------

      TEXT

      ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

      ATTRIBUTE MESSAGE_NUMBER

      --------- --------------

      SYSTEM                         PTAPI                          PACKAGE BODY          2         15          1

      PL/SQL: SQL Statement ignored

      ERROR                  0

       

       

      SYSTEM                         PTAPI                          PACKAGE BODY          1         23          7

      PL/SQL: ORA-00942: table or view does not exist

      ERROR                  0

       

       

      SYSTEM                         PTAPI                          PACKAGE BODY          4         37          1

      PL/SQL: Statement ignored

      ERROR                  0

       

       

      SYSTEM                         PTAPI                          PACKAGE BODY          3         37          3

      PLS-00364: loop index variable 'R_INVALID' use is invalid

      ERROR                364

       

       

      SYSTEM                         PTAPI                          PACKAGE BODY          6         38          1

      PL/SQL: Statement ignored

      ERROR                  0

       

       

      SYSTEM                         PTAPI                          PACKAGE BODY          5         38         19

      PLS-00364: loop index variable 'R_INVALID' use is invalid

      ERROR                364

       

       

       

       

      6 rows selected.

       

      PROD DB

      --------------

      DB Version - 10.1.0.5.0

      OS - SunOS ahc524 5.10 Generic_120011-14 sun4u sparc SUNW,Netra-T12

       

      Code Execution

      -----------------------

       

      SQL> CREATE OR REPLACE PACKAGE "SYSTEM"."PTAPI3"

      as

      procedure p(p_text varchar2);

      procedure recompile;

      end ptapi3;

        2    3    4    5    6

        7  /

       

       

      Package created.

       

       

      SQL> CREATE OR REPLACE PACKAGE BODY "SYSTEM"."PTAPI3"

        2  as

      g_part_date_format varchar2(1000) := 'YYYYMMDD';

        3    4

        5  procedure p(p_text varchar2)

      as

        6    7  begin

        8  dbms_output.put_line(p_text);

        9  end;

      10

      procedure recompile

      11   12  as

      13  cursor c_invalid

      is

      14   15  select 'alter '||

      decode( o.object_type, 'PACKAGE BODY', 'PACKAGE', object_type )

      16   17  || ' '

      18  || o.owner

      19  || '.'

      20  || o.object_name

      21  || ' compile'

      22  || decode( object_type, 'PACKAGE BODY', ' BODY', '') statement

      23  from  dba_objects o,( SELECT MAX(LEVEL) dlevel, object_id

      24  FROM PUBLIC_DEPENDENCY

      25  CONNECT BY object_id = PRIOR referenced_object_id

      26  GROUP BY object_id

      27  ) d

      28  where o.status = 'INVALID'

      29  and   o.object_type in ( 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY', 'VIEW', 'TRIGGER' )

      30  and   d.object_id   = o.object_id

      31  order by d.dlevel desc,  o.object_type,  o.object_name

      ;

      32   33

      34  begin

      35  for r_invalid in c_invalid

      loop

      36   37  p(r_invalid.statement);

      38  execute immediate r_invalid.statement;

      39  end loop;

      40  end;

      41  end ptapi3;

      42  /

       

       

      Package body created.