Oracle7 Server SQL Reference Manual 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

Elements of Oracle7 SQL

This chapter contains reference information on the basic elements of Oracle7 SQL. Before using any of the commands described[*], "Commands," you should familiarize yourself with the concepts covered in this chapter:

Database Objects

Schema Objects

A schema is a collection of logical structures of data, or schema objects. A schema is owned by a database user and has the same name as that user. Each user owns a single schema. Schema objects can be created and manipulated with SQL and include the following types of objects.

* These objects are available only if PL/SQL is installed.

+ These objects are available only if the distributed option is installed.

Non-Schema Objects

Other types of objects are also stored in the database and can be created and manipulated with SQL, but are not contained in a schema:

Most of these objects occupy space in the database. In this manual, each type of object is briefly defined[*], "Commands" in the section describing the command that creates the database object. These commands begin with the keyword CREATE. For example, for the definition of a cluster, see the CREATE CLUSTER command [*]. For an overview of database objects, see Oracle7 Server Concepts.

You must provide names for most types of objects when you create them. These names must follow the rules listed in the following sections.

Parts of Objects

Some objects are made up of parts that you must also name, such as:

Object Names and Qualifiers

This section tells provides:

Object Naming Rules

The following rules apply when naming objects:

Note: You cannot use special characters from European or Asian character sets in a database name, global database name, or database link names. For example, the umlaut is not allowed.

Reserved words
ACCESS ADD ALL* ALTER AND* ANY* AS* ASC* AUDIT BETWEEN* BY* CHAR* CHECK* CLUSTER COLUMN COMMENT COMPRESS CONNECT CREATE* CURRENT* DATE DECIMAL DEFAULT* DELETE* DESC* DISTINCT* DROP ELSE EXCLUSIVE EXISTS* FILE FLOAT* FOR* FROM* GRANT* GROUP* HAVING* IDENTIFIED IMMEDIATE IN* INCREMENT INDEX INITIAL INSERT* INTEGER* INTERSECT INTO* IS* LEVEL LIKE* LOCK LONG MAXEXTENTS MINUS MODE MODIFY NOAUDIT NOCOMPRESS NOT* NOWAIT NULL* NUMBER OF* OFFLINE ON* ONLINE OPTION* OR* ORDER* PCTFREE PRIOR PRIVILEGES* PUBLIC* RAW RENAME RESOURCE REVOKE ROW ROWID ROWLABEL ROWNUM ROWS SELECT* SESSION SET* SHARE SIZE SMALLINT* START SUCCESSFUL SYNONYM SYSDATE TABLE* THEN TO* TRIGGER UID UNION* UNIQUE* UPDATE* USER* VALIDATE VALUES* VARCHAR VARCHAR2 VIEW* WHENEVER WHERE* WITH*

Keywords
ADMIN AFTER ALLOCATE ANALYZE ARCHIVE ARCHIVELOG AUTHORIZATION* AVG* BACKUP BEGIN* BECOME BEFORE BLOCK BODY CACHE CANCEL CASCADE CHANGE CHARACTER* CHECKPOINT CLOSE* COBOL* COMMIT* COMPILE CONSTRAINT CONSTRAINTS CONTENTS CONTINUE* CONTROLFILE COUNT* CURSOR* CYCLE DATABASE DATAFILE DBA DEC* DECLARE* DISABLE DISMOUNT DOUBLE* DUMP EACH ENABLE END* ESCAPE* EVENTS EXCEPT EXCEPTIONS EXEC* EXPLAIN EXECUTE EXTENT EXTERNALLY FETCH* FLUSH FREELIST FREELISTS FORCE FOREIGN* FORTRAN* FOUND* FUNCTION GO* GOTO* GROUPS INCLUDING INDICATOR* INITRANS INSTANCE INT* KEY* LANGUAGE* LAYER LINK LISTS LOGFILE MANAGE MANUAL MAX* MAXDATAFILES MAXINISTANCES MAXLOGFILES MAXLOGHISTORY MAXLOGMEMBERS MAXTRANS MAXVALUE MIN* MINEXTENTS MINVALUE MODULE* MOUNT NEXT NEW NOARCHIVELOG NOCACHE NOCYCLE NOMAXVALUE NOMINVALUE NONE NOORDER NORESETLOGS NORMAL NOSORT NUMERIC* OFF OLD ONLY OPTIMAL OPEN* OWN PACKAGE PARALLEL PASCAL* PCTINCREASE PCTUSED PLAN PLI* PRECISION* PRIMARY* PRIVATE PROCEDURE* PROFILE QUOTA READ REAL* RECOVER REFERENCES* REFERENCING RESETLOGS RESTRICTED REUSE ROLE ROLES ROLLBACK* SAVEPOINT SCHEMA* SCN SECTION* SEGMENT SEQUENCE SHARED SNAPSHOT SOME* SORT SQLCODE* SQLERROR* STATEMENT_ID STATISTICS STOP STORAGE SUM* SWITCH SYSTEM TABLES TABLESPACE TEMPORARY THREAD TIME TRACING TRANSACTION TRIGGERS TRUNCATE UNDER UNLIMITED UNTIL USE USING WHEN WRITE WORK*

Figure 2 - 1. Namespaces For Schema Objects

Figure 2 - 2. Namespaces For Other Objects

	emp
	"emp"
	"Emp"
	"EMP "

	emp
	EMP
	"EMP"

Examples

The following are valid examples of names:

ename
horse
scott.hiredate
"EVEN THIS & THAT!"
a_very_long_and_valid_name

Although column aliases, table aliases, usernames, or passwords are not objects or parts of objects, they must also follow these naming rules with these exceptions

Object Naming Guidelines

There are several helpful guidelines for naming objects and their parts:

When naming objects, balance the objective of keeping names short and easy to use with the objective of making name as long and descriptive as possible. When in doubt, choose the more descriptive name because the objects in the database may be used by many people over a period of time. Your counterpart ten years from now may have difficulty understanding a database with names like PMDD instead of PAYMENT_DUE_DATE.

Using consistent naming rules helps users understand the part that each table plays in your application. One such rule might be to begin the names of all tables belonging to the FINANCE application with FIN_.

Use the same names to describe the same things across tables. For example, the department number columns of the EMP and DEPT tables are both named DEPTNO.

Referring to Objects and Parts

This section tells you how to refer to objects and their parts in the context of a SQL statement. This section shows you:

This syntax diagram shows the general syntax for referring to an object or a part:

where:

object

is the name of the object.

schema

is the schema containing the object. The schema qualifier allows you to refer to an object in a schema other than your own. Note that you must be granted privileges to refer to objects in other schemas. If you omit this qualifier, Oracle7 assumes that you are referring to an object in your own schema.

Only schema objects can be qualified with schema. Schema objects are shown in Figure 2 - 1 [*]. Other objects, shown in Figure 2 - 2 [*], cannot be qualified with schema because they are not schema objects, except for public synonyms which can optionally be qualified with "PUBLIC" (quotation marks required).

part

is a part of the object. This identifier allows you to refer to a part of a schema object, such as a column of a table. Note that not all types of objects have parts.

dblink

applies only to those using Oracle7 with the distributed option. This is the name of the database containing the object. The dblink qualifier allows you to refer to an object in a database other than your local database. If you omit this qualifier, Oracle7 assumes that you are referring to an object in your local database. Note that not all SQL statements allow you to access objects on remote databases.

You can include spaces around the periods separating the components of the reference to the object, but it is conventional to omit them.

How Oracle7 Resolves Object References

When you refer to an object in a SQL statement, Oracle7 considers the context of the SQL statement and locates the object in the appropriate namespace. If the named object cannot be found in the appropriate namespace, Oracle7 returns an error message. After locating the object, Oracle7 performs the statement's operation on the object.

The following example illustrates how Oracle7 resolves references to objects within SQL statements. Consider this statement that adds a row of data to a table identified by the name DEPT:

INSERT INTO dept 
	VALUES (50, 'SUPPORT', 'PARIS')

Based on the context of the statement, Oracle7 determines that DEPT can be:

Oracle7 always attempts to resolve an object reference within the namespaces in your own schema before considering namespaces outside your schema. In this example, Oracle7 attempts to resolve the name DEPT in these ways:

Referring to Objects in Other Schemas

To refer to objects in schemas other than your own, prefix the object name with the schema name:

schema.object

For example, this statement drops the EMP table in the schema SCOTT:

DROP TABLE scott.emp

Referring to Objects in Remote Databases

To refer to objects in databases other than your local database, follow the object name with the name of the database link to that database. A database link is a schema object that causes Oracle7 to connect to a remote database to access an object there. This section tells you:

Creating Database Links

You can create a database link with the CREATE DATABASE LINK command described[*], "Commands," of this manual. The command allows you to specify this information about the database link:

Oracle7 stores this information in the data dictionary.

Names When you create a database link, you must specify its name. The name of a database link can be as long as 128 bytes and can contain periods (.) and the special character @. In these ways, database link names are different from names of other types of objects.

The name that you give to a database link must correspond to the name of the database to which the database link refers and the location of that database in the hierarchy of database names. The following syntax diagram shows the form of the name of a database link:

where:

database

specifies the name of the remote database to which the database link connects. The name of the remote database is specified by its initialization parameter DB_NAME.

domain

specifies the domain of the remote database to which the database link connects. If you omit the domains from the name of a database link, Oracle7 expands the name by qualifying database with the domain of your local database before storing it in the data dictionary. The domain of a database is specified by the value of its initialization parameter DB_DOMAIN.

connection_qualifier

allows you to further qualify a database link. Using connection qualifiers, you can create multiple database links to the same database. For example, you can use connection qualifiers to create multiple database links to different instances of the Oracle7 Parallel Server that access the same database.

Username and Password The username and password are used by Oracle7 to connect to the remote database. The username and password for a database link are optional.

Database String The database string is the specification used by SQL*Net to access the remote database. For information on writing database connect strings, see the SQL*Net documentation for your specific network protocol. The database string for a database link is optional.

Referring to Database Links

Database links are available only to those using Oracle7 with the distributed option. When you issue a SQL statement that contains a database link, you can specify the database link name in one of these forms:

complete

is the complete database link name as stored in the data dictionary including the database, domain, and optional connection_qualifier components.

partial

contains the database and optional connection_qualifier components, but not the domain component.

Oracle7 performs these tasks before connecting to the remote database:

    1. 2.1 Oracle7 always determines the username and password from the first matching database link (either private or public). If the first matching database link has an associated username and password, Oracle7 uses it. If it does not have an associated username and password, Oracle7 uses your current username and password.
    1. 2.2 If the first matching database link has an associated database string, Oracle7 uses it. If not, Oracle7 searches for the next matching (public) database link. If there is no matching database link, or if no matching link has an associated database string, Oracle7 returns an error message.

You can enable and disable Oracle7 resolution of names for remote objects using the initialization parameter GLOBAL_NAMES and the GLOBAL_NAMES parameter of the ALTER SYSTEM and ALTER SESSION commands.

You cannot use the USERENV('TERMINAL') variable in the WHERE clauses of INSERT, UPDATE, or DELETE statements that access remote objects, although you can do so in SELECT statements.

For more information on remote name resolution, see the "Database Administration" chapter of Oracle7 Server Distributed Systems, Volume I.

Literals

The terms literal and constant value are synonymous in this manual and refer to a fixed data value. For example, 'JACK', 'BLUE ISLAND', and '101' are all character literals. 5001 is a numeric literal. Note that character literals are enclosed in single quotation marks. The quotation marks allow Oracle7 to distinguish them from schema object names.

Many SQL statements and functions require you to specify character and numeric literal values. You can also specify literals as part of expressions and conditions. You can specify character literals with the 'text' notation and numeric literals with the integer or number notation, depending on the context of the literal. The syntactic forms of these notations appear in the following sections.

Text

Purpose

To specify a text or character literal. You must use this notation to specify values whenever 'text' or char appear in expressions, conditions, SQL functions, and SQL commands in other parts of this manual.

Syntax

Keywords and Parameters

c

is any member of the user's character set, except a single quotation mark (').

''

are two single quotation marks. Because a single quotation mark is used to begin and end text literals, you must use two single quotation marks to represent one single quotation mark within a literal.

Usage Notes

A text literal must be enclosed in single quotation marks. This manual uses the terms text literal and character literal interchangeably.

Text literals have properties of both the CHAR and VARCHAR2 datatypes:

Examples

'Hello'

'ORACLE.dbs'
'Jackie''s raincoat'
'09-MAR-92'

Related Topics

The syntax description of expr [*].

Integer

Purpose

To specify a positive integer. You must use this notation to specify values whenever integer appears in expressions, conditions, SQL functions, and SQL commands described in other parts of this manual.

Syntax

Keywords and Parameters

digit is one of 0, 1, 2, 3, 4, 5, 6, 7, 8, 9.

Usage Notes

An integer can store a maximum of 38 digits of precision.

Examples

7

255

Related Topics

The syntax description of expr [*].

Number

Purpose

To specify an integer or a real number. You must use this notation to specify values whenever number appears in expressions, conditions, SQL functions, and SQL commands in other parts of this manual.

Syntax

Keywords and Parameters

+, - indicates a positive or negative value. If you omit the sign, a positive value is the default.

digit

is one of 0, 1, 2, 3, 4, 5, 6, 7, 8 or 9.

e, E

indicates that the number is specified in scientific notation. The digits after the E specify the exponent. The exponent can range between -130 and 125.

Usage Notes

A number can store a maximum of 38 digits of precision.

If you have established a decimal character other than a period (.) with the initialization parameter NLS_NUMERIC_CHARACTERS, you must specify numeric literals with 'text' notation. In such cases, Oracle7 automatically converts the text literal to a numeric value.

For more information on this parameter, see Oracle7 Server Reference.

Examples

25

+6.34
0.5
25e-03
-1

Related Topics

The syntax description of expr [*].

Datatypes

Each literal or column value manipulated by Oracle7 has a datatype. A value's datatype associates a fixed set of properties with the value. These properties cause Oracle7 to treat values of one datatype differently from values of another. For example, you can add values of NUMBER datatype, but not values of RAW datatype.

When you create a table or cluster, you must specify an internal datatype for each of its columns. When you create a procedure or stored function, you must specify an internal datatype for each of its arguments. These datatypes define the domain of values that each column can contain or each argument can have. For example, DATE columns cannot accept the value February 29 (except for a leap year) or the values 2 or 'SHOE'. Each value subsequently placed in a column assumes the column's datatype. For example, if you insert '01-JAN-92' into a DATE column, Oracle7 treats the '01-JAN-92' character string as a DATE value after verifying that it translates to a valid date.

Table 2 - 1 summarizes Oracle7 internal datatypes. The rest of this section describes these datatypes in detail.

Note: The Oracle precompilers recognize other datatypes in embedded SQL programs. These datatypes are called external datatypes and are associated with host variables. Do not confuse the internal datatypes with external datatypes. For information on external datatypes, including how Oracle7 converts between internal and external datatypes, see Programmer's Guide to the Oracle Precompilers.

Code Internal Datatype Description
1 VARCHAR2(size) Variable length character string having maximum length size bytes. Maximum size is 2000, and minimum is 1. You must specify size for a VARCHAR2
2 NUMBER(p,s) Number having precision p and scale s. The precision p can range from 1 to 38. The scale s can range from -84 to 127.
8 LONG Character data of variable length up to 2 gigabytes, or 231 -1 bytes.
12 DATE Valid date range from January 1, 4712 BC to December 31, 4712 AD.
23 RAW(size) Raw binary data of length size bytes. Maximum size is 255 bytes. You must specify size for a RAW value.
24 LONG RAW Raw binary data of variable length up to 2 gigabytes.
69 ROWID (see note below) Hexadecimal string representing the unique address of a row in its table. This datatype is primarily for values returned by the ROWID pseudocolumn.
96 CHAR(size) Fixed length character data of length size bytes. Maximum size is 255. Default and minimum size is 1 byte.
106 MLSLABEL Binary format of an operating system label. This datatype is used with Trusted Oracle7.
Table 2 - 1. Internal Datatype Summary

The codes listed for the datatypes are used internally by Oracle7. The datatype code of a column is returned when you use the DUMP function.

Note: The DESCRIBE embedded SQL command and the ODESCR call of the Oracle Call Interfaces (OCIs) returns a code of 11 for the ROWID datatype.

Character Datatypes

Character datatypes are used to manipulate words and free-form text. These datatypes are used to store character (alphanumeric) data in the database character set. They are less restrictive than other datatypes and consequently have fewer properties. For example, character columns can store all alphanumeric values, but NUMBER columns can only store numeric values.

Character data is stored in strings with byte values corresponding to the character set, such as 7-bit ASCII or EBCDIC Code Page 500, specified when the database was created. Oracle7 supports both single-byte and multi-byte character sets.

These datatypes are used for character data:

The character datatypes in Oracle7 are different from those in Oracle Version 6. For a summary of the differences and compatibility issues, see Appendix C "Operating System -Specific Dependendies" of this manual.

CHAR Datatype

The CHAR datatype specifies a fixed length character string. When you create a table with a CHAR column, you can supply the column length in bytes. Oracle7 subsequently ensures that all values stored in that column have this length. If you insert a value that is shorter than the column length, Oracle7 blank-pads the value to column length. If you try to insert a value that is too long for the column, Oracle7 returns an error.

The default for a CHAR column is 1 character and the maximum allowed is 255 characters. A zero-length string can be inserted into a CHAR column, but the column is blank-padded to 1 character when used in comparisons. For information on comparison semantics, see the section "Datatype Comparison Rules" [*].

VARCHAR2 Datatype

The VARCHAR2 datatype specifies a variable length character string. When you create a VARCHAR2 column, you can supply the maximum number of bytes of data that it can hold. Oracle7 subsequently stores each value in the column exactly as you specify it, provided it does not exceed the column's maximum length. This maximum must be at least 1 byte, although the actual length of the string stored is permitted to be zero. If you try to insert a value that exceeds the specified length, Oracle7 returns an error.

You must specify a maximum length for a VARCHAR2 column. The maximum length of VARCHAR2 data is 2000 bytes. Oracle7 compares VARCHAR2 values using non-padded comparison semantics. For information on comparison semantics, see the section "Datatype Comparison Rules" [*].

VARCHAR Datatype

The VARCHAR datatype is currently synonymous with the VARCHAR2 datatype. It is recommended that you use VARCHAR2 rather than VARCHAR. In a future version of Oracle7, VARCHAR might be a separate datatype used for variable length character strings compared with different comparison semantics.

NUMBER Datatype

The NUMBER datatype is used to store zero, positive and negative fixed and floating point numbers with magnitudes between 1.0 x 10-130 and 9.9...9 x 10125 (38 9s followed by 88 0s) with 38 digits of precision. If you specify an arithmetic expression whose value has a magnitude greater than or equal to 1.0 x 10126, Oracle7 returns an error.

You can specify a fixed point number using the following form:

NUMBER(p,s)

where:

p

is the precision, or the total number of digits. Oracle7 guarantees the portability of numbers with precision ranging from 1 to 38.

s

is the scale, or the number of digits to the right of the decimal point. The scale can range from -84 to 127.

You specify an integer using the following form:

NUMBER(p)

is a fixed point number with precision p and scale 0. (Equivalent to NUMBER(p,0).)

You specify a floating point number using the following form:

NUMBER

is a floating point number with precision 38. Note that a scale value is not applicable for floating point numbers.

Scale and Precision

Specify the scale and precision of a fixed point number column for extra integrity checking on input. Specifying scale and precision does not force all values to a fixed length. If a value exceeds the precision, Oracle7 returns an error. If a value exceeds the scale, Oracle7 rounds it.

The following examples show how Oracle7 stores data using different precisions and scales.

Actual Data Specified As Stored As
7456123.89 NUMBER 7456123.89
7456123.89 NUMBER(9) 7456124
7456123.89 NUMBER(9,2) 7456123.89
7456123.89 NUMBER(9,1) 7456123.9
7456123.8 NUMBER(6) exceeds precision
7456123.8 NUMBER(15,1) 7456123.8
7456123.89 NUMBER(7,-2) 7456100
7456123.89 NUMBER(-7,2) exceeds precision

Negative Scale

If the scale is negative, the actual data is rounded to the specified number of places to the left of the decimal point. For example, a specification of (10,-2) means to round to hundreds.

Scale Greater than Precision

You can specify a scale that is greater than precision, although it is uncommon. In this case, the precision specifies the maximum number of digits to the right of the decimal point. As with all number datatypes, if the value exceeds the precision, Oracle7 returns an error message. If the value exceeds the scale, Oracle7 rounds the value. For example, a column defined as NUMBER(4,5) requires a zero for the first digit after the decimal point and rounds all values past the fifth digit after the decimal point. The following examples show the effects of a scale greater than precision:

Actual Data Specified As Stored As
.01234 NUMBER(4,5) .01234
.00012 NUMBER(4,5) .00012
.000127 NUMBER(4,5) .00013
.0000012 NUMBER(2,7) .0000012
.00000123 NUMBER(2,7) .0000012

Floating Point Numbers

Oracle7 also allows you to specify floating point numbers. A floating point value either can have a decimal point anywhere from the first to the last digit or can omit the decimal point altogether. A scale value is not applicable to floating point numbers because there is no restriction on the number of digits that can appear after the decimal point.

You can specify floating point numbers with the appropriate forms of the NUMBER datatype discussed in the section "NUMBER Datatype" [*]. Oracle7 also supports the ANSI datatype FLOAT. You can specify this datatype using one of these syntactic forms:

FLOAT

specifies a floating point number with decimal precision 38, or a binary precision of 126.

FLOAT(b)

specifies a floating point number with binary precision b. The precision b can range from 1 to 126.

To convert from binary to decimal precision, multiply b by 0.30103. To convert from decimal to binary precision, multiply the decimal precision by 3.32193. The maximum of 126 digits of binary precision is roughly equivalent to 38 digits of decimal precision.

LONG Datatype

LONG columns store variable length character strings containing up to 2 gigabytes, or 231-1 bytes. LONG columns have many of the characteristics of VARCHAR2 columns. You can use LONG columns to store long text strings. Oracle7 uses LONG columns in the data dictionary to store the text of view definitions. The length of LONG values may also be limited by the memory available on your computer.

You can reference LONG columns in SQL statements in these places:

The use of LONG values are subject to some restrictions:

Also, LONG columns cannot appear in certain SQL statements:

Also, LONG columns cannot appear in certain parts of SQL statements:

Triggers can use the LONG datatype in the following manner:

You can use the Oracle Call Interfaces to retrieve a portion of a LONG value from the database. See Programmer's Guide to the Oracle Call Interface.

DATE Datatype

The DATE datatype is used to store date and time information. Although date and time information can be represented in both CHAR and NUMBER datatypes, the DATE datatype has special associated properties.

For each DATE value the following information is stored:

To specify a date value, you must convert a character or numeric value to a data value with the TO_DATE function. Oracle7 automatically converts character values that are in the default date format into date values when they are used in date expressions. The default date format is specified by the initialization parameter NLS_DATE_FORMAT and is a string such as 'DD-MON-YY'. This example date format includes a two-digit number for the day of the month, an abbreviation of the month name, and the last two digits of the year.

If you specify a date value without a time component, the default time is 12:00:00a.m. (midnight). If you specify a date value without a date, the default date is the first day of the current month.

The date function SYSDATE returns the current date and time. For information on the SYSDATE and TO_DATE functions and the default date format, see Chapter 3 "Operators, Functions, Expressions, Conditions" of this manual.

Date Arithmetic

You can add and subtract number constants as well as other dates from dates. Oracle7 interprets number constants in arithmetic date expressions as numbers of days. For example, SYSDATE + 1 is tomorrow. SYSDATE - 7 is one week ago. SYSDATE + (10/1440) is ten minutes from now. Subtracting the HIREDATE column of the EMP table from SYSDATE returns the number of days since each employee was hired. You cannot multiply or divide DATE values.

Oracle7 provides functions for many of the common date operations. For example, the ADD_MONTHS function allows you to add or subtract months from a date. The MONTHS_BETWEEN function returns the number of months between two dates. The fractional portion of the result represents that portion of a 31-day month. For more information on date functions, see the section "Date Functions" [*].

Because each date contains a time component, most results of date operations include a fraction. This fraction means a portion of one day. For example, 1.5 days is 36 hours.

Using Julian Dates

A Julian date is the number of days since Jan 1, 4712 BC. Julian dates allow continuous dating from a common reference. You can use the date format model "J" with date functions TO_DATE and TO_CHAR to convert between Oracle7 DATE values and their Julian equivalents.

Example

This statement returns the Julian equivalent of January 1, 1992:

SELECT TO_CHAR(TO_DATE('01-01-1992', 'MM-DD-YYYY'),'J')
    FROM DUAL
TO_CHAR(TO_DATE('01-01-1992','MM-DD-YYYY),'J')
----------------------------------------------
2448623

RAW and LONG RAW Datatypes

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

RAW is a variable-length datatype like the VARCHAR2 character datatype, except that SQL*Net (which connects user sessions to the instance) and the Import and Export utilities do not perform character conversion when transmitting RAW or LONG RAW data. In contrast, SQL*Net and Import/Export automatically convert CHAR, VARCHAR2, and LONG data between the database character set to the user session character set (set by the NLS_LANGUAGE parameter of the ALTER SESSION command), if the two character sets are different.

When Oracle automatically converts RAW or LONG RAW data to and from CHAR data, the binary data is represented in hexadecimal form with one hexadecimal character representing 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.

ROWID Datatype

Each row in the database has an address. You can examine a row's address by querying the pseudocolumn ROWID. Values of this pseudocolumn are hexadecimal strings representing the address of each row. These string have the datatype ROWID. For more information on the ROWID pseudocolumn, see the section "Pseudocolumns" [*]. You can also create tables and clusters that contain actual columns having the ROWID datatype. Oracle7 does not guarantee that the values of such columns are valid ROWIDs.

Character values representing ROWIDs:

block.row.file

where:

block

is a hexadecimal string identifying the data block of the data file containing the row. The length of this string may vary depending on your operating system.

row

is a four-digit hexadecimal string identifying the row in the data block. The first row in the block has the number 0.

file

is a hexadecimal string identifying the database file containing the row. The first data file has the number 1. The length of this string may vary depending on your operating system.

Example

Consider this ROWID value:

0000000F.0000.0002

The row corresponding to this ROWID is the first row (0000) in the fifteenth data block (0000000F) of the second data file (0002).

MLSLABEL Datatype

The MLSLABEL datatype is used to store the binary format a label used on a secure operating system. Labels are used by Trusted Oracle7 to mediate access to information. You can also define columns with this datatype if you are using the standard Oracle7 Server. For more information on Trusted Oracle7, including this datatype and labels, see Trusted Oracle7 Server Administrator's Guide.

ANSI, DB2, and SQL/DS Datatypes

SQL commands that create tables and clusters can also both ANSI datatypes and datatypes from IBM's products SQL/DS and DB2. Oracle7 creates columns with Oracle7 datatypes based on the conversions defined in Table 2 - 2 and Table 2 - 3.

ANSI SQL Datatype Oracle7 Datatype
CHARACTER(n) CHAR(n) CHAR(n)
CHARACTER VARYING(n) CHAR VARYING(n) VARCHAR(n)
NUMERIC(p,s) DECIMAL(p,s) NUMBER(p,s)
INTEGER INT SMALLINT NUMBER(38)
FLOAT(b) 2 DOUBLE PRECISION 3 REAL 4 NUMBER
Table 2 - 2. ANSI Datatypes Converted to Oracle7 Datatypes
SQL/DS or DB2 Datatype Oracle7 Datatype
CHARACTER(n) CHAR(n)
VARCHAR(n) VARCHAR(n)
LONG VARCHAR(n) LONG
DECIMAL(p,s) 1 NUMBER(p,s)
INTEGER SMALLINT NUMBER(38)
FLOAT(b) 2 NUMBER
Table 2 - 3. SQL/DS and DB2 Datatypes Converted to Oracle7 Datatypes
1 The NUMERIC, DECIMAL, and DEC datatypes can specify only fixed point numbers. For these datatypes, s defaults to 0. 2 The FLOAT datatype is a floating point number with a binary precision b. This default precision for this datatype is 126 binary, or 38 decimal. 3 The DOUBLE PRECISION datatype is a floating point number with binary precision 126. 4 The REAL datatype is a floating point number with a binary precision of 63, or 18 decimal.

Do not define columns with these SQL/DS and DB2 datatypes because they have no corresponding Oracle7 datatype:

Note that data of type TIME and TIMESTAMP can also be expressed as Oracle7 DATE data.

Datatype Comparison Rules

This section describes how Oracle7 compares values of each datatype.

Number Values

A larger value is considered greater than a smaller one. All negative numbers are less than zero and all positive numbers. Thus, -1 is less than 100; -100 is less than -1.

Date Values

A later date is considered greater than an earlier one. For example, the date equivalent of '29-MAR-1991' is less than that of '05-JAN-1992' and '05-JAN-1992 1:35pm' is greater than '05-JAN-1992 10:09am'.

Character String Values

Character values are compared using one of these comparison rules:

The following sections explain these comparison semantics. The results of comparing two character values using different comparison semantics may be different. Table 2 - 4 shows the results of comparing five pairs of character values using each comparison semantic. The last comparison in the table illustrates the differences between the blank-padded and non-padded comparison semantics.

The results of blank-padded and non-padded comparisons is shown in Table 2 - 4. Usually, the results of blank-padded and non-padded comparisons are the same. However, note the exception highlighted in bold in Table 2 - 4 where blanks are considered less than any character, which is true in most character sets.

Blank-Padded Non-Padded
'ab' > 'aa' 'ab' > 'aa'
'ab' > 'a?' 'ab' > 'a?'
'ab' > 'a' 'ab' > 'a'
'ab' = 'ab' 'ab' = 'ab'
'a?' = 'a' 'a?' > 'a'
Table 2 - 4. Results of Comparisons with Blank-Padded and Non-Padded Comparison Semantics
Blank-Padded Comparison Semantics If the two values have different lengths, Oracle7 first adds blanks to the end of the shorter one so their lengths are equal. Oracle7 then compares the values character by character up to the first character that differs. The value with the greater character in the first differing position is considered greater. If two values have no differing characters, then they are considered equal. This rule means that two values are equal if they differ only in the number of trailing blanks. Oracle7 uses blank-padded comparison semantics only when both values in the comparison are either expressions of datatype CHAR, text literals, or values returned by the USER function.

Non-Padded Comparison Semantics Oracle7 compares two values character by character up to the first character that differs. The value with the greater character in that position is considered greater. If two values of different length are identical up to the end of the shorter one, the longer value is considered greater. If two values of equal length have no differing characters, then the values are considered equal. Oracle7 uses non-padded comparison semantics whenever one or both values in the comparison have the datatype VARCHAR2.

Single Characters

Oracle7 compares single characters according to their numeric values in the database character set. One character is greater than another if it has a greater numeric value than the other in the character set. In Table 2 - 4, blanks are considered less than any character, which is true in most character sets.

These are some common character sets:

Portions of the ASCII and EBCDIC character sets appear in Table 2 - 5 and Table 2 - 6. Note that uppercase and lowercase letters are not equivalent. Also, note that the numeric values for the characters of a character set may not match the linguistic sequence for a particular language.

ASCII Character Set

Table 2 - 5 lists the 7-bit ASCII character set.

Decimal value Symbol Decimal value Symbol
32 blank 59 ;
33 ! 60 <
34 " 61 =
35 # 62 >
36 $ 63 ?
37 % 64 @
38 & 65-90 A-Z
39 ' 91 [
40 ( 92 \
41 ) 93 ]
42 * 94 ^^
43 + 95 _
44 , 96 `
45 - 97-122 a-z
46 . 123 {
47 / 124 |
48-57 0-9 125 }
58 : 126 ~
Table 2 - 5. ASCII Character Set

EBCDIC Character Set

Table 2 - 6 lists a common portion of the EBCDIC character set.

Decimal value Symbol Decimal value Symbol
64 blank 108 %
74 ¢ 109 _
75 . 110 >
76 < 111 ?
77 ( 122 :
78 + 123 #
79 | 124 @
80 & 125 '
90 ! 126 =
91 $ 127 "
92 * 129-137 a-i
93 ) 145-153 j-r
94 ; 162-169 s-z
95 ¬ 193-201 A-I
96 - 209-217 J-R
97 / 226-233 S-Z
Table 2 - 6. EBCDIC Character Set

Data Conversion

Generally an expression cannot contain values of different datatypes. For example, an expression cannot multiply 5 by 10 and then add 'JAMES'. However, Oracle7 supports both implicit and explicit conversion of values from one datatype to another.

Implicit Data Conversion

Oracle7 automatically converts a value from one datatype to another when such a conversion makes sense. Oracle7 performs datatype conversions in these cases:

Example I

The text literal '10' has datatype CHAR. Oracle7 implicitly converts it to the NUMBER datatype if it appears in a numeric expression as in the following statement:

SELECT sal + '10'
    FROM emp

Example II

When a condition compares a character value and a NUMBER value, Oracle7 implicitly converts the character value to a NUMBER value, rather than converting the NUMBER value to a character value. in the following statement, Oracle7 implicitly converts '7936' to 7936:

SELECT ename
    FROM emp 
	WHERE empno = '7936'

If the character value is too short to fit the entire number value, the number value is rounded.

Example III

In the following statement, Oracle7 implicitly converts '12-MAR-1993' to a DATE value using the default date format 'DD-MON-YYYY':

SELECT ename
    FROM emp 
	WHERE hiredate = '12-MAR-1993'

Example IV

In the following statement, Oracle7 implicitly converts the text literal '00002514.0001.0001' to a ROWID value:

SELECT ename
    FROM emp 
	WHERE ROWID = '00002514.0001.0001'

Explicit Data Conversion

You can also explicitly specify datatype conversions using SQL conversion functions. Table 2 - 7 shows SQL functions that explicitly convert a value from one datatype to another.

TO FROM CHAR NUMBER DATE RAW ROWID
CHAR unnecessary TO_NUMBER TO_DATE HEXTORAW CHARTOROWID
NUMBER TO_CHAR unnecessary TO_DATE (number,'J')
DATE TO_CHAR TO_CHAR (date,'J') unnecessary
RAW RAWTOHEX unnecessary
ROWID ROWIDTOCHAR unnecessary
Table 2 - 7. SQL Functions for Datatype Conversion
For information on these functions, see the section "Conversion Functions" [*].

Note: Note that Table 2 - 7 does not show conversions from LONG and LONG RAW values because it is impossible to specify LONG and LONG RAW values in cases in which Oracle7 can perform implicit datatype conversion. For example, LONG and LONG RAW values cannot appear in expressions with functions or operators. For information on the limitations on LONG and LONG RAW datatypes, see the section "LONG Datatype" [*].

Implicit vs. Explicit Data Conversion

It is recommended that you specify explicit conversions rather than rely on implicit or automatic conversions for these reasons:

Nulls

If a column in a row has no value, then column is said to be null, or to contain a null. Nulls can appear in columns of any datatype that are not restricted by NOT NULL or PRIMARY KEY integrity constraints. Use a null when the actual value is not known or when a value would not be meaningful.

Oracle7 currently treats a character value with a length of zero as null. However, this may not continue to be true in future versions of Oracle7.

Do not use null to represent a value of zero, because they are not equivalent. Any arithmetic expression containing a null always evaluates to null. For example, null added to 10 is null. In fact, all operators (except concatenation) return null when given a null operand.

Nulls in SQL Functions

All scalar functions (except NVL and TRANSLATE) return null when given a null argument. The NVL function can be used to return a value when a null occurs. For example, the expression NVL(COMM,0) returns 0 if COMM is null or the value of COMM if it is not null.

Most group functions ignore nulls. For example, consider a query that averages the five values 1000, null, null, null, and 2000. Such a query ignores the nulls and calculates the average to be (1000+2000)/2 = 1500.

Nulls with Comparison Operators

To test for nulls, only use the comparison operators IS NULL and IS NOT NULL. If you use any other operator with nulls and the result depends on the value of the null, the result is UNKNOWN. Because null represents a lack of data, a null cannot be equal or unequal to any value or to another null. However, note that Oracle7 considers two nulls to be equal when evaluating a DECODE expression. For information on the DECODE syntax, see the section "Expr" [*].

Nulls in Conditions

A condition that evaluates to UNKNOWN acts almost like FALSE. For example, a SELECT statement with a condition in the WHERE clause that evaluates to UNKNOWN will return no rows. However, a condition evaluating to UNKNOWN differs from FALSE in that further operations on an UNKNOWN condition evaluation will evaluate to UNKNOWN. Thus, NOT FALSE evaluates to TRUE, but NOT UNKNOWN evaluates to UNKNOWN.

Table 2 - 8 shows examples of various evaluations involving nulls in conditions. If the conditions evaluating to UNKNOWN were used in a WHERE clause of a SELECT statement, then no rows would be returned for that query.

If A is: Condition Evaluates to:
10 a IS NULL FALSE
10 a IS NOT NULL TRUE
NULL a IS NULL TRUE
NULL a IS NOT NULL FALSE
10 a = NULL UNKNOWN
10 a != NULL UNKNOWN
NULL a = NULL UNKNOWN
NULL a != NULL UNKNOWN
NULL a = 10 UNKNOWN
NULL a != 10 UNKNOWN
Table 2 - 8. Conditions Containing Nulls
For the truth tables showing the results of logical expressions containing nulls, see Table 3 - 6, Table 3 - 7, and Table 3 - 8 beginning [*].

Pseudocolumns

A pseudocolumn behaves like a table column, but is not actually stored in the table. You can select from pseudocolumns, but you cannot insert, update, or delete their values. This section describes these pseudocolumns:

CURRVAL and NEXTVAL

A sequence is a schema object that can generate unique sequential values. These values are often used for primary and unique keys. You can refer to sequence values in SQL statements with these pseudocolumns:

CURRVAL

returns the current value of a sequence.

NEXTVAL

increments the sequence and returns the next value.

You must qualify CURRVAL and NEXTVAL with the name of the sequence:

sequence.CURRVAL
sequence.NEXTVAL

To refer to the current or next value of a sequence in the schema of another user, you must have been granted either SELECT object privilege on the sequence or SELECT ANY SEQUENCE system privilege and you must qualify the sequence with the schema containing it:

schema.sequence.CURRVAL
schema.sequence.NEXTVAL

To refer to the value of a sequence on a remote database, you must qualify the sequence with a complete or partial name of a database link:

schema.sequence.CURRVAL@dblink
schema.sequence.NEXTVAL@dblink

For more information on referring to database links, see the section "Referring to Objects in Remote Databases" [*].

If you are using Trusted Oracle7 in DBMS MAC mode, you can only refer to a sequence if your DBMS label dominates the sequence's creation label or if one of these criteria is satisfied:

If you are using Trusted Oracle7 in OS MAC mode, you cannot refer to a sequence with a lower creation label than your DBMS label.

Using Sequence Values

You can use CURRVAL and NEXTVAL in these places:

You cannot use CURRVAL and NEXTVAL in these places:

Also, within a single SQL statement, all referenced LONG columns, updated tables, and locked tables must be located on the same database.

When you create a sequence, you can define its initial value and the increment between its values. The first reference to NEXTVAL returns the sequence's initial value. Subsequent references to NEXTVAL increment the sequence value by the defined increment and return the new value. Any reference to CURRVAL always returns the sequence's current value, which is the value returned by the last reference to NEXTVAL. Note that before you use CURRVAL for a sequence in your session, you must first initialize the sequence with NEXTVAL.

If a statement contains more than one reference to NEXTVAL for a sequence, Oracle7 increments the sequence once and returns the same value for all occurrences of NEXTVAL. If a statement contains references to both CURRVAL and NEXTVAL, Oracle7 increments the sequence and returns the same value for both CURRVAL and NEXTVAL regardless of their order within the statement.

A sequence can be accessed by many users concurrently with no waiting or locking. For information on sequences, see the CREATE SEQUENCE command [*].

Example I

This example selects the current value of the employee sequence:

SELECT empseq.currval 
    FROM DUAL

Example II

This example increments the employee sequence and uses its value for a new employee inserted into the employee table:

INSERT INTO emp
    VALUES (empseq.nextval, 'LEWIS', 'CLERK',
            7902, SYSDATE, 1200, NULL, 20)

Example III

This example adds a new order with the next order number to the master order table and then adds sub-orders with this number to the detail order table:

INSERT INTO master_order(orderno, customer, orderdate)
    VALUES (orderseq.nextval, 'Al''s Auto Shop', SYSDATE)
INSERT INTO detail_order (orderno, part, quantity)
    VALUES (orderseq.currval, 'SPARKPLUG', 4)
INSERT INTO detail_order (orderno, part, quantity)
    VALUES (orderseq.currval, 'FUEL PUMP', 1)
INSERT INTO detail_order (orderno, part, quantity)
    VALUES (orderseq.currval, 'TAILPIPE', 2)

LEVEL

For each row returned by a hierarchical query, the LEVEL pseudocolumn returns 1 for a root node, 2 for a child of a root, and so on. A root node is the highest node within an inverted tree. A child node is any non-root node. A parent node is any node that has children. A leaf node is any node without children. Figure 2 - 3 shows the nodes of an inverted tree with their LEVEL values.

Figure 2 - 3. Hierarchical Tree

To define a hierarchical relationship in a query, you must use the START WITH and CONNECT BY clauses. For more information on using the LEVEL pseudocolumn, see the SELECT command [*].

ROWID

For each row in the database, the ROWID pseudocolumn returns a row's address. ROWID values contain information necessary to locate a row:

Usually, a ROWID value uniquely identifies a row in the database. However, rows in different tables that are stored together in the same cluster can have the same ROWID.

Values of the ROWID pseudocolumn have the datatype ROWID. For information on the ROWID datatype, see the section "ROWID Datatype" [*].

ROWID values have several important uses:

A ROWID does not change during the lifetime of its row. However, you should not use ROWID as a table's primary key. If you delete and reinsert a row with the Import and Export utilities, for example, its ROWID may change. If you delete a row, Oracle7 may reassign its ROWID to a new row inserted later.

Although you can use the ROWID pseudocolumn in the SELECT and WHERE clauses of a query, these pseudocolumn values are not actually stored in the database. You cannot insert, update, or delete a value of the ROWID pseudocolumn.

Example

This statement selects the address of all rows that contain data for employees in department 20:

SELECT ROWID, ename
    FROM emp
    WHERE deptno = 20
ROWID              ENAME
------------------ ----------
0000000F.0000.0002 SMITH
0000000F.0003.0002 JONES
0000000F.0007.0002 SCOTT
0000000F.000A.0002 ADAMS
0000000F.000C.0002 FORD

ROWNUM

For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle7 selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.

You can use ROWNUM to limit the number of rows returned by a query, as in this example:

SELECT *
    FROM emp
    WHERE ROWNUM < 10

You can also use ROWNUM to assign unique values to each row of a table, as in this example:

UPDATE tabx
    SET col1 = ROWNUM

Oracle7 assigns a ROWNUM value to each row as it is retrieved, before rows are sorted for an ORDER BY clause, so an ORDER BY clause normally does not affect the ROWNUM of each row. However, if an ORDER BY clause causes Oracle7 to use an index to access the data, Oracle7 may retrieve the rows in a different order than without the index, so the ROWNUMs may differ than without the ORDER BY clause.

Note that conditions testing for ROWNUM values greater than a positive integer are always false. For example, this query returns no rows:

SELECT * FROM emp
    WHERE ROWNUM > 1

The first row fetched is assigned a ROWNUM of 1 and makes the condition false. The second row to be fetched is now the first row and is also assigned a ROWNUM of 1 and makes the condition false. All rows subsequently fail to satisfy the condition, so no rows are returned.

Comments

You can associate comments with SQL statements and schema objects.

Comments Within SQL Statements

Comments within SQL statements do not affect the statement execution, but they may make your application easier for you to read and maintain. You may want to include a comment in a statement that describes the statement's purpose within your application.

A comment can appear between any keywords, parameters or punctuation marks in a statement. You can include a comment in a statement using either of these means:

A SQL statement can contain multiple comments of both styles. The text of a comment can contain any printable characters in your database character set.

You can use comments in a SQL statement to pass instructions, or hints, to the Oracle7 optimizer. The optimizer uses these hints to choose an execution plan for the statement. For more information on hints, see the "Tuning SQL Statements" chapter of Oracle7 Server Tuning.

Note that you cannot use these styles of comments between SQL statements in a SQL script. You can use the Server Manager or SQL*Plus REMARK command for this purpose. For information on these commands, see Oracle Server Manager User's Guide or SQL*Plus User's Guide and Reference.

Example

These statements contain many comments:

SELECT ename, sal + NVL(comm, 0), job, loc
/* Select all employees whose compensation is
greater than that of Jones.*/
	FROM emp, dept
		 /*The DEPT table is used to get the department name.*/
	WHERE emp.deptno = dept.deptno
	  AND sal + NVL(comm,0) >	 /* Subquery:		   */
   (SELECT sal + NLV(comm,0)
   /* total compensation is sal + comm */
			FROM emp 
			WHERE ename = 'JONES')
SELECT ename,			-- select the name
		sal + NVL(comm, 0)		-- total compensation
		job				-- job
		loc				-- and city containing the office
	FROM emp,			-- of all employees
	     dept
	WHERE emp.deptno = dept.deptno
	  AND sal + NVL(comm, 0) >	-- whose compensation 
						-- is greater than
	    (SELECT sal + NVL(comm,0)	-- the compensation
		FROM emp 
		WHERE ename = 'JONES') 	-- of Jones.

Comments on Schema Objects

You can associate a comment with a table, view, snapshot, or column using the COMMENT command described[*], "Commands" of this manual. Comments associated with schema objects are stored in the data dictionary.


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