1. Learn the Basic Oracle Database 11g Data Types
Very early in one’s journey through the world of Oracle Database 11g,
it becomes time to learn its common data types. Regardless of your past
experience in information technology, data types are nothing new. Let’s
look at the most common type of data that can be stored in Oracle
Database 11g; keep in mind that the list is much longer than the one presented here.
varchar2
By
far the most common data type, varchar2 allows storage of just about
any character that can be entered from a computer keyboard. In earlier
software solutions, this was commonly referred to as alphanumeric
data. The maximum length of varchar2 is 4000 bytes or characters, and
it possible to store numeric data in this data type. This is a variable
length character string, with no storing of trailing insignificant white
space:
create table ... (
name varchar2(30),
city varchar2(30),
...
...
state varchar2(2));
If a program or SQL statement
tries to store a set of characters in a varchar2 field that is longer
than the field’s specification, an error is returned, and the statement
stops running and returns control back to you.
number
The number data type allows
the storing of integer as well as integer/decimal digits. When
non-integer data is stored, the total number of significant digits of
the number is referred to as precision, while the portion to the right is called scale or decimal places.
For example, the number 29.1963 has a precision of 6 and a scale of 4.
The maximum precision is 38 and the maximum scale is 127. The confusing
part of the specification of a number data type comes into play when
storing non-integer information. Table 1 illustrates this concept.
Table 1. Number Data Type Specification
| Number Specification | Column Length (Precision) | Decimal Digits (Scale) |
|---|
| (3,2) | 3 | 2 |
| (6,3) | 6 | 3 |
| (17,12) | 17 | 12 |
When defining a number
data type with decimal places, it’s important to know that the maximum
integer portion of the number data type is the difference between the
two numbers specified. The specification (6,3) allows for two, not six,
integer digits. If more decimal digits are received than the column
definition permits, it rounds the value before storage.
date
The date data type stores time and date information, with the time component rounded to the nearest full second.
There are many, many functions available to be performed on date fields as they are extracted from an Oracle Database 11g.
When date columns are selected from Oracle Database 11g,
it is common to perform a function on their values to make them more
readable. By default, the time component of a date column is not
displayed without manipulating its contents using a to_char function.
By default the general display format for a date is DD-MON-YY (day,
month, and year). This format may be changed via the NLS_DATE_FORMAT
parameter or by using a display format function.
timestamp
The timestamp
data type is a close relative of date. The major advantage is that the
timestamp stores information about the second to a much higher accuracy.
In this time when every subsecond counts, the timestamp can be a
valuable asset. There is a time component in this data type, displayed
with the data without the need for the to_char function. This listing
illustrates this concept:
SQL> create table timestamp_test (ts timestamp);
Table created.
SQL> insert into timestamp_test values (sysdate);
1 row created.
SQL> select * from timestamp_test;
TS
--------------------------------------------------------------------------
14-DEC-09 05.25.07.000000 PM
SQL> create table date_test (d date);
Table created.
SQL> insert into date_test values (sysdate);
1 row created.
SQL> select * from date_test;
TS
---------
14-DEC-06
clob
The clob
data type allows storage of very large objects in excess of four
gigabytes in size. Since this is a true character data type, it is very
similar to the varchar2 data type except for its much larger maximum
size.
blob
The blob data type permits storage of large unstructured binary objects. Sound and video are examples of blob data.
It’s now time to have a look at the most common object in Oracle Database 11g:
the table. After that, we will have a look at a few types of
programming units written using SQL, which a person can store in Oracle
Database 11g.
2. Work with Tables
The best way to think of a table in a relational database such as Oracle Database 11g is to see it as a spreadsheet with rows and columns. With this in mind, note the following:
Rows are often referred to as records.
Each column has a name unique to the table that it resides in.
The intersection of each row and column, referred to as a cell in a spreadsheet, is called a field in Oracle Database 11g.
Picture the following SQL statement, which creates a table (the line numbers are not part of the code):
1- create table part_master (
2- id number(8) not null,
3- manufacturer_code number(4) not null,
4- inception date not null,
5- description varchar2(60) not null,
6- unit_price number(6,2) not null,
7- in_stock varchar2(1));
Let’s pick apart the code and highlight the main points in Table 2.
Table 2. part_master Table Definitions
| Line | Important Points |
|---|
| 1 | The table has a unique name, from 1 to 30 characters. It is stored in Oracle Database’s data dictionary in uppercase. |
| 2 | The
ID column is numeric with anywhere from one to eight digits. The
application that creates and keeps track of parts may insist that the
first character of the ID be a digit between 1 and 9. Since the field is
defined as numeric, if the leading digit were a 0, the part ID would
only be seven digits long. |
| 3 | The
manufacturer_code is the only manufacturer information stored in
part_master. Further information about who made the product is in a
related table—hence, the terminology relational database. |
| 4 | inception,
as a date field, contains a date and time specification, though it will
display a default month abbreviation and a two-character year unless
some manual manipulation is performed (for example, 12-NOV-05). |
| 5 | description is a free-form field with a variable length of up to 30 characters. |
| 6 | unit_price can accommodate up to four integer and two decimal digits. |
| 7 | in_stock
is a one-character flag of sorts; thus, the system designers can decide
to use an indicator like a “1” or “X” to represent items that are in
stock. Notice how this is the only one of seven fields in the
PART_MASTER table that can be left blank. |
Table 2 mentions the concept of a relational database. Let’s inspect a few other tables and see how they are related to one another.
Tables Related to part_master
The manufacturer_code
column in part_master points to a record in manufacturer. Also, some
columns in manufacturer may end up being related to column values in
other tables. Figure 1 illustrates these relationship concepts, the heart of the Oracle Database 11g implementation.
Suppose someone wanted to know where in the country a certain part was manufactured. By looking at Figure 1-3,
that information is not readily available in part_master. However,
part_master has a manufacturer_code. So, a person would traverse to
manufacturer using manufacturer_code to get a location_id. Armed with
that value, one then proceeds to location to get a quadrant column
value. After this navigation is complete, a person would know where a
specific part is built. Table 3 maps out this journey.
Table 3. Following Relationships Between Tables
| Table | Part Number | Column Value | Related Column Value |
|---|
| part_master | 33499909 | manufacturer_code | 3490 |
| manufacturer | 3490 | location_id | 5 |
| location | 5 | quadrant | Pacific Northwest |
As illustrated in Table 1-3,
you can deduce that part 33499909 comes from the Pacific Northwest—a
deduction that is made by following the relationships between matching
columns in the three tables in question.
3. Work with Stored Programmed Objects
Oracle Database 11g offers the ability to store user-defined programming units in the data dictionary, called stored objects.
Views
Views are predefined subsets of data from an Oracle Database 11g
table. The SQL query that builds the view is stored in the data
dictionary and need not be reassembled every time the view is used.
Suppose a personnel application stores the location of all employees in
its EMPLOYEE_MASTER table in the loc_id column. With Oracle Database 11g, you can define a view called emp_hq as follows:
create or replace view emp_hq
as select * from employee_master
where loc_id = '2';
EMP_HQ becomes a valid object of the select statement just as if it were a table of its own.
| Q: | What is the major difference between the clob and blob data types in Oracle Database 11g? | | A: | The clob stores only alphanumeric data, whereas the blob can accommodate any type of data, including sound and video. | | Q: | When specifying the number data type, how is the total length of the field determined? | | A: | The
total length of a numeric field is determined by the digit(s) to the
left of the comma if the specification includes an integer and decimal
component. For example, number(4,1) denotes a maximum of four digits, of which one digit can be stored to the right of the decimal point. | | Q: | Which of the Oracle Database 11g background processes is responsible for writing information from memory into the database files? | | A: | This is the job of the database writer, or dbw0, process. | | Q: | Where does Oracle Database 11g read its environment from as it is started? | | A: | The startup parameters are read from the system parameter file, which can be a binary file stored in Oracle Database 11g. | | Q: | As sessions interact with the data in Oracle Database 11g, what role does the undo tablespace play in the architecture of the software? | | A: | When transactions change the contents of information in Oracle Database 11g’s
tables, this special tablespace keeps a “before image” of the changes
in case the operator decides to back out before saving newly entered
information. |
|
Note
At
this point, you should realize that views are generally built based on
more than one table. A view provides the perfect environment to
predefine join conditions between tables to ensure that they adhere to
business rules and perform at an optimal level.
Triggers
Just as their name implies, triggers
are stored objects that fire based on the execution of certain events.
Suppose a payroll application wants to audit salary increases: a trigger
is created that fires when the salary column in hr_master is updated.
The trigger could do the following:
Create a record in sal_audit.
Trap the time and date of the transaction.
Place the user’s login ID in the doer column.
Place the old salary value in the old_sal column.
Place the new salary value in the new_sal column.
Code in the trigger traps the event by specifying on update. While triggers are commonly used for auditing, the types of activities they can initiate are endless.
Note
Triggers cannot exist independently of an Oracle Database 11g table. They are associated with one and only one table and, if a table is dropped, so is the trigger.
Triggers, as well as
procedures, packages, and functions described next, are most commonly
written using PL/SQL.
Procedures
Procedures perform specific tasks as applications interact with Oracle Database 11g.
If there are a number of interrelated activities to carry out in one
place, a procedure is an ideal way to do this. Procedures can accept
parameters when invoked and can interact with objects in Oracle Database
11g.
They encapsulate related activities into single programming units that
simplify logic and share data values as they perform various activities.
They offer extremely flexible features, many of which are not available
with triggers.
Functions
Functions are very close relatives of procedures, except that they return a value to the code that called them. Oracle Database 11g delivers many functions out of the box
and developers can create their own functions to augment what is
delivered with the software. Suppose you want to strip all the vowels
out of a name with a function. You can pass in a name (for instance,
Bellissimo) and gets back the text “Bllssm” when the function completes
its work. Let’s look at the get_age function, which operates based on
the following logic:
given a date of birth (format DD-MON-YYYY)
using an SQL function
get the months between today's date and the date passed in
divide the number of months by 12
truncate the results (giving the span in years between the 2 dates)
pass integer back
Packages
Packages
roll functions and procedures together into a cohesive programming
unit. Often, developers prefer to bundle like functionality together
since it makes logical sense to call one larger unit and have it perform
a series of tasks. Let’s look at the CREATE_EMPLOYEE package in Table 4.
Table 4. Members of the CREATE_EMPLOYEE Package
| Component Name | Type | Work Accomplished |
|---|
| give_holidays | Procedure | Creates the default holiday quota based on the new person’s rank in the company. |
| notify_benefits | Procedure | Creates a record in the BEN_QUEUE table to alert the benefits people of the new employee. |
| is_under_25 | Function | Returns a “1” if the new employee is under 25 years old as of December 31 of the year they were hired. |
| is_over_59 | Function | Returns a “1” if the new employee is 60 years old or older as of the calendar date of hire. |
