(a) Scalar data types |
PL/SQL scalar data types are:
More information on Oracle and SQL Data types... |
- 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 |
|
Memory allocation |
|
Blank Padding |
|
Value Comparisons |
|
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 withLONG or LONG RAW.
(c) Stmt can referenceLONG or LONG RAW
column ONLY if the column data can be converted toCHAR 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
When does a ROWID change?
(more on ROWIDs here)
rowids are used in the construction of indexes. In addition to this,
- Rowids are the fastest means of accessing particular rows.
- Rowids provide the ability to see how a table is organized.
- Rowids are unique identifiers for rows in a given table.
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
orDBMS_OUTPUT.PUTLINE
subprogram.
PLS_INTEGER and BINARY_INTEGER
User-Defined PL/SQL Subtypes
No comments:
Post a Comment