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.
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 to search the archives.
Here comes the answer to the problem of finding the Nth highest salary. You can extend this logic to find the Nth highest row of any table.
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 that format. This can be used before data entry, or coded on a table as a check constraint.
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’ and have any two characters in between them (since there are two dots within).
6 Reasons Why You Should Use PL/SQL Packages
A package is a database object that groups together logically related procedures/functions, and other constructs such as variables, constants, cursors, PL/SQL types and exceptions.
One may well ask: when it’s possible to write standalone procedures/functions and define related variables within them, why have packages at all?
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 table ALL_DEPENDENCIES has the answer to the question: Which objects refer to this table? Walk through the following examples for more.
RETURNING the value of an auto-increment column
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 it?
You might want to use the current inserted row’s id, maybe for a further transaction in related tables, or maybe for tracing/logging purposes. The implementation is hidden, which means that you don’t know directly the value of the generated ID.
Auto-Increment Columns: FAQ
What is an auto-increment column?
An auto-increment column is one of which the value increments automatically each time a row is inserted into the table.
What is the use of auto-increment columns?
An auto-increment column usually serves as the primary key or unique identifier for each row of the table. Since the value automatically increments with each insert, the column guarantees that each row has a unique value associated with it.
An auto-increment column also gives useful information about the sequence of transactions. The higher the column value, the later the row was entered into the table. This can be useful for finding data such as
How to Find Out Your Oracle Database Name
Oracle gives you a number of ways to know the name of the database you are connected to, from inside a SQL*Plus session. Here are three ways to find out your Oracle database name.
Through V$DATABASE
SQL> select name from V$database; NAME --------- XE
The above will work only if your login has access to V$DATABASE. This is generally accessible to DBA logins only. For non-DBA logins, you may need to grant SELECT on V$ views.
In case access to V$DATABASE cannot be granted to you, use one of the two publicly accessible methods below.