How to Create Database Objects in Oracle 12c - dummies

How to Create Database Objects in Oracle 12c

By Chris Ruel, Michael Wessler

Multiple object types exist in an Oracle 12c database, and it’s important to know what’s available. Periodically, Oracle adds new object types to extend functionality. New options within each object type are regularly added as well.

The multitude of objects in Oracle grows with every release, and the options available for each object grow even faster. To get the most up-to-date listing of syntax and options, visit the Oracle Database SQL Language Reference 12c documentation.

After you decide what type of objects to create, you need to know how to create them. The most common object creation methods are

  • Via SQL*Plus with scripts or command line statements

  • Via a GUI tool, such as Enterprise Manager Cloud Control

  • Via Oracle-supplied SQL*Developer

The guts of a database are its objects; and tables are at the core because they contain the rows of data. The following objects are common in an Oracle database.

The table object in Oracle 12c

A table contains rows of data and is the core of the database. Tables are composed of column names, each with a defined data type. Data is loaded into the table as rows.

Create specific constraints on each column of data to restrict data. Create primary keys on one or more columns to enforce uniqueness for each row. Foreign keys generate relationships between rows in one table and rows in other tables.

Tables are contained within a tablespace and may be split between multiple tablespaces (partitioning) to improve performance and manageability.

The view object in Oracle 12c

A view is a SQL statement that joins one or more tables to form a logical representation of data. Rather than the user or program unit issuing a complex statement on multiple tables, the view allows that data to already be joined. Views provide the benefits of reduced complexity and improved performance when created as materialized views, in which data is already selected and stored.

The index object in Oracle 12c

An index is an internal mechanism that allows fast access to selected rows within a table. Just as you look in a book’s index to find a topic, a database index is a pointer to selected data within data tables.

You can use multiple types of indexes depending on the nature of the table and data:

  • B*Tree indexes are the default and most common.

  • Bitmap indexes are used for data with low cardinality or low levels of uniqueness, such as a YES/NO column.

  • Function-based indexes exist on functions on SQL statements. For example, if you want to search for LAST_NAME in uppercase, you might create an uppercase function-based index.

Indexes are key to fast data access, but they come at a cost. The index must be updated every time data is inserted, updated, or deleted. Indexes also consume disk space and are commonly stored in tablespaces separate from the corresponding data tablespaces. Bottom line: Index enough to speed up common searches, but, not so much as to slow down data modifications.

The procedure object in Oracle 12c

A procedure is a PL/SQL program unit that executes program code to process rows of data. Application and business logic exist as procedures within a database.

A procedure can

  • Stand alone within a schema

  • Be part of a package

  • Be an anonymous PL/SQL block

The function object in Oracle 12c

A function is a PL/SQL program unit that executes and returns a value to the program unit that called it. Conceptually, an Oracle function isn’t unlike functions in other programming languages. Functions typically accept input parameters from the calling program, perform some type of processing on that input, and return a value to the calling program unit.

Functions come in two ways:

  • Oracle provides many useful built-in functions: for example, time, date, and mathematical functions.

  • The user can write customized functions.

A function can exist in the following ways:

  • Stand alone within a schema

  • As part of a package

  • As an anonymous PL/SQL block

The package object in Oracle 12c

A package is a group of related PL/SQL procedures and functions that form a larger program unit. A package typically has procedures and functions related to a specific business purpose; that way, the functionality is contained to that package. A package contains two things:

  • A package spec, or header, which lists the publicly exposed program units

  • The package body, which holds the actual PL/SQL program code for each contained procedure or function

The trigger object in Oracle 12c

A trigger is a PL/SQL program unit that is executed when a table is updated, inserted, or deleted, or when other database events occur. Here’s a common trigger example:

The database link object in Oracle 12c

A connection from one database to another is a database link. It allows a user or program unit to select or modify data from another Oracle database. The link specifies a Transport Network Substrate alias to connect to a remote database. For example, if you execute

SELECT * FROM CUSTOMER@ROLLING_MEADOWS_DB;

You select all the data from the CUSTOMER table in the ROLLING_MEADOWS_DB database.

The synonym object in Oracle 12c

A synonym in a database is just what it is in everyday life: a different name for the same thing. Synonyms can be

  • Private: The name is available only to the owner of that synonym.

  • Public: The name is more common and provides a short name for all users within a database so they don’t have to list the schema owner for each object in their queries.

By default, objects are accessed by SCHEMA_OWNER.OBJECT_NAME. For example, ACME_OWN.CUSTOMER is the customer table for ACME_OWN and is how any other application user must access that table: for instance, SELECT * FROM ACME_OWN.CUSTOMER. A public synonym allows you to drop the ACME_OWN from the query.