Oracle7 Server Application Developer's Guide Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Contents for this book
Contents
Go to Index
Index



Go to previous file in sequence Go to next file in sequence

Selecting a Datatype


This chapter discusses the Oracle datatypes, their properties, and mapping to non-Oracle datatypes. Topics discussed include the following:

Oracle Datatypes

The following sections describe the Oracle datatypes that can be used for column definitions.

Character Datatypes

The CHAR and VARCHAR2 datatypes are used to store alphanumeric data; any character can be stored in a column of these datatypes. Character data is stored in strings with byte values corresponding to the character encoding scheme (usually called a character set or code page) defined for the database when it was created; for example, 7-bit ASCII, EBCDIC Code Page 500, or Japan Extended UNIX. Oracle supports both single-byte and multi-byte encoding schemes. See the Oracle7 Server Reference manual for more information about National Language Support features of Oracle and support for different character encoding schemes.

Because Oracle blank-pads values stored in CHAR columns but not in VARCHAR2 columns, a value stored in a VARCHAR2 column may take up less space than if it were stored in a CHAR column. For this reason, a full table scan on a large table containing VARCHAR2 columns may read fewer data blocks than a full table scan on a table containing the same data stored in CHAR columns. If your application often performs full table scans on large tables containing character data, you might be able to improve performance by storing this data in VARCHAR2 columns rather than in CHAR columns.

However, performance is not the only factor to consider when deciding which of these datatypes to use. Oracle uses different semantics to compare values of each datatype. You might choose one datatype over the other if your application is sensitive to the differences between these semantics. For example, if you want Oracle to ignore trailing blanks when comparing character values, you must store these values in CHAR columns. For information on the comparison semantics for these datatypes, see the Oracle7 Server SQL Reference manual.

CHAR Datatype

The CHAR datatype is used to store fixed-length character strings. When you create a table with a CHAR column, specify a column length (in bytes, not characters) between 1 and 255 (default is 1). Oracle then guarantees the following:

Oracle compares CHAR values using blank-padded comparison semantics. If two values have different lengths, Oracle adds blanks to the shorter value, until the two values are the same length. Two values that differ only in the number of trailing blanks are considered equal.

VARCHAR2 Datatype

Use the VARCHAR2 datatype to store variable-length character strings. When you create a table with a VARCHAR2 column, specify a maximum column length (in bytes, not characters) between 1 and 2000. For each row, each value in the column is stored as a variable-length field. For example, assume a column is declared VARCHAR2 with a maximum size of 50 characters. If only 10 single-byte characters are given for the VARCHAR2 column value in a particular row, the column in the row's row piece only stores the 10 characters (10 bytes), not 50.

Oracle compares VARCHAR2 values using non-padded comparison semantics. Two values are only considered equal if they have the same characters and are of equal length.

VARCHAR Datatype

The VARCHAR datatype is currently synonymous with the VARCHAR2 datatype. However, in a future version of Oracle, the VARCHAR datatype might be changed to use different comparison semantics. Therefore, you should use the VARCHAR2 datatype to store variable-length character strings.

Column Lengths for Character Datatypes

The lengths of CHAR and VARCHAR2 columns are specified in bytes rather than characters, and are constrained as such. If the database character encoding scheme is single-byte, the number of bytes and the number of characters in a column is the same. If it is multi-byte, there generally is no such correspondence. A character might be comprised of one or more bytes depending upon the specific multi-byte encoding scheme, and whether shift-in/shift-out control codes are present. When using a multi-byte database character encoding scheme, consider carefully the space required for tables with character columns.

NUMBER Datatype

Use the NUMBER datatype to store real numbers in a fixed-point format. Numbers using this datatype are guaranteed to be portable among different Oracle platforms, and offer up to 38 decimal digits of precision. You can store positive and negative numbers of magnitude 1 x 10^-130 to 9.99...x10^125, as well as zero, in a NUMBER column.

For numeric columns you can specify the column as

column_name NUMBER

or you can specify a precision (total number of digits) and scale (number of digits to right of decimal point):

column_name NUMBER (precision, scale)

If a precision is not specified, the column stores values as given. If no scale is specified, it is implied that the scale is zero. The scale can range from -84 to 127.

Although not required, specifying the precision and scale for numeric fields provides extra integrity checking on input. Table 5 - 1 shows examples of how data would be stored using different scale factors.

Notice that if a negative scale is specified, the actual data is rounded to the specified number of places to the left of the decimal point. For example, a specification of (7,-2) means to round to the nearest hundreds, as shown in Table 5 - 1.

Input Data Specified As Stored As
7,456,123.89 NUMBER 7456123.89
7,456,123.89 NUMBER (9) 7456124
7,456,123.89 NUMBER (9,2) 7456123.89
7,456,123.89 NUMBER (9,1) 7456123.9
7,456,123.89 NUMBER (6) (not accepted, exceeds precision)
7,456,123.89 NUMBER (7, -2) 7456100
Table 5 - 1. How Scale Factors Affect Numeric Data Storage

DATE Datatype

Use the DATE datatype to store point-in-time values (dates and times) in a table. The DATE datatype stores the century, year, month, day, hours, minutes, and seconds. Oracle can store dates in the Julian era, ranging from January 1, 4712 BCE through December 31, 4712 CE (Common Era). Unless BCE ('BC' in the format mask) is specifically used, CE date entries are the default.

Oracle uses its own internal format to store dates. Date data is stored in fixed-length fields of seven bytes each, corresponding to century, year, month, day, hour, minute, and second. See Chapter 3 in the Programmer's Guide to the Oracle Call Interface for a complete description of the Oracle internal date format.

For input and output of dates, the standard Oracle default date format is DD-MON-YY, as in

'13-NOV-92'

To change this default date format on an instance-wide basis, use the NLS_DATE_FORMAT parameter. To change the format during a session, use the ALTER SESSION statement. To enter dates that are not in the current default date format, use the TO_DATE function with a format mask, as in

TO_DATE ('November 13, 1992', 'MONTH DD, YYYY')

If the date format DD-MON-YY is used, YY indicates the year in the 20th century (for example, 31-DEC-92 is December 31, 1992). If you want to indicate years in any century other than the 20th century, use a different format mask, as shown above.

Time is stored in 24-hour format--HH:MM:SS. By default, the time in a date field is 12:00:00 A.M. if no time portion is entered. In a time-only entry, the date portion defaults to the first day of the current month. To enter the time portion of a date, use the TO_DATE function with a format mask indicating the time portion, as in

INSERT INTO birthdays (bname, bday) VALUES
('ANNIE',TO_DATE('13-NOV-92 10:56 A.M.','DD-MON-YY HH:MI A.M.'));

To compare dates that have time data, use the SQL function TRUNC if you want to ignore the time component. Use the SQL function SYSDATE to return the system date and time. The FIXED_DATE initialization parameter allows you to set SYSDATE to a constant; this can be useful for testing.

Using Julian Dates

Julian dates allow continuous dating from a common reference. (The epoch is 1 January 4712 BCE, so current dates are somewhere in the 2.4 million range.) A Julian date is nominally a non-integer, the fractional part being a portion of a day. Oracle uses a simplified approach that results in integer values. Julian dates can be calculated and interpreted differently; the calculation method used by Oracle results in a seven-digit number for dates most often used, as in 2448355 for 08-APR-1991.

Use the format mask `J' with date functions (TO_DATE or TO_CHAR, but not TO_NUMBER) to convert date data into Julian dates. For example, the following query returns all dates in Julian date format:

SELECT TO_CHAR (hiredate, 'J') FROM emp;

Use the TO_NUMBER function to use Julian dates in calculations. You can use the TO_DATE function to enter Julian dates:

INSERT INTO emp (hiredate) VALUES (TO_DATE(2448921, 'J'));

Note: Oracle Julian dates might not be compatible with Julian dates generated by other date algorithms.

Date Arithmetic

Oracle date arithmetic takes into account the anomalies of the Western calendars used throughout history. For example, the switch from the Julian to the Gregorian calendar, 15 October 1582, eliminated the previous 10 days (05 October - 14 October). Additionally, the year 0 does not exist.

Missing dates can be entered into the database, but are ignored in date arithmetic and treated as the next date. For example, the next day after 04 October 1582 is 15 October 1582 and the day following 05 October 1582 is 16 Oct 1582.

Note: This discussion of date arithmetic may not apply to all countries' date standards (for example, Asian countries).

LONG Datatype

Columns defined as LONG can store variable-length character data containing up to 2 gigabytes of information. LONG columns have many of the characteristics of VARCHAR2 columns. The length of LONG values might be limited by the memory available on your computer.

Uses of LONG Data

Columns defined with the LONG datatype are used in the data dictionary to store the text of view definitions. You can use columns defined as LONG in SELECT lists, SET clauses of UPDATE statements, and VALUES clauses of INSERT statements.

Restrictions on LONG and LONG RAW Data

Though LONG (and LONG RAW) columns have many uses, there are some restrictions on their use:

Suggestion: When you design tables containing LONG or LONG RAW data, you should place each LONG or LONG RAW column in a table separate from any other data associated with it, rather than storing the LONG or LONG RAW column and its associated data together in the same table. You can then relate the two tables with a referential integrity constraint. This design allows SQL statements that access only the associated data to avoid reading through LONG or LONG RAW data.

Example

To store information on magazine articles, including the texts of each article, create two tables:

CREATE TABLE article_header (id             NUMBER
                                            PRIMARY KEY,
                             title          VARCHAR2(200),
                             first_author   VARCHAR2(30),
                             journal        VARCHAR2(50),
                             pub_date       DATE)
CREATE TABLE article_text   (id             NUMBER
                                            REFERENCES
                                            article_header,
                             text           LONG)

The ARTICLE_TEXT table stores only the text of each article. The ARTICLE_HEADER table stores all other information about the article, including the title, first author, and journal and date of publication. The tables are related by the referential integrity constraint on the ID column of each table.

This design allows SQL statements to query data other than the text of an article without reading through the text. If you want to select all first authors published in Nature magazine during July 1991, you can issue this statement that queries the ARTICLE_HEADER table:

SELECT first_author
   FROM article_header
   WHERE journal = 'NATURE'
      AND TO_CHAR(pub_date, 'MM YYYY') = '07 1991')

If the text of each article were stored in the same table with the first author, publication, and publication date, Oracle would have to read through the text to perform this query.

RAW and LONG RAW Datatypes

Use the RAW and LONG RAW datatypes for data that is not to be interpreted (not to be converted when moving data between different systems) by Oracle. These datatypes are intended for binary data and byte strings. For example, LONG RAW can be used to store graphics, sound, documents, and arrays of binary data; the interpretation is dependent on the use.

Like the character datatype VARCHAR2, RAW and LONG RAW are variable length; however, the length of RAW cannot exceed 255 bytes, and there is no character set conversion done for RAW or LONG RAW data. CHAR, VARCHAR2, and LONG data is automatically converted from the database character set to the character set defined for the user session by the NLS_LANG parameter, where these are different.

When Oracle automatically converts RAW or LONG RAW data to and from CHAR data (as is the case when entering RAW data as a literal in an INSERT statement), the data is represented as one hexadecimal character representing the bit pattern for every four bits of RAW data. For example, one byte of RAW data with bits 11001011 is displayed and entered as 'CB'.

LONG RAW data cannot be indexed, but RAW data can be indexed.

ROWIDs and the ROWID Datatype

Every row in a non-clustered table of an Oracle database is assigned a unique ROWID that corresponds to the physical address of a row's row piece (initial row piece if the row is chained among multiple row pieces). In the case of clustered tables, rows in different tables that are in the same data block can have the same ROWID.

Each table in an Oracle database internally has a pseudo-column named ROWID; this pseudo-column is not evident when listing the structure of a table by executing a SELECT * FROM . . . statement, or a DESCRIBE . . . statement using SQL*Plus. However, each row's address can be retrieved with a SQL query using the reserved word ROWID as a column name, as in

SELECT ROWID, ename FROM emp;

ROWIDs use a binary representation of the physical address for each row selected. When queried using SQL*Plus or SQL*DBA, the binary representation is converted to a VARCHAR2/hexadecimal representation. The above query might return the following row information:

ROWID              ENAME
------------------ ----------
00000DD5.0000.0001 SMITH
00000DD5.0001.0001 ALLEN
00000DD5.0002.0001 WARD

As shown above, a ROWID's VARCHAR2/hexadecimal representation is divided into three pieces: block.row.file.

A row's assigned ROWID remains unchanged unless the row is exported and imported (using the IMPORT and EXPORT utilities). When you delete a row from a table (and commit the encompassing transaction), the deleted row's associated ROWID can be assigned to a row inserted in a subsequent transaction.

You cannot set the value of the pseudo-column ROWID in INSERT or UPDATE statements. The ROWIDs in the pseudo-column ROWID are used internally by Oracle for various operations (see the next section). Though you can reference ROWIDs in the pseudo-column ROWID like other table columns (used in SELECT lists and WHERE clauses), ROWIDs in this pseudo-column are not stored in the database, nor are they database data.

ROWIDs and Non-Oracle Databases To execute Oracle database applications against non-Oracle database servers, use the Oracle Open Gateway. In such cases, the binary format of ROWIDs varies according to the characteristics of the non-Oracle system. Furthermore, no standard translation to VARCHAR2/hexadecimal format is available. Programs can still use the ROWID datatype; however, they must use a non-standard translation to hexadecimal format of length up to 256 bytes. Refer to the relevant manual for OCIs or Precompilers for further details on handling ROWIDs with non-Oracle systems.

How ROWIDs Are Used

ROWIDs are used internally by Oracle in the construction of indexes. Each key in an index is associated with a ROWID that points to the associated row's address for fast access. Some of the characteristics of ROWIDs include the following:

Before using ROWIDs in DML statements, ROWIDs should be verified and guaranteed not to change; that is, the intended rows should be locked so they cannot be deleted. Attempting to retrieve a row using an invalid ROWID results in either no row being returned or error 1410, invalid ROWID.

You can also create tables with columns defined using the ROWID datatype; for example, you might define an exception table with a column of datatype ROWID to store the ROWIDs of rows in the database that violate integrity constraints. Such columns defined using the ROWID datatype behave like other table columns; that is, values can be updated, etc. All values in a column defined as datatype ROWID require six bytes to store pertinent column data.

Examples of Using ROWIDs

Using some group functions with ROWID, you can see how data is internally stored in an Oracle database. The function SUBSTR can be used to break the data in ROWID into its three components (file, block, and row). For example, the query

SELECT ROWID, SUBSTR(ROWID,15,4) "FILE",
      SUBSTR(ROWID,1,8) "BLOCK",
      SUBSTR(ROWID,10,4) "ROW"
      FROM emp;

might return the following data:

ROWID               FILE  BLOCK     ROW
------------------  ----  --------  ----
00000DD5.0000.0001  0001  00000DD5  0000
00000DD5.0001.0001  0001  00000DD5  0001
00000DD5.0002.0001  0001  00000DD5  0002

ROWIDs can be useful to reveal information about the physical storage of a table's data. For example, if you are interested in the physical location of a table's rows (for example, for table striping), the following query tells how many datafiles contain rows of a given table:

SELECT COUNT(DISTINCT(SUBSTR(ROWID,15,4))) "FILES" FROM tablename;

which might return

   FILES
--------

       2

Summary of Oracle Datatype Information

For quick reference, Table 5 - 2 summarizes the important information about each Oracle datatype.

Datatype Description
CHAR (size) Fixed-length character data of length size. Maximum size is 255 bytes. Default size is 1 byte.
VARCHAR2 (size) Variable-length character data. Maximum size is 2000 bytes.
FLOAT (p) A floating-point number with binary precision p. FLOAT with no precision is the same as FLOAT (126).
NUMBER (p, s) Variable-length numeric data. The precision p (total number of digits) can range from 1 to 38. The scale is s (number of decimal places) and can range from -84 to 127.
DATE Fixed-length date and time data, ranging from January 1, 4712 BC to December 31, 4712 AD. Default format is the value of the NLS_DATE_FORMAT or ALTER SESSION parameter.
LONG Variable-length character date up to 2^31 * 1 bytes - 1, or 2 gigabytes - 1 byte.
RAW (size) Variable-length raw binary data. A maximum size must be specified, up to 255.
LONG RAW Variable-length raw binary data up to 2^31 * 1 bytes - 1, or 2 gigabytes - 1 byte.
ROWID Binary data representing row addresses.
MLSLABEL Variable-length tag (2 - 5 bytes) that maps to a binary operating system label. For use with Trusted Oracle; see page 5 - 13 for details.
Table 5 - 2. Summary of Oracle Datatype Information

Trusted Oracle MLSLABEL Datatype

Trusted Oracle provides one new datatype: the MLSLABEL datatype. You can declare columns of the MLSLABEL datatype in standard Oracle, as well as Trusted Oracle, for compatibility with Trusted Oracle applications. In Oracle7, these columns contain nulls.

The MLSLABEL datatype is used to store the binary format of an operating system label. The maximum width of a column declared as MLSLABEL is 255 bytes.

Any labels that are valid on your operating system can be inserted into an MLSLABEL column. When you insert a label into an MLSLABEL column, Trusted Oracle implicitly converts the data into the binary format of the label.

The ALL_LABELS Data Dictionary View

The ALL_LABELS data dictionary view lists all of the labels ever stored in the database, including the values of DBHIGH and DBLOW. Any label ever stored in an MLSLABEL column (including the ROWLABEL column) is automatically added to this view.

Note that this view does not necessarily contain all labels that are valid in the database, since any valid operating system label, in any valid format, is a valid label within Trusted Oracle. Also note that this view may contain labels that are invalid within the database (if those labels were once used in the database, but are no longer valid).

Adding New Labels

If a label is not already in the ALL_LABELS view, and it is a valid operating system label, it is automatically added to the data dictionary as a valid label when you specify it in an INSERT or UPDATE statement.

For example, if TRULY_SENSITIVE:ALPHA, SENSITIVE, and UNCLASSIFIED are the only labels in the ALL_LABELS view, you can enter SENSITIVE:ALPHA into a column declared as MLSLABEL as long as SENSITIVE:ALPHA is a valid label in your operating system. Once inserted, this label automatically becomes a valid label in Trusted Oracle and is displayed in the ALL_LABELS view.

Trusted Oracle ROWLABEL Column

The ROWLABEL column is automatically appended to each Trusted Oracle table at table creation. This column contains a label of the MLSLABEL datatype for every row in the table.

In OS MAC mode, given that a table can contain rows at one label only, the values in this column are always uniform within a table (and within a single database).

In DBMS MAC mode, the values in this column can range within a single table from DBHIGH to DBLOW (within any constraints defined for that table).

Retrieving Row Labels

The ROWLABEL column is not automatically returned as part of a query. To retrieve the label of a row, you must explicitly select the ROWLABEL column.

For example, to retrieve the label of a row from a table or view, specify the ROWLABEL column in the SELECT statement:

SELECT rowlabel, ename FROM emp
   WHERE ename = 'JASUJA'

which returns

ROWLABEL                           ENAME
--------------------------------   ----------
SENSITIVE                          JASUJA

You can also specify the ROWLABEL pseudo-column in the WHERE clause of a SELECT statement:

SELECT rowlabel,ename FROM emp
    WHERE rowlabel = 'SENSITIVE'

which returns

ROWLABEL                           ENAME
--------------------------------   ----------
SENSITIVE                          JASUJA
SENSITIVE                          ASHER

Retrieving All Labels From a Table

Note that when you select all columns from a table (SELECT *), the ROWLABEL column is not returned. You must explicitly specify that you want to retrieve the label in order to retrieve it. For example, to retrieve all columns, including the ROWLABEL column, from the DEPT table, enter the following:

SELECT rowlabel, dept.*
    FROM dept

This provides for compatibility with older applications (for example, Oracle RDBMS Version 6) or those designed to run in single-level and multi-level environments. However, the values for ROWLABEL are always utilized for MAC enforcement, and are always available in Trusted Oracle for retrieval and display when specified.

Retrieving Labels from Multiple Tables

To retrieve the label from more than one table or view, you must preface the ROWLABEL column with the table or view name. For example, to retrieve the ROWLABEL column from the EMP and DEPT tables wherever there is a match in department number, enter:

SELECT emp.rowlabel, dept.rowlabel
   FROM emp, dept
   WHERE emp.deptno = dept.deptno

If specifying the names of multiple tables becomes too lengthy, you can create synonyms for the table names within the query itself.

For example, the following query is identical to the query above:

SELECT e.rowlabel, d.rowlabel
    FROM emp e, dept d
    WHERE e.deptno = d.deptno

Modifying Row Labels

You can specify any valid operating system label in the ROWLABEL column, as described in"Adding New Labels" [*].

In OS MAC mode, you can perform inserts and updates to the ROWLABEL column under certain restrictions. In DBMS MAC mode, you can modify the ROWLABEL column if you have the appropriate MAC privileges

For detailed information on modifying the ROWLABEL column, see your Trusted Oracle7 Server Administrator's Guide.

Displaying Your DBMS Label

Your DBMS label is the label at which you are connected to Trusted Oracle. In OS MAC mode, this is always equivalent to the label of the database to which you are connected and always equivalent to your operating system session label. In DBMS MAC mode, this is the label with which you are connected to Trusted Oracle; because privileged users can alter their DBMS labels, this does not necessarily equal the label of your operating system session.

You can display your DBMS label in any of the following ways:

Displaying Your Label with the USERENV Function

To retrieve your DBMS label in any of the interactive tools produced by Oracle Corporation (including SQL*Plus and SQL*DBA), use the LABEL parameter of the USERENV function.

For example, assume that you are logged in to the database at SENSITIVE. To determine your DBMS label, enter

SELECT USERENV('label') FROM dual

which returns the following:

ROWLABEL
--------------------------------
SENSITIVE

Displaying Your Label with the Server Manager SHOW Command

To display your label in Server Manager, enter

SVRMGR>	SHOW LABEL

which returns the following:

Label                    SENSITIVE

Altering Your DBMS Label

In DBMS MAC mode, you can alter your DBMS label with the SET LABEL parameter of the ALTER SESSION command. See your Trusted Oracle7 Server Administrator's Guide for more information about using this command.

The ALTER SESSION SET LABEL command is valid in OS MAC mode; however, as you cannot set your DBMS label to any label other than that of the database to which you are connected, the command is superfluous.

ANSI/ISO, DB2, and SQL/DS Datatypes

In addition to Oracle datatypes, you can define columns of tables in an Oracle database using ANSI/ISO, DB2, and SQL/DS datatypes. However, Oracle internally converts such datatypes to Oracle datatypes. The ANSI datatype conversions to Oracle datatypes are shown in Table 5 - 3; Table 5 - 4 shows the DB2 and SQL/DS conversions.

ANSI SQL Datatype Oracle Datatype
CHARACTER (n), CHAR (n) CHAR (n)
NUMERIC (p,s), DECIMAL (p,s), DEC (p,s) NUMBER (p,s)
INTEGER, INT, SMALLINT NUMBER (38)
FLOAT (p) FLOAT (p)
REAL FLOAT (63)
DOUBLE PRECISION FLOAT (126)
CHARACTER VARYING(n), CHAR VARYING(n) VARCHAR2 (n)
Table 5 - 3. ANSI Datatype Conversions to Oracle Datatypes

DB2 or SQL/DS Datatype Oracle Datatype
CHARACTER (n) CHAR (n)
VARCHAR (n) VARCHAR2 (n)
LONG VARCHAR LONG
DECIMAL (p,s) NUMBER (p,s)
INTEGER, SMALLINT NUMBER (38)
FLOAT (p) FLOAT (p)
DATE DATE
Table 5 - 4. SQL/DS, DB2 Datatype Conversions to Oracle Datatypes

The IBM products SQL/DS, and DB2 datatypes TIME, TIMESTAMP, GRAPHIC, VARGRAPHIC, and LONG VARGRAPHIC have no corresponding Oracle datatype and cannot be used. The TIME and TIMESTAMP datatypes are subcomponents of the Oracle datatype DATE.

The ANSI/ISO datatypes NUMERIC, DECIMAL, and DEC can specify only fixed-point numbers. For these datatypes, s defaults to 0.

Data Conversion

In some cases, Oracle allows data of one datatype where it expects data of a different datatype. Generally, an expression cannot contain values with different datatypes. However, Oracle can use the following functions to automatically convert data to the expected datatype:

Implicit datatype conversions work according to the rules explained in the following two sections.

Note: In this discussion, CHAR is used to refer to the class of all character string datatypes.

If using Trusted Oracle, see page 5 - 13 for additional information involving data conversions and the MLSLABEL and RAW MLSLABEL datatypes.

Rule 1: Assignments

For assignments, Oracle can perform the following conversions automatically

The assignment succeeds if Oracle can convert the datatype of the value used in the assignment to that of the assignment's target. The four types of assignments are explained below.

For the examples in the following list, assume a package with a public variable declared as

var1   CHAR(5);

and a table created with the following statement:

CREATE TABLE table1 (col1 NUMBER);

		var1 := 0

		INSERT INTO table1 VALUES ('19');

		UPDATE table1 SET col1 = '30';

		SELECT col1 INTO var1 FROM table1 WHERE col1 = 30;

Rule 2: Expression Evaluation

For expression evaluation, Oracle can automatically perform the same conversions as for assignments. An expression is converted to a type based on its context. For example, operands to arithmetic operators are converted to NUMBER and operands to string functions are converted to VARCHAR2.

Some common types of expressions follow:

		comm + '500'

		bonus > sal / '10'

		MOD (counter, '2')

		WHERE hiredate = TO_DATE('1993-01-01','yyyy-mm-dd')

		WHERE rowid = '00000DC5.000C.0001'

In general, Oracle uses the rule for expression evaluation when a datatype conversion is needed in places not covered by the rule for assignment conversions.

In assignments of the form

variable := expression

Oracle first evaluates expression using the conversions covered by Rule 2; expression can be as simple or complex as desired. If it succeeds, the evaluation of expression results in a single value and datatype. Then, Oracle tries to assign this value to the assignment's target using Rule 1.

CHAR to NUMBER conversions only succeed if the character string represents a valid number. CHAR to DATE conversions only succeed if the character string satisfies the session default format, NLS_DATE_FORMAT.

Data Conversion for Trusted Oracle

In Trusted Oracle, labels are stored internally as compact binary structures. Trusted Oracle provides two functions that allow you to convert a label from external (character or human readable) to internal (binary) format, and vice versa. These functions are described in the following sections.

The TO_CHAR Function

You can use the TO_CHAR function to convert a label from its binary to character representation, as follows:

TO_CHAR(label [,format])

where label is the binary representation of a label and format is a valid label format (see the following section, "Formatting Labels" [*], for more details about this parameter).

This function is useful because it allows you to retrieve a label in any supported format that suits your needs.

The TO_LABEL Function

You can use the TO_LABEL function to convert a label from a character string to its internal, binary representation.

To convert a label from character string to binary representation, enter

TO_LABEL(string [,format])

where string is the character representation of a label and format is a valid label format (see the following section, "Formatting Labels" for more details about this parameter).

You must use the TO_LABEL function when converting a label from one external format to another (see "Formatting Labels" [*] for examples of how to use this function).

Formatting Labels

The default format in which Trusted Oracle returns a label may not necessarily be the most appropriate format for a given display, report, or application. To accommodate different formatting needs, Trusted Oracle provides the ability to

The following sections describe how to use these features in more detail.

The TO_CHAR and TO_LABEL Functions

Depending upon your operating system, a label can have several components: sensitivity, integrity, information, and installation defined. Both the sensitivity and integrity components consists of one classification and zero or more categories.

Using the format parameter of the TO_CHAR and TO_LABEL functions, you can format the classification and categories of each of these components in one of several ways: numeric, short, or long. You can also specify whether you want the numeric, short, or long representation of a label to be displayed in the format used by the operating system on which Trusted Oracle is running, and you can specify whether you want a label displayed in full operating system format. Note that not all operating systems support separate long and short formats.

Setting the DBMS Label Format for Your Session

If you want to display labels in a format other than the system-wide default, you can alter the default format for your session with the SET MLS_LABEL_FORMAT parameter of the ALTER SESSION command. You need no special privileges to execute this command.

For example, assume that the system-wide default label format (set in the parameter file) is SEN; this displays the full sensitivity label in short format. However, you want the full sensitivity label to be displayed in numeric format just for your session or a portion of your session. To change the label format for your DBMS session to S, enter

ALTER SESSION SET MLS_LABEL_FORMAT = 's'

All labels will then be displayed in this format for the duration of your session, or until you issue another ALTER SESSION SET MLS_LABEL_FORMAT command.

Note that altering the MLS_LABEL_FORMAT parameter not only changes the format in which labels are displayed for your session; it also changes the format in which you must enter labels. For example, after altering the MLS_LABEL_FORMAT parameter as described in the above example, you must enter labels in numeric format for the rest of your session.

Changing the MLS_LABEL_FORMAT parameter does not affect constraint enforcement.

For more information about different label formats, see your Trusted Oracle7 Server Administrator's Guide.

Comparing Labels

In addition to formatting labels, you may need to perform comparison operations on labels. Trusted Oracle lets you use standard comparison operators to

You can use the standard Oracle comparison operators to compare labels in Trusted Oracle.

Guidelines for Defining Labels

Labels and label formats vary from operating system to operating system. While your installation currently may not use more than one type of operating system, you should be aware of potential differences between labels and label formats in a distributed database configuration and for future porting considerations, particularly when writing applications that should be easily ported to other operating systems.

To promote portability between labels in databases from one operating system to another, you should consider the following guidelines when defining labels in your database.

Embedded Spaces and Punctuation

Be cautious when using embedded spaces and punctuation in labels, as not all operating systems can interpret them.

For example, a label of SENSITIVE:ALPHA/BETA may be correctly interpreted on one operating system, but not correctly interpreted by other operating systems.

Case Sensitivity

Use care when specifying labels using upper and lowercase, as some operating systems are case sensitive and some are not. You should avoid defining labels that are distinguished only by case (for those operating systems that are not case sensitive); in addition, you should avoid comparing labels for an exact string match, including case (for those operating systems that are).

For example, one operating system may interpret Truly_Sensitive and TRULY_SENSITIVE as different labels; however, an operating system that is not case sensitive would interpret them as identical.

Label Components

Because not all operating systems support the integrity, information, and installation-defined portions of a label, you should use care when defining these in Trusted Oracle or using them in applications.

Note that if you request a label component that is not supported by your operating system, Trusted Oracle will return a null value for that component.

Numeric Format

You should avoid using '0' to represent either a classification or a category in numeric format, as some operating systems begin numbering with '0' and some with '1'.

Number of Classifications and Categories

Be aware that the number of classifications and categories supported by operating systems differs. While most operating systems support between 16 and 256 hierarchical classifications, a few may fall above or below this range.




Go to previous file in sequence Go to next file in sequence
Prev Next
Oracle
Copyright © 1996 Oracle Corporation.
All Rights Reserved.
Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Contents for this book
Contents
Go to Index
Index