Recompile Packages in Oracle

Back to: Oracle Tutorials for Beginners and Professionals

Recompile Packages in Oracle with Examples

In this article, I am going to discuss Recompile Packages in Oracle with Examples. Please read our previous article where we discussed Package Components in Oracle.

Recompile Packages in Oracle

Until now we have learned the variable visibility of the packages. We have created a package specification and package body. We will use the same example but we will learn something different which is the impact of changing the package specification and package body.

Sometimes if there are any major changes it shows us to compile the package specification and package body again. We are using the same package and package body p_test.

create or replace package p_test
is
c_var1 constant number:=10;
c_var2 varchar2(100):='welcome';

procedure print;
end;
------------
create or replace package body p_test
is
c_var3 varchar2(100):='hi there'; 
 procedure print
 is 
 c_var4 varchar2(100):='hi';
 begin
 dbms_output.put_line('this variable came from package spec. '||c_var1);
 dbms_output.put_line('this variable came from package spec. '||c_var2);
 dbms_output.put_line('this variable came from package body. '||c_var3);
  dbms_output.put_line('this variable came from print Proc. '||c_var4);
 end;

end;

Note: We can update the package body by compiling the package specification.

Let’s re-create the package body and recreate the entire package body again.

create or replace package body p_test
is
c_var3 varchar2(100):='hi there'; 
 procedure print
 is 
 c_var4 varchar2(100):='hi';
 begin
 dbms_output.put_line('this variable came from package spec. '||c_var1);
 dbms_output.put_line('this variable came from package spec. '||c_var2);
 dbms_output.put_line('this variable came from package body. '||c_var3);
  dbms_output.put_line('this variable came from print Proc. '||c_var4);
 end;
 
 begin
dbms_output.put_line('this is optional');
end;

We already discussed that the Begin is optional in the package body. Now, we are including the begin & end PL/SQL block in the package body. Let’s compile the package body.

So, the package body p_test is compiled now. This Bbegin block will not make any impact on the package specification. Let’s try to execute the package p_test.

execute p_test.print;

The package p_test is executed successfully. We can see the new begin PL/SQL block is also displayed in dbms_output. Now let’s try to change the package specification. Let’s try to add a new variable in the package specification.

create or replace package p_test
is
c_var1 constant number:=10;
c_var2 varchar2(100):='welcome';
p_n number;
procedure print;
end;

We have added a new variable p_n with datatype as a number. Let’s try to execute this package specification.

So, the package specification is now compiled. So, let’s try to execute the package p_test.

execute p_test.print;

This is the same output as the previous compilation. As the changes that we made are not critical so in these types of changes we no need to re-compile the package specification. So, let’s add a major change to the package specification and try to compile the package.

create or replace package p_test
is
c_var1 constant number:=10;
c_var2 varchar2(100):='welcome';
p_n number;
procedure print;
function get_name (p number) return varchar2;
end;

We have added a function that takes as p_number and displays the character. This is the major change because the code of this function needs to be present inside the package body. Let’s try to compile this package.

So, the package specification p_test is now compiled. Let’s try to execute the package.

execute p_test.print;

We can see we got an error which shows package body hr.p_test has an error and could not find the program. In this case, we have to add the function in the package body and then compile the package body again and execute the package to get the result.

Note: Adding a new procedure or function in the package specification will lead to an error if we try to execute the package. So, we have to go to the package body and make changes as well.

The information of the package can be viewed from the table user_objects where object_name we have to mention the package name.

select * from user_objects where object_name=’P_TEST’;

Let’s try to run this query.

We can see two results one is package and the other is package body. We can see the status of the package is valid and the package body is invalid. Whenever the status of any package is invalid, we can recompile the package again. Whenever there are any changes to package specification then recompiling will update the package details to the database. We can view the code for the package in the table user_source. We can query as below.

SELECT * FROM USER_SOURCE WHERE NAME=’P_TEST’ AND TYPE=’PACKAGE’;

We can see the package specification source code here. Let’s go ahead and check the source code for the package body as well.

SELECT * FROM USER_SOURCE WHERE NAME=’P_TEST’ AND TYPE=’PACKAGE BODY’;

We can see the source code about the package body. We can even drop the packages as well.

To drop packages: drop package p_test;

To drop package body: Drop package body p_test;

In the next article, I am going to discuss Overloading Subprograms with Procedures in Oracle with Examples. Here, in this article, I try to explain Recompile Packages in Oracle with Examples. I hope you enjoy this Recompile Packages in Oracle article.