MacLochlainns Weblog

This section shows you how to INSERT scalar values into tables.

The basic syntax for an INSERT statement with a VALUES clause can include an optional override signature between the table name and VALUES keyword. With an override signature, you designate the column names and the order of entry for the VALUES clause elements. Without an override signature, the INSERT signature checks the definition of the table in the database catalog. The positional order of the column in the data catalog defines the positional, or default, signature for the INSERT statement. As shown previously, you can discover the structure of a table in Oracle with the DESCRIBE command issued at the SQL*Plus command line:

DESCRIBE table_name

You’ll see the following after describing the rental table in SQL*Plus:

Name                                 Null?    Type
------------------------------------ -------- --------
RENTAL_ID                            NOT NULL NUMBER
CUSTOMER_ID                          NOT NULL NUMBER
CHECK_OUT_DATE                       NOT NULL DATE
RETURN_DATE                                   DATE
CREATED_BY                           NOT NULL NUMBER
CREATION_DATE                        NOT NULL DATE
LAST_UPDATED_BY                      NOT NULL NUMBER
LAST_UPDATE_DATE                     NOT NULL DATE

The rental_id column is a surrogate key, or an artificial numbering sequence. The combination of the customer_id and check_out_date columns serves as a natural key because a DATE data type is a date-time value. If it were only a date, the customer would be limited to a single entry for each day, and limiting customer rentals to one per day isn’t a good business model.

The basic INSERT statement would require that you look up the next sequence value before using it. You should also look up the surrogate key column value that maps to the row where your unique customer is stored in the contact table. For this example, assume the following facts:

• Next sequence value is 1086
• Customer’s surrogate key value is 1009
• Current date-time is represented by the value from the SYSDATE function
• Return date is the fifth date from today
• User adding and updating the row has a primary (surrogate) key value of 1
• Creation and last update date are the value returned from the SYSDATE function.

An INSERT statement must include a list of values that match the positional data types of the database catalog, or it must use an override signature for all mandatory columns.

You can now write the following INSERT statement, which relies on the default signature:

Name                                      Null?    Type
------------------------------------ -------- --------
RENTAL_ID                            NOT NULL NUMBER
CUSTOMER_ID                          NOT NULL NUMBER
CHECK_OUT_DATE                       NOT NULL DATE
RETURN_DATE                                   DATE
CREATED_BY                           NOT NULL NUMBER
CREATION_DATE                        NOT NULL DATE
LAST_UPDATED_BY                      NOT NULL NUMBER
LAST_UPDATE_DATE                     NOT NULL DATE

The rental_id column is a surrogate key, or an artificial numbering sequence. The combination of the customer_id and check_out_date columns serves as a natural key because a DATE data type is a date-time value. If it were only a date, the customer would be limited to a single entry for each day, and limiting customer rentals to one per day isn’t a good business model.

The basic INSERT statement would require that you look up the next sequence value before using it. You should also look up the surrogate key column value that maps to the row where your unique customer is stored in the contact table. For this example, assume the following facts:

• Next sequence value is 1086
• Customer’s surrogate key value is 1009
• Current date-time is represented by the value from the SYSDATE function
• Return date is the fifth date from today
• User adding and updating the row has a primary (surrogate) key value of 1
• Creation and last update date are the value returned from the SYSDATE function.

An INSERT statement must include a list of values that match the positional data types of the database catalog, or it must use an override signature for all mandatory columns.

You can now write the following INSERT statement, which relies on the default signature:

SQL> INSERT INTO rental
  2  VALUES
  3  ( 1086
  4  , 1009
  5  , SYSDATE
  6  , TRUNC(SYSDATE + 5) 7 ,1
  8  , SYSDATE
  9  , 1
 10  , SYSDATE);

If you weren’t using SYSDATE for the date-time value on line 5, you could manually enter a date-time with the following Oracle proprietary syntax:

  5  , TO_DATE('15-APR-2011 12:53:01','DD-MON-YYYY HH24:MI:SS')

The TO_DATE function is an Oracle-specific function. The generic conversion function would be the CAST function. The problem with a CAST function by itself is that it can’t handle a format mask other than the database defaults (‘DD-MON-RR‘ or ‘DD-MON-YYYY‘). For example, consider this syntax:

  5  , CAST('15-APR-2011 12:53:02' AS DATE)

It raises the following error:

  5  ,  CAST('15-APR-2011 12:53:02' AS DATE) FROM dual
        *
ERROR AT line 1:
ORA-01830: DATE format picture ends before converting entire input string

You actually need to double cast this type of format mask when you want to store it as a DATE data type. The working syntax casts the date-time string as a TIMESTAMP data type before recasting the TIMESTAMP to a DATE, like

  5  ,  CAST(CAST('15-APR-2011 12:53:02' AS TIMESTAMP) AS DATE)

Before you could write the preceding INSERT statement, you would need to run some queries to find the values. You would secure the next value from a rental_s1 sequence in an Oracle database with the following command:

SQL> SELECT   rental_s1.NEXTVAL FROM dual;

This assumes two things, because sequences are separate objects from tables. First, code from which the values in a table’s surrogate key column come must appear in the correct sequence. Second, a sequence value is inserted only once into a table as a primary key value.

In place of a query that finds the next sequence value, you would simply use a call against the .nextval pseudocolumn in the VALUES clause. You would replace line 3 with this:

  3  ( rental_s1.NEXTVAL

The .nextval is a pseudocolumn, and it instantiates an instance of a sequence in the current session. After a call to a sequence with the .nextval pseudocolumn, you can also call back the prior sequence value with the .currval pseudocolumn.

Assuming the following query would return a single-row, you can use the contact_id value as the customer_id value in the rental table:

SQL> SELECT   contact_id
  2  FROM     contact
  3  WHERE    last_name = 'Potter'
  4  AND      first_name = 'Harry';

Taking three steps like this is unnecessary, however, because you can call the next sequence value and find the valid customer_id value inside the VALUES clause of the INSERT statement. The following INSERT statement uses an override signature and calls for the next sequence value on line 11. It also uses a scalar subquery to look up the correct customer_id value with a scalar subquery on lines 12 through 15.

SQL> INSERT INTO rental
  2  ( rental_id
  3  , customer_id
  4  , check_out_date
  5  , return_date
  6  , created_by
  7  , creation_date
  8  , last_updated_by
  9  , last_update_date )
10 VALUES
 11  ( rental_s1.NEXTVAL
 12  ,(SELECT   contact_id
 13    FROM     contact
 14    WHERE    last_name = 'Potter'
 15    AND      first_name = 'Harry')
 16  , SYSDATE
 17  , TRUNC(SYSDATE + 5)
 18  , 1
 19  , SYSDATE
 20  , 3
 21  , SYSDATE);

When a subquery returns two or more rows because the conditions in the WHERE clause failed to find and return a unique row, the INSERT statement would fail with the following message:

,(SELECT   contact_id
  *
ERROR AT line 3:
ORA-01427: single-ROW subquery returns more than one ROW

In fact, the statement could fail when there are two or more “Harry Potter” names in the data set because three columns make up the natural key of the contact table. The third column is the member_id, and all three should be qualified inside a scalar subquery to guarantee that it returns only one row of data.

This section shows you how to INSERT large object values into tables.

Oracle’s large objects present a small problem when they’re not null constrained in the table definition. You must insert empty object containers or references when you perform an INSERT statement.

Assume, for example, that you have the following three large object columns in a table:

Name                             Null?    Type
 ------------------------------- -------- -----------------------
ITEM_DESC                        NOT NULL CLOB
ITEM_ICON                        NOT NULL BLOB
ITEM_PHOTO                                BINARY FILE LOB

The item_desc column uses a CLOB (Character Large Object) data type, and it is a required column; it could hold a lengthy description of a movie, for example. The item_icon column uses a BLOB (Binary Large Object) data type, and it is also a required column. It could hold a graphic image. The item_photo column uses a binary file (an externally managed file) but is fortunately null allowed or an optional column in any INSERT statement. It can hold a null or a reference to an external graphic image.

Oracle provides two functions that let you enter an empty large object, and they are:

EMPTY_BLOB()
EMPTY_CLOB()

Although you could insert a null value in the item_photo column, you can also enter a reference to an Oracle database virtual directory file. Here’s the syntax to enter a valid BFILE name with the BFILENAME function call:

 10  , BFILENAME('VIRTUAL_DIRECTORY_NAME', 'file_name.png')

You can insert a large character or binary stream into BLOB and CLOB data types by using the stored procedures and functions available in the dbms_lob package. Chapter 13 covers the dbms_lob package.

You can use an empty_clob function or a string literal up to 32,767 bytes long in a VALUES clause. You must use the dbms_lob package when you insert a string that is longer than 32,767 bytes. That also changes the nature of the INSERT statement and requires that you append the RETURNING INTO clause. Here’s the prototype for this Oracle proprietary syntax:

INSERT INTO some_table
[( column1, column2, column3, ...)]
VALUES
( value1, value2, value3, ...)
RETURNING column1 INTO local_variable;

The local_variable is a reference to a procedural programming language. It lets you insert a character stream into a target CLOB column or insert a binary stream into a BLOB column.

This section shows you how to INSERT a new sequence in a parent table and a copy of that new sequence as a foreign key value in a child table.

Sometimes you insert into a series of tables in the scope of a transaction. In this scenario, one table gets the new sequence value (with a call to sequence_name.nextval) and enters it as the surrogate primary key, and another table needs a copy of that primary key to enter into a foreign key column. While scalar subqueries can solve this problem, Oracle provides the .currval pseudocolumn for this purpose.

The steps to demonstrate this behavior require a parent table and a child table. The parent table is defined as follows:

Name                                 Null?    Type
------------------------------------ -------- --------------
PARENT_ID                            NOT NULL NUMBER
PARENT_NAME                                   VARCHAR2(10)

The parent_id column is the primary key for the parent table. You include the parent_id column in the child table. In the child table, the parent_id column holds a copy of a valid primary key column value as a foreign key to the parent table.

Name                                 Null?    Type
------------------------------------ -------- --------------
CHILD_ID                             NOT NULL NUMBER
PARENT_ID                                     NUMBER
PARENT_NAME                                   VARCHAR2(10)

After creating the two tables, you can manage inserts into them with the .nextval and .currval pseudocolumns. The sequence calls with the .nextval pseudocolumn insert primary key values, and the sequence calls with the .currval pseudocolumn insert foreign key values.

You would perform these two INSERT statements as a group:

SQL> INSERT INTO parent
  2  VALUES
  3  ( parent_s1.NEXTVAL 4 ,'One Parent');
 
SQL> INSERT INTO child
  2  VALUES
  3  ( child_s1.NEXTVAL 4 , parent_s1.CURRVAL 5 ,'One Child');

The .currval pseudocolumn for any sequence fetches the value placed in memory by call to the .nextval pseudocolumn. Any attempt to call the .currval pseudocolumn before the .nextval pseudocolumn raises an ORA-02289 exception. The text message for that error says the sequence doesn’t exist, which actually means that it doesn’t exist in the scope of the current session. Line 4 in the insert into the child table depends on line 3 in the insert into the parent table.

You can use comments in INSERT statements to map to columns in the table. For example, the following shows the technique for the child table from the preceding example:

SQL> INSERT INTO child
  2  VALUES
  3  ( child_s1.NEXTVAL -- CHILD_ID
  4  , parent_s1.CURRVAL -- PARENT_ID
  5  ,'One Child') -- CHILD_NAME
  6  /

Comments on the lines of the VALUES clause identify the columns where the values are inserted. A semicolon doesn’t execute this statement, because a trailing comment would trigger a runtime exception. You must use the semicolon or forward slash on the line below the last VALUES element to include the last comment.