dbms-notes: writing blocks to disk: (ref) On Data Types

[ Oracle PL/SQL Data Types ]

(a) Scalar data types

Can have subtypes. A data type and its subtypes comprise a data type family.

PL/SQL scalar data types are:


BOOLEAN
PLS_INTEGER
BINARY_INTEGER
REF CURSOR

The PL/SQL data types include the SQL data types. Some have larger max sizes:
CHAR, NCHAR, RAW, VARCHAR2, NVARCHAR2, LONG, LONG RAW, BLOB, CLOB, NCLOB
PL/SQL define subtypes for BINARY_FLOAT: SIMPLE_FLOAT and BINARY_DOUBLE: SIMPLE_DOUBLE

CHAR vs. VARCHAR2

Predefined Subtypes	`CHAR: CHARACTER (PL/SQL and SQL) VARCHAR2: VARCHAR (PL/SQL and SQL), STRING (PL/SQL alone)`
Memory allocation	`CHAR(MaxSize)`: During compile `MaxSize` is allocated in memory. `VARCHAR2(MaxSize): If MaxSize < 4k`: full allocation at compile time. (for performance) `VARCHAR2(MaxSize): If MaxSize >= 4k`: allocation enough for the actual value during run time. (for efficient memory use)
Blank Padding	`CHAR` will do blank-pads to the maximum size. Information loss. `VARCHAR2` will NOT do blank-pads to the maximum size. No information loss.
Value Comparisons	If one or both values in the comparison have the data type `VARCHAR2` or `NVARCHAR2`, nonpadded comparison semantics apply; otherwise, blank-padded semantics apply

LONG and LONG RAW
Supported for backward compatibility only.

Instead of LONG: use VARCHAR2(32760), BLOB, CLOB, NCLOB
Instead of LONG RAW: use BLOB
From LONG variable => LONG column: ANY Value.
From LONG RAW variable => LONG RAW column: ANY Value.
From LONG (or LONG RAW) column => LONG (LONG RAW) variable: 32Kb maximum.
TRIGGER restrictions: (a) Cannot declare LONG or LONG RAW. (b)Cannot use correlation name NEW or PARENT with LONG or LONG RAW. (c) Stmt can reference LONG or LONG RAW column ONLY if the column data can be converted to CHAR or VARCHAR2.

ROWID and UROWID variables (more on ROWIDs here)

ROWIDTOCHAR and CHARTOROWID: Conversion functions.
UROWID: more versatile. Compatible with logical, physical and foreign rowids.
DBMS_ROWID package:
The DBMS_ROWID package lets you create ROWIDs and obtain information about ROWIDs from PL/SQL programs and SQL statements. You can find the data block number, the object number, and other ROWID components.
DBMS_ROWID is intended for upgrading from Oracle 7 to 8.X.

About Rowids
rowids are used in the construction of indexes. In addition to this,

ROWID Pseudocolumn

Every Oracle table has a pseudocolumn named ROWID. It value, however, is not actually stored in the table.
You can select from pseudocolumns, but you cannot insert, update, or delete their values.
Values of the ROWID pseudocolumn are strings representing the address of each row.

-- Query to show the extended rowid
SQL> select rowid from hr.employees where employee_id=100;

ROWID
------------------
AAAC9EAAEAAAABXAAA

ROWID is not physically stored in the database.It is inferred from the file and block address of the data.
An extended rowid includes a data object number. This rowid type uses a base 64 encoding of the physical address for each row.

Extended rowid: four-piece format.
AAAC9E: The data object number identifies the segment. A data object number is assigned to every database segment.
AAE: The data file number: Tablespace-relative. Identifies the file that contains the row.
AAAABX: The data block number Identifies the block that contains the row. Relative to the datafile.
AAA: The row number: identifies the row in the block.

When does a ROWID change?

If row movement is enabled: ROWID can change because of partition key updates, Flashback Table operations, shrink table operations, and so on.
If row movement is disabled: ROWID can change if the row is exported and imported using Oracle Database utilities

You can create table column using the ROWID data type. However, storing a rowid with the intent of using it latter, for example, as an audit trail record, may not be a good idea:
rowid may change as the result of an alter table or after partition movement (for partitioned tables).

(more on ROWIDs here)

Boolean

Valid values: {TRUE | FALSE | NULL}
SQL has no equivalent to BOOLEAN. Thus you cannot:
Assign a BOOLEAN value to a database table column
Select or fetch the value of a database table column into a BOOLEAN variable
Use a BOOLEAN value in a SQL statement, SQL function, or PL/SQL function invoked from a SQL statement
Cannot pass a BOOLEAN value to the DBMS_OUTPUT.PUT or DBMS_OUTPUT.PUTLINE subprogram.

PLS_INTEGER and BINARY_INTEGER
User-Defined PL/SQL Subtypes

Pages