Difference between package and serially_reusable package

A package once executed will have its variables in UGA (User Global Area). This is by default. If you are creating a package which will be executed only once, then the memory used by the package could be freed up after its execution.

It is using a pragma (hint to compiler) that we do so. This directive is called as serially_reusable. It tells the compiler to run the package and free the space once its executed. The compiler on getting this directive does not save the program variables in UGA but it does in SGA (Shared Global Area).

Each time the package with serially_reusable directive is called, its public variables are initialized. But in a normal package its not initialized every time the package is called.

Here is an example:
create or replace package pkg_with_pragma is
pragma serially_reusable;
n number := 5; -- default initialization
end pkg_with_pragma;


set serveroutput on


BEGIN
pkg_with_pragma.N := 10;
dbms_output.put_line(pkg_with_pragma.N);
END;
/


Output is
10


BEGIN
dbms_output.put_line(pkg_with_pragma.N);
END;
/


Output is
5


create or replace package pkg_without_pragma is
n number := 5; -- default initialization
end pkg_without_pragma;
/


BEGIN
pkg_without_pragma.N := 10;
dbms_output.put_line(pkg_without_pragma.N);
END;
/


Output is
10


BEGIN
pkg_without_pragma.N := 10;
dbms_output.put_line(pkg_without_pragma.N);
END;
/


Output is
10


References: PL/SQL User's Guide and Reference, Oracle® Database Application Developer's Guide - Fundamentals, Burleson Consulting

Unwrap Oracle 10g/11g PLSQL

Article and Script Courtesy: Niels Teusink


The Oracle wrap utility can be used to obfuscate PL/SQL code, to ensure it can't be easily read. Pete Finnigan described (pdf) the wrapping process for Oracle 9g, but for 10g and 11g it still remains a bit of a mystery. I decided to release my Python unwrapping utility (supports 10g and 11g).

The unwrapping steps for 10g are nicely described in the Oracle Hacker's Handbook, but the actual substitution table needed to decode the package is omitted. Nobody (as far as I know) has published it. A lot of people seem to know how to do it though, there is even an online unwrapper available (and I'm sure everyone seriously involved in Oracle security knows how to do it). A Russian-made closed source tool is also available, but tends to upset virus scanners.

So to save everyone a couple of hours of figuring it out, here it is: unwrap.py

It's easy to use (I've used the wrapped procedure from this article as an example):

$ ./unwrap.py wrapped.txt
=== Oracle 10g/11g PL/SQL unwrapper - by Niels Teusink - blog.teusink.net ===

PROCEDURE WRAP_IT (SEED_IN NUMBER)
IS
  V_RAND INTEGER;
BEGIN
  DBMS_RANDOM.INITIALIZE (SEED_IN);
  FOR I IN 1..5 LOOP
   V_RAND := MOD(ABS(DBMS_RANDOM.RANDOM),45);
   DBMS_OUTPUT.PUT_LINE(I||': '||V_RAND);
  END LOOP;
END;


Ask Anantha Team thanks Niels for permitting to post his article here. 

Oracle 11g Enhancements-Compound Triggers

This feature is part of trigger enhancements released in Oracle 11g Release 1. This particular enhancement paves way to use a single trigger to perform more tasks which could'nt have been possible before.

For example, you can use a single trigger for row triggers as well as row-level triggers. The syntax for a compound trigger is as follows:

CREATE OR REPLACE TRIGGER triggername
  FOR triggeraction ON tablename
  COMPOUND TRIGGER


  -- Variable declaration.


  BEFORE STATEMENT IS
  BEGIN
    /*Some PL/SQL Code here*/
  END BEFORE STATEMENT;


  BEFORE EACH ROW IS
  BEGIN
    /*Some PL/SQL Code here*/
  END BEFORE EACH ROW;


  AFTER EACH ROW IS
  BEGIN
    /*Some PL/SQL Code here*/
  END AFTER EACH ROW;


  AFTER STATEMENT IS
  BEGIN
    /*Some PL/SQL Code here*/
  END AFTER STATEMENT;


END triggername;
/

Where triggeraction can be INSERT, UPDATE or DELETE.

Example:-

CREATE OR REPLACE TRIGGER t_employee FOR INSERT OR UPDATE OR DELETE ON emp COMPOUND TRIGGER
  l_emp_id emp.empno%type;


  BEFORE STATEMENT IS
  BEGIN
    IF INSERTING THEN
dbms_output.put_line('INSERT - Before Statement Trigger');
END IF;
IF UPDATING THEN
dbms_output.put_line('UPDATE - Before Statement Trigger');
END IF;
IF DELETING THEN
dbms_output.put_line('DELETE - Before Statement Trigger');
END IF;
  END BEFORE STATEMENT;
  
  BEFORE EACH ROW IS
  BEGIN
    IF INSERTING THEN
dbms_output.put_line('INSERT - Before Each Row Trigger');
END IF;
IF UPDATING THEN
dbms_output.put_line('UPDATE - Before Each Row Trigger');
END IF;
IF DELETING THEN
dbms_output.put_line('DELETE - Before Each Row Trigger');
END IF;
  END BEFORE EACH ROW;
  
  AFTER EACH ROW IS
  BEGIN
    IF INSERTING THEN
dbms_output.put_line('INSERT - After Each Row Trigger');
END IF;
IF UPDATING THEN
dbms_output.put_line('UPDATE - After Each Row Trigger');
END IF;
IF DELETING THEN
dbms_output.put_line('DELETE - After Each Row Trigger');
END IF;
  END AFTER EACH ROW;
  
  AFTER STATEMENT IS
  BEGIN
    IF INSERTING THEN
dbms_output.put_line('INSERT - After Statement Trigger');
END IF;
IF UPDATING THEN
dbms_output.put_line('UPDATE - After Statement Trigger');
END IF;
IF DELETING THEN
dbms_output.put_line('DELETE - After Statement Trigger');
END IF;
  END AFTER STATEMENT;


END t_emplouee;
/

I don't have a 11g database to test create this script. Just create this trigger and there goes the tension of maintaining multiple triggers.

Oracle Interview Questions - 1

Some Oracle Interview questions uploaded:

• What are the components of physical database structure of Oracle database?
• What are the components of logical database structure of Oracle database?
• What is a tablespace?
• What is SYSTEM tablespace and when is it created?
• Explain the relationship among database, tablespace and data file.
• What is schema?
• What are Schema Objects?

and much more answered...


Oracle_interview_Questions -