• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 622
  • Last Modified:

Compiling Oracle objects in right order

Hi

I have a scenario like i have around 100 invalid objects in my system and i need to recompile them in one shot.
Is there any method by which this can be done with a condition that objects should be recompiled in a manner that..compilation of one object should not invalidate any other object in my database.

0
suhinrasheed
Asked:
suhinrasheed
  • 5
  • 4
  • 2
  • +2
1 Solution
 
prasanthi_kCommented:
Here is a script that compiles all invalid objects...
If there are any links between 2 objects, then 1st object can invalidate the second object. Otherwise validating an object will not make any other object invalid.

set heading off
set feedback off
spool x.dat
select 'alter ' || decode(object_type,'PACKAGE
BODY','PACKAGE',object_type)
|| ' ' || object_name || ' compile '  ||
decode(object_type,'PACKAGE BODY',' body;',';')  from user_objects
where object_type in ('FUNCTION','PACKAGE','PACKAGE
BODY','PROCEDURE','TRIGGER','VIEW')
and status = 'INVALID'
order by object_type , object_name;
spool off
 
set heading on
set feedback on
@x.dat  

0
 
suhinrasheedAuthor Commented:
What did yu mean by anylinks between 2 objects?
0
 
sonicefuCommented:

SET SERVEROUTPUT ON SIZE 1000000
 
BEGIN
   FOR cur_rec IN (SELECT   owner, object_name, object_type,
                            DECODE (object_type,
                                    'PACKAGE', 1,
                                    'PACKAGE BODY', 2,
                                    3
                                   ) AS recompile_order
                       FROM dba_objects
                      WHERE object_type IN
                               ('PACKAGE',
                                'PACKAGE BODY',
                                'FUNCTION',
                                'PROCEDURE'
                               )
                        AND status != 'VALID'
                   ORDER BY 4)
   LOOP
      BEGIN
         IF cur_rec.object_type ('FUNCTION', 'PROCEDURE')
         THEN
            EXECUTE IMMEDIATE    'ALTER '
                              || cur_rec.object_type
                              || ' "'
                              || cur_rec.owner
                              || '"."'
                              || cur_rec.object_name
                              || '" COMPILE';
         ELSIF cur_rec.object_type = 'PACKAGE'
         THEN
            EXECUTE IMMEDIATE    'ALTER '
                              || cur_rec.object_type
                              || ' "'
                              || cur_rec.owner
                              || '"."'
                              || cur_rec.object_name
                              || '" COMPILE';
         ELSE
            EXECUTE IMMEDIATE    'ALTER PACKAGE "'
                              || cur_rec.owner
                              || '"."'
                              || cur_rec.object_name
                              || '" COMPILE BODY';
         END IF;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line (   cur_rec.object_type
                                  || ' : '
                                  || cur_rec.owner
                                  || ' : '
                                  || cur_rec.object_name
                                 );
      END;
   END LOOP;
END;
/

Open in new window

0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
prasanthi_kCommented:
Suppose 1 procedure calls another procedure in its code.
Suppose 2nd procedure has some problem (for eg: column names written there are modified later in the table) and is not compiled, then automatically the 1st procedure is also invalidated.
0
 
suhinrasheedAuthor Commented:
Prashanth ..so i would prefer a script which takes care of all such scenarios you mentioned before.

That is my if my recompilation script is validating package 2 and during that recopilation of package 2..package 1 which was recompiled before becomes invalidated..i want my script to handle such a scenario and validate package1

So the compilation script should work in such a manner that objects are validated in the corect order
0
 
prasanthi_kCommented:
As far as I know such a script is not possible...

See these scenarios for example :

1. Package-1 has a column name 'Employee_name' in its code from table 'Employee' and is a valid package. On the object 'Employee' the column name is altered as 'Emp_name' instead of 'Employee_name'

Now Package-1 becomes 'INVALID'. whether it is compiled before table 'Employee' or after that. It can be compiled properly only when the column name in its code is changed accordingly as in the table... (This is just an example, the problem behind the object not getting validated can be anything that needs to be found...)

2. Package-2 calls a function in 'Package-1'. Now as 'Package-1' is INVALID, in what ever order it is compiled 'Package-2' should become 'INVALID' as it has got a problem. So irrespective of the order, the Invalid packages cannot be made 'VALID' unless the issue with them is corrected.
0
 
suhinrasheedAuthor Commented:
but i have seen scenarios in whch say i had to recompile a package and once i recompile it some of the dependent objects becomes invalid,athough these dependent objects are recompiled and validated automatically by oracle the next time they are invoked...My qstn is it not possible to do that recompilation of all dependent objects the same time the base object is recompiled
0
 
prasanthi_kCommented:
What is the current method you are following to compile the package?
Try following the above given scripts that will compile all the objects at a shot...
0
 
suhinrasheedAuthor Commented:
Prashanth will the above script ensure that the objects are recompiled in the correct manner
0
 
suhinrasheedAuthor Commented:
Sonice can you give me some brief witten explanation what your script exactly does.. i mean part by part
0
 
jwittenmCommented:
Have you used UTLRP?
0
 
sonicefuCommented:
my code gets invalid package, package body, functions and procedure names and then compile them all in the following order
1. PACKAGE
2. PACKAGE BODY
3. Functions and procedures
0
 
Computer101Commented:
Forced accept.

Computer101
EE Admin
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 5
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now