I guess the first question is really, "What is a database"? A database is an organized collection of data. The data can be textual, like order or inventory data, or it can be pictures, programs or anything else that can be stored on a computer in binary form.

A relational database stores the data in the form of tables and columns. A table is the category of data, like Employee, and the columns are information about the category, like name or address.

Some databases have minimal feature sets and only store data, while others include programming languages, facilities and utilities to support enterprise-level applications like ERP and data warehousing. Oracle is the #1 database and has the most advanced feature set.

Oracle is made up of a set of processes running in your operating system. These processes manage how data is stored and how it is accessed. I will cover these processes in detail in the future; but for now we just need to understand that Oracle is a program that is running in the background, maintaining your data for you and figuring out where it should go on your hard drive.

In almost all relational databases, data is accessed through SQL, or Structured Query Language, and Oracle is no exception. SQL allows you to SELECT your data, INSERT new records, UPDATE existing records and DELETE records you want to get rid of. SQL can be embedded in other languages or you can run scripts of SQL directly against the database.

PL/SQL is the procedural language extension to SQL. PL/SQL is a programming language like C, Java or Pascal. In the Oracle world, there is no better way to access your data from inside a program. SQL can be natively embedded in PL/SQL programs. I will be using both SQL and PL/SQL very heavily in my future articles.

PL/SQL is a feature-rich language geared toward developing database applications. PL/SQL is the procedural language of the database, but it is also the procedural language for most of Oracle's tools. Programs that run inside the database are called stored procedures. These stored procedures are almost always PL/SQL, but can be written in Java.

The most basic syntax of SQL starts with a SELECT clause and a FROM clause. A SELECT clause tells the database WHAT you want to select. A FROM clause tells the database where to find that data.

From SQL*Plus, login as whatever user you have available and enter:

SELECT table_name
FROM all_tables;

When I run my query, I get a list of tables followed by the text "113 rows selected.". The SELECT clause is TABLE_NAME and the FROM clause is all_tables. The semi colon at the end tells Oracle that you have completed your command. You MUST enter a semi-colon or a slash before Oracle will process you command.

This was a very useful command but it could be made more useful. The list of tables was just that, a list. If we were looking for a specific table, it would be hard with an unordered list like that. Let's try putting some order to it.

Enter:

SELECT table_name
FROM all_tables
ORDER BY table_name
/

Ok, there are three differences here. There is no semi colon but there is a slash, "/". The slash also tells Oracle that you have completed a command. The slash must appear ion column 1 on a line by itself. A semi colon can follow anywhere as long as there are no blank lines between it and the command.

Type slash on a line by itself and Oracle will re-execute the previous command. Try it now.

Ok, we also added an ORDER BY clause to our command. The ORDER BY tells Oracle to, oddly enough, order the result set. What did it order by? TABLE_NAME.

You can scroll through the list and find a specific table. Scroll through it now and find the table, DUAL. If you don't see this table, something is either not configured properly in your database or not configured properly with your user. DUAL is a special table provide by Oracle. It should always have one row and only have one column. That column, DUMMY, should always have the value 'X'.

Enter:

SELECT table_name
FROM all_tables
WHERE table_name = 'DUAL'
/

The first join we'll cover is called an equijoin, also called an inner join. That is where a column (or multiple columns) in two or more tables match. For our example:

SELECT emp.ename, dept.dname
FROM emp JOIN dept
ON emp.deptno = dept.deptno
/

What this select says is to select the ename column from the emp table along with the dname column in the dept table. Join emp and dept by comparing the deptno columns in each for equality.

I can think of at least three other ways to write this query in Oracle, but I believe this is the clearest and most intuitive way to do so. You may also see the above query written like this:

SELECT emp.ename, dept.dname
FROM emp INNER JOIN dept
ON emp.deptno = dept.deptno
/

Oracle automatically defaults the JOIN to INNER so that the INNER keyword is not required. They are the same query, though. I just prefer not to type the INNER keyword.

And how would we limit this to just the SALES department:

SELECT emp.ename, dept.dname
FROM emp JOIN dept
ON emp.deptno = dept.deptno
WHERE dept.dname = 'SALES'
/

Now this is getting useful. Ok, let's return all employees in the department OPERATIONS.

SELECT emp.ename, dept.dname
FROM emp JOIN dept
ON emp.deptno = dept.deptno
WHERE dept.dname = 'OPERATIONS'
/

Uh, oh. We got no rows returned. If we refer back to our earlier query, we can see that there is a dname of operations, but we have no employees in that department. How can we get back a record with dname? We need an OUTER JOIN. An outer join tells Oracle to return the rows on the left or right (of the JOIN clause) even if there are no rows.

SELECT emp.ename, dept.dname
FROM emp RIGHT OUTER JOIN dept
ON emp.deptno = dept.deptno
WHERE dept.dname = 'OPERATIONS'
/

Notice that I added the RIGHT OUTER keyword to the JOIN clause. That says return the rows to the right (in this case DEPT) even if there are no rows on the left (in this case emp).

We could re-write the query as such:

SELECT dept.dname, emp.ename
FROM dept LEFT OUTER JOIN emp
ON dept.deptno = emp.deptno
WHERE dept.dname = 'OPERATIONS'
/

In this case, we put DEPT on the left and used a LEFT OUTER join to get the same results.

VARCHAR2

VARCHAR2 is the workhorse string type in the Oracle database. VARCHAR2 is a variable length string that must have it's maximum length declared before use. In Oracle 10g, a VARCHAR2 column may be up to 4000 bytes and it may be up to 32767 bytes in a PL/SQL program.

VARCHAR2 can store any type of non-binary data. Depending on the language defined in the database (either double or multibyte), the actual number of characters may be less than the number of bytes. For example, if the language requires three bytes per character, the most you can store would be 32767 divided by 3 characters.

To declare a VARCHAR2, you can specify either the number of bytes or the number of characters. Specify the number of characters if you use a multi-byte character set. Otherwise, specify bytes.

A declaration looks like this: VARCHAR2(30) That would declare a 30 byte string and is equivalent to VARCHAR2(30 BYTE). To declare a 30 character string, use: VARCHAR2(30 CHAR). In the character set I use, VARCHAR2(30), VARCHAR2(30 BYTE) and VARCHAR2(30 CHAR) will store exactly the same number of characters.

In my databases, I use VARCHAR2 almost exclusively. The only time I don't use VARCHAR2 is if I need a large string (say for documents and such) when I will use a CLOB. I'll cover CLOBs in the future.

If I need a PL/SQL variable that is larger than 4000 bytes, I will still sometimes declare a LONG. A PL/SQL LONG is synonymous with VARCHAR2(32760). I do not use LONGs in tables as CLOBs are much better. A LONG in a table is NOT synonymous with VARCHAR2 at all.

DATE

The DATE datatype is a datetime datatype. It stores a date and a time. The date portion is based on the number of days since January 1, 4712 BC. The time portion is based on the number of seconds since mid-night.

A date field is declared as a variable name followed by the DATE keyword:

v_date_field DATE;

There is no "correct" format for a date. The format is determined by several variables. At the database level, you can set the NLS_DATE_FORMAT. Regardless of how the default is set in your database, you should always make sure you explicitly use a date format mask when converting.

I will cover conversion functions for all data types in a future article but I think this is important. If you need to compare a date to a literal, make sure to use a date format mask. You could say:

WHERE date_field = '01-01-1999'

or you could say:

WHERE date_field = to_date('01-01-1999')

I would consider both of those bad programming practices. The first is allowing an implicit conversion between character and date and both of them are assuming some kind of default date format. A better practice would be to use:

WHERE date_field = to_date('01-01-1999', 'DD-MM-YYYY')

And that leads us to why we use a DATE instead of a character field or a numeric. I'm often asked about best practices with dates and times. For some reason I have never understood, some people feel that storing dates and times as characters and numbers is better than allowing Oracle to handle that data in a native format. Call me lazy, but if Oracle can do it for me, I am going to let it.

As an example, let's say you want to add 1 day to a date. If that date is stored in a character field, you would have to do some kind of conversion and manipulation to accomplish that. If we use a date field, it's as easy as date_field + 1. How would you accomplish adding an hour? Using a date type, I can add date_field + 1/24. That is, I add a 1 to get 1 day and I add 1/24th of a day to get 1 hour. To add 2 hours, I would add date_field + 2/24. How would you do that with a character field?

I admit if you store your data in a numeric field, it might be easier than storing it as a character. In that case, adding 1 day or 1 hour might be pretty easy. But how would you add 3 months? With a date field, you would use add_months(date_field, 3). How would you substract 3 months? With a date field, you would use add_months(date_field, -3). How would you get the last day of the month in a date field? With a date field, you can say last_day(date_field). The list goes on an on.

Here is one of the most important rules you can learn about using a database. If it can do it for you, let it. If it's date or time, let the database manage that for you. Spend your time writing applications, not low level support routines.

Some rules about date math are that when you subtract a date from a date, you get a number of days. When you subtract a number from a date, you get a date less than the starting date. When you add a number to a date, you get a date later than the starting date. You cannot add a date to a date.

CHAR

CHAR is a fixed length datatype. If you don't specify a max size, it defaults to 1. The maximum size for a CHAR in a column is 2000 bytes and 32767 in PL/SQL.

I pretty much consider CHAR to be a legacy datatype. Some people will use CHAR(1) to store indicators (Y,N, etc) or as a table BOOLEAN. I prefer to use VARCHAR2 even in those cases.

One case that may be justified (in my mind anyway, this IS my opinion) would be to populate data from or to a report format that will need to be reproduced. For example, if you read in a fixed length file, and you may need to generate that fixed length file in the future, you will save yourself some conversion overhead by storing the data in a fixed length datatype (i.e. CHAR).

One area that people get into trouble are CHAR and VARCHAR2 comparisons. If you stick with VARCHAR2, you won't have issues.

NVARCHAR2 and NCHAR

You may declare a NVARCHAR2 instead of VARCHAR2. NVARCHAR2 is used for storing unicode character sets. I personally have never had a use for NVARCHAR2. If you write multi-lingual applications and use a unicode character set, use NVARCHAR2 instead of VARCHAR2. CHAR also has a corresponding NCHAR.

Length restrictions are the same as a VARCHAR2 and UTF8 can store 32767 divided by 2 and UTF16 can store 32767 divided by 3. You cannot mix and match VARCHAR2 and NVARCHAR2 variables. You should pick one or the other and stick with it. Both are available in SQL and PL/SQL.

RAW

Technically, RAW is not a string. A RAW data type stores binary data. In the database, you should use BLOB or BFILE to store binary data. In PL/SQL, there are times you need to store smaller binary chunks of data. There are also some functions that return a RAW type.

RAW is declared just like VARCHAR2 or CHAR, you declare a maximum size. A RAW can hold 32767 bytes. There is no NRAW (because binary is binary regardless of language).

You would use the built-in functions HEXTORAW and RAWTOHEX to convert between a binary raw and a character string of hex values. If you have a need to use the encryption functionality, that is one place you may use a RAW.

NUMBER

NUMBER, as you might have guessed, stores numeric data. There aren't any differences between the SQL type NUMBER and the PL/SQL type NUMBER. A NUMBER can store 38 digits of precision. A fully populated NUMBER requires 22 bytes to store. That's a very large number. You can constrain a NUMBER to limit the size of the values it can store.

NUMBER(precision,scale) defines how precise the number can be (think of it as how many digits it can be ) and what the scale can be (think of scale as how small it can be).

Both precision and scale are optional in a NUMBER declaration. You can declare a NUMBER with a precision and no scale but if you define a scale, you must define a precision.

You can also declare a negative scale. A negative scale will round up to the number of digits specified.

You can use NUMBER as a table datatype and in your programs. Constrain your number with a precision and scale when your data rules call for it.

PLS_INTEGER and BINARY_INTEGER

PLS_INTEGER and BINARY_INTEGER are identical data types and are only available in PL/SQL. You cannot create a column in a table with either of these data types. PLS_INTEGER is a highly efficient integer 32-bit data type. You will most commonly see PLS_INETGER (and BINARY_INETGER) in PL/SQL routines as an index variable. An associative array (INDEX BY TABLE) index. Both PLS_INTEGER and BINARY_INTEGER allow whole numbers only. Decimal fractions are rounded to the nearest whole number.

There performance benefits to using PLS_INTEGER or BINARY_INTEGER is some places (as opposed to a NUMBER). Those performance issues are beyond the scope of this article, but if you would like to read about it, do a web search on "performance pls_integer oracle" and read some of the articles.

There's not much more to say about these two types. In 10g, they are interchangeable and the primary place to use them is in computing intensive PL/SQL code.

BINARY_FLOAT and BINARY_DOUBLE

These two data types are provided to allow very efficient floating point operations. BINARY_FLOAT is 32 bit and BINARY_DOUBLE is 64 bit.

There are two special cases (three including a negative) for these datatypes can be tested: Not a Number (NaN) and infinity (INF) (and negative infinity -INF). You can test for INF and NaN using IS or IS NOT, as in: SELECT * FROM TAB WHERE float_field IS NaN.

While these two data types are more efficient, they are less precise than a regular NUMBER datatype.

A database link is the way Oracle allows you to connect from one database to another. A database link connects to a remote database using an ID and a password just like a user but only allows programmatic access.

Database links enable distributed processing. Replication is an excellent example of an application using database links. Oracle replication uses database links to move data from one database to another.

A database link is a one-way conduit allowing you to perform DML against remote databases just like you would against a local database. Your SQL does not need to change except to qualify which database you wish to access.

Second, database links require the remote database to be defined to your server. Your client may know nothing about a database and you will still be able to create a link. The reverse also holds true; if your client is aware of a database but the server is not, you cannot create a link.

Examples Creating and Using DB Links

Select sysdate from the dual table in a remote database names RemoteDB using a connected user link:

CREATE DATABASE LINK MyRemoteDB USING 'RemoteDB';

SELECT sysdate FROM DUAL@MyRemoteDB;

Execute a stored procedure in the remote database:

BEGIN
runProc@MyRemoteDB;
END;

Execute a packaged function in the remote database:

DECLARE
var1 VARCHAR2(10);
BEGIN
Var1 := runFunc@MyRemoteDB;
END;

Create a view using the select above:

CREATE OR REPLACE VIEW Remote_Sysdate_VW AS
SELECT sysdate AS remoteSysdate
FROM DUAL@MyRemoteDB;

SELECT * FROM remote_sysdate_vw;