Interview Questions

The Fallacy of Interview Questions

A series of posts about Oracle interviews starts with this article. “Interview Questions” is a hot topic on many Oracle forums. Job aspirants want these questions to be listed down, emailed to them, complete with model answers. Some imagine that by learning answers to such questions, they will land a job, though they have not […]

Read the Full Article →
delete-truncate-drop

The Difference between DELETE, TRUNCATE and DROP

DELETE, TRUNCATE and DROP – all three commands get rid of table data. How exactly are they different? When should you use which? A bird’s eye view of their differences, a table comparing the three.

Read the Full Article →
Top N Query in Oracle

Nth Highest Salary in Oracle

“How can I select the Nth highest salary of the EMP table?” This is a question that every Oracle newbie stumbles over. Ask it on a forum and you’re pointed to the archives. That gets you nowhere as when you search the archives, all you find is a host of other messages also asking you […]

Read the Full Article →
Validate Email Address in SQL

How to Validate Email Address in SQL

In applications that take user email id as input, there is a need to check for email id validity. Here is a very easy validation for syntax of an email address, using regular expressions in Oracle SQL. The basic email address format is username@example.com. The SQL will verify that the email address provided fits into […]

Read the Full Article →
Regular Expressions in Oracle

Regular Expressions in Oracle Made Easy

What is a regular expression? A regular expression (also called regex or regexp for short) is a sequence of characters that describes a pattern in text. Some examples of regular expressions: p..t => A dot stands for a single character. This regular expression will match words that start with a ‘p’, end with a ‘t’ […]

Read the Full Article →
Thumbnail image for WHEN OTHERS THEN NULL

WHEN OTHERS THEN NULL

There is a time and place for using exception handlers in Oracle PL/SQL, but you must be extra-wary of the handler WHEN OTHERS THEN NULL. Here’s why.

Read the Full Article →
Why Should You Use PL/SQL Packages

6 Reasons Why You Should Use PL/SQL Packages

When stored procedures/functions can exist standalone, why have packages at all? This article tells you the benefits of using PL/SQL packages.

Read the Full Article →
Database Object Dependencies

Which objects depend on a given table?

A database table usually has other objects referring to it – tables linked through foreign keys, stored procedures referring to it. You might want to find out – which packages refer to this table? Are there views created on it? If I change the table design, how many and which objects will be affected? The […]

Read the Full Article →
Auto-Increment Column

What’s the value of auto-increment column in this insert?

In the last post, we saw a neat way to implement auto-increment functionality in an Oracle table. The auto-incremented column gets its value populated in the background, without the issuer of the insert statement even getting to know about it. BUT – what if the issuer of the insert statement does want to know about […]

Read the Full Article →
Auto-Increment Columns: FAQ

Auto-Increment Columns: FAQ

What is an auto-increment column? A table column of which the value increments automatically each time a row is inserted into the table. What is the use of auto-increment columns? This column usually serves as the primary key or unique identifier for each row of the table. Since the value automatically increments with each insert, […]

Read the Full Article →