| Oracle7 Server Application Developer's Guide |  Library |
 Product |
 Contents |
 Index |
| Oracle7 Server Application Developer's Guide | Library |
Product |
Contents |
Index |
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.
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.
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 |
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.
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.
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).
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.
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.
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.
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.
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.
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
| 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. |
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.
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).
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.
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).
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
SELECT rowlabel, dept.*
FROM deptThis 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.
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
![[*]](jump.gif)
.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.
You can display your DBMS label in any of the following ways:
For example, assume that you are logged in to the database at SENSITIVE. To determine your DBMS label, enter
SELECT USERENV('label') FROM dualwhich returns the following:
ROWLABEL -------------------------------- SENSITIVE
SVRMGR> SHOW LABEL
which returns the following:
Label SENSITIVE
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 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) |
| 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 |
The ANSI/ISO datatypes NUMERIC, DECIMAL, and DEC can specify only fixed-point numbers. For these datatypes, s defaults to 0.
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.
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;
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.
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.
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).
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.
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.
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.
For example, a label of SENSITIVE:ALPHA/BETA may be correctly interpreted on one operating system, but not correctly interpreted by other operating systems.
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.
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.
|
Prev Next |
 Copyright © 1996 Oracle Corporation. All Rights Reserved. |
Library |
Product |
Contents |
Index |