ROWID Data Type

The ROWID data type is not supported in TimesTen Scaleout. The address of a row in a table or materialized view is called a rowid. The rowid data type is ROWID. You can examine a rowid by querying the ROWID pseudocolumn. See ROWID Pseudocolumn for details on the ROWID pseudocolumn.

Specify literal ROWID values in SQL statements as constants enclosed in single quotes, as follows:

Command> SELECT ROWID, last_name FROM employees 
         WHERE department_id = 20;

< BMUFVUAAACOAAAALhM, Hartstein >
< BMUFVUAAACOAAAAMhM, Fay >
2 rows found.

Command> SELECT ROWID, last_name FROM employees
         WHERE ROWID='BMUFVUAAACOAAAALhM';
< BMUFVUAAACOAAAALhM, Hartstein >
1 row found.

Use the ROWID data type as follows:

• As the data type for a table column or materialized view column

• In these types of expressions:

  • Literals

  • Comparisons: <, <=, >, >=, BETWEEN

  • CASE Expressions

  • CAST

  • COALESCE

  • COUNT

  • DECODE

  • GREATEST

  • IN

  • IS NULL

  • LEAST

  • MAX

  • MIN

  • NVL

  • TO_CHAR

  • TT_HASH

• In ORDER BY and GROUP BY clauses

• In INSERT...SELECT statements. Column col1 has been defined with the ROWID data type for these examples:

  Command> DESCRIBE master;
   
  Table MYUSER.MASTER:
    Columns:
     *ID                              ROWID NOT NULL
      NAME                            CHAR (30)
   
  1 table found.
  (primary key columns are indicated with *)
  
  Command> INSERT INTO master(id, name) SELECT ROWID, last_name 
           FROM employees;
  107 rows inserted.
  Command> SELECT * FROM master;
  < BMUFVUAAACOAAAAGhG, King                           >
  < BMUFVUAAACOAAAAHhG, Kochhar                        >
  < BMUFVUAAACOAAAAIhG, De Haan                        >
  ...
  107 rows found.
  

  You can use the TO_CHAR function with the ROWID pseudocolumn as shown below:

  Command> INSERT INTO master(id, name) 
           SELECT TO_CHAR(ROWID), last_name
           FROM employees;
  107 rows inserted.
  Command> SELECT * FROM master;
  < BMUFVUAAACOAAAAGhG, King                           >
  < BMUFVUAAACOAAAAHhG, Kochhar                        >
  ...
  107 rows found.
  

  You can use the CAST function with the ROWID pseudocolumn as shown below:

  Command> CREATE TABLE master (id CHAR(20) NOT NULL PRIMARY KEY, 
                                name CHAR(30));
  Command> INSERT INTO master(id, name) 
           SELECT CAST(ROWID AS CHAR(20)), last_name 
           FROM employees;
  107 rows inserted.
  

Implicit type conversions are supported for assigning values and comparison operations between ROWID and CHAR or between ROWID and VARCHAR2 data.

When CHAR, VARCHAR2, and ROWID operands are combined in COALESCE, DECODE, NVL, or CASE expressions (see CASE Expressions), the result data type is ROWID. Expressions with CHAR and VARCHAR2 values are converted to ROWID values to evaluate the expression.

To use ROWID values with string functions such as CONCAT, the application must convert ROWID values explicitly to CHAR values using the SQL TO_CHAR function.