Showing posts with label Packages. Show all posts
Showing posts with label Packages. Show all posts

(12) Packages


About Packages:
  • Schema object that groups logically related PL/SQL types, variables, constants, subprograms, cursors, and exceptions.

Package specification:
  • Declares the public items that can be referenced from outside the package. Works as an (API).
  • If the public items include cursors or subprograms, then the package must also have a body.
  • The scope of a public item is the schema of the package. A public item is visible everywhere in the schema.
  • AUTHID clause determines:
    1. whether subprograms and cursors in the package run with the definer (the default) or invoker privileges.
    2. whether unqualified references to schema objects are resolved in the schema of the definer or invoker.

Package Body:
  • Package body is optional, unless package specification declares cursors or subprograms.
  • The package body and package specification must be in the same schema.
  • Can also declare and define private items that cannot be referenced from outside the package
  • Can have an initialization part, whose statements initialize variables and do other one-time setup steps.
  • In either the package specification or package body, you can map a package subprogram to an external Java or C subprogram
  • For this, you use a call specification, which maps the external subprogram name, parameter types, and return type to their SQL counterparts.

Why use packages?
  • Modularity:
    • encapsulate logically related types, variables, constants, subprograms, cursors, and exceptions.
    • You can modify package objects without recompiling dependent schema objects
    • Let you OVERLOAD subprograms.
  • Easier Application design
    • Can contain global variables and cursors that are available to all subprograms in the package.
    • Packages increase your namespace -- "you get pkg.proc instead of 5,000 little procedures with meaningless names, you can have 500 packages with good module names, each with 10 procedures that have meaningful names (that might be the same as the name of a procedure in another package). (ask tom)
  • good coding practices
    • Allow many small procedures instead a few large ones.
    • Packages maintain a state
    • Packages allow you to create types
  • Information Hiding
    • You may have 20 procedures in the body, but choose to make only 10 of them public in the specificaiton
  • Added functionality: Package public variables and cursors can persist for the life of a session.
  • Better performance:
    After first time you invoke a package subprogram, Oracle Database loads the package as needed into memory. Packages ARE NOT entirely loaded immediately.
  • Better Security control and privilege management: more efficient grant privileges

What are appropriate public items?
  • types, variables, constants, subprograms, cursors, and exceptions
  • Associative arrays type of standalone subprogram parameters: Since you cannot declare an AA type at schema level.
  • variables that must remain available between subprogram invocations in the same session.
  • subprograms that read and write public variables.
  • subprograms that invoke each other.
  • overloaded subprograms

Note: You cannot reference remote package public variables, even indirectly.


Package instantiation and initialization:
  • When a session references a package item, Oracle Database instantiates the package for that session.
  • Every session that references a package has its own instantiation of that package.
  • Initialization includes whichever of the following are applicable:
    • Assigning initial values to public constants
    • Assigning initial values to public variables whose declarations specify them.
    • Executing the initialization part of the package body

Package State

  • Package state: the values of the variables, constants, and cursors that a package declares (in either its specification or body)
  • If a PL/SQL package declares at least one variable, constant, or cursor, then the package is stateful;
  • otherwise, it is stateless.
  • Each session that references a package item has its own instantiation of that package.
  • If the package is stateful, the instantiation includes its state.
  • The package state persists for the life of a session, except in these situations:
    • package is SERIALLY_REUSABLE
    • package body recompiled

SERIALLY_REUSABLE
  • The package state is stored in a work area in a small pool in the system global area (SGA).
  • If a package is NOT SERIALLY_REUSABLE:
    • its package state is stored in the user global area (UGA) for each user.
    • Limit scalability: the amount of UGA memory needed increases linearly with the number of users.
    • Potential high usage of memory: The package state can persist for the life of a session, locking UGA memory until the session ends.
  • For a SERIALLY_REUSABLE package, the work unit is a server call.
  • You must use its public variables only within the work unit.
  • If you depend on the value of a public variable that was set in a previous work unit, your program can fail. PL/SQL cannot check for such cases.


About Packages: Initialization and variables.
(1) The package body can have an executable section.
(2) This section is executed when the first reference to a package object
is made
. In the example, when you print the value of variable 'value'
in an anonymous block.

(3) The package body can have an initialization part, whose statements
initialize public variables and do other one-time setup steps.
The initialization part runs only the first time the package is referenced.
The initialization part can include an exception handler.


-- (1) create package ..
create or replace package my_pack 
as
 value number := 100;
 const constant number := 999;
 procedure update_value (pval number);
end my_pack;
/

-- (2) .. and package body
create or replace package body my_pack
as 
  procedure update_value (pval number) 
  is
  begin
    my_pack.value := pval;
    dbms_output.put_line('my_pack.value updated to: '|| my_pack.value);
    dbms_output.put_line('my_pack.const: '|| my_pack.const);    
  end;
begin         --- executable section of package. Runs once. 
  dbms_output.put_line('initial value of my_pack.value: '|| my_pack.value);
  dbms_output.put_line('initial value of value: '|| value);
  dbms_output.put_line('initial value of my_pack.const: '|| my_pack.const);
  my_pack.value := 300;
  dbms_output.put_line('New value of my_pack.value: '|| my_pack.value);  
end my_pack;
/

--(3) The initialization code is executed after the first call to a package object..
set serveroutput on
declare
  x number;
begin 
  x := my_pack.const;            -- First call to a package object. 
  dbms_output.put_line('x is: '|| x);
  x := my_pack.value;
 dbms_output.put_line('x is: '|| x);
end;
/
anonymous block completed
initial value of my_pack.value: 100
initial value of value: 100
initial value of my_pack.const: 999
New value of my_pack.value: 300
x is: 999
x is: 300

--(4) Run the anonymous block again. The executable section in the package body IS NOT executed again.
anonymous block completed
x is: 999
x is: 300

--(5) If you recompile the package, the initialization code is executed again, in the first call to a package object.
SQL> alter package my_pack compile;
package MY_PACK altered.

set serveroutput on
declare
 x number;
begin 
 x := my_pack.const;
 dbms_output.put_line('x is: '|| x);
 
  x := my_pack.value;
 dbms_output.put_line('x is: '|| x);
end;
/
anonymous block completed
initial value of my_pack.value: 100
initial value of value: 100
initial value of my_pack.const: 999
New value of my_pack.value: 300
x is: 999
x is: 300

One-time Procedures and Global Package variables

USER A Executes:
create or replace package set_global_commission
as
 serv_commission number(4,2);
end;
/

create or replace package body set_global_commission
as 
 vtemp number(4,2);
begin 
  select avg(commission_pct) into serv_commission
  from usera.employees;
  vtemp := serv_commission;
  set_global_commission.serv_commission := set_global_commission.serv_commission * 2;
  dbms_output.put_line('Original: '|| to_char(vtemp) || 
                       ' Current: ' || to_char(set_global_commission.serv_commission));
end set_global_commission;
/
Package body created.

-- Now initializing the package state:

set serveroutput on
begin
 dbms_output.put_line('Service commission is: '|| set_global_commission.serv_commission);
 set_global_commission.serv_commission := set_global_commission.serv_commission * 5.37;
 dbms_output.put_line('New value for User A session is: '|| set_global_commission.serv_commission);
end;
/
anonymous block completed
Original: .22 Current: .44
Service commission is: .44
New value for User A session is: 2.36

Question: Now Can User B access the variable value?
Answer: User B CANNOT access the value held in the session by User A. User B can ONLY see the value for the variable set within his own session:

As USER B:
set serveroutput on
begin
 dbms_output.put_line('Service commission is: '|| usera.set_global_commission.serv_commission);
end;
/
Error report:
ORA-06550: line 2, column 51:
PLS-00201: identifier 'USERA.SET_GLOBAL_COMMISSION' must be declared
ORA-06550: line 2, column 2:

Accessing Package variables: restriction
  • If a subprogram has as formal parameter a variable defined on package specification,
    calls to the subprogram need to reference the package variable definition when defining
    the ACTUAL parameter that will be used.

-- Example:
  1. (1) package my_rec defines a RECORD type used as formal parameter type in a procedure proc1.
  2. (2) When calling proc1 from OUTSIDE the package, the ACTUAL parameter needs to be defined using the
    record type defined in the package
    .
create or replace package my_rec
is 
 type t_emprec is record (
    empid employees.employee_id%type,
    ln    employees.last_name%type,
    fs    employees.first_name%type);
 procedure proc1 (p_empid in number, p_emp_info OUT t_emprec);
end my_rec;
/

create or replace package body my_rec 
is 
  procedure proc1 (p_empid in number, p_emp_info OUT t_emprec)
  is
  begin
   select employee_id, last_name, first_name 
   into p_emp_info
   from employees
   where employee_id = p_empid;
  end proc1;
end my_rec;

-- Case 1: Success
set serveroutput on
declare
 emprec my_rec.t_emprec; 
begin
  my_rec.proc1(100, emprec);
  dbms_output.put('Id => '|| emprec.empid || ' ');
  dbms_output.put_line('name => '|| emprec.fs || ' '|| emprec.ln);
end;
/
anonymous block completed
Id => 100 name => Steven King

-- Case 2: Failure, even though the data type of the actual parameter RECORD was
formed by the same components used in the definition of the FORMAL parameter.
set serveroutput on
declare
 type t_emprec2 is record (
    empid employees.employee_id%type,
    ln    employees.last_name%type,
    fs    employees.first_name%type);
 emprec2 t_emprec2;
 
begin
  my_rec.proc1(100, emprec2);
  dbms_output.put('Id => '|| emprec2.empid || ' ');
  dbms_output.put_line('name => '|| emprec2.fs || ' '|| emprec2.ln);
end;
/
Error report:
ORA-06550: line 9, column 3:
PLS-00306: wrong number or types of arguments in call to 'PROC1'
ORA-06550: line 9, column 3:



Information about Packages in the Data Dictionary
SQL> SELECT object_name, object_type, last_ddl_time, timestamp, status
FROM   user_objects
WHERE  object_type IN ('PACKAGE','PACKAGE BODY');
/

OBJECT_NAME             OBJECT_TYPE          LAST_DDL_  TIMESTAMP           STATUS
------------------------- ------------------ ---------- ------------------- -------
DEPARTMENT_PKG          PACKAGE              03-OCT-11  2011-10-03:14:22:52 VALID
DEPARTMENT_PKG          PACKAGE BODY         03-OCT-11  2011-10-03:14:23:45 VALID
MESSAGE_API             PACKAGE              19-SEP-11  2011-09-19:01:15:22 VALID
MESSAGE_API             PACKAGE BODY         19-SEP-11  2011-09-19:01:18:22 VALID
MY_PACK                 PACKAGE              19-SEP-11  2011-09-19:13:11:00 VALID
MY_PACK                 PACKAGE BODY         19-SEP-11  2011-09-19:13:22:10 VALID
SET_EMPNO_CTX_PKG       PACKAGE BODY         27-SEP-11  2011-09-27:09:56:41 INVALID
SET_GLOBAL_COMMISSION   PACKAGE              23-SEP-11  2011-09-23:10:26:32 VALID
SET_GLOBAL_COMMISSION   PACKAGE BODY         23-SEP-11  2011-09-23:10:37:00 VALID


SQL> DESCRIBE my_pack
PROCEDURE    Argument Name Type   IN/OUT Default 
------------ ------------- ------ ------ ------- 
UPDATE_VALUE PVAL          NUMBER IN     unknown 

Dependencies between subprograms
  • The view all_dependencies; user_dependencies; dba_dependencies; let you examine dependencies between stored programs.
  • Invalid package body does not invalidate procedures that reference (and thus depend upon) the package specification.
  • This is because the package specification is the schema-level component.
  • In this case, since the subprograms don't see the invalid package body, a run-time error will be raised if the
    procedure makes a call to an object that needs to execute within the invalid package body.
SQL>SELECT name, type, referenced_name, referenced_type, dependency_type
FROM user_dependencies
ORDER BY name;

NAME              TYPE        REFERENCED_NAME                    REFERENCED DEPE
-------------------- ------------ ------------------------------ ---------- ----
DEPARTMENT_PKG       PACKAGE BODY STANDARD                       PACKAGE    HARD
DEPARTMENT_PKG       PACKAGE      STANDARD                       PACKAGE    HARD
DEPARTMENT_PKG       PACKAGE      EMPLOYEES                      TABLE      HARD
DEPARTMENT_PKG       PACKAGE BODY EMPLOYEES                      TABLE      HARD
DEPARTMENT_PKG       PACKAGE BODY DEPARTMENT_PKG                 PACKAGE    HARD
DEPARTMENT_PKG       PACKAGE      DEPARTMENTS                    TABLE      HARD
DEPARTMENT_PKG       PACKAGE BODY DEPARTMENTS                    TABLE      HARD
EMP_NO_SAL           VIEW         EMPLOYEES                      TABLE      HARD
FIBONACCI            FUNCTION     STANDARD                       PACKAGE    HARD
GET_BONUS            PROCEDURE    STANDARD                       PACKAGE    HARD
GET_BONUS            PROCEDURE    BONUS                          TABLE      HARD
GET_BONUS            PROCEDURE    DBMS_OUTPUT                    SYNONYM    HARD
GET_BONUS            PROCEDURE    SYS_STUB_FOR_PURITY_ANALYSIS   PACKAGE    HARD
MESSAGE_API          PACKAGE BODY DBMS_PIPE                      SYNONYM    HARD
MESSAGE_API          PACKAGE BODY EMPLOYEES                      TABLE      HARD
MESSAGE_API          PACKAGE BODY STANDARD                       PACKAGE    HARD
MESSAGE_API          PACKAGE BODY DBMS_STANDARD                  PACKAGE    HARD
MESSAGE_API          PACKAGE BODY MESSAGE_API                    PACKAGE    HARD
MESSAGE_API          PACKAGE BODY DBMS_OUTPUT                    SYNONYM    HARD
MESSAGE_API          PACKAGE      EMPLOYEES                      TABLE      HARD
MY_PACK              PACKAGE BODY MY_PACK                        PACKAGE    HARD
MY_PACK              PACKAGE BODY DBMS_OUTPUT                    SYNONYM    HARD
MY_PACK              PACKAGE BODY STANDARD                       PACKAGE    HARD
MY_PACK              PACKAGE      STANDARD                       PACKAGE    HARD

Checking dependency and status:

create or replace procedure pget_salary 
(p_empid in employees.employee_id%type) 
as 
  vsal employees.salary%type;
begin
  select salary into vsal 
  from employees
  where employee_id = p_empid;
  dbms_output.put_line('Salary of empid: '|| p_empid || ' is '|| vsal);
end pget_salary;
/

create or replace package my_pack as
 procedure p1 (p_empid employees.employee_id%type);
end;
/

create or replace package body my_pack as
  procedure p1 (p_empid employees.employee_id%type) 
  is
  begin
    dbms_output.put_line('calling pget_salary for employee '|| p_empid);
    pget_salary(p_empid);
  end p1;
end my_pack;
/

Now check user_objects, user_dependencies, user_arguments

-- (1) Check object dependencies on USER_DEPENDENCIES

select name, type, referenced_name, referenced_type, dependency_type
from user_dependencies
where name in ('MY_PACK', 'PGET_SALARY')
order by name, type;

NAME          TYPE       REFERENCED_NAME            REFERENCED_T DEPENDENCY
---------------- ------------ ---------------------------- ------------ ----------
MY_PACK          PACKAGE      EMPLOYEES                    TABLE        HARD
MY_PACK          PACKAGE BODY STANDARD                     PACKAGE      HARD
MY_PACK          PACKAGE BODY DBMS_OUTPUT                  SYNONYM      HARD
MY_PACK          PACKAGE BODY MY_PACK                      PACKAGE      HARD
MY_PACK          PACKAGE BODY EMPLOYEES                    TABLE        HARD
MY_PACK          PACKAGE BODY PGET_SALARY                  PROCEDURE    HARD
PGET_SALARY      PROCEDURE    EMPLOYEES                    TABLE        HARD
PGET_SALARY      PROCEDURE    STANDARD                     PACKAGE      HARD
PGET_SALARY      PROCEDURE    DBMS_OUTPUT                  SYNONYM      HARD
PGET_SALARY      PROCEDURE    SYS_STUB_FOR_PURITY_ANALYSIS PACKAGE      HARD


-- (2) Check LAST_DDL_TIME and validity status on USER_OBJECTS 
select object_name, object_type, 
       to_char(last_ddl_time, 'DD-MON-YY:HH:MI:SS') LAST_DDL_TIME, 
       timestamp, status 
from user_objects
where object_name in ('MY_PACK', 'PGET_SALARY');

/
OBJECT_NAME   OBJECT_TYPE    LAST_DDL_TIME      TIMESTAMP           STATUS
------------- -------------- ------------------ ------------------- -------
MY_PACK       PACKAGE        04-OCT-11:11:35:09 2011-10-04:11:35:09 VALID
MY_PACK       PACKAGE BODY   04-OCT-11:11:35:14 2011-10-04:11:35:14 VALID
PGET_SALARY   PROCEDURE      04-OCT-11:12:06:52 2011-10-04:11:31:12 VALID


set serveroutput on 
begin
 my_pack.p1(100);
end;
/
anonymous block completed
calling pget_salary for employee 100
Salary of empid: 100 is 7500


alter package my_pack compile;

OBJECT_NAME   OBJECT_TYPE    LAST_DDL_TIME TIMESTAMP     STATUS
------------- -------------- ------------------ ------------------- -------
MY_PACK       PACKAGE        04-OCT-11:12:20:21 2011-10-04:11:35:09 VALID
MY_PACK       PACKAGE BODY   04-OCT-11:12:20:21 2011-10-04:11:35:14 VALID
PGET_SALARY   PROCEDURE      04-OCT-11:12:06:52 2011-10-04:11:31:12 VALID


Packages break the cycle of dependency.
source: Ask Tom

Scenario:
  • (a) Procedure A does something.
  • (b) Procedure B calls Procedure A :(B depends on A)
  • (c) If procedure A is recompiled, then Procedure B becomes invalid.

-- (1) - create procedure A
SQL> create or replace procedure A as
 begin null; end;
/

-- (2) create procedure B. B calls A.
SQL> create or replace procedure B as
 begin A; end;
/
 
-- (3) Check object status: A and B are VALID
SQL> select object_type, object_name, status
 from user_objects
 where object_name in ('A', 'B');
 
OBJECT_TYPE         OBJECT_NAME     STATUS  
------------------- -------------   ------- 
PROCEDURE           A               VALID   
PROCEDURE           B               VALID   


--(4) B depends on A. If you modify A, B becomes invalid.

SQL> create or replace procedure A as
  x number;
begin 
   x :=1;
   null; 
end;
/

SQL> select object_type, object_name, status
 from user_objects
 where object_name in ('A', 'B');

 OBJECT_TYPE         OBJECT_NAME   STATUS  
------------------- -------------- ------- 
PROCEDURE           A              VALID   
PROCEDURE           B              INVALID 


Now, if A and B are within PACKAGES:
SQL> create or replace package a as
 procedure a; end;
/

SQL> create or replace package b as
 procedure b; end;
/

SQL> create or replace package body a as
 procedure a is
  begin null; end;
end;
/

SQL> create or replace package body b as
 procedure b is
  begin a.a; end;
end;
/

SQL> select object_type, object_name, status
 from user_objects
 where object_name in ('A', 'B');

OBJECT_TYPE         OBJECT_NAME  STATUS  
------------------- ------------ ------- 
PACKAGE             A            VALID   
PACKAGE BODY        A            VALID   
PACKAGE             B            VALID   
PACKAGE BODY        B            VALID   


-- Now, if package body B is modified:
SQL> create or replace package body a as
 procedure a is
   x number;
  begin 
   x:=1; 
   null;
  end;
end;

SQL> select object_type, object_name, status
 from user_objects
 where object_name in ('A', 'B');

OBJECT_TYPE         OBJECT_NAME   STATUS  
------------------- ------------- ------- 
PACKAGE             A             VALID   
PACKAGE BODY        A             VALID   
PACKAGE             B             VALID   
PACKAGE BODY        B             VALID   

Here package B remains VALID. 
  • This is because only package BODY b was modified.
  • As long as the spec does not change, package B will remain valid.

(14) Oracle-Supplied Packages



DBMS_PIPE
Provides API for intersession communication

Methods for intersession communication:
(a) Requiring permanent or semipermanent structures
  1. Advanced Queuing (introduced Oracle 9):
    Use DBMS_AQADM and DBMS_AQ packages.
    AQ need to be set up for each participant. Use messages to exchange information between sessions.
  2. Use tables, grants, and synonyms to exchange data between sessions.
    Subject to transaction control limitations: commits required.

(b) NOT requiring permanent or semipermanent structures
  1. Use DBMS_PIPE.
    • Uses dynamic memory structures in the SGA (pipes).
    • Similar to Unix pipes. pipes may be local, private, or publicly accessible.
    • Act as FIFO queues.
    • Not binded by transaction control.
    • Data transmitted asynchronously.
  2. DBMS_ALERT.
    • Uses memory structure in SGA, that works as a public pipe.
    • Populated on event triggers and subject to transaction control limits.
    • Alert pipes communicate between sessions asynchronously at the conclusion of an event.
    • Events are anything that you can build a trigger against, like DML or system action.
    • Unlike DBMS_PIPE, DBMS_ALERT works on a publich-and-subscribe paradigm.
    • It publishes notifications, and then enables subscribers to register their interest in the alert and receive the notifications.
    • Alerts also support two or more sessions of a single user.


Using public pipes with DBMS_PIPE


Using DBMS_PIPE
  • Support communication between two or more sessions of a single user.
  • Can also support communication between two or more users.
  • DBMS_PIPE can help you mimic Unix pipes or POSIX-compliant threads.
  • Unix pipes allow you to move data between two active processes.
  • Unix pipes control communication at the process level.
  • C/C++ also lets you control threading activities with mutex variables, which work at the process and thread levels.
  • Both provide higher programming language equivalents to operating system semaphores.

  • DBMS_PIPE provides a non-secure mechanism for inter-session messaging.
  • Non-secure because messages can be lost from the pipe if the instance crashes or is shutdown before they are processed.
  • Advanced Queues are arguably a better mechanism when secure messaging and greater flexibility are required.
  • DBMS_PIPE: can also be used for passing information to external processes that may monitor or control system resources. With DBMS_PIPE you can:
    • Use local pipes to control a single program’s execution.
    • Use private pipes to control concurrent programs run by a single user.
    • Use public pipes to control concurrent programs run by multiple users.

Pipes can be:
  • Implicit Pipes: created automatically when a message is sent with an unkown pipename using the SEND_MESSAGE function.
  • Implicit pipes disappear when they are empty.
  • Explicit pipes: created using CREATE_PIPE function. Must be removed with REMOVE_PIPE function.

Two levels of security:
  • Public Pipes: Accessible by any user with EXECUTE permission on DBMS_PIPE package.
    • Implicit pipes are always public.
    • Implicit pipes can be created explicitly by calling the CREATE_PIPE function with the private parameter set to FALSE.
    • The domain of a public pipe is the schema in which it was created, either explicitly or implicitly.
    • Public pipe works asynchronously. Any number of schema users can write to it.
  • Private Pipes: Accessible only by sessions with the same ownerid as the pipe creator, stored programs owned by the pipe creator, or by users connected as SYSDBA.

  • V$DB_PIPES: Display info about pipes.
  • Writing - Reading pipes:
    • The sending session builds a message using one or more calls to the PACK_MESSAGE procedure. This procedure adds the message to the session's local message buffer.

Potential applications:

- External Service interface: communicate with user-written services external to the RDBMS. Services available asynchronously.
- Independent transactions: communicate to a separate session which can perform an operation in an independent transaction (i.e. logging)
- Alerters (non-transactional):
- Debugging: Triggers and Stored procedures can send debug info to a pipe, that other session can keep reading and displaying
- Concentrator: multiplexing large # of users over a fewer # of netwrok connections.



Using DBMS_PIPE: (1) Sending to and Receiving from the Local Pipe or Buffer
(a) resets the local buffer, packs messages, and send them to an implicit public pipe.
(b) Check the created pipe on V$DB_PIPES.
(c) reads from the pipe, unpacks the buffer, reads the data, and prints the contents of the pipe to the console
-- (a) Resets the local buffer, packs messages, and send them to the pipe. 
set serveroutput on
declare
 msg varchar2(30);
 success integer;
begin
 msg := dbms_pipe.unique_session_name;
 -- dbms_pipe.unique_session_name returns a VARCHAR2 string that 
 -- represents the current session.
 dbms_pipe.reset_buffer;
 -- PACK_MESSAGE takes the value of the actual parameter and puts it 
 -- into the buffer stack (FIFO)
 dbms_pipe.pack_message(msg);
 dbms_output.put_line('Written to pipe ['||msg||']');
 msg := 'Message body: read me';
 dbms_pipe.pack_message(msg);
 dbms_output.put_line('Written to pipe ['||msg||']');
 
 -- creates an IMPLICIT public pipe. Pipe will be deleted when last 
 -- message in its buffer is read..
 success := dbms_pipe.send_message(pipename => 'pipe1');
 if success = 0 then
   dbms_output.put_line('Contens of the buffer sent..');
 else
   dbms_output.put_line('Error. Return # ' || success);
 end if;
end;

-- (b) Check the created pipe on V$DB_PIPES:
SQL> SELECT * from v$DB_PIPES;

OWNERID  NAME   TYPE     PIPE_SIZE              
-------- ------ -------- ---------
         PIPE1  PUBLIC        4448                   

--(c) reads from the pipe, unpacks the buffer, reads the data, and prints the contents of the 
-- pipe to the console
set serveroutput on
declare
 msg varchar2(50);
 success integer;
 no_more_items exception;
 e_read_timeout exception;
 pragma exception_init(no_more_items, -06556);
begin
  -- read the message from the pipe. Wait max 5 seconds for 
  -- message in the pipe..
  success := dbms_pipe.receive_message(pipename => 'pipe1', 
                                       timeout => 5);
  if success = 0 then
    dbms_output.put_line('Message received...');
  elsif success = 1 then 
    raise e_read_timeout;
  else
    dbms_output.put_line('Error. Return # ' || success);
  end if;  
  
  begin
    loop    -- loop through all packed messages
      dbms_pipe.unpack_message(msg);
      dbms_output.put_line('Message ['||msg||']');
    end loop; 
  exception
      when no_more_items then 
        dbms_output.put_line('end of buffer reached. exiting..');
  end;
exception
  when e_read_timeout then 
    dbms_output.put_line('Receiving message timeout. Nothing in buffer..');        
end;


Intersession Communication:
Session UserA:
(1) Creates an explicit public pipe (explicit_public_pipe)
(2) Opens cursor with the list of employees with highest salaries in all departments [format: dept, last_name, salary]
(2) Writes into the pipe the contents of the cursor. Each row one message.

Session UserB:
(1) Reads from explicit_public_pipe the list of employees and salaries.
(2) Uses UTL_FILE to create or append an OS file with the contents of the pipe. Each message one line.
-- USER A:

set serveroutput on
DECLARE 
  result integer;
  msg    varchar2(100);
  i      number;
  -- cursor returns 
  cursor c1 is 
   select e1.department_id dept, last_name, salary
   from employees e1, 
       ( select department_id, max(salary) sal
         from employees
         group by department_id ) sel
   where e1.department_id = sel.department_id
   and   e1.salary = sel.sal
   order by 1;

  type t_emprec is record (dept employees.department_id%type,
                          ln employees.last_name%type, 
                          sal employees.salary%type);
  emprec t_emprec;
BEGIN
  -- (1) CREATE explicit public pipe
  result := dbms_pipe.remove_pipe('explicit_public_pipe');
  result := dbms_pipe.create_pipe(pipename => 'explicit_public_pipe',
                                  private  => FALSE);
  
  -- (2) PACK messages into the pipes.. (all employees from dept 10)
  open c1;
  i := 0;
  -- packs Session ID
  msg := i ||'$'|| dbms_pipe.unique_session_name;
  dbms_output.put_line('Packing: '|| msg);
  dbms_pipe.pack_message(msg);
  loop
    fetch c1 into emprec;
    exit when c1%notfound;
          i := i + 1;
          msg := i ||':'||emprec.dept||';'||emprec.ln ||';'||emprec.sal||'!';
          dbms_output.put_line('Packing: '|| msg);
          dbms_pipe.pack_message(msg);
  end loop;
  close c1;
  result := dbms_pipe.send_message(pipename => 'explicit_public_pipe',
                                   timeout => 5);
  dbms_output.put_line('Message sent...');
END;
/

anonymous block completed
Packing: 0$ORA$PIPE$007D02160001
Packing: 1:10;Whalen;4400!
Packing: 2:20;Hartstein;13000!
Packing: 3:30;Raphaely;11000!
Packing: 4:40;Mavris;6500!
Packing: 5:50;Fripp;8200!
Packing: 6:60;Hunold;9000!
Packing: 7:70;Baer;10000!
Packing: 8:80;Russell;14000!
Packing: 9:90;King;24000!
Packing: 10:100;Greenberg;12008!
Packing: 11:100;Chen;12008!
Packing: 12:110;Higgins;12008!
Message sent...

-- USER B runs this code:

set serveroutput on
DECLARE
  msg   varchar2(200);
  vinfo varchar2(200);
  result integer;
  no_more_items exception;
  e_read_timeout exception;
  pragma exception_init(no_more_items, -06556);
  fdest utl_file.file_type;
  type FileAttrRec is Record (
                      vfilexists BOOLEAN,
                      vfilelength number,
                      vblocksize  binary_integer);
  vfilerec fileattrrec;
BEGIN
  -- read the message from the pipe. Wait max 5 seconds for 
  -- message in the pipe..
  result := dbms_pipe.receive_message(pipename => 'explicit_public_pipe', 
                                      timeout => 5);
  if result = 0 then
    dbms_output.put_line('Message received...');
  elsif result = 1 then 
    raise e_read_timeout;
  else
    dbms_output.put_line('Error. Return # ' || result);
  end if;  
  
  -- Open FILE for writing..
  -- Check: If file ALREADY Exists: Append. Otherwise: Write
  utl_file.fgetattr('USER_DIR', 'fpipeout.txt', vfilerec.vfilexists, 
                     vfilerec.vfilelength, vfilerec.vblocksize);
  If vfilerec.vfilexists THEN
    fdest := utl_file.fopen('USER_DIR', 'fpipeout.txt', 'A', 1024);
    dbms_output.put_line('Destination file exists. Appending..');
  Else
    fdest := utl_file.fopen('USER_DIR', 'fpipeout.txt', 'W', 1024);
  End if; 
  select sysdate into vinfo from dual;
  vinfo := 'Entering new data on ' || vinfo||':';
  utl_file.put_line(fdest, vinfo, true);
  
  -- iteract through the messages in the buffer. 
  -- Write each one into the file..
  begin
    loop
      dbms_pipe.unpack_message(msg);
      dbms_output.put_line('Message ['||msg||']');
      utl_file.put_line(fdest, msg, true);
    end loop; 
  exception
      when no_more_items then 
        dbms_output.put_line('end of buffer reached. exiting..');
  end;
  -- close file
  utl_file.fclose(fdest);
  -- close pipe
  result := dbms_pipe.remove_pipe('explicit_public_pipe');
exception
  when e_read_timeout then 
    dbms_output.put_line('Receiving message timeout. Nothing in buffer..');        
end;
/

Check contents of file fpipeout.txt: 
$ cat fpipeout.txt 
Entering new data on 19-SEP-11:
0$ORA$PIPE$007D02160001
1:10;Whalen;4400!
2:20;Hartstein;13000!
3:30;Raphaely;11000!
4:40;Mavris;6500!
5:50;Fripp;8200!
6:60;Hunold;9000!
7:70;Baer;10000!
8:80;Russell;14000!
9:90;King;24000!
10:100;Greenberg;12008!
11:100;Chen;12008!
12:110;Higgins;12008!





















Intersession Communication: an api for communication
Creates PACKAGE with send and receive procedures
-- In a package
create or replace package message_api AS
  procedure send_max_sal(p_dept in employees.department_id%type);
  procedure receive;
end message_api;

create or replace package body message_api AS
  procedure send_max_sal(p_dept in employees.department_id%type) AS
    result integer;
    msg    varchar2(100);
    cursor c1(dept employees.department_id%type) is 
      select last_name, salary
      from employees
      where salary = ( select max(salary)
                         from employees
                         where department_id = 20)
      and department_id =dept;
    vlastname employees.last_name%type;
    vsal      employees.salary%type;
  BEGIN  
    -- (2) PACK messages into the pipe.. 
    open c1(p_dept);
    fetch c1 into vlastname, vsal;
    close c1;
    msg := vlastname||':'||vsal;
    dbms_output.put_line('Packing: '|| msg);
    dbms_pipe.pack_message(msg);  
    result := dbms_pipe.send_message(pipename => 'message_pipe',
                                   timeout => 5);
    if result != 0 then
      raise_application_error(-20001, 'message_pipe error');
    end if;
    dbms_output.put_line('Message sent...');
  END send_max_sal;
    
  procedure receive as
    result integer;
    msg    varchar2(200);
  begin
    result := dbms_pipe.receive_message( pipename => 'message_pipe', 
                                         timeout  => dbms_pipe.maxwait);
    if result = 0 then
      dbms_pipe.unpack_message(msg);
      dbms_output.put_line('Message is: '|| msg);
    else
      raise_application_error(-20002, 'message_api.receive error');
    end if;
  end receive;
end message_api;
/

-- time t0: USER B: 
set serveroutput on
exec usera.message_api.receive;
(hangs)..

-- time t1: USER A:
begin 
 message_api.send_max_sal(20);
end;
/

anonymous block completed
Packing: Hartstein:13000
Message sent...

-- time t2: USER B:
anonymous block completed
Message is: Hartstein:13000





DBMS_SPACE - check space requirements

SQL> analyze table emp2 compute statistics;
table EMP2 analyzed.

SQL> set serveroutput on 
SQL> declare
 l_used_bytes number;
 l_alloc_bytes number;
begin
  dbms_space.create_index_cost
    ( ddl => 'create index emp_idx1 on emp2' ||
             '(first_name, department_id)',
      used_bytes => l_used_bytes,
      alloc_bytes => l_alloc_bytes);
  
  dbms_output.put_line('Used bytes: ' || l_used_bytes);
  dbms_output.put_line('Allocated bytes:  ' || l_alloc_bytes);
end;
/

Used bytes: 1753088
Allocated bytes:  6291456

SQL>




UTL_FILE

(1) Using UTL_FILE package
  • UTL_FILE provides a restricted version of operating system stream file I/O.
  • Files and directories accessible through UTL_FILE: controlled by a number of factors and database parameters.
    The most important of these is the set of directory objects that have been granted to the user.
  • Provides file access both on the client side (FORMS apps) and on the server side.
  • Note that symbolic links are not supported.

Previous Oracle Versions:
  • In the past, accessible directories for UTL_FILE were specified in the init.ora using the UTL_FILE_DIR parameter.
  • UTL_FILE_DIR access is NO LONGER recommended.
  • Oracle recommends that you instead use the DIRECTORY object feature, which replaces UTL_FILE_DIR.
  • Directory objects:
    (a) offer more flexibility and granular control to the UTL_FILE application administrator,
    (b) can be maintained dynamically (that is, without shutting down the database), and
    (c) are consistent with other Oracle tools.














UTL_FILE.GET_LINE
  • the len parameter of UTL_FILE.GET_LINE specifies the requested number of bytes of character data.
  • The number of bytes actually returned to the user will be the lesser of:
  • - The GET_LINE len parameter, or - The number of bytes until the next line terminator character, or - The max_linesize parameter specified by UTL_FOPEN
  • The FOPEN max_linesize parameter must be a number in the range 1 and 32767.
    If unspecified, Oracle supplies a default value of 1024.
  • The GET_LINE len parameter must be a number in the range 1 and 32767.
    If unspecified, Oracle supplies the default value of max_linesize.
  • If max_linesize and len are defined to be different values, then the lesser value takes precedence.

Examples:

First create the directory objects that identify the accessible directories:
As System:
SQL> create directory user_dir AS '/home/oracle/fileio/user';
SQL> grant read on directory user_dir to public;
SQL> grant write on directory user_dir to public;

Note:
  • Oracle does not check whether the directory exists during the creation of the directory object.
  • with revoke write on directory user_dir to public:
    - The file is open and read successfully when the user has only READ permission in the directory.
    - Directory name in utl_file.fopen is CASE SENSITIVE. ALL CAPS required.


(a) Simple Read:
(1) Declare a file handler (utl_file.file_type)
(2) Open file. (The file is only acessible in the directory specified by the directory object).
(3) Read and prints chunks of 1kb until reach EOF. (until no_data_found)

set serveroutput on 
declare 
  v1 varchar2(32767);
  v2 varchar2(32767);
  f1 utl_file.file_type;   -- declare a file handler.
begin
 f1 := utl_file.fopen('USER_DIR', 'ftest1.txt', 'R', 1024);
 begin
   loop 
     utl_file.get_line(f1, v1, 32767);
     v2 := v2 || chr(10)|| v1;
   end  loop;
 exception
   when no_data_found then 
     null;
 end;
 utl_file.fclose(f1);
 dbms_output.put_line(v2);
end;
/
anonymous block completed

"By midmorning the rain had stopped. 
Water dripped from the trees in the alameda and the crepe hung in soggy strings. 
He stood with the horses and watched the wedding party emerge from the church. 
The groom wore a dull black suit too large for him and he looked not uneasy but half 
desperate, as if unused to clothes at all. 
The bride was embarrassed and clung to him and they stood on the steps for their photograph to
be taken and in their antique formalwear posed there in front of the church they already had 
the look of old photos. 
In the sepia monochrome of a rainy day in the lost village they'd grown old instantly"
Cormac McCarthy


(b) Copying data from file A into File B.
  • If destination file exists, open it in Append mode.
set serveroutput on 
declare 
  v1 varchar2(32767);
  v2 varchar2(32767);
  fsource utl_file.file_type;
  fdest utl_file.file_type;
  vinfo varchar2(1000);
  type FileAttrRec is Record (
                      vfilexists BOOLEAN,
                      vfilelength number,
                      vblocksize  binary_integer);
  vfilerec fileattrrec;
  
begin
  fsource := utl_file.fopen('USER_DIR', 'fsource.txt', 'R', 1024);
    utl_file.fgetattr('USER_DIR', 'fdest.txt', vfilerec.vfilexists, 
                     vfilerec.vfilelength, vfilerec.vblocksize);
  
  If vfilerec.vfilexists THEN 
      fdest   := utl_file.fopen('USER_DIR', 'fdest.txt', 'a', 1024);
      dbms_output.put_line('Destination file exists. Appending..');
  ELSE
      fdest   := utl_file.fopen('USER_DIR', 'fdest.txt', 'W', 1024);
  END IF;
  
  if utl_file.is_open(fsource) and utl_file.is_open(fdest) then 
     dbms_output.put_line('Files fsource and fdest opened successfully..');
  end if;

  begin
    select sysdate into vinfo from dual;
    vinfo := vinfo || '. Entering new data: ';
    utl_file.put_line(fdest, vinfo, true);
    loop 
      utl_file.get_line(fsource, v1, 32767);
      dbms_output.put_line('Transferring: ' || v1);
      utl_file.put_line(fdest, v1, TRUE);
      v2 := v2 || chr(10)|| v1;
    end  loop;
  exception
    when no_data_found then 
      null;
  end;
  utl_file.fclose(fsource);
  utl_file.fclose(fdest);
  dbms_output.put_line(v2);
end;