Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Procedure in package specification

I have a package named save_db_values

I have two procedures named store_records and another one called db_activities. db_activities will be called from my application by passing all values in db_activities I will be calling store_records procedure to do insert and delete.

Do I need to define store_records procedure in package specification? When I did not define store_records in specification I am getting error store_records not declared in this scope.

store_records procedure I do not want to expose and hence I did not add in specification. How can I resolve this issue?

like image 484
Jacob Avatar asked Oct 03 '12 11:10

Jacob


People also ask

What is procedure and package?

A package is a group of related procedures and functions, together with the cursors and variables they use, stored together in the database for continued use as a unit. Similar to standalone procedures and functions, packaged procedures and functions can be called explicitly by applications or users.

What is a package specification?

A package specification establishes which package objects can be referenced from outside of the package. Objects that can be referenced from outside of a package are called the public elements of that package.

Can we create procedure in package without specification Yes No Explain?

Answer: Yes, we can create a package body without package specification.

Where can we see procedure function package code?

You can use user_source or all_source data dictionary view to view the procedure or function code.


1 Answers

If you do not want some procedures to be publicly available you may not to declare them in the package specification. Declare them only in the package body. The cause of the error you are facing is declaration order of the procedures in the package body or lack of forward declaration. For example:

 create or replace package Test_pkg as
  2    procedure Proc1;
  3  end;
  4  /

Package created

create or replace package body Test_pkg as
  2  
  3    procedure proc1 is
  4    begin
  5      proc2;
  6    end;
  7  
  8    procedure Proc2 is
  9    begin
 10      dbms_output.put_line('proc2 is being executed');
 11    end;
 12  
 13  end;
 14  /

Warning: Package body created with compilation errors
Error: PLS-00313: 'PROC2' not declared in this scope

This is happening because we are calling Proc2 which declared later in the package. In this case our choices are:

Declare pro2 before the procedure which calls it

 create or replace package body Test_pkg as
  2  
  3  
  4    procedure Proc2 is
  5    begin
  6      dbms_output.put_line('proc2 is being executed');
  7    end;
  8  
  9    procedure proc1 is
 10    begin
 11      proc2;
 12    end;
 13  
 14  end;
 15  /

Package body created

Use forward declaration.

create or replace package body Test_pkg as
  2  
  3    procedure Proc2;
  4  
  5    procedure proc1 is
  6    begin
  7      proc2;
  8    end;
  9  
 10    procedure Proc2 is
 11    begin
 12      dbms_output.put_line('proc2 is being executed');
 13    end;
 14  
 15  
 16  end;
 17  /

Package body created

SQL> exec test_pkg.Proc1;

proc2 is being executed

PL/SQL procedure successfully completed
like image 137
Nick Krasnov Avatar answered Oct 18 '22 13:10

Nick Krasnov