Ask Anantha

What is SQL Injection?

2011-11-29T00:52:00.000+05:30

SQL Injection as the name suggests is injecting arbitrary SQL commands or clauses into an executing program to defeat its purpose.

Why does one inject SQL commands to defeat the purpose of a procedure. The answer is 'hackers'. Hackers are always looking for easy preys to steal another ones information. In this age of Information Technology, unnoticed stealing is for information. The information thus gathered can be consolidated by an experienced hacker and cause 'hell lot of trouble'.

Now as we are clear of the intentions of defeating a program, we will understand the simple types of SQL Injection which are very well-known.

First Order Attack
Second Order Attack
Lateral Injection

First Order Attack is caused when a hacker simply modifies the string passed to a procedure and adds a malicious string to make the program work even if without valid data.

For example consider the following code:

create table users (username varchar2(20), password varchar2(20));

insert into users values('Anantha','111111');

commit;

create or replace function get_cnt (qry in varchar2) return number is

lv_cnt number;

begin

execute immediate qry into lv_cnt;

return lv_cnt;

end;

begin

if get_cnt('select count(1) from users where username=''Anantha''

and password=''111111''') = 0 then

dbms_output.put_line('Invalid Credentials');

else

dbms_output.put_line('Valid Credentials');

end if;

end;

In the above example an inexperienced programmer has written a procedure that accepts a string for a query. He has also written an EXECUTE IMMEDIATE which executes the query and returns the count of records (His assumptions are that the query will have a single select list with COUNT(1) in it. The values for username and password, say are being captured from front-end and modified in the string.

It seems fair enough until the hacker injects a ALL-SUCCESS where clause to the password field. The hacker simply types 222' OR 1=1 in the password field. The resulting string passed to the procedure will be of the following form:

begin

if get_cnt('select count(1) from users where username=''Anantha''

and password=''222'' OR 1=1') = 0 then

dbms_output.put_line('Invalid Credentials');

else

dbms_output.put_line('Valid Credentials');

end if;

end;

The procedure has been defeated. Now imagine a procedure safe-guarding your application. Isn't this a nightmare?

How do we defend such simple attacks? The inexperienced programmer has now some experience and rewrites his procedure as follows:

create or replace procedure validate_user(uname in users.username%type, pwd in users.password%type) is

lv_cnt number;

begin

select count(1) into lv_cnt from users u where u.username=uname and u.password=pwd;

if lv_cnt = 0 then dbms_output.put_line('Invalid credentials');

else dbms_output.put_line('Valid credentials');

end if;

end;

Now what if the hacker attacks again? The procedure code will be like:

begin

validate_user('Anantha','222'' OR 1=1');

end;

But the output always defeat the hacker's purpose of defeating the logic. How come this did not work in favor of the hacker. Because Oracle is intelligent enough to understand/bind value to a variable when already the query is known. The late binding of variable to the column does not allow the hacker to get his success.

The moral learnt is when using EXECUTE IMMEDIATE or similar query when it has been framed by the programe, always test it thoroughly.

Next issue, Second Order Attack.

PL/SQL Server Pages or PSP

2011-11-27T22:10:00.001+05:30

Have you heard of JSP?
Have you heard of ASP?
Have you heard of PHP?

Of course I have heard of them all. They all does one function, generate web pages dynamically to deliver a nice and rich front-end to the web.

But Have you heard of PSP? What?

PSP is acronym for Oracle's PL/SQL Server Pages. Oracle has this kind of capability? Well Oracle always has this sort of capability but it was called in a rather different name. It was and is called PL/SQL Web Toolkit.

But rather unknown or less used fact is Oracle also has an extension to this. This is called as PSP. I have created a whitepaper which is here for you to read:

For those who are unable to view the presentation in this web page or to view in Full screen, click here.

Remove leading space in to_char

2011-11-02T22:44:00.000+05:30

One point which is usually overseen with the usage of TO_CHAR() is the leading space that it returns when converting a number to character.

Example:
select length(to_char('109','000.00')) from dual;

TO_CHAR('109','000.00')
" 109.00"

Do you notice the leading space that TO_CHAR() has returned? If you have not noticed and surprised, execute this and find it for yourself.

This is not a bug.

The Reason is Oracle reserves the first character for Sign. If you won't beleive me, execute the following statement:

select to_char('-109','000.00') from dual;

TO_CHAR('-109','000.00')

"-109.00"

Notice that the length of the string has not increased with the negative value being converted by TO_CHAR().

Hold on your tendency of calling TRIM() function to trim out the space. TO_CHAR() has a format model that suppresses this trailing space.

To suppress this leading space simply use the FM (stands for Fill Mode) in your format string.

select to_char('109','fm000.00') from dual;

TO_CHAR('109','fm000.00')

"109.00"

In case you don't need any specific format for the string, but just to ignore the trailing space, use the TM (stands for Text Minimum) in your format string.

select to_char('109.90','tm') from dual;

TO_CHAR('109','tm')

"109"

Further Reading: Ask Tom, Oracle Documentation for Format Models

Difference between two timestamp in seconds

2011-09-28T12:34:00.000+05:30

Question: How can I get difference between two timestamp variables in seconds in Oracle?

Answer: Use interval datatype to store the difference. Example code is below.

DECLARE
l_start_time TIMESTAMP;
l_end_time TIMESTAMP;
l_sec INTERVAL DAY(9) TO SECOND(6);
BEGIN
l_start_time := systimestamp;
FOR i IN 1 .. 100000 LOOP
NULL;
END LOOP;
l_end_time := systimestamp;

l_sec := l_end_time - l_start_time;
dbms_output.put_line('Seconds past=' || abs(extract(SECOND FROM l_sec) + extract(minute FROM l_sec) * 60 + extract(hour FROM l_sec) * 60 * 60 + extract(DAY FROM l_sec) * 24 * 60 * 60));
END;

Output is:
Seconds past=.001144

Oracle XE - Ubuntu - Solution to 'cannot access http://127.0.0.1:8080/apex'

2011-09-17T14:49:00.001+05:30

I received some queries lately from various users regarding unable to open page http://localhost:8081/apex or http://127.0.0.1:8081/apex.

The linux environment reported was Ubuntu and here is how to solve this error:

Open command prompt and execute the following command:

sudo /etc/init.d/oracle-xe force-reload

This command will reload the database and settings. (If you are using SQL Plus session connection with database be sure to save all your data as the above command will restart the database.)

How to import a Java Jar to Oracle Forms

2011-06-09T12:29:00.002+05:30

The steps involved are simple as below:

1. Save the jar file where forms jar files are saved
Usually {FormsInstalledPath}\forms\java folder

2. Open Registry editor (REGEDIT)
Browse to the following Key
HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE/KEY_DevSuiteHome1

3. Add Java archive name in the Name FORMS_BUILDER_CLASSPATH

4. Now open Form Builder and create new form.

5. Open Menu Program > Import Java Classes and find your java class added and Import.

Enjoy

CSV data to multiple rows - Oracle Query

2011-06-02T13:28:00.000+05:30

I have a data in CSV format (comma separated values) like follows:

first,second,third,fourth,fifth

I want the output as follows:
column_value
first
second
third
fourth
fifth

The easiest way to achieve is to use a CONNECT BY clause in DUAL table and to populate the results. Here is the query:

SELECT substr(str, instr(str, ',', 1, LEVEL) + 1, instr(str, ',', 1, LEVEL + 1) - instr(str, ',', 1, LEVEL) - 1) column_value
FROM (SELECT ',' || '&mystring' || ',' str FROM dual)
CONNECT BY LEVEL <= length(str) - length(REPLACE(str, ',')) - 1;

Pass value first,second,third,fourth,fifth to mystring.

11g New Feature - SIMPLE_INTEGER

2011-03-03T18:51:00.000+05:30

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

Load CSV data to Oracle or other databases

2011-02-24T19:47:00.000+05:30

This time it is a review of a product which is freely available for download. The CSV Loader. It is a Java Application that can load data to databases such as Oracle, MySQL and PostgreSQL databases. The software is available for download here http://sourceforge.net/projects/csvloader/

The installation instructions are also available through this website http://csvloader.sourceforge.net/

What makes this tool unique is its integration with SunGard Banner software. It is a big replacement for SQL Loader provided by Oracle. You can either use its interface to specify the tables in which the data needs to be imported or pretty advanced mode where you can edit the configuration file and upload the details.

I loved the tool, and in initial attempt loaded about 1000s of records to a table. I as a developer became free to use the data to load it to appropriate tables.

I give a cool 4.5 star rating out of 5 for this tool. I reserve 0.5 points for little user-interface difficulties which prevent me from specifying the target table/column easily. I had to type the schema and table name each time, and then select the column.

From its interface it looks like target table can be multiple, have not tested it. Anyone interested in knowing more, I am happy to help.

Virtual Columns in Oracle Database 11g Release 1

2011-01-11T11:33:00.001+05:30

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 VARCHAR2(10),
last_name   VARCHAR2(10),
salary      NUMBER(9,2),
comm1       NUMBER(3),
comm2       NUMBER(3),
salary1     AS (ROUND(salary*(1+comm1/100),2)),
salary2     NUMBER GENERATED ALWAYS AS (ROUND(salary*(1+comm2/100),2)) VIRTUAL,
CONSTRAINT employees_pk PRIMARY KEY (id)
);

INSERT INTO employees (id, first_name, last_name, salary, comm1, comm2)
VALUES (1, 'JOHN', 'DOE', 100, 5, 10);

INSERT INTO employees (id, first_name, last_name, salary, comm1, comm2)
VALUES (2, 'JAYNE', 'DOE', 200, 10, 20);
COMMIT;

Querying the table shows the inserted data plus the derived commission-based salaries.

SELECT * FROM employees;

       ID FIRST_NAME LAST_NAME      SALARY      COMM1      COMM2    SALARY1    SALARY2
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
        1 JOHN       DOE               100          5         10        105        110
        2 JAYNE      DOE               200         10         20        220        240

2 rows selected.

The expression used to generate the virtual column is listed in the DATA_DEFAULT column of the
[DBA | ALL | USER]_TAB_COLUMNS views.

COLUMN data_default FORMAT A50
SELECT column_name, data_default
FROM   user_tab_columns
WHERE table_name = 'EMPLOYEES';

COLUMN_NAME                    DATA_DEFAULT
------------------------------ --------------------------------------------------
ID
FIRST_NAME
LAST_NAME
SALARY
COMM1
COMM2
SALARY1                        ROUND("SALARY"*(1+"COMM1"/100),2)
SALARY2                        ROUND("SALARY"*(1+"COMM2"/100),2)

8 rows selected.

Notes and restrictions on virtual columns include:

Indexes defined against virtual columns are equivalent to function-based indexes.
Virtual columns can be referenced in the WHERE clause of updates and deletes, but they cannot be manipulated by DML.
Tables containing virtual columns can still be eligible for result caching.
Functions in expressions must be deterministic at the time of table creation, but can subsequently be recompiled and made non-deterministic without invalidating the virtual column. In such cases the following steps must be taken after the function is recompiled:

Constraint on the virtual column must be disabled and re-enabled.
Indexes on the virtual column must be rebuilt.
Materialized views that access the virtual column must be fully refreshed.
The result cache must be flushed if cached queries have accessed the virtual column.
Table statistics must be regathered.

Virtual columns are not supported for index-organized, external, object, cluster, or temporary tables.
The expression used in the virtual column definition has the following restrictions:

It cannot refer to another virtual column by name.
It can only refer to columns defined in the same table.
If it refers to a deterministic user-defined function, it cannot be used as a partitioning key column.
The output of the expression must be a scalar value. It cannot return an Oracle supplied datatype, a user-defined type, or LOB or LONG RAW.

Here is an example of Virtual Column-Based Partitioning.

For more information see:

Brought to you from

How to get the last password changed time for a oracle user

2010-10-26T18:47:00.000+05:30

Question: How to get the last password changed time for a oracle user?
Answer: The SYS view user$ consists of a column PTIME which tells you what was the last time the password was changed for the user.

Try this query:

SELECT name,
   ctime,
   ptime
FROM sys.user$
WHERE name = 'USER-NAME';

Note: Replace USER-NAME with the user name which you want to know the information.

CTIME Indicates - Creation Time
PTIME Indicates - Password Change Time

Here's the table DESCription:
Name Type Nullable Default Comments
------------ ------------------- -------- ------- --------
USER# NUMBER
NAME VARCHAR2(30 BYTE)
TYPE# NUMBER
PASSWORD VARCHAR2(30 BYTE) Y
DATATS# NUMBER
TEMPTS# NUMBER
CTIME DATE
PTIME DATE Y
EXPTIME DATE Y
LTIME DATE Y
RESOURCE$ NUMBER
AUDIT$ VARCHAR2(38 BYTE) Y
DEFROLE NUMBER
DEFGRP# NUMBER Y
DEFGRP_SEQ# NUMBER Y
ASTATUS NUMBER 0
LCOUNT NUMBER 0
DEFSCHCLASS VARCHAR2(30 BYTE) Y
EXT_USERNAME VARCHAR2(4000 BYTE) Y
SPARE1 NUMBER Y
SPARE2 NUMBER Y
SPARE3 NUMBER Y
SPARE4 VARCHAR2(1000 BYTE) Y
SPARE5 VARCHAR2(1000 BYTE) Y
SPARE6 DATE Y

Default Schemas in 11g

2010-10-19T14:15:00.000+05:30

When an 11g database is created without tweaking any of the options, using either dbca or the installer, the schema listed in the table below, 36 of them(!), are created by default.

This document gives an overview of their purpose and function and, should the functionailty not be required, whether they can be safely deleted or not without compromising the fundamental operation of the database. Additionally, having been removed, if a schema is required again, there are details of which script(s) are required to be run in order to recreate it.

Click here to read the entire article which lists the schema's and its importance in 11g.

Metalink Note:
Further information can be found in Metalink article 160861.1

Difference between Oracle 10g, 11g with regard to Index Rebuild Online

2010-10-14T17:13:00.000+05:30

Creating or Rebuilding Indexes Online:

Online Index Rebuilds allows you to perform DML operations on the base table during index creation. You can use the CREATE INDEX ONLINE and DROP INDEX ONLINE statements to create and drop an index in an online environment.

During index build you can use the CREATE INDEX ONLINE to create an index without placing an exclusive lock over the table. CREATE INDEX ONLINE statement can speed up the creation as it works even when reads or updates are happening on the table.

The ALTER INDEX REBUILD ONLINE can be used to rebuild the index, resuming failed operations, performing batch DML, adding stop words to index or for optimizing the index.

The CREATE INDEX ONLINE and ALTER INDEX REBUILD ONLINE options have been there for a long time to easy the task of online index rebuilding. However in highly active they still can introduce locking issues.

Table Locks:

A table lock is required on the index base table at the start of the CREATE or REBUILD process to guarantee DD information. A lock at the end of the process is also required so as to merge changes into the final index structure.

The time taken to complete the indexing process will increase as the indexing process will hang if there is an active transaction on the base table of the index being created or rebuilt at the time one of these locks is required. Another important issue to be considered is that any other transaction taking place on the base table that started after indexing process will also be locked unless the indexing releases its locked.

This issue can have serious impact on the response time in highly concurrent systems. This backlog of locked transactions can be quite significant depending on the time taken by initial transactions to commit or rollback.

Oracle 11g

Oracle11g has provided enormous improvements in the locking implications regarding creating or rebuilding indexes online. Creating or Rebuilding Indexes Online in Oracle 11g also requires two associated locks on the base table. One lock is required at the start of indexing process and other at the end of indexing process.

The indexing process still hangs until all prior transactions have been completed if an active transaction is going on the base table at the time one of these locks is required.

However such transaction will no longer be locked and complete successfully if the indexing process has been locked out and subsequent transactions relating to the base table starts afterwards.

In Oracle 11g the indexing process no longer effects other concurrent transactions on the base table. The only process potentially left hanging while waiting to acquire its associated lock resource. Now we will compare the index rebuild locking mechanism in Oracle 10g and Oracle 11g.

The difference on ground (10g vs 11g)

Session No.	Executing in 10g	Executing in 11g
1	CREATE TABLE MYTABLE AS SELECT rownum id, ‘Anantha’ name from dual connect by level <= 200000; Table Created. CREATE INDEX MYTABLE_IDX on MYTABLE(id); Index Created.	CREATE TABLE MYTABLE AS SELECT rownum id, ‘Anantha’ name from dual connect by level <= 200000; Table Created. CREATE INDEX MYTABLE_IDX on MYTABLE(id); Index Created.
2	INSERT INTO MYTABLE VALUES(200001, ‘No Name’); 1 row created.	INSERT INTO MYTABLE VALUES(200001, ‘No Name’); 1 row created.
1	ALTER INDEX MYTABLE_INDEX REBUILD ONLINE; (Session gets hanged)	ALTER INDEX MYTABLE_INDEX REBUILD ONLINE; (Session gets hanged)
3	INSERT INTO MYTABLE VALUES(200002, ‘Again No Name’); 1 row created.	INSERT INTO MYTABLE VALUES(200001, ‘Again No Name’); 1 row created.
2	COMMIT; Commit Complete.	COMMIT; Commit Complete.
2	INSERT INTO MYTABLE VALUES(200003, ‘Some Name’); 1 row created.	INSERT INTO MYTABLE VALUES(200003, ‘Some Name’); 1 row created.
3	COMMIT; Commit Complete.	COMMIT; Commit Complete. (Session 2) Index Altered (Session 1)

To conclude I would say that even if an index rebuild process is performed online still it requires a lock at the start and at the end of the index rebuild process and these locks eventually causes other concurrent transactions on the table to be hanged.

Therefore an Online Index Rebuilding in Oracle 11g can be still affected by concurrent transactions. However it will not in turn cause locking issues for other concurrent transactions on the base table.

Who has locked my package?

2010-08-26T19:01:00.001+05:30

This question I have frequently faced while developing packages/procedures in PL/SQL in Oracle.

Question: How will I identify who has locked a procedure or a package. I need to identify this because it should not hang after the compilation is issued?

Answer: You may see the details from v$sql with the search text (Your package name). If the query fetches results then join it with v$session to get the session information.

select *
from v$sql, v$session
where sql_address=address
and upper(sql_text) like '%PACKAGE_NAME%';

Hope this simple tip saves you some time.

Getting file size with PL/SQL

2010-08-02T14:16:00.001+05:30

UTL_FILE procedure has been enhanced in Oracle 9i and since then it provides a procedure fgetattr to return file size. PL/SQL Evangelist Steven Feuerstein has come up with this function that returns file length.

CREATE OR REPLACE FUNCTION flength (
   location_in IN VARCHAR2,
   file_in IN VARCHAR2
)
   RETURN PLS_INTEGER
IS
   TYPE fgetattr_t IS RECORD (
   fexists BOOLEAN,
   file_length PLS_INTEGER,
   block_size PLS_INTEGER
   );

   fgetattr_rec fgetattr_t;
BEGIN
   UTL_FILE.fgetattr (
   location => location_in,
   filename => file_in,
   fexists => fgetattr_rec.fexists,
   file_length => fgetattr_rec.file_length,
   block_size => fgetattr_rec.block_size
   );
   RETURN fgetattr_rec.file_length;
END flength;

Pass a directory structure name as first parameter and the file name as the second parameter.

Create directory TEST as '/tmp';

select flength('TEST','test.txt') size_in_bytes from dual;

size_in_bytes
-------------
   18568

CAUTION: If you are using 9.2.0.6 then there is a database bug which might not return the exact file size. This bug has been fixed in 9.2.0.7 version.

For more information visit here.

Puzzle this time from Steven Feuerstein in Toad World

2010-05-12T11:54:00.000+05:30

Hurry up, its an easy Puzzle this time from Steven Feuerstein in Toad World

Its May 2010, and this time Steven Feuerstein chose to play easy to his readers. I am reproducing the question and options here:

Which of the following choices correctly describe a feature of the Oracle database called "external tables"?

A. There is no such thing as an "external table" in Oracle; all tables are stored within the database.
B. External tables are tables defined in other databases, such as mySQL and SQL Server, which can be queried from within an Oracle database.
C. An external table is a read-only table defined in Oracle (it appears, for example, in the ALL_TABLES data dictionary view), whose data is stored in an operating system file.
D. An external table is a normal relational table that is accessed externally, usually from a Java method, and whose contents are displayed on web pages.

I am betting that all must know the answer:
Go to this webpage and click on the answer.

Best of luck to all of you.

Find Value in database/schema

2010-05-11T18:14:00.000+05:30

Question: How to find any value in any column in a schema?

I have been searching for this answer for long, and stumbled upon a beautiful response in Oracle Forums and tried the block and stunning to find the output much easily. This saved my day as I was searching for occurence of a value in entire schema. Thanks for pollywog for the posting.

I will reproduce the anonymous block here for my readers:

declare
aCount pls_integer;
begin
for c in
(select table_name, column_name
from all_tab_columns
where owner = 'OWNER'
and data_type = 'DATATYPE') loop

execute immediate 'select count(*) from '||c.table_name||' where '||c.column_name||' = ''value_to_find'' ' into aCount;
if aCount > 0 then
dbms_output.put_line('found value_to_find in table '||c.table_name||', column '||c.column_name);
end if;
end loop;
end;

Just modify the following in the block:
1. OWNER (The schema in which find is required)
2. DATATYPE (Data type of value which you are searching)
3. value_to_find (The string you are finding)

The output is like:

found value_to_find in table , column

Hope this tip helps you too, leave back a comment in case you like this tip.

Difference between package and serially_reusable package

2010-04-28T12:46:00.000+05:30

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

2010-04-12T21:36:00.002+05:30

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

2010-04-12T13:31:00.000+05:30

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

2010-04-06T12:25:00.001+05:30

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 -

Oracle Script - List of Locked Users

2010-03-12T11:20:00.001+05:30

The following SQL script will list out all users with its status:

SELECT username, account_status
FROM dba_users;

Book Review: Oracle 10g/11g Data and Database Management Utilities

2009-09-15T19:06:00.002+05:30

From Technologies to Solutions:
Oracle 10g/11g Data and Database Management Utilities
by Hector R. Madrid
PACKT Publishing

The book is well within its perimeters. It discusses in detail nearly all database management utilities that an administrator uses in his day-to-day activities. The book will prove to be helpful for DBAs in a junior role as well as for those who are preparing for database administration certification examinations. The language used in this book helps the reader understand the complex tools offered by Oracle even though he may not be aware of all the tools discussed.

The authenticity of the topics discussed and extensive examples/screen shots used are a real added advantage in understanding the utilities better. This book can also prove to be a good reference material for those involved in routine tasks not worrying about what all Oracle offers.

It deals with normal DBA tools like Data Pump, External tables, SQL Loader, Recovery Manager, Oracle Scheduler, Security Management, Database Configuration Assistant (DBCA) and much more. These are a must to be understood for a database administrator as well as a database developer/enthusiast. For a long time, there has been a demand for technical details of what a database administrator deals in his day-to-day life and this book addresses to this need. The book explains in detail the various tools the DBAs would use and then discusses the feature enhancements done in 11g. Also examples constitute a sizable portion of the book which are nice to have in a book of this magnitude.

In short this book deserves its place in your custody and will prove to be best in times of database administration activities.

Packt Publishing | English | ISBN: 1847196284 | 432 pages

PACKT Publishing

Buy Book from Amazon

Oracle SQL Developer Data Modeler

2009-07-10T19:55:00.004+05:30

Oracle SQL Developer Data Modeler is the latest product offering from the Oracle Database Tools group. SQL Developer Data Modeler is an independent, standalone product with a full spectrum of data and database modeling tools and utilities, including modeling for Entity Relationship Diagrams (ERD), Relational (database design), Data Type and Multi-dimensional modeling, full forward and reverse engineering and DDL code generation. The Data Modeler imports from and exports to a variety of sources and targets, provides a variety of formatting options and validates the models through a predefined set of design rules.

Oracle SQL Developer Data Modeler can connect to any supported Oracle Database and is platform independent. A read-only SQL Developer Data Modeler Viewer is also available for download. The viewer allows users to open models created in the Data Modeler.

Click here for Oracle® SQL Developer Data Modeler User's Guide

Oracle® SQL Developer Data Modeler

Note: For download you require free OTN account. If you do not have one, read this for creating one account for you.

For more information visit Oracle website.

Puzzler from Steven Feuerstein (July 2009)

2009-07-10T17:10:00.001+05:30

New Puzzler from Steven Feuerstein (July 2009)

TEST YOUR PL/SQL KNOWLEDGE
Which of the following identifiers is the name of the implicit cursor attribute that allows you to find out how many rows were updated by each individual statement processed by the FORALL statement?

SQL%ROWCOUNT
SQL%FORALL_ROWCOUNT
SQL%BULK_ROWCOUNT

To Understand about SQL%BULK_ROWCOUNT click here.

For subscribing to more Puzzlers from Steve and to answer to Steve click here.