Ask Anantha

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

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

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*/ ...

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 -