Enable/Disable Bind Variable in PL/SQL

When we execute a series of insert statements which contains ampersand (&) or colon (:), PL/SQL will prompt us for an input. Most of the cases, we want to avoid this. Here is a simple tips.

set define off


Then, to turn back on, do the opposite as below.

set define on

Hitler's response to iPad

When Steve Jobs unveiled the long-awaited iPad in San Francisco on Wednesday, analysts all over the world have given their opinions and reviews on Apple's latest product.

Here's Adolf Hitler's reaction when he was informed about the iPad. You gotta hear from the Führer himself. Enjoy!

Accenture Replaces Tiger with Elephants and Frogs

Accenture, a global management consulting, technology services and outsourcing company that once heralded Tiger Woods as the icon of its marketing campaign, unveiled new ads after dropping the golfer following a sex scandal.

Accenture’s "High Performance" ads, which for six years featured Tiger in precarious golfing situations, are being replaced with animals in similar predicaments - an elephant surfing and a frog leaping over three companions.

Oracle PL/SQL Tips: Create Table Express

CREATE TABLE NEW_TABLE_NAME AS
SELECT * FROM EXISTING_TABLE_NAME WHERE 1=2;

The above statement will create an empty table which has the same structure as the existing table.

CREATE TABLE NEW_TABLE_NAME AS
SELECT COLUMN1, COLUMN2 FROM EXISTING_TABLE_NAME WHERE 1=2;

You can use the above statement to create an empty table with selected fields from the existing table.

Oracle PL/SQL Tips: Deleting Duplicate Rows

You can use one of the following queries to identify and/or remove duplicate rows from a table leaving only unique records in the table.

Method 1: Based on key field(s), delete all rows which have greater ROWIDs than the smallest ROWID. ROWID is a pseudocolumn that uniquely identifies a row within a table.

DELETE FROM table_name A
WHERE ROWID > ( SELECT min(ROWID)
              FROM table_name B
              WHERE A.key_field = B.key_field );

--delete all rows which have smaller ROWIDs than
--the biggest ROWID for given key values
DELETE FROM table_name A
WHERE ROWID > ( SELECT max(ROWID)
                FROM table_name B
                WHERE A.key_field = B.key_field );

Method 2: Almost similar to Method 1, but using group by key field(s) criteria to delete.

DELETE FROM my_table t1
WHERE ROWID NOT IN ( SELECT min(ROWID)
                 FROM my_table t2
                 GROUP BY key_field1, key_field2 );

The Exploding Growth of the Internet

Mount Point

Starting a new blog on the world of information technology (IT), from an analyst's perspective. Stay tuned for interesting entries on problems, challenges, rants and tips.