Thumbnail image for How to 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 →
Thumbnail image for Regular Expressions in Oracle Made Easy

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 […]

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

WHEN OTHERS THEN NULL

Read the Full Article →
plsql-package

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 like variables, constants, PL/SQL types, cursors, 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?

Read the Full Article →
Oracle 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 →
Thumbnail image for What’s the value of auto-increment column in this insert?

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 →
find-database-name

Quick way to find out your 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. Through V$DATABASE: The above will work only if your login has access to V$DATABASE. This is generally accessible to DBA logins only. If you have a non-DBA login, use one of the […]

Read the Full Article →
Thumbnail image for About ORACLE_HOME

About ORACLE_HOME

In Oracle installation guides and scripts, you will often come across the term ORACLE_HOME. In fact, we used it on this site a couple of posts back, in the script to install SCOTT schema in Oracle. This post will cover: the meaning and purpose of ORACLE_HOME how to check if ORACLE_HOME is set how to […]

Read the Full Article →
Thumbnail image for Oracle 10G Express Edition

Oracle 10G Express Edition

Oracle Database 10g Express Edition (Oracle Database XE) is a free, lightweight version of Oracle. If you’re a small enterprise with modest database needs, or an individual looking to learn Oracle on your home computer, Oracle XE is just the answer for you. This starter version of Oracle is free to download, distribute and deploy. […]

Read the Full Article →