DATABASE

Oracle Database 11g : Database Fundamentals - Learn the Basic Oracle Database 11g Data Types, Work with Tables, Work with Stored Programmed Objects

9/25/2012 1:33:58 AM

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 SpecificationColumn Length (Precision)Decimal Digits (Scale)
(3,2)32
(6,3)63
(17,12)1712

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
LineImportant Points
1The table has a unique name, from 1 to 30 characters. It is stored in Oracle Database’s data dictionary in uppercase.
2The 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.
3The 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.
4inception, 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).
5description is a free-form field with a variable length of up to 30 characters.
6unit_price can accommodate up to four integer and two decimal digits.
7in_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.

Figure 1. Relationships to part_master


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
TablePart NumberColumn ValueRelated Column Value
part_master33499909manufacturer_code3490
manufacturer3490location_id5
location5quadrantPacific 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.

Ask the Expert

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:

  1. Create a record in sal_audit.

  2. Trap the time and date of the transaction.

  3. Place the user’s login ID in the doer column.

  4. Place the old salary value in the old_sal column.

  5. 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 NameTypeWork Accomplished
give_holidaysProcedureCreates the default holiday quota based on the new person’s rank in the company.
notify_benefitsProcedureCreates a record in the BEN_QUEUE table to alert the benefits people of the new employee.
is_under_25FunctionReturns a “1” if the new employee is under 25 years old as of December 31 of the year they were hired.
is_over_59FunctionReturns a “1” if the new employee is 60 years old or older as of the calendar date of hire.
Other  
  •  Oracle Database 11g : Database Fundamentals - Define a Database, Learn the Oracle Database 11g Architecture
  •  SQL Server 2005 : Advanced OLAP - Calculations (part 2) - Named Sets, More on Script View
  •  SQL Server 2005 : Advanced OLAP - Calculations (part 1) - Calculated Members
  •   Exploiting SQL Injection : Automating SQL Injection Exploitation
  •   Exploiting SQL Injection : Out-of-Band Communication
  •  SQL Server 2008 R2 : Dropping Indexes, Online Indexing Operations, Indexes on Views
  •  SQL Server 2008 R2 : Managing Indexes - Managing Indexes with T-SQL, Managing Indexes with SSMS
  •  SQL Server 2005 : Advanced OLAP - Advanced Dimensions and Measures (part 3)
  •  SQL Server 2005 : Advanced OLAP - Advanced Dimensions and Measures (part 2) - Parent-Child Dimensions
  •  SQL Server 2005 : Advanced OLAP - Advanced Dimensions and Measures (part 1)
  •  
    Most View
    Windows 7 : Programming WMI Support (part 4) - Troubleshooting Specific WMI Problems
    Windows Server 2008 and Windows Vista : GPMC Scripts - GPO Reporting (part 2)
    Sony Xperia ZL - A Powerhouse Phone In An Amazingly Compact Chassis (Part 1)
    Sony Xperia TL - Much Improved But Still Imperfect (Part 1)
    Android 4.0 Ice Cream Sandwich Guided Tour (Part 1)
    Apple - iPhone 5 - Loving It Is Easy
    The Roundup Of 120mm Fans: 1,350RPM Speed And More (Part 10)
    Hands-On with the iPad Mini (Part 2)
    CraftArtist2 Professional - Traditional Crafting On Your Computer
    Programming Windows Services with Microsoft Visual Basic 2008 : Service Notification
    Top 10
    Evasive Motorsports’ ’04 S2000 – Evasive S2k V.2 (Part 2)
    Evasive Motorsports’ ’04 S2000 – Evasive S2k V.2 (Part 1)
    Focus ST – Speed Demon (Part 3)
    Focus ST – Speed Demon (Part 2)
    Focus ST – Speed Demon (Part 1)
    Cyrus Lyric 09 System Review (Part 2)
    Cyrus Lyric 09 System Review (Part 1)
    HD/SD Meter Revez HD-TSF Review
    Integrated Amplifier KR Audio VA880 Review (Part 2)
    Integrated Amplifier KR Audio VA880 Review (Part 1)