PL/SQL For Practitioners – #7 Packages

Hi folks,

 

package is a schema object that groups logically related PL/SQL types, variables, constants, subprograms, cursors, and exceptions. A package always has a specification, which declares the public items that can be referenced from outside the package.  If the public items include cursors or subprograms, then the package must also have a body. For example, if you define a pack that only contains variables declaration, you don’t need to declare the body.

The body must define queries for public cursors and code for public subprograms. The body can also declare and define private items that cannot be referenced from outside the package but are necessary for the internal workings of the package. Finally, the body can have an initialization part, whose statements initialize variables and do other one-time setup steps, and an exception-handling part.

Basic Syntax for body and specification

--spec
create or replace PACKAGE <name> AS
  --your code
END <name>;
/
--body
create or replace PACKAGE BODY <name>AS
   --your code
END <name>;
/

 

Example 1

Well, in this example I’m creating a package with spec and body with a procedure and a function, you can check how I did each statement separated in the links below

create or replace PACKAGE PACKAGE2 AS

FUNCTION F_GET_NEW_CITY_ID RETURN NUMBER;
PROCEDURE p_insert_log (p_descr_log varchar2, p_msg_return OUT varchar2);

END PACKAGE2;
/

create or replace
PACKAGE BODY PACKAGE2 AS

FUNCTION F_GET_NEW_CITY_ID RETURN NUMBER AS
   v_new_id number := 0;
   v_is_an_used_id varchar2(1) := 'N';
BEGIN
  
  select max(nvl(id, 0)) + 1 into v_new_id from city;
  
  LOOP
  
    BEGIN
      select 'Y' into v_is_an_used_id from city where id = v_new_id;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        v_is_an_used_id := 'N';
    END;
    
    EXIT WHEN v_is_an_used_id = 'N';
    
    v_new_id := v_new_id +1;
    
  END LOOP;
  
  RETURN v_new_id;
  
END F_GET_NEW_CITY_ID;

PROCEDURE p_insert_log (p_descr_log varchar2,
                        p_msg_return OUT varchar2) AS

   v_new_id NUMBER;
   v_local_except EXCEPTION;
   v_error_msg VARCHAR2(255);
   
   
BEGIN
  
  select nvl(max(id), 0) + 1 into v_new_id from it_tutorial_log;
  
  IF NVL(p_descr_log, 'N') = 'N' THEN
     v_error_msg := 'The description is a required column!';
     raise v_local_except;
  END IF;
     
  insert into it_tutorial_log (id, descr_log, date_log, user_log) values (v_new_id, p_descr_log, sysdate, user);

EXCEPTION
   WHEN v_local_except THEN
       p_msg_return := 'Program: p_insert_log - Error: ' || v_error_msg;
  
END p_insert_log;
  
END PACKAGE2;

 

How to test/execute in an anonymous block

 

Procedure

DECLARE
  P_DESCR_LOG VARCHAR2(200);
  P_MSG_RETURN VARCHAR2(200);
BEGIN
  P_DESCR_LOG := NULL;

  PACKAGE2.P_INSERT_LOG(
    P_DESCR_LOG => P_DESCR_LOG,
    P_MSG_RETURN => P_MSG_RETURN
  );
  DBMS_OUTPUT.PUT_LINE('P_MSG_RETURN = ' || P_MSG_RETURN);
END;

 

Function

DECLARE
  v_Return NUMBER;
BEGIN

  v_Return := PACKAGE2.F_GET_NEW_CITY_ID();
  DBMS_OUTPUT.PUT_LINE('v_Return = ' || v_Return);
END;

 

This post is part of a series about the most common structures in PL/SQL. You can check the rest of them in the link below.

PL/SQL for real world – 8 features that you have to know

 

Please write down in the comments any further questions you may have. I’ll be happy to help you.

About Joel Medeiros

I am an experienced Business and Data Analyst, ETL and PL/SQL with over 13 years of experience. My technical skills are focus on Oracle mainly in developing with tools such as PL/SQL, ODI and RIB.You also can check all my post right here https://ittutorial.org/author/joel-medeiros/Please, feel free to keep in touch by e-mail or Linkedin at: [email protected] | https://www.linkedin.com/in/joel-medeiros/

Leave a Reply

Your email address will not be published. Required fields are marked *