(19-20) PL/SQL Compiler


PL/SQL Compiler
  • Several initialization parameters (compiler parameters) affect the compilation of PL/SQL units.
  • The values at the time of compilation of the PLSQL_CCFLAGS, PLSQL_CODE_TYPE, PLSQL_DEBUG, PLSQL_OPTIMIZE_LEVEL, PLSQL_WARNINGS, and NLS_LENGTH_SEMANTICS initialization parameters are stored with the unit's metadata.
  • ALL_PLSQL_OBJECT_SETTINGS view: Keeps information about the settings of these parameters.


PLSCOPE_SETTINGS Controls compile-time collection, cross-reference, and storage of PL/SQL source text identifier data.
PLSCOPE_SETTINGS = IDENTIFIERS:{ NONE | ALL }
PLSQL_CCFLAGS Enables you to control conditional compilation of each PL/SQL unit independently.
PLQL_CODE_TYPE Defines compilation mode for PL/SQL libary units.
PLSQL_CODE_TYPE = { INTERPRETED | NATIVE }
PLSQL_OPTIMIZE_LEVEL The higher the setting of this parameter, the more effort the compiler makes to optimize PL/SQL library units.
Range: {0,3}
PLSQL_DEBUG Specifies whether to compile PL/SQL units for debugging.
Deprecated on 11g.
To compile PL/SQL units for debugging, specify PLSQL_OPTIMIZE_LEVEL=1.
PLSQL_NATIVE_LIBRARY_DIR
PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT
Related to Native Compilation.Deprecated on 11g.
PLSQL_WARNINGS Enables/disables the reporting of warning messages by the PL/SQL compiler.
Compile-time warning categories: [ SEVERE | PERFORMANCE | INFORMATIONAL ]
NLS_LENGTH_SEMANTICS




PL/SQL Optimizer
Prior to Oracle Database 10g Release 1 (10.1), the PL/SQL compiler translated your source text to system code without applying many changes to improve performance. Now, PL/SQL uses an optimizer that can rearrange code for better performance.

The optimizer is enabled by default. In rare cases, if the overhead of the optimizer makes compilation of very large applications too slow, you can lower the optimization by setting the compilation parameter PLSQL_OPTIMIZE_LEVEL=1 instead of its default value 2. In even rarer cases, PL/SQL might raise an exception earlier than expected or not at all. Setting PLSQL_OPTIMIZE_LEVEL=1 prevents the code from being rearranged.



On 10g R1

PLSQL_OPTIMIZE_LEVEL init parameter: Controls Global optimization of Pl/SQL Code. Default value is often good enough.

Native compilation: fewer initialization parameters to set and less compiler configuration. Object code is stored in the database.
  • PLSQL_NATIVE_LIBRARY_DIR: Only parameter required.(Deprecated on 11g)
  • PLSQL_CODE_TYPE: turn native compilation ON|OFF.
  • PLSQL_COMPILER_FLAGS: Deprecated.
  • $ORACLE_HOME/plsql/spnc_commands file contains the commands and options for compiling and linking
  • dbmsupgnv.sql: recompiles all the PL/SQL modules in a database as NATIVE.
  • dbmsupgin.sql: recompiles all the PL/SQL modules in a database as INTERPRETED.

Compile-Time Warnings
  • May be issued when you compile subprograms that produce ambiguous results or use inefficient constructs.
  • Enable | Disable warnigns: Use PLSQL_WARNINGS init parameter or DBMS_WARNING package.


On 10g R2
  • Conditional Compilation
    • Enables selective inclusion of code depending on the values of the conditions evaluated during compilation. For example:
    • You can determine which PL/SQL features in a PL/SQL application are used for specific database releases.
    • Also useful when you want to execute debugging procedures in a development environment, but want to turn off the debugging routines in a production environment.
  • Dynamic Wrap
    • DBMS_DDL wrap subprograms obfuscate (hide) dynamically generated PL/SQL code units.

--------------------------------------


Pragmas
A pragma is an instruction to the compiler that it processes at compile time


Pragma Autonomous_transaction
  • Marks a routine [(not-nested) anonymous block, subprogram, method (of ADT), Trigger] as autonomous; that is, independent of the main transaction
  • Autonomous transactions do SQL operations and commit or rollback, without committing or rolling back the main transaction.
  • When you enter the executable section of an autonomous routine, the main transaction suspends. When you exit the routine, the main transaction resumes.
  • If you try to exit an active autonomous transaction without committing or rolling back, the database raises an exception.
  • To exit normally, you must explicitly commit or roll back all autonomous transactions.

(1) Autonomous PL/SQL Block
-- create table emp as bellow:
SQL> create table emp
 (name varchar2(20), jobid varchar2(10));

-- insert four initial rows and commit inserts.
SQL> insert into emp values('John', 'Accounting');
SQL> insert into emp values('Paul', 'Accounting');
SQL> insert into emp values('George', 'Ads');
SQL> insert into emp values('Ringo', 'Ads');
SQL> commit;

-- check the inserted rows.
SQL> select * from emp;

NAME                 JOBID
-------------------- ----------
John                 Accounting
Paul                 Accounting
George               Ads
Ringo                Ads

-- insert a 5th row. and do not commit.
-- Check the inserted row.
SQL> insert into emp values('Ghost', 'Ads');
1 row created.

SQL> select * from emp;
NAME                 JOBID
-------------------- ----------
John                 Accounting
Paul                 Accounting
George               Ads
Ringo                Ads
Ghost                Ads

-- Now add the autonomous block.

SQL> set serveroutput on
SQL> declare
  2     pragma autonomous_transaction;
  3    numemp number;
  4  begin
  5    select count(*) into numemp from emp;
  6    dbms_output.put_line('num emps before autonomous insert: ' || numemp);
  7    insert into emp values('Autonomous', 'New');
  8    dbms_output.put_line('Inserted: Autonomous');
  9    select count(*) into numemp from emp;
 10    dbms_output.put_line('num emps after autonomous insert: ' || numemp);
 11    commit;
 12  end;
 13  /
num emps before autonomous insert: 4
Inserted: Autonomous
num emps after autonomous insert: 5

PL/SQL procedure successfully completed.

-- Now outside the autonomous block, check the existing 
-- rows and rollback the insertion of "Ghost"
SQL> select * from emp;
NAME                 JOBID
-------------------- ----------
John                 Accounting
Paul                 Accounting
George               Ads
Ringo                Ads
Ghost                Ads
Autonomous           New

SQL> rollback;

Rollback complete.

-- Check that the row for "Ghost" was rolled back, but the 
-- value inserted in the autonomous transaction remained.
SQL> select * from emp;

NAME                 JOBID
-------------------- ----------
John                 Accounting
Paul                 Accounting
George               Ads
Ringo                Ads
Autonomous           New
SQL>

(2) Autonomous standalone procedure
In the example above, the anonymous block could be rewritten as an autonomous procedure
as below:
CREATE OR REPLACE PROCEDURE ins_emp
  AS
    pragma autonomous_transaction;
    numemp number;
BEGIN
    select count(*) into numemp from emp;
    dbms_output.put_line('num emps before autonomous insert: ' || numemp);
    insert into emp values('Autonomous', 'New');
    dbms_output.put_line('Inserted: Autonomous');
    select count(*) into numemp from emp;
    dbms_output.put_line('num emps after autonomous insert: ' || numemp);
    commit;
END ins_emp;

(3) Autonomous Triggers
A trigger must be autonomous to run TCL or DDL statements.
To run DDL statements, the trigger must use native dynamic SQL.






Associates a user-defined exception name with an error code.
Can appear only in the same declarative part as its associated exception, anywhere after the exception declaration.
DECLARE
    past_due  EXCEPTION;                       -- declare exception
    PRAGMA EXCEPTION_INIT (past_due, -20000);  -- assign error code to exception
  BEGIN
    ...
Check herefor an example.

Pragma Inline (check here)
  • Specifies whether a subprogram invocation (or statement) is to be inlined.
  • Inlining replaces a subprogram invocation with a copy of the invoked subprogram (if the invoked and invoking subprograms are in the same program unit).
  • To allow subprogram inlining: PLSQL_OPTIMIZE_LEVEL compilation parameter := [2 (default) | 3 ]
  • If a particular subprogram is inlined, performance almost always improves. However, because the compiler inlines subprograms early in the optimization process, it is possible for subprogram inlining to preclude later, more powerful optimizations.

Pragma Restrict_References
  • Asserts that a user-defined subprogram does not read or write database tables or package variables.
  • Can appear only in a package specification or ADT specification. Typically, this pragma is specified for functions.
  • Subprograms that read or write database tables or package variables are difficult to optimize, because any invocation of the subprogram might produce different results or encounter errors.
  • .





Package...memory usage
...IS NOT
SERIALY_REUSABLE
Package state stored in the user global area (UGA) for each user.
The amount of UGA memory needed increases linearly with the number of users.
Package state can persist for the life of a session, locking UGA memory.
...IS
SERIALY_REUSABLE
Package state stored in a work area in a small pool in the system global area (SGA).
Package state persists only for the life of a server call, after which the work area returns to the pool. If a subsequent server call references the package, then Oracle Database reuses an instantiation from the pool. Reusing an instantiation re-initializes it; therefore, changes made to the package state in previous server calls are invisible.

CREATE OR REPLACE PACKAGE pkg IS
  PRAGMA SERIALLY_REUSABLE;
  n number:= 5;
END;
/
 
CREATE OR REPLACE PACKAGE BODY pkg IS
  PRAGMA SERIALLY_REUSABLE;
BEGIN
  n := 5;
END;
/

  • Allow you to better manage memory for scalability.
  • Specifies that the package state is needed for only one call to the server, after which the storage for the package variables can be reused, reducing the memory overhead for long-running sessions.
  • Appropriate for packages that declare large temporary work areas that are used once in the same session.

--------------------------

Conditional compilation
  • The Conditional compilation (CC) feature and related PL/SQL packages are available for the 10g Release 1 (10.1.0.4) and later.
  • CC allows constructs — with formally defined syntax and semantics — to be used to mark up text so that a preprocessor can deterministically derive the text that will be submitted to the compiler proper.
  • CC uses (a) selection directives($) (similar to IF stmt), (b) inquiry directives($$), and (c) error directives.
  • CC uses preprocessor control tokens($) to mark code that is processed before the PL/SQL unit is compiled: $IF, $THEN, $ELSE, $ELSIF, $ERROR
  • Oracle’s Applications Division provided the use case that motivated the introduction of PL/SQL conditional compilation. They wanted their code to be able to span different releases of Oracle Database, using the latest features in the latest release and using a fallback in earlier releases.(see more here)

Uses of Conditional Compilation (CC) {see detailed discussion in this white paper}
  • Allowing self-tracing code to be turned on during development and to be turned off when the code goes live.
  • Allowing alternative code fragments, each appropriate for the peculiarities of a particular operating system and inappropriate or illegal for other operating systems, to coexist in the same source text so the correct fragment can be selected for compilation according to the circumstances.
  • Newer Oracle releases introduce new features with new syntax and programs that take advantage of these are illegal in earlier releases. PL/SQL conditional compilation supports this use in an elegant and powerful way.
  • A developer often realizes that more than one approach to the design of a subprogram will result in its correct behavior; sometimes the alternative approaches result in source code versions which are textually largely the same but which differ critically in small areas distributed fairly evenly thought the source. PL/SQL conditional compilation allows all the approaches to be coded in a single source text — while they are being evaluated — and thereby eliminates the risk of carelessly introduced unintended differences.
  • Modular delivery of extra functionality can be implemented by optional PL/SQL compilation units which are installed according to what the customer has licensed. PL/SQL’s dependency model prevents the core part of the application referring statically to optional components that are not installed. However, the core part of the application should not need reinstallation in order to accommodate the installation of a new optional component. This has forced the use of dynamic invocation — which has some drawbacks. Conditional compilation allows a new approach.
Using selection directives ($IF, $ELSE) and error directives ($ERROR)
set serveroutput on
begin
  $IF dbms_db_version.ver_le_10_1 $THEN
     $ERROR  'unsupported database release' $END
  $ELSE
     dbms_output.put_line (
        'release ' || dbms_db_version.version || '.' || 
        dbms_db_version.release || ' is supported.'
     ); 
     -- since its a newer release, use a new commit syntax
    -- supported in 10.2
    commit write immediate nowait;
  $END
end;
/

anonymous block completed
release 11.2 is supported.

The package DBMS_DB_VERSION provide static constants:
  • dbms_db_version.version
  • dbms_db_version.release
  • dbms_db_version.ver_le_v
  • dbms_db_version.ver_le_v_r



Inquiry directives($$)
  • $$PLSQL_LINE: the number of the source line on which the directive appears in the current PL/SQL unit.
  • $$PLSQL_UNIT: the name of the current PL/SQL unit.

  • You can assign values to inquiry directives with the PLSQL_CCFLAGS compilation parameter
    • ALTER SESSION set PLSQL_CCFLAGS = 'flag:True, val:5'
  • $$plsql_compilation_parameter: a PL/SQL compilation parameter

Displaying Values of PL/SQL Compilation Parameters
BEGIN
  DBMS_OUTPUT.PUT_LINE('$$PLSCOPE_SETTINGS = '     || $$PLSCOPE_SETTINGS);
  DBMS_OUTPUT.PUT_LINE('$$PLSQL_CCFLAGS = '        || $$PLSQL_CCFLAGS);
  DBMS_OUTPUT.PUT_LINE('$$PLSQL_CODE_TYPE = '      || $$PLSQL_CODE_TYPE);
  DBMS_OUTPUT.PUT_LINE('$$PLSQL_OPTIMIZE_LEVEL = ' || $$PLSQL_OPTIMIZE_LEVEL);
  DBMS_OUTPUT.PUT_LINE('$$PLSQL_WARNINGS = '       || $$PLSQL_WARNINGS);
  DBMS_OUTPUT.PUT_LINE('$$NLS_LENGTH_SEMANTICS = ' || $$NLS_LENGTH_SEMANTICS);
END;
/

anonymous block completed
$$PLSCOPE_SETTINGS = 
$$PLSQL_CCFLAGS = 
$$PLSQL_CODE_TYPE = INTERPRETED
$$PLSQL_OPTIMIZE_LEVEL = 0
$$PLSQL_WARNINGS = DISABLE:ALL
$$NLS_LENGTH_SEMANTICS = BYTE

Using inquiry directives (example extracted from here)
-- USE PACKAGE SPEC and BODY to define and declare a USER-DEFINED subtype.

-- Package specification: Use selection directive ($IF) to test for the 
-- version of the database.
-- If version < 10g, define subtype as NUMBER.
-- If version >= 10g, define subtype as BINARY_DOUBLE
create or replace package my_pkg AS
  subtype my_real IS
    $IF dbms_db_version.version < 10 $THEN
      number;
    $ELSE
      binary_double;
    $END
  
  my_pi my_real;
  my_e  my_real;
end my_pkg;
/

-- Package body: test for db version. Assign values
-- for the subtype variable accordingly. 
create or replace package body my_pkg AS
begin
$IF dbms_db_version.version < 10 $THEN 
     my_pi := 3.14159265358979323846264338327950288420;
    my_e  := 2.71828182845904523536028747135266249775;
  $ELSE
    my_pi := 3.14159265358979323846264338327950288420d;
    my_e  := 2.71828182845904523536028747135266249775d;
  $END
END my_pkg;
/ 

-- Use the subtype defined in the package on a standalone procedure.

-- Uses inquiry directive($$my_debug) to decide whether or not 
-- to run some code. If $$my_debug = TRUE, run the debug code that 
-- check for the datatype of the subtype my_real.
create or replace procedure circle_area (radius my_pkg.my_real) IS
  my_area       my_pkg.my_real;
  my_data_type  varchar2(30);
begin
  my_area := my_pkg.my_pi * (radius**2);
  
  dbms_output.put_line
    ('Radius: '|| to_char(radius) || ' Area: '|| to_char(my_area));
  
  $IF $$my_debug $THEN
    select data_type into my_data_type
    from user_arguments
    where object_name = 'CIRCLE_AREA'
    and ARGUMENT_NAME = 'RADIUS';
    
    dbms_output.put_line
     ('Data type of the RADIUS argument is: '|| my_data_type);
  $END
end circle_area;
/

-- Run procedure circle_area 
(1) Set $$my_debug: FALSE
SQL> alter session set plsql_ccflags = 'my_debug:FALSE';
Session SET altered.

SQL> set serveroutput on 
SQL> exec circle_area(2);
Radius: 2.0E+000 Area: 1.2566370614359172E+001
PL/SQL procedure successfully completed.

(1) Set $$my_debug: TRUE
SQL> alter session set plsql_ccflags = 'my_debug:TRUE';
Session SET altered.

-- You need to recompile the procedure circle_area so it can 
-- see the change in the session 
SQL> alter procedure circle_area compile;
Procedure altered.

SQL> set serveroutput on 
SQL> exec circle_area(2);
Radius: 2.0E+000 Area: 1.2566370614359172E+001
Data type of the RADIUS argument is: BINARY_DOUBLE

PL/SQL procedure successfully completed.

Using DBMS_PREPROCESSOR to print source text
  • You can use the dbms_preprocessor package to print the source text executed after
    the conditional compilation directives were all processed.
  • For the procedure circle_area above, you can retrieve the source code processed when the
    debug directive ($$my_debug) is set to TRUE.
  • Subprograms
    • DBMS_PREPROCESSOR.PRINT_POST_PROCESSED_SOURCE
    • DBMS_PREPROCESSOR.GET_POST_PROCESSED_SOURCE (overloaded (3))


SQL> call dbms_preprocessor.print_post_processed_source
  2                                             ('PROCEDURE', 'DEV2', 'CIRCLE_AREA');
procedure circle_area (radius my_pkg.my_real) IS
my_area       my_pkg.my_real;
my_data_type  varchar2(30);
begin
my_area := my_pkg.my_pi * (radius**2);
dbms_output.put_line
('Radius: '|| to_char(radius) || ' Area: '|| to_char(my_area));
select data_type into my_data_type
from user_arguments
where object_name = 'CIRCLE_AREA'
and ARGUMENT_NAME = 'RADIUS';
dbms_output.put_line
('Data type of the RADIUS argument is: '|| my_data_type);
end circle_area;

Call completed.


CC Restrictions:
  • You cannot have a CC directive within a CREATE TYPE statement. (Why? Attributes of a TYPE will determine the physical structure of dependent tables.)
  • Package Spec, Package body, type body, procedure/function w/o parameters: first conditional compilation directive cannot appear before the keyword IS or AS.
  • subprogram with at least one formal parameter: the first conditional compilation directive cannot appear before the left parenthesis that follows the subprogram name.
  • Trigger or an anonymous block, the first conditional compilation directive cannot appear before the keyword DECLARE or BEGIN, whichever comes first.



On 11g R2: Compiling PL/SQL Units for Native Execution

  • PL/SQL units can be compiled into native code (processor-dependent system code), which is stored in the SYSTEM tablespace.
  • Any PL/SQL unit of any type can be natively compiled, including Oracle supplied ones.
  • Natively compiled program units work in all server environments.
  • Greatest performance gains: for computation-intensive procedural operations. (i.e. Data warehouse and applications with extensive server-side transformations of data for display).
  • Least performance gains: for PL/SQL subprograms that spend most of their time running SQL.
  • The PLSQL_CODE_TYPE compilation parameter determines whether PL/SQL code is natively compiled or interpreted.
    • ALTER PROCEDURE my_proc COMPILE PLSQL_CODE_TYPE=NATIVE REUSE SETTINGS;
  • Run this query to determine how many objects are compiled NATIVE and INTERPRETED:
SELECT TYPE, PLSQL_CODE_TYPE, COUNT(*)
FROM DBA_PLSQL_OBJECT_SETTINGS
WHERE PLSQL_CODE_TYPE IS NOT NULL
GROUP BY TYPE, PLSQL_CODE_TYPE
ORDER BY TYPE, PLSQL_CODE_TYPE;


Obfuscation and wrapping













































Oracle Backup and Recovery solutions


Basic solutions available for a backup and recovery strategy:





Backup techniques: comparison

Feature Recovery Manager User-managed Data Pump exp
Closed database backup Yes. Needs instance Mounted Yes No
Open database backup Need use of BEGIN/END BACKUP Need use of BEGIN/END BACKUP Requires rollback or undo segments
Incremental backup Yes No No
Corrupt block detection Indentifies corrupt blocks and logs in V$DATABASE_BLOCK_CORRUPTION No Identifies corrupt blocks in the export log.
Automatic specification of files to include in a backup Establishes name and location of all files to be backed up Files must be located and copied manually n/a
Backup repository Backups recorded in the control file. You Can store backup metadata in a recovery catalog. DBA needs to keep records. n/a
Backup to a media manager Yes. RMAN also supports proxy copy. Backup to tape is manual or controlled by media manager. Not supported.
Backup init.ora file Yes Yes No
Backup password and networking files No Yes No
Platform-independent language for backups Yes No yes















Flashback features: Logical and Physical levels

Oracle Flashback features
requires flashback data archiving. OFF for all tables (default).
Turn it on for individual tables
Operate at the logical level, using undo data.
Oracle flashback query Specify target time. Run queries. Results displayed as they would have appeared at the target time.
Oracle flashback version query View all version of rows that existed in a table within a time range
Oracle flashback transaction query View changes made by a single transaction
Oracle flashback transaction Reverse a transaction. Wow.
Oracle flashback table Recover table or set of tables to a point in time.
Oracle flashback drop Reverse the effects of a drop table.
Flashback database (RMAN: FLASHBACK DATABASE)
operates at the physical level. Uses flashback logs.
Can be used against specific data files.
More efficient than database point-in-time recovery.
Faster because does not require datafile restor.
Uses flashback logs. Not enabled by default.
Need a fast recovery area congifured.
You can set guaranteed restore points (alias for an SCN).





















































(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.

Compilation Warnings, Compilation errors, Run-time errors








Compilation Warnings
  • PL/SQL compiler generates warnings for conditions that are not serious enough to cause errors and prevent compilation
  • The message code of a PL/SQL warning has the form PLW-nnnnn
  • Warnings can be: SEVERE, PERFORMANCE, or INFORMATIONAL.
  • Compilation parameter: PLSQL_WARNINGS. You may:
    • Enable or disable the reporting of warning messages by the PL/SQL compiler, and specify which warning messages to show as errors.
    • Treat specific warnings as errors (so that those conditions must be corrected before you can compile the PL/SQL unit)
    • Use with ALTER SESSION or ALTER SYSTEM or on a stored PL/SQL unit.


Syntax:


PLSQL_WARNINGS = { ENABLE | DISABLE | ERROR }:
   { ALL | SEVERE | INFORMATIONAL | PERFORMANCE | { int | (int [, int ] ...)}}

---- i.e. ---- 
SQL> ALTER SESSION SET PLSQL_WARINGS = 'ENABLE:ALL';
SQL> ALTER PROCEDURE p1 COMPILE 
                               PLSQL_WARNINGS = 'ENABLE:PERFORMANCE';
SQL> ALTER SYSTEM SET PLSQL_WARNINGS = 'ENABLE:(5000,5001,5002)', 'DISABLE:(6000,6001)';


  • With Oracle 11g, a new PL/SQL Compiler Warning has been added to identify WHEN OTHERS exception handlers that do no re-raise errors using RAISE or RAISE_APPLICATION_ERROR.
  • Such exception handlers can often hide code failures that result in hard to identify bugs.
  • The example below shows the expected compiler warning when the PLSQL_WARNINGS flag is set.
-- to check the current warnings status, execute the query below as SYSTEM:

SQL> select name, value from gv$parameter 
     where name like 'plsql%warn%';  
NAME                    VALUE
----------------------  -------------
plsql_warnings          DISABLE:ALL

Alternativelly, you can also use DBMS_WARNING pagckage:

SQL> select dbms_warning.get_warning_setting_string() from dual;

DBMS_WARNING.GET_WARNING_SETTING_STRING()
----------------------------------------- 
DISABLE:ALL    

SQL> alter session set plsql_warnings = 'ENABLE:ALL'; 

SQL> create or replace procedure others_test as
begin 
 raise_application_error(-20000, 'Force and exception'); 
exception
 when others then 
   null;
end;
/

Warning(5,1): PLW-06009: procedure "OTHERS_TEST" OTHERS handler does not end in RAISE or RAISE_APPLICATION_ERROR

Using the DBMS_WARNING package.
SQL> desc dbms_warning

PROCEDURE                             Argument Name    Type           IN/OUT Default 
------------------------------------- ---------------- -------------- ------ ------- 
ADD_WARNING_SETTING_CAT               WARNING_CATEGORY VARCHAR2       IN     unknown 
ADD_WARNING_SETTING_CAT               WARNING_VALUE    VARCHAR2       IN     unknown 
ADD_WARNING_SETTING_CAT               SCOPE            VARCHAR2       IN     unknown 
ADD_WARNING_SETTING_NUM               WARNING_NUMBER   BINARY_INTEGER IN     unknown 
ADD_WARNING_SETTING_NUM               WARNING_VALUE    VARCHAR2       IN     unknown 
ADD_WARNING_SETTING_NUM               SCOPE            VARCHAR2       IN     unknown 
GET_CATEGORY (FUNCTION)                  VARCHAR2       OUT    unknown 
GET_CATEGORY                          WARNING_NUMBER   BINARY_INTEGER IN     unknown 
GET_WARNING_SETTING_CAT (FUNCTION)       VARCHAR2       OUT    unknown 
GET_WARNING_SETTING_CAT               WARNING_CATEGORY VARCHAR2       IN     unknown 
GET_WARNING_SETTING_NUM (FUNCTION)       VARCHAR2       OUT    unknown 
GET_WARNING_SETTING_NUM               WARNING_NUMBER   BINARY_INTEGER IN     unknown 
GET_WARNING_SETTING_STRING (FUNCTION)    VARCHAR2       OUT    unknown 
SET_WARNING_SETTING_STRING            VALUE            VARCHAR2       IN     unknown 
SET_WARNING_SETTING_STRING            SCOPE            VARCHAR2       IN     unknown 

  • with all warnings disabled, a procedure with unreachable code compiles silently.
  • In the example below, since x is constant, the IF always evaluates TRUE: ELSE code never runs (unreachable).

SQL> alter session set plsql_warnings='DISABLE:ALL';
Session altered.

SQL> create or replace procedure unreach 
        authid definer as
  x constant boolean := true;
begin
  if x then
    dbms_output.put_line('true');
  else
    dbms_output.put_line('false');
  end if;
end unreach;
/
Procedure created.


(2) Now enable plsql warnings using the DBMS_WARNING package.
When the procedure is recompiled, a warning is issued.
SQL> call dbms_warning.set_warning_setting_string('ENABLE:ALL', 'SESSION');

Call completed

SQL> select dbms_warning.get_warning_setting_string() from dual;

DBMS_WARNING.GET_WARNING_SETTING_STRING()
----------------------------------------------
ENABLE:ALL

-- Recompile the  procedure.
SQL> alter procedure unreach compile;

SP2-0805: Procedure altered with compilation warnings

-- Check the errors in SQL*PLUS:
SQL> show errors procedure unreach;
Errors for PROCEDURE BROKEN:

LINE/COL ERROR
-------- -----------------------------------------------------------------
8/5  PLW-06002: Unreachable code

OR Query the USER_ERRORS view.
SQL> select name, type, line, position, text, attribute, message_number err 
     from user_errors;

(formatted output):
NAME      TYPE        LINE  POSITION   TEXT                          ATTRIBUTE ERR
--------- ----------- ----- ---------- ----------------------------- --------- -----
BROKEN    PROCEDURE   4      6          PLW-06002: Unreachable code  WARNING   6002
BROKEN    PROCEDURE   7      5          PLW-06002: Unreachable code  WARNING   6002

Compilation Errors
  • Generally these are typing errors
  • the parser transforms the PL/SQL text file into a set of interpreted instructions (p-code): it may find lexical errors.
  • Lexical errors caught when the plain text file is parsed during compilation.
  • Deferred compilation errors: when actual data values don’t fit during assignment because they are too large or the wrong datatype.
  • Compilation errors cannot be handled by your local exception handler, but you can catch them in a wrapper (containing outer) block.
  • Compilation errors can be: prior line, current line or declaration errors.


Viewing errors
To see warnings (and errors) generated during compilation, use either:
  • Static data dictionary view *_ERRORS
  • In SQL*Plus: command SHOW ERRORS.


Run-time errors
  • Occur when actual data fails to meet the rules defined by your program unit
  • run-time manageable errors (execution or exception blocks)
  • run-time unmanageable errors (declaration section)

(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;