Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

EnterpriseDB: Packaged Variables
Oracle Tips by Burleson
 

Packages offer three varieties of variable:  local, public and private. 

* Local – Local variables are variables declared within an anonymous block or within a procedure or function.  The scope, or lifetime, of a local variable is the scope of the block.  When a block completes, the local variable is removed.  A local variable cannot be referenced from outside of its scope.  That means that a calling program cannot reference a variable within a called procedure.  Stand-alone procedures and functions can only contain local variables.

* Public – A public variable is declared within a package specification.  A public variable is also referred to as a global session variable because it is available globally to any process in the same session.  Any procedure, function or external application that is connected to the database can change the value of a public variable.   Public variables are available only in packages, not in standalone procedures or functions.

* Private – A private variable, also called protected in some languages, is declared within a package body.  Private variables are accessible by any procedure or function within that package.  A private variable is a package global variable.  Private variables are available only in packages, not in standalone procedures or functions.

Overloading

Overloading is the ability to declare multiple procedures and functions with the same name but different parameter signatures.  For example, the TO_CHAR function accepts DATE or NUMERIC input.  That is an overloaded function.

In SPL, you may overload packaged procedures and functions and standalone functions but not standalone procedures.   I talk about this topic in Chapter 8.

Declaring a Package

Below is an example showing the creation of a package specification and a package body.  The specification example will show most of the topics discussed above including the definition of a public record type, public table type, public scalar variable, public ref cursor, public procedure and a public function.  I will step through the specification line by line and will continue this example by creating a package body and calling the code.

CREATE OR REPLACE PACKAGE sample_pkg
AS 

  -- Step 1

  TYPE r_data IS RECORD (
    text VARCHAR2(25),
    text_length VARCHAR2(50) ); 

  -- Step 2

  TYPE a_table IS TABLE OF r_data; 

  -- Step 3

  v_table a_table; 

  -- Step 4

  g_global_empno emp.empno%TYPE; 

  --Step 5, Public constants are not currently supported

  --gc_global_raise_pct CONSTANT NUMBER := 1.03;

  -- Step 6

  TYPE c_ref IS REF CURSOR; 

  -- Step 7

  v_ref c_ref; 

  -- Step 8

  PROCEDURE public_proc(
          p_in_char IN VARCHAR2,
          p_data OUT SYS_REFCURSOR ); 

  -- Step 9

  FUNCTION public_func(
          p_in_char IN VARCHAR2 )
    RETURN SYS_REFCURSOR;

  -- Step 10

  PROCEDURE display_data( p_data IN VARCHAR2 ); 

  -- Step 11

  PROCEDURE display_data( p_data IN DATE ); 

  -- Step 12

  PROCEDURE display_data( p_data IN NUMBER ); 

END;

*

edb_chap_3_pkg_spc_complete.sql 

CREATE OR REPLACE PACKAGE sample_pkg
AS

  -- Step 1

  TYPE r_data IS RECORD (
    text VARCHAR2(25),
    text_length VARCHAR2(50) ); 

  -- Step 2

  TYPE a_table IS TABLE OF r_data; 

  -- Step 3

  --v_table a_table; 

  -- Step 4

  g_global_empno emp.empno%TYPE; 

  --Step 5, Public constants are not currently supported

  --gc_global_raise_pct CONSTANT NUMBER := 1.03; 

  -- Step 6

  TYPE c_ref IS REF CURSOR; 

  -- Step 7

  v_ref c_ref; 

  -- Step 8

  PROCEDURE public_proc(
          p_in_char IN VARCHAR2,
          p_data OUT SYS_REFCURSOR ); 

  -- Step 9

  FUNCTION public_func(
          p_in_char IN VARCHAR2 )
    RETURN SYS_REFCURSOR;

  -- Step 10

  PROCEDURE display_data( p_data IN VARCHAR2 ); 

  -- Step 11

  PROCEDURE display_data( p_data IN DATE ); 

  -- Step 12

  PROCEDURE display_data( p_data IN NUMBER );

END;

The complete package specification is available in the Code Depot at:  edb_chap_3_pkg_body_complete.sql

In Step 1, I declare a record type.  This is a record type just as I declared in the section on records types.  Nothing special here.

Step 2 declares a table type, a_table, of r_data, the record type declared in Step 1.

Step 3 creates a variable, v_table, of the newly created type, a_table.  This step is commented out.  The variable will instead be declared in the package body so that it will be a protected variable.

Step 4 creates a scalar variable of the same type as the empno column in the emp table.  Remember that there are three types of variables: composites, collections and scalar types.  Scalars are the types like varchar2, number and date.

Step 5 is a commented out declaration for a global constant.  PL/SQL does support global constants but SPL does not.  I will talk more about this in the chapters on programming for Oracle compatibility.

Step 6 creates a public, weakly typed ref cursor and step 7 creates a variable of the type.

Step 8 defines a public procedure, public_proc.  Public_proc has two parameters, a varchar2 in parameter and a ref cursor out parameter.

Step 9 defines a public function.  This declaration is much like the above public procedure but the ref cursor will be returned as a return value instead of an OUT parameter.

Steps 10, 11 and 12 all create a procedure called display_data.  This is an overloaded procedure.  Each version of the procedure has a different set of parameter types.

Below is the package body for the spec above. 

CREATE OR REPLACE PACKAGE BODY sample_pkg
AS 

  -- Step 1

  v_table a_table; 

  --Step 2, Private constants are not currently supported

  --gc_global_raise_pct CONSTANT NUMBER := 1.03; 

  -- Step 3

  PROCEDURE a_private_proc
  AS
  BEGIN
    NULL;
  END; 

  -- Step 4

  PROCEDURE public_proc(
          p_in_char IN VARCHAR2,
          p_data OUT SYS_REFCURSOR )
  AS
  BEGIN
    NULL;

 
END; 

  -- Step 5

  FUNCTION public_func(
          p_in_char IN VARCHAR2 )
    RETURN SYS_REFCURSOR
  AS
  BEGIN
    RETURN NULL;
  END;

  -- Step 6

  PROCEDURE display_data( p_data IN VARCHAR2 )
  AS
  BEGIN

   
NULL;
  END;

  -- Step 7

  PROCEDURE display_data( p_data IN DATE )
  AS
  BEGIN
    NULL;
  END;

  -- Step 8

  PROCEDURE display_data( p_data IN NUMBER )
  AS
  BEGIN
    NULL;

 
END;

  -- Step 9

  PROCEDURE init
  AS
  BEGIN
   NULL;
  END; 

-- Step 10

BEGIN
 
NULL;
END;

This is the minimal example of the package body, for the package specification above, that will successfully compile.  Rather than repeat the full listing repeatedly here in the book, you can get the complete script from the Code Depot.  The complete script is available at: edb_chap_3_pkg_body_complete.sql 

* edb_chap_3_pkg_body_complete.sql

CREATE OR REPLACE PACKAGE BODY sample_pkg

AS

  -- Step 1

  v_table a_table; 

  --Step 2, Private constants are not currently supported

  --gc_global_raise_pct CONSTANT NUMBER := 1.03; 

  -- Step 3

  PROCEDURE a_private_proc
  AS

    -- Step 3a

    v_output_string VARCHAR2(32000) := 'Debug: ';
 
BEGIN 

    -- Step 3b

    -- If the private variable v_table has records

    -- Loop through them and display the values

    -- Else just say that it's null

    IF v_table.COUNT > 0
    THEN
      FOR i IN 1..v_table.COUNT

      LOOP

        v_output_string := v_output_string ||
                '  v_table(' || TO_CHAR(i) ||
                '):' || nvl(v_table(i).txt, 'NA') || ', ' ||
                nvl(v_table(i).txt_length,'NA' );
      END LOOP;
    ELSE
      v_output_string := v_output_string ||

           
   '  v_table is empty';
    END IF;
 

    -- Step 3c

    -- If g_global_empno has a value, display it

    -- Else just say it's null

    IF g_global_empno IS NOT NULL
    THEN
        v_output_string := v_output_string ||
              ', g_global_empno is: ' || TO_CHAR(g_global_empno);
    ELSE
        v_output_string := v_output_string ||
              ', g_global_empno is NULL';
    END IF;

    -- Step 3d

    -- If the public cursor v_ref has been opened,

    -- show how many records have been fetched

    -- Else just say that it is not open

    IF v_ref%ISOPEN
    THEN   
        v_output_string := v_output_string ||
             ', Cursor v_ref is open and has fetched ' ||
              to_Char(v_ref%ROWCOUNT) || ' rows.';
    ELSE
        v_output_string := v_output_string ||
              ', Cursor v_ref is not open.';
    END IF; 

    -- Step 3e

    dbms_output.put_line( v_output_string ); 

  END;

  -- Step 4

  PROCEDURE public_proc(
          p_in_char IN VARCHAR2,
          p_empno IN emp.empno%TYPE,
          p_data OUT SYS_REFCURSOR )
  AS
  BEGIN

    OPEN p_data FOR p_in_char USING p_empno;

    a_private_proc;
  END;

 

  -- Step 5

  FUNCTION public_func(
          p_in_char IN VARCHAR2,
          p_empno IN emp.empno%TYPE )
    RETURN SYS_REFCURSOR
  AS
   v_cursor SYS_REFCURSOR;
  BEGIN
    public_proc( p_in_char, p_empno, v_cursor );
    RETURN v_cursor;   

 
END;

  -- Step 6

   PROCEDURE display_data( p_data IN VARCHAR2 )
  AS
   v_next_row INTEGER := v_table.COUNT + 1;
  BEGIN
    DBMS_OUTPUT.PUT_LINE('This is VARCHAR2 data: ' || p_data );
    v_table(v_next_row).txt := p_data;
    v_table(v_next_row).txt_length := LENGTH(p_data);
    a_private_proc;
  END;

  -- Step 7

  PROCEDURE display_data( p_data IN DATE )
  AS
   v_char_version VARCHAR2(32000);
   v_next_row INTEGER := v_table.COUNT + 1;
  BEGIN
    v_char_version := TO_CHAR(p_data, 'FMDD Month, YYYY');
    DBMS_OUTPUT.PUT_LINE('This is DATE data: ' || v_char_version );
    v_table(v_next_row).txt := v_char_version;
    v_table(v_next_row).txt_length := LENGTH(v_char_version);
    a_private_proc;
  END;

  -- Step 8

  PROCEDURE display_data( p_data IN NUMBER )
  AS
   v_char_version VARCHAR2(32000);
   v_next_row INTEGER := v_table.COUNT + 1;
  BEGIN

    v_char_version := TO_CHAR(p_data, 'FM$999,990.00');

    DBMS_OUTPUT.PUT_LINE('This is NUMBER data: ' || v_char_version );
    v_table(v_next_row).txt := v_char_version;
    v_table(v_next_row).txt_length := LENGTH(v_char_version);
    a_private_proc;
  END;

  -- Step 9

  PROCEDURE init
  AS
   temp_tab sample_pkg.a_table;
  BEGIN
   g_global_empno := 7788;
   v_table := temp_tab;
  END;

-- Step 10

BEGIN
  init;
END;

        
     

This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter