Simple Demonstration of Halo Database Package Functionality

CREATE [ OR REPLACE ] PACKAGE package_name
  [ { AUTHID DEFINER } | { AUTHID CURRENT_USER } ]  -- Related permission settings
  { IS | AS }
  [ declaration; ] ... -- Custom types, constants, variables, cursors, etc. can be declared here 
   [                  
     {                 -- Procedure declaration 
       PROCEDURE proc_name
       ([ argname [ IN | IN OUT | OUT ] argtype [ DEFAULT value ] ... ]) ; 
     } 
     |
     {                 -- Function declaration  
        FUNCTION func_name ([ argname [ IN | IN OUT | OUT ] argtype [ DEFAULT value ] ... ]) 
         RETURN rettype ; 
      } 
   ] ...
 END [ package_name ] ;

CREATE [ OR REPLACE ] PACKAGE BODY package_name
  { IS | AS }
  [ private_declaration; ] ...     -- Private constants, variables, cursors, functions, procedures, etc. can be defined here... not exposed externally for internal Package use only   
  [ 
    {
       PROCEDURE proc_name[ {argname [ IN | IN OUT | OUT ] argtype [ DEFAULT value ] ... }] 
        { IS | AS }
       [ declaration; ] [, ...]
        BEGIN
         statement; [...]
        [ EXCEPTION
           { WHEN exception [OR exception] [...]] THEN statement; }
           [...]
        ]
       END [ proc_name ] ;
    }
    |
    {
      FUNCTION func_name [ argument_list ]
        RETURN rettype [DETERMINISTIC]
        { IS | AS }
       [ declaration; ] [, ...]
        BEGIN
         statement; [...]
        [ EXCEPTION
          { WHEN exception [ OR exception ] [...] THEN statement; }
          [...]
        ]
       END [ func_name ] ;
    }  
    ] ...
 END [ package_name ] ;

DROP PACKAGE package_name;  -- Remove the entire package
DROP PACKAGE BODY package_name; -- Remove the current package body

CREATE OR REPLACE PACKAGE PKG_CONSTANT AS
    C_CODE_SUCCESS  CONSTANT VARCHAR2(10) := 'SUCCESS'; -- Declare constant and assign value
    PROCEDURE PRO_TEST;
END PKG_CONSTANT;                                     
/
CREATE OR REPLACE PACKAGE BODY PKG_CONSTANT AS
PROCEDURE PRO_TEST AS
BEGIN
DBMS_OUTPUT.PUT_LINE('PKG_CONSTANT.C_CODE_SUCCESS: '||PKG_CONSTANT.C_CODE_SUCCESS);
END PRO_TEST;
END PKG_CONSTANT;
/
-- Execution
EXEC PKG_CONSTANT.PRO_TEST; 
-- Constants are not allowed to be modified; attempting to modify will result in an error
BEGIN
PKG_CONSTANT.C_CODE_SUCCESS := 'ERROR';
DBMS_OUTPUT.PUT_LINE('PKG_CONSTANT.C_CODE_SUCCESS: '||PKG_CONSTANT.C_CODE_SUCCESS);
END;
/

CREATE OR REPLACE PACKAGE PKG_VAR AS
    VAR_A   VARCHAR2;   -- Declare variable
    PROCEDURE PRO_TEST;
END PKG_VAR;
/
CREATE OR REPLACE PACKAGE BODY PKG_VAR AS
PROCEDURE PRO_TEST AS
BEGIN
-- Initial assignment
PKG_VAR.VAR_A := 'VAR_TEST';
DBMS_OUTPUT.PUT_LINE('PKG_VAR.VAR_A: '||PKG_VAR.VAR_A);
-- Change variable value again
PKG_VAR.VAR_A := 'VAR_A';
DBMS_OUTPUT.PUT_LINE('PKG_VAR.VAR_A: '||PKG_VAR.VAR_A);
END PRO_TEST;
END PKG_VAR;
/
-- Execution
EXEC PKG_VAR.PRO_TEST;

CREATE TABLE example(manager_id INT, employee_id INT, employee VARCHAR2(30));
INSERT INTO example VALUES(5, 1, 'Nick');
INSERT INTO example VALUES(1, 2, 'Josh');
INSERT INTO example VALUES(2, 3, 'Ali');
INSERT INTO example VALUES(6, 4, 'Joe');
INSERT INTO example VALUES(4, 5, 'Kyle');
CREATE OR REPLACE PACKAGE PKG_CURSOR
AS
TYPE PKG_TYPE_CURSOR IS REF CURSOR;
PROCEDURE PRO_TEST;
END PKG_CURSOR;
/
CREATE OR REPLACE PACKAGE BODY PKG_CURSOR
AS
PROCEDURE PRO_TEST
IS
  cur_cursor PKG_TYPE_CURSOR;
  cur_val example%ROWTYPE;
BEGIN
  OPEN cur_cursor FOR SELECT * FROM example;
  LOOP 
  FETCH cur_cursor INTO cur_val; 
  EXIT WHEN NOT FOUND; 
  DBMS_OUTPUT.PUT_LINE('manager_id: '||cur_val.manager_id||' employee_id: '||cur_val.employee_id||' employee: '||cur_val.employee);
  END LOOP;
  CLOSE cur_cursor;
END PRO_TEST;
END PKG_CURSOR;
/
-- Execution
EXEC PKG_CURSOR.PRO_TEST

CREATE OR REPLACE PACKAGE PKG_TEST AS
  FUNCTION F_TEST( f_a INTEGER , f_b INTEGER ) RETURN INTEGER;
  PROCEDURE PRO_TEST;
END PKG_TEST;  
/
CREATE OR REPLACE PACKAGE BODY PKG_TEST AS
FUNCTION F_TEST( f_a IN INTEGER , f_b IN INTEGER )
RETURN INTEGER
AS
  f_c INTEGER;
BEGIN 
  f_c := f_a + f_b;
  RETURN f_c;
END F_TEST;
PROCEDURE PRO_TEST
AS 
  var_a INTEGER;
BEGIN  
  var_a := PKG_TEST.F_TEST(12,13);
  DBMS_OUTPUT.PUT_line('PKG_TEST.F_TEST(12,13) = '|| var_a); 
END PRO_TEST; 
END PKG_TEST;
/

CREATE OR REPLACE PACKAGE PKG_RECODE_TEST AS
  TYPE RECODE_TYPE IS RECORD(
    DS NUMBER(10, 3),
    DA VARCHAR(2)
    );
  PROCEDURE PRO_TEST;
END PKG_RECODE_TEST;  
/
CREATE OR REPLACE PACKAGE BODY PKG_RECODE_TEST AS
PROCEDURE PRO_TEST
AS 
  recode_a RECODE_TYPE;
BEGIN  
  SELECT 1,'x' INTO recode_a FROM dual;
  DBMS_OUTPUT.PUT_line(recode_a.ds||' '||recode_a.da); 
END PRO_TEST; 
END PKG_RECODE_TEST;
/
-- Execution
EXEC PKG_RECODE_TEST.PRO_TEST;

CREATE OR REPLACE PACKAGE ERRLOG
IS
   no_such_table EXCEPTION;
      PRAGMA EXCEPTION_INIT (no_such_table, -942);
   invalid_table_name EXCEPTION;
      PRAGMA EXCEPTION_INIT (invalid_table_name, -903);
   PROCEDURE PRO_TEST;
END;
/
CREATE OR REPLACE PACKAGE BODY ERRLOG AS
PROCEDURE PRO_TEST
AS 
BEGIN
  RAISE ERRLOG.no_such_table;
  DBMS_OUTPUT.PUT_LINE('ERROR');
EXCEPTION
  WHEN no_such_table THEN
  DBMS_OUTPUT.PUT_LINE('SQLCODE: '||SQLCODE||' SQLERRM: '||SQLERRM); 
END PRO_TEST; 
END ERRLOG;
/
-- Execution
EXEC ERRLOG.PRO_TEST;

\set                         -- View ENABLE_PL_BLOCK parameter
 \set ENABLE_PL_BLOCK on      -- Enable ENABLE_PL_BLOCK