In an INSERT statement, you can mix literal values with expressions. Listing 6.5 demonstrates the use of an INSERT statement in which the additional fee for a course is specified as 10% of the number of units multiplied by $250 per unit.

Listing 6.5. Using an expression in an INSERT statement.

SQL> insert into Course
      2  (Course_ID, Department_ID, Title, Description, Units, Additional_Fees)
  3  values
  4  (`501', `PSYCH', `PSYCH IN FILM',
    5  `Seminar on the portrayal of psychologists and psychiatrists in film',
  6  3, 0.10 * 3 * 250);

 1 row created.

SQL> select Additional_Fees
  2  from Course
  3  where
  4  Course_ID = `501' and
  5  Department_ID = `PSYCH';

ADDITIONAL_FEES
---------------
             75

Column and Value Datatype Must Match

With a few exceptions (which will be discussed), the datatypes for a column and its corresponding value must be identical. Inserting an alphanumeric string into a numeric column, for example, doesn't make any sense, as shown in Listing 6.6.

Listing 6.6. Mismatch between column and value datatypes in an INSERT.

SQL> insert into Course
      2  (Course_ID, Department_ID, Title, Description, Units, Additional_Fees)
  3  values
  4  (`501', `PSYCH', `PSYCH IN FILM',
    5  `Seminar on the portrayal of psychologists and psychiatrists in film',
    6  3, `Seventy-five dollars');
  3, `Seventy-five dollars')
   *
ERROR at line 6:
ORA-01722: invalid number

In the preceding example, Oracle returned an error code ORA-01722 because the string "Seventy-five dollars" cannot be stored in a column defined as a number. However, if the

string is a valid number, the INSERT statement processes successfully, which is illustrated in Listing 6.7. This is an exception to the rule that a column and the value assigned to it must have the same datatype.

Listing 6.7. Conversion of a numeric value to a string in an INSERT.

SQL> insert into Course
      2  (Course_ID, Department_ID, Title, Description, Units, Additional_Fees)
  3  values
  4  (`501', `PSYCH', `PSYCH IN FILM',
    5  `Seminar on the portrayal of psychologists and psychiatrists in film',
  6  3, `75');

1 row created.

Another exception to this rule involves strings and dates. Refer to Listing 6.8 to see how a literal string that adheres to the Oracle default date format (DD-MMM-YY) can be inserted into a date column.

Listing 6.8. Inserting a date value with the default date format.

SQL> insert into Student_Schedule
      2  (Student_ID, Class_ID, Grade, Date_Grade_Assigned)
  3  values
    4  (`10231311', `104200', `B', '02-JUN-97');

1 row created.

Although 02-JUN-97 is a string literal rather than a date literal, it can be assigned to Date_Grade_Assigned because it uses the default Oracle date format: DD-MON-YY.

Using Pseudocolumns as Values

Oracle provides a set of functions called pseudocolumns. Oracle named these functions "pseudocolumns" because to the uninitiated they appear to be columns. Two commonly used pseudocolumns are

You can use pseudocolumns in an INSERT statement to assign a value to a column. For instance, USER and SYSDATE can store the name of the Oracle user who created the row and the date and time when the row was inserted, as shown in Listing 6.9.

Listing 6.9. Using USER and SYSDATE in an INSERT.

SQL> insert into Student_Schedule
  2  (Student_ID, Class_ID, Grade, Date_Grade_Assigned,
  3  Created_Username, Created_Timestamp)
 4  values
   5  (`10231311', `104200', `B', '02-JUN-97',
  6  USER, SYSDATE);

1 row created.

You can assign the pseudocolumn USER to a string column only. Similarly, you can assign the pseudocolumn SYSDATE to a date column only. You can manipulate these pseudocolumns by applying functions and operators. In Listing 6.10, a string literal is concatenated with the current user's name and stored in a string column—Created_Username.

Listing 6.10. Concatenating a pseudocolumn with a string literal.

SQL> insert into Student_Schedule
      2  (Student_ID, Class_ID, Grade, Date_Grade_Assigned,
      3  Created_Username, Created_Timestamp)
    4   values
      5  (`10231311', `104500', `B-', '03-JUN-97',
    6 `User is: ` || USER, SYSDATE);

1 row created.

SQL> select Student_ID, Grade, Created_Username, Created_Timestamp
  2  from Student_Schedule
 3  where
  4  Student_ID = `10231311' and
  5  Class_ID = `104500';

STUDENT_ID           GR CREATED_USERNAME               CREATED_T
-------------------- -- ------------------------------ ---------
10231311             B- User is: TYO                   06-MAR-97

ANALYSIS
The INSERT statement specifies two pseudocolumns: USER and SYSDATE. The SELECT statement shows that USER and SYSDATE provided the expected values for the INSERT statement.

Why Columns Should Be Specified in an INSERT

If you look carefully at the syntax diagram for the INSERT statement in the online Oracle SQL Language Reference Manual, you'll notice that the column list is an optional element. Therefore, if you don't specify the column names to be assigned values, Oracle, by default, uses all the columns. In addition, the column order that Oracle uses is the order in which the

columns were specified when the table was created; this order is the same order that you see when you apply the DESCRIBE command to a table in SQL*Plus.

As shown in Listing 6.11, if the column names are not specified in the INSERT statement, Oracle expects the first column to be the Class Building.

Listing 6.11. Danger of not specifying column names in an INSERT.

SQL> desc class_location
 Name                           Null?    Type
 ------------------------------ -------- ----
 CLASS_BUILDING                 NOT NULL VARCHAR2(25)
 CLASS_ROOM                     NOT NULL VARCHAR2(25)
 SEATING_CAPACITY                        NUMBER(38)

SQL> insert into Class_Location
     2  values
   3  (`250', `MACLAREN COMPLEX', 500);

1 row created.

SQL> select Class_Building, Class_Room, Seating_Capacity
     2  from Class_Location
    3  where
     4  Class_Building = `250';

CLASS_BUILDING           CLASS_ROOM              SEATING_CAPACITY
------------------------ ----------------------- ----------------
250                      MACLAREN COMPLEX        500

Because the column names were not specified in the INSERT statement, the value that was supposed to be stored in Class_Room, 250, looks fine—the row was successfully inserted into the table. However, the use of this syntax can be dangerous.

• The table definition might change; the number of columns might decrease or increase, and the INSERT fails as a result.
• The INSERT statement might be difficult to comprehend. The values might be indistinguishable from one another and, by accident, be transposed. Tracking down the problem without being able to visually match a column with its corresponding value is tricky.
• The INSERT statement might succeed, but the wrong data could be entered in the table. This situation can occur when two adjoining values with the same datatype are accidentally transposed. This scenario is the most serious of the potential dangers because Oracle might not return an error message to inform you that something is wrong.