June 4, 2008

Performance differences between IN and EXISTS

Very often I hear people wondering if it’s better the EXISTS or the IN operator. Let’s start by saying that the two operators behave differently[...]
Read More »

June 4, 2008

NOT IN vs. NOT EXISTS when NULL values are involved

NOT IN does not behave as you would expect when NULL values are involved. Suppose you have the following tables: TABLE_A CD_FIELD DS_FIELD 1 ALPHA 2 BETA TABLE_B CD_FIELD DS_FIELD 1 GAMMA NULL DELTA Now[...]
Read More »

October 9, 2007

How to compute a timestamp in Oracle (PL SQL)

Many sources use the term timestamp to refer specifically to Unix time, the number of seconds since 00:00:00 UTC on January 1, 1970. In Oracle you can[...]
Read More »

July 4, 2007

How to create a copy/backup of a table in Oracle

You can create a backup of a table (structure and data) in Oracle by using the following syntax: CREATE TABLE customers_backup AS (SELECT * FROM customers) The[...]
Read More »

May 24, 2007

How to commit a single DML statement in Oracle (PL/SQL)

Sometimes you might need to commit a single INSERT, UPDATE or DELETE statement and not the whole main transaction. In such a case you can use an AUTONOMOUS[...]
Read More »

April 27, 2007

How to put a set of rows into a single row in Oracle

In Oracle you can execute hierarchical queries using some cool operators–CONNECT BY PRIOR, START WITH and the pseudocolumn SYS_CONNECT_BY_PATH, namely.[...]
Read More »

April 26, 2007

How to tell Oracle to use more than one byte per character

When you create a table, by default, the number of bytes used to represent a character in a VARCHAR2 type is 1, irrespective of the characterset of the[...]
Read More »

September 6, 2006

Using dynamic SQL statements from PL/SQL

Sometimes you need to execute dynamic SQL statements. Starting from Oracle8i you can accomplish this task using the “EXECUTE IMMEDIATE” statement.[...]
Read More »

Proudly powered by WordPress and Sweet Tech Theme