Ask Anantha

Prior to Oracle 11g, we have used PLS_INTEGER data type in PL/SQL programs. In 11g, a new data type SIMPLE_INTEGER has been introduced. It is a sub-type of PLS_INTEGER data type and has the same range as PLS_INTEGER. The basic difference between the two is that SIMPLE_INTEGER is always NOT NULL . When the value of the declared variable is never going to be null then we can declare it with SIMPLE_INTEGER data type. Another major difference is that it never gives numeric overflow error like its parent data type instead it wraps around without giving any error. When we don’t have to worry about null checking and overflow errors, SIMPLE_INTEGER data type is the best to use. Posted by decipherinfosys , More information check Oracle Documentation

Virtual Columns has been introduced in Oracle Database 11g Release 1. Here is a good tutorial I could find from Oracle-Base website. The link for the tutorial is at the bottom of this article. - Anantha When queried, virtual columns appear to be normal table columns, but their values are derived rather than being stored on disc. The syntax for defining a virtual column is listed below. column_name [datatype] [GENERATED ALWAYS] AS (expression) [VIRTUAL] If the datatype is omitted, it is determined based on the result of the expression. The GENERATED ALWAYS and VIRTUAL keywords are provided for clarity only. The script below creates and populates an employees table with two levels of commission. It includes two virtual columns to display the commission-based salary. The first uses the most abbreviated syntax while the second uses the most verbose form. CREATE TABLE employees (  id          NUMBER,  first_name  VARCHAR...

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