Database Tutorial

SQL> CREATE TABLE sample_date
  2  ( column_name DATE DEFAULT SYSDATE);

A DATE data type can be assigned a date-time that is equal to midnight by enclosing the date column in a TRUNC function. The TRUNC function shaves off any decimal value from the date- time value. The SYSDATE is a current date-time function available inside an Oracle database. You would define a date-only value as a default value with this syntax:

SQL> CREATE TABLE sample_date
  2 ( column_name DATE DEFAULT TRUNC(SYSDATE));

A more accurate timestamp and timestamps with local or general time zone are also available starting with the release of Oracle Database 11g. They’re more accurate because they measure time beyond hundredths of a second. You would define a TIMESTAMP like this:

SQL> CREATE TABLE sample_timestamp
  2  ( column_name TIMESTAMP DEFAULT SYSTIMESTAMP);

You also have INTERVAL DAY TO SECOND and INTERVAL DAY TO MONTH data types. They measure intervening time (like the number of minutes or seconds between two timestamps), which is similar to measuring the difference between two decimal parts of DATE data types.

Oracle supports a series of conversion functions that let you convert data types from one type to another. The generic SQL conversion function is CAST, which lets you convert the following data types:

• Convert from BINARY_FLOAT or BINARY_DOUBLE data type to BINARY_FLOAT, BINARY_DOUBLE, CHAR, VARCHAR2, NUMBER, DATE, TIMESTAMP, NCHAR, NVARCHAR.
• Convert from CHAR or VARCHAR2 data type to BINARY_FLOAT, BINARY_DOUBLE, CHAR, VARCHAR2, NUMBER, DATE, TIMESTAMP, DATE, TIMESTAMP, INTERVAL, RAW, ROWID, UROWID, NCHAR, NVARCHAR. Here’s an example of converting a string literal date into a timestamp:

  CAST('14-FEB-2011' AS TIMESTAMP WITH LOCAL TIME ZONE)

  CAST('14-FEB-2011' AS TIMESTAMP WITH LOCAL TIME ZONE)

  This example works because the date literal conforms to the default format mask for a date in an Oracle database. A nonconforming date literal would raise a conversion error. Many possibilities are available, because you can organize the valid elements of dates many ways. A nonconforming date literal should be converted by using the TO_DATE or TO_TIMESTAMP function, because each of these lets you specify an overriding date format mask value, such as this conversion to a DATE data type:

  TO_DATE('2011-02-14', 'YYYY-MM-DD')

  TO_DATE('2011-02-14', 'YYYY-MM-DD')

  or this conversion to a TIMESTAMP data type:

  TO_TIMESTAMP('2011-02-14 18:11:28.1500', 'YYYY-MM-DD HH24:MI:SS.FF')

  TO_TIMESTAMP('2011-02-14 18:11:28.1500', 'YYYY-MM-DD HH24:MI:SS.FF')

  Converting to an INTERVAL data type is covered in the next bullet because you first must extract a time property as a number. It’s also possible that implicit casting of a numeric string can change the base data type to an integer for you. The method of implicit or explicit conversion depends on how you get the initial data value.

• Convert from NUMBER data type to BINARY_FLOAT, BINARY_DOUBLE, CHAR, VARCHAR2, NUMBER, DATE, TIMESTAMP, NCHAR, NVARCHAR. Interval conversions are a bit more complex, because you need more than one function to convert them. Typically, you pull the value from a DATE or TIMESTAMP data type and EXTRACT the element of time by identifying its type before converting that value to an INTERVAL type. The following provides an example:

  NUMTODSINTERVAL(EXTRACT(MINUTE FROM some_date), 'MINUTE')

  NUMTODSINTERVAL(EXTRACT(MINUTE FROM some_date), 'MINUTE')

  You will use this type of built-in function layering frequently in some situations. It’s always a better approach to understand and use the built-in functions before you write your own stored functions.

• Convert from DATETIME or INTERVAL data type to CHAR, VARCHAR2, DATE, TIMESTAMP, DATE, TIMESTAMP, INTERVAL, NCHAR, NVARCHAR.
• Convert from RAW data type to CHAR, VARCHAR2, RAW, NCHAR, NVARCHAR.
• Convert from ROWID or UROWID data type to CHAR, VARCHAR2, ROWID, UROWID,
  NCHAR, NVARCHAR.
• Convert from NCHAR or NVARCHAR2 data type to BINARY_FLOAT, BINARY_DOUBLE, NUMBER, NCHAR, NVARCHAR.

The CAST function is very useful because it converts built-in data types to another built-in data type, or converts collection-typed variables to another collection-typed variable. The CAST function does have some limits; for instance, it uses only the default date conversion format mask. Unlike most functions, CAST works with all but the LONG, LONG RAW, ROWID, and UROWID built-in data types. CAST also limits how it casts data from BLOB and CLOB types into a RAW data type because it relies on an implicit database behavior. You raise an exception when CAST tries to convert a large object into a RAW type when it is too large to fit inside a RAW data type.

There are two prototypes: one for scalar built-in variables and another for collections. The scalar variable built-in prototype is

CAST(type1_variable_name AS type2_variable_name)

and the collection prototype is

CAST(MULTISET(subquery)) AS collection_type_variable_name)

The following program shows how to cast a date to a string:

SQL> DECLARE
  2    source DATE := TO_DATE('30-SEP-07');
  3    target VARCHAR2(24);
  4  BEGIN
  5    target := CAST(source AS VARCHAR2);
  6    dbms_output.put_line(target);
  7  END;
  8  /

This type of usage is exactly the same in both SQL and PL/SQL contexts, but the MULTISET context is restricted to SQL statements. If you attempt to use a CAST function with a MULTISET and subquery as a right operand, you raise a PLS-00405 exception. However, you can embed these in SQL statements inside your PL/SQL blocks.

The CAST operation inside a query statement requires that you cast to a SQL data type, such as a collection of scalar variables. This leaves you with a choice between varrays and nested tables. You should use nested tables because they are easier to manage and don’t require incremental conversion with the TABLE function call.

This creates a nested table of strings as a SQL data type:

SQL> CREATE OR REPLACE
  2 TYPE collection IS TABLE OF VARCHAR2(5); 3/

You should create a table or view because the MULTISET operator disallows queries that use set operators, like INTERSECT, MINUS, UNION, and UNION ALL. The MULTISET operator raises a PLS-00605 exception when set operators are found in the subquery.
The following builds a sample table:

SQL> CREATE TABLE casting (num VARCHAR2(5));

Next, you can insert into the table the English ordinal numbers “one” to “nine” by using the table fabrication pattern:

SQL> INSERT INTO casting
  2  (SELECT 'One'   FROM dual UNION ALL
  3   SELECT 'Two'   FROM dual UNION ALL
  4   SELECT 'Three' FROM dual UNION ALL
  5   SELECT 'Four'  FROM dual UNION ALL
  6   SELECT 'Five'  FROM dual UNION ALL
  7   SELECT 'Six'   FROM dual UNION ALL
  8   SELECT 'Seven' FROM dual UNION ALL
  9   SELECT 'Eight' FROM dual UNION ALL
 10   SELECT 'Nine' FROM dual);

The sample program demonstrates how to use the CAST and MULTISET functions together:

SQL> DECLARE
  2    counter NUMBER := 1;
  3  BEGIN
  4    FOR i IN (SELECT CAST(
  5                       MULTISET(
  6                         SELECT num
  7                         FROM   casting) AS COLLECTION) AS rs
  8              FROM   casting) LOOP
  9      dbms_output.put_line(i.rs(counter));
 10      counter := counter + 1;
 11    END LOOP;
 12  END;
 13  /

This prints the ordinal number words in a list. The CAST function returns a collection of items. Unfortunately, you also need to match a collection structure to the row structure of the query. You use this type of structure to return nested table contents from tables.

The CONVERT function converts a string from one character set to another. It has the following prototype:

CONVERT(string, destination_character_set, source_character_set)

The following demonstrates converting the French word forêt (forest in English) from the AL32UTF8 character set to the UTF-8 character set:

SQL> DECLARE
  2    text VARCHAR2(10) := 'forêt';
  3  BEGIN
  4    dbms_output.put_line(CONVERT(text,'AL32UTF8','UTF8'));
  5  END;
  6  /

This prints the same forêt but it now takes 3 bytes of storage rather than 2 bytes. You will find this function handy when you work in multiple character sets.

The EXTRACT function lets you find an integer equivalent of a month, day, or year for any date data type. It’s handy when you want to identify transactions occurring on a day, in a month, in a year, or in a set of months or years.
The basic example for finding a day is

SQL> SELECT EXTRACT(DAY FROM TO_DATE('15-APR-2013')) AS DAY 2 FROM dual;

Naturally, the example returns

       DAY
----------
        15

You can substitute a case-insensitive MONTH or YEAR keyword before the FROM keyword to extract the month or year. The argument following the FROM keyword must be a DATE or date-time data type. That means converting a literal date to a timestamp would let you extract the
hour, minute, or second from a timestamp, like this:

SQL> SELECT EXTRACT(HOUR FROM TO_TIMESTAMP('15-APR-2013')) AS HOUR
  2  FROM   dual;

This returns

      HOUR
----------
         0

While extracting a time element isn’t generally too useful by itself, the foregoing example lets me qualify that the time element of any DATE data type is equal to zero, or 00:00:00 hours, minutes, and seconds. Moreover, a DATE data type in Oracle is a timestamp set to the first second of each day.

The EXTRACT function also lets you find ranges of days, months, or years in a WHERE predicate, like

SQL> SELECT SUM(transaction_amount)
  2  FROM   TRANSACTION
  3  WHERE  EXTRACT(MONTH FROM transaction_date) IN (1,2,3);

The IN operator is often called a lookup operator because it checks whether one thing matches one thing in a set. More or less, a lookup function compares a single value or variable against a list of values.

Combining the EXTRACT function and CASE statement enables you to transform data like a pivot function.

The TO_CHAR function lets you do several types of conversion. You can convert CLOB, DATE, NCHAR, NCLOB, NUMBER, or TIMESTAMP data types to VARCHAR2 data types. This function is overloaded and has two prototypes. The prototype for string data types is

TO_CHAR({clob_type | nchar_type | nclob_type})

The alternative prototype for dates, numbers, and times is

TO_CHAR({date_type | timestamp_type | number_type} [, format_mask [, nls_param ]])

The following subsections demonstrate converting other types of strings to character strings, dates to characters strings, and numbers to character strings. The date and number subsections also have two examples each: one with the native National Language Support (NLS) character set of the instance, and one that overrides the instance default.

Converting a String to a Character String

The following demonstrates converting a CLOB data type to a CHAR data type:

SQL> DECLARE 
  2    big_string CLOB := 'Not really that big, eh?'; 
  3  BEGIN
  4    dbms_output.put_line(TO_CHAR(big_string));
  5  END;
  6  /

Converting a Date to a Character String

The TO_CHAR function takes two arguments, or call parameters: the date or date-time, and the format mask. The following demonstrates converting a DATE to a CHAR:

SQL> DECLARE
  2    today DATE := SYSDATE;
  3  BEGIN
  4 dbms_output .put_line(TO_CHAR(today,'Mon DD, YYYY')); 5 END;
  6  /

While your date will reflect the current system date, this prints the day this was written:

Sep 27, 2007

When you add the NLS_DATE_LANGUAGE parameter to the function, you can override the NLS setting for the database. The following resets the NLS_DATE_LANGUAGE parameter to French:

SQL> DECLARE
  2    today DATE := SYSDATE;
  3  BEGIN
  4    dbms_output.put_line(TO_CHAR(today,'Mon DD, YYYY'
  5                        ,'NLS_DATE_LANGUAGE = FRENCH'));
  6  END;
  7  /

This then prints the date in the French style, which adds a period after the abbreviation of the month, like

Sept. 27, 2007

Converting a Number to a Character String

Converting numbers to characters works much like converting dates to characters. The following illustrates converting a number to a formatted dollar amount in American English:

SQL> DECLARE
  2    amount NUMBER := 2.9;
  3  BEGIN
  4    dbms_output.put_line(TO_CHAR(amount,'$9,999.90'));
  5  END;
  6  /

The format mask prints a digit if one is found when there’s a 9 and always print a 0 when there is no value. The format mask substitutes a 0 since there is no value in the hundredths placeholder, printing

$2.90

Adding the NLS parameter, you can now format the currency return in Euros:

SQL> DECLARE
  2    amount NUMBER := 2.9;
  3  BEGIN
  4    dbms_output.put_line(TO_CHAR(amount,'9,999.90L'
  5                        ,'nls_currency = EUR'));
  6  END;
  7  /

This prints

2.90EUR

This section has demonstrated how to use the TO_CHAR function to convert national language and large object strings to character strings and convert dates, timestamps, and numbers to character strings. The format masks only cover characters in those specific positions. You need to expand the format mask when dealing with larger numbers.

The TO_CLOB function lets you convert the NCLOB column data type or other character types to character large objects. You can convert CHAR, NCHAR, NVARCHAR2, and VARCHAR2 data types to NCLOB types.

The prototype for this is

TO_CLOB({char_type | nchar_type | nclob_type | nvarchar2_type | varchar2_type}) The

following converts a string to CLOB and then uses TO_CHAR to reconvert for printing by the DBMS_OUTPUT.PUT_LINE procedure:

SQL> DECLARE
  2    initial_string VARCHAR2(2000) := 'Not really required. :-)';
  3  BEGIN  
  4    dbms_output.put_line(TO_CHAR(TO_CLOB(initial_string)));
  5  END;
  6  /

This is a handy function when you’re moving an array of strings into a CLOB variable. It also lets you move NLS large object columns into a standard format for your programs.

The TO_DATE function lets you convert strings to dates. The prototype for this is

TO_DATE(string_type [, format_make [, nls_param ]])

The following program demonstrates converting a string through implicit conversion:

SQL> DECLARE
  2    target DATE;
  3  BEGIN
  4    target := '29-SEP-94';
  5    dbms_output.put_line(
  6      'Back to a string ['||TO_CHAR(target)||']');
  7  END;
  8  /

The implicit cast works because the default format mask for a date is DD-MON-RR or DD-MON-YYYY. When the string or source is not in that format, you must provide a format mask to cast the string into a date.

The next example explicitly casts a string by providing a format mask:

SQL> DECLARE
  2    target DATE;
  3  BEGIN
  4    target := TO_DATE('September 29, 1994 10:00 A.M.'
  5                     ,'Month DD, YYYY HH:MI A.M.');
  6    dbms_output.put_line(
  7     'Back to a string ['||TO_CHAR(target)||']');
  8  END;
  9  /

The A.M. formatting option is a mask available in some National Language Support (NLS) languages, like American English. It is not supported in French because the appropriate format mask in that language is AM. If you apply an unsupported format mask, you raise an ORA-01855 exception.

The following example demonstrates overriding the default of language:

SQL> DECLARE
  2    target DATE;
  3  BEGIN
  4    target := TO_DATE('Septembre 29, 1994 10:00 AM'
  5                     ,'Month DD, YYYY HH:MI AM'
  6                      ,'NLS_DATE_LANGUAGE = French');
  7    dbms_output.put_line('Back to a string ['
  8   ||                   TO_CHAR(target
  9                               ,'Month DD, YYYY HH:MI AM'
 10                               ,'NLS_DATE_LANGUAGE = French')||']');

The nature of converting to a date from a string is a virtual mirror to reversing the process, as should be clearly seen in the example. The TO_DATE function is frequently used in PL/SQL.

The TO_NUMBER function lets you convert an expression into a numeric value. The expression can be a BINARY_DOUBLE, CHAR, NCHAR, NVARCHAR2, or VARCHAR2 data type. You can also use the NLS_NUMERIC_CHARACTERS parameter or NLS_CURRENCY parameter for National Language Support (NLS).

The prototype for this is

TO_NUMBER(expression [, format_mask [, nls_param ]])

The example converts a formatted string to a number by using a format mask:

SQL> DECLARE
  2    source VARCHAR2(38) := '$9,999.90';
  3  BEGIN
  4    dbms_output.put_line(TO_NUMBER(source,'$9,999.99'));
  5  END;
  6  /

The program prints a number without a hundredth placeholder:

9999.9

You can also use NLS formatting like that shown in the TO_CHAR function examples, or use it this way:

SQL> DECLARE
  2    source VARCHAR2(38) := '9,999.90EUR';
  3  BEGIN
  4    dbms_output.put_line(
  5      TO_NUMBER(source,'9G999D99L','nls_currency = EUR'));
  6  END;
  7  /

It also prints

9999.9

The G stands for comma, D for decimal point (or period), and L for string qualifying the currency format. There is no dollar symbol leading a currency expression when you use an ISO currency string such as USA, JPY, or EUR. The string provided as the value of nls_currency must also match the value in the original string.