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:
- object names and qualifiers
- referring to objects and parts
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.
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:
- columns in a table or view
- integrity constraints on a table
- packaged procedures, packaged stored functions, and other objects stored within a package
Object Names and Qualifiers
This section tells provides:
- rules for naming objects and object location qualifiers
- guidelines for naming objects and qualifiers
Object Naming Rules
The following rules apply when naming objects:
1. Names must be from 1 to 30 characters long with these exceptions:
- Names of databases are limited to 8 characters.
- Names of database links can be as long as 128 characters.
2. Names cannot contain quotation marks.
3. Names are not case-sensitive
4. A name must begin with an alphabetic character from your database character set unless surrounded by double quotation marks.
5. Names can only contain alphanumeric characters from your database character set and the characters _, $, and #. You are strongly discourage from using $ and #.
If your database character set contains multi-byte characters, It is recommended that each name for a user or a role contain at least one single-byte character.
Names of database links can also contain periods (.) and ampersands (@).
6. A name cannot be an Oracle7 reserved word. The following list contains these reserved words. Words followed by an asterisk (*) are also ANSI reserved words.
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.
Depending on the Oracle product you plan to use to access a database object, names might be further restricted by other product-specific reserved words. For a list of a product's reserved words, see the manual for the specific product, such as PL/SQL User's Guide and Reference.
7. The word DUAL should not be used as a name for an object or part. DUAL is the name of a dummy table frequently accessed by Oracle7 tools such as SQL*Plus and SQL*Forms.
8. The Oracle7 SQL language contains other keywords that have special meanings. Because these keywords are not reserved, you can also use them as names for objects and object parts. However, using them as names may make your SQL statements more difficult for you to read.
The following list contains keywords. Keywords marked with asterisks (*) are also ANSI reserved words. For maximum portability to other implementations of SQL, do not use the following words as object names.
Figure 2 - 1 shows the namespaces for schema objects. Objects in the same namespace are grouped by solid lines. Because tables and views are in the same namespace, a table and a view in the same schema cannot have the same name. However, because tables and indexes are in different namespaces, a table and an index in the same schema can have the same name.
Each schema in the database has its own namespaces for the objects it contains. This means, for example, that two tables in different schemas are in different namespaces and can have the same name.
Figure 2 - 1. Namespaces For Schema Objects
Figure 2 - 2 shows the namespaces for other objects. Because the objects in these namespaces are not contained in schemas, these namespaces span the entire database.
Figure 2 - 2. Namespaces For Other Objects
Columns in the same table or view cannot have the same name. However, columns in different tables or views can have the same name.
Procedures or functions contained in the same package can have the same name, provided that their arguments are not of the same number and datatypes. Creating multiple procedures or functions with the same name in the same package with different arguments is called overloading the procedure or function.
Once you have given an object a name enclosed in double quotation marks, you must use double quotation marks whenever you refer to the object.
You may want to enclose a name in double quotation marks for any of these reasons:
- if you want it to contain spaces
- if you want it to be case-sensitive
- if you want it to begin with a character other than an alphabetic character, such as a numeric character
- if you want it to contain characters other than alphanumeric characters and _, $, and #
- if you want to use a reserved word as a name
By enclosing names in double quotation marks, you can give the following names to different objects in the same namespace:
Note that Oracle7 interprets the following names the same, so they cannot be used for different objects in the same namespace:
If you give a user or password a quoted name, the name cannot contain lowercase letters.
Database link names cannot be quoted.
The following are valid examples of names:
"EVEN THIS & THAT!"
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
- Column aliases and table aliases only exist for the execution of a single SQL statement and are not stored in the database, so rule 9 does not apply to them.
- Passwords do not have namespaces, so rule 9 does not apply to apply to them.
- Do not use quotation marks to make usernames and passwords case-sensitive. For additional rules for naming users and passwords, see the CREATE USER command .
Object Naming Guidelines
There are several helpful guidelines for naming objects and their parts:
- Use full, descriptive, pronounceable names (or well-known abbreviations).
- Use consistent naming rules.
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.
- Use the same name to describe the same entity or attribute across tables.
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:
- the general syntax for referring to an object
- how Oracle7 resolves a reference to an object
- how to refer to objects in schemas other than your own
This syntax diagram shows the general syntax for referring to an object or a part:
- how to refer to objects in remote databases
is the name of the object.
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).
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.
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:
- a table in your own schema
- a view in your own schema
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:
- a private synonym for a table or view
1. Oracle7 first attempts to locate the object in the namespace in your own schema containing tables, views, and private synonyms (see Figure 2 - 1 ). If the object is a private synonym, Oracle7 locates the object for which the synonym stands. This object could be in your own schema, another schema, or on another database. The object could also be another synonym, in which case Oracle7 locates the object for which this synonym stands.
If the object is in the namespace, Oracle7 attempts to perform the statement on the object. In this example, Oracle7 attempts to add the row of data to DEPT. If the object is not of the correct type for the statement, Oracle7 returns an error message. In this example, DEPT must be a table, view, or a private synonym resolving to a table or view. If DEPT is a sequence, Oracle7 returns an error message.
2. If the object is not in the namespace searched in Step 1, Oracle7 searches the namespace containing public synonyms (see Figure 2 - 2 ). If the object is in the namespace, Oracle7 attempts to perform the statement on it. If the object is not of the correct type for the statement, Oracle7 returns an error message. In this example, if DEPT is a public synonym for a sequence, Oracle7 returns an error message.
Referring to Objects in Other Schemas
To refer to objects in schemas other than your own, prefix the object name with the schema name:
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:
- how to create database links
- how to use database links in your SQL statements
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:
- the name of the database link
- the connect string to access the remote database
Oracle7 stores this information in the data dictionary.
- the username and password to connect to the remote database
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:
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.
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.
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:
is the complete database link name as stored in the data dictionary including the database, domain, and optional connection_qualifier components.
contains the database and optional connection_qualifier components, but not the domain component.
Oracle7 performs these tasks before connecting to the remote database:
1. If the database link name specified in the statement is partial, Oracle7 expands the name to contain the domain of the local database (specified by the initialization parameter DB_DOMAIN).
2. Oracle7 first searches for a private database link in your own schema with the same name as the database link in the statement, and then, if necessary, searches for a public database link with the same name.
- 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.
- 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.
3. Oracle7 uses the database string to access the remote database. After accessing the remote database, Oracle7 verifies that both of these conditions are true:
- The name of the remote database (specified by its initialization parameter DB_NAME) must match the database component of the database link name.
- The domain (specified by the initialization parameter DB_DOMAIN) of the remote database must match the domain component of the database link name.
If both of these conditions are true, Oracle7 proceeds with the connection, using the username and password chosen in step 2a. If not, Oracle7 returns an error message.
4. If the connection using the database string, username, and password is successful, Oracle7 attempts to access the specified object on the remote database using the rules for resolving object references and referring to objects in other schemas presented earlier in this section.
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.
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.
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.
Keywords and Parameters
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.
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:
- Within expressions and conditions, Oracle7 treats text literals as though they have the datatype CHAR by comparing them using blank-padded comparison semantics.
- A text literal can have a maximum length of 2000 bytes.
The syntax description of expr .
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.
Keywords and Parameters
digit is one of 0, 1, 2, 3, 4, 5, 6, 7, 8, 9.
An integer can store a maximum of 38 digits of precision.
The syntax description of expr .
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.
Keywords and Parameters
+, - indicates a positive or negative value. If you omit the sign, a positive value is the default.
is one of 0, 1, 2, 3, 4, 5, 6, 7, 8 or 9.
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.
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.
The syntax description of expr .
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.
Table 2 - 1. Internal Datatype Summary
||Variable length character string having maximum length size bytes. Maximum size is 2000, and minimum is 1. You must specify size for a VARCHAR2
||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.
||Character data of variable length up to 2 gigabytes, or 231 -1 bytes.
||Valid date range from January 1, 4712 BC to December 31, 4712 AD.
||Raw binary data of length size bytes. Maximum size is 255 bytes. You must specify size for a RAW value.
||Raw binary data of variable length up to 2 gigabytes.
(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.
||Fixed length character data of length size bytes. Maximum size is 255. Default and minimum size is 1 byte.
||Binary format of an operating system label. This datatype is used with Trusted Oracle7.
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 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.
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" .
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" .
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.
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:
is the precision, or the total number of digits. Oracle7 guarantees the portability of numbers with precision ranging from 1 to 38.
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:
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:
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.
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:
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:
specifies a floating point number with decimal precision 38, or a binary precision of 126.
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 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:
- SET clauses of UPDATE statements
The use of LONG values are subject to some restrictions:
- VALUES clauses of INSERT statements
- A table cannot contain more than one LONG column.
- LONG columns cannot appear in integrity constraints (except for NULL and NOT NULL constraints).
- LONG columns cannot be indexed.
- A stored function cannot return a LONG value.
Also, LONG columns cannot appear in certain SQL statements:
- Within a single SQL statement, all LONG columns, updated tables, and locked tables must be located on the same database.
Also, LONG columns cannot appear in certain parts of SQL statements:
- WHERE, GROUP BY, ORDER BY, or CONNECT BY clauses or with the DISTINCT operator in SELECT statements
- UNIQUE clause of a SELECT statement
- the column datatype clause of a CREATE CLUSTER statement
- SQL functions (such as SUBSTR or INSTR)
- expressions or conditions
- select lists of queries containing GROUP BY clauses
- select lists of subqueries or queries combined by set operators
- select lists of CREATE TABLE AS SELECT statements
Triggers can use the LONG datatype in the following manner:
- select lists in subqueries in INSERT statements
- A SQL statement within a trigger can insert data into a LONG column.
- If data from a LONG column can be converted to a constrained datatype (such as CHAR and VARCHAR2), a LONG column can be referenced in a SQL statement within a trigger. Note that the maximum length for these datatypes is 32 Kbytes.
- Variables in triggers cannot be declared using the LONG datatype.
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.
- :NEW and :OLD cannot be used with LONG columns.
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.
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.
This statement returns the Julian equivalent of January 1, 1992:
SELECT TO_CHAR(TO_DATE('01-01-1992', 'MM-DD-YYYY'),'J')
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.
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:
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.
is a four-digit hexadecimal string identifying the row in the data block. The first row in the block has the number 0.
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.
Consider this ROWID value:
The row corresponding to this ROWID is the first row (0000) in the fifteenth data block (0000000F) of the second data file (0002).
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
DOUBLE PRECISION 3
|Table 2 - 2. ANSI 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.
|SQL/DS or DB2 Datatype
|Table 2 - 3. SQL/DS and DB2 Datatypes Converted to Oracle7 Datatypes
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.
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.
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:
- blank-padded comparison semantics
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.
- 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 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.
| '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
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.
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:
- 7-bit ASCII (American Standard Code for Information Interchange)
- EBCDIC (Extended Binary Coded Decimal Interchange Code) Code Page 500
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.
- ISO 8859/1 (International Standards Organization)
ASCII Character Set
Table 2 - 5 lists the 7-bit ASCII character set.
|Table 2 - 5. ASCII Character Set
EBCDIC Character Set
Table 2 - 6 lists a common portion of the EBCDIC character set.
|Table 2 - 6. EBCDIC Character Set
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:
- When an INSERT or UPDATE statement assigns a value of one datatype to a column of another, Oracle7 converts the value to the datatype of the column.
- When you use a SQL function or operator with an argument with a datatype other than the one it accepts, Oracle7 converts the argument to the accepted datatype.
- When you use a comparison operator on values of different datatypes, Oracle7 converts one of the expressions to the datatype of the other.
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'
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:
WHERE empno = '7936'
If the character value is too short to fit the entire number value, the number value is rounded.
In the following statement, Oracle7 implicitly converts '12-MAR-1993' to a DATE value using the default date format 'DD-MON-YYYY':
WHERE hiredate = '12-MAR-1993'
In the following statement, Oracle7 implicitly converts the text literal '00002514.0001.0001' to a ROWID value:
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.
For information on these functions, see the section "Conversion Functions" .
|Table 2 - 7. SQL Functions for Datatype Conversion
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:
- SQL statements are easier to understand when you use explicit datatype conversions functions.
- Automatic datatype conversion can have a negative impact on performance, especially if the datatype of a column value is converted to that of a constant rather than the other way around.
- Implicit conversion depends on the context in which it occurs and may not work the same way in every case.
- Algorithms for implicit conversion are subject to change across software releases and among Oracle products. Behavior of explicit conversions is more predictable.
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.
For the truth tables showing the results of logical expressions containing nulls, see Table 3 - 6, Table 3 - 7, and Table 3 - 8 beginning .
|If A is:
||a IS NULL
||a IS NOT NULL
||a IS NULL
||a IS NOT NULL
||a = NULL
||a != NULL
||a = NULL
||a != NULL
||a = 10
||a != 10
|Table 2 - 8. Conditions Containing Nulls
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:
returns the current value of a sequence.
increments the sequence and returns the next value.
You must qualify CURRVAL and NEXTVAL with the name of the sequence:
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:
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:
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 the sequence's creation label is higher than your DBMS label, you must have READUP and WRITEUP system privileges
- If the sequence's creation label is lower than your DBMS label, you must have WRITEDOWN system privilege.
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.
- If the sequence's creation label and your DBMS label are not comparable, you must have READUP, WRITEUP, and WRITEDOWN system privileges.
Using Sequence Values
You can use CURRVAL and NEXTVAL in these places:
- the SELECT list of a SELECT statement that is not contained in a subquery, snapshot or view
- the SELECT list of a subquery in an INSERT statement
- the VALUES clause of an INSERT statement
You cannot use CURRVAL and NEXTVAL in these places:
- the SET clause of an UPDATE statement
- a subquery in a DELETE, SELECT, or UPDATE statement
- a view's query or snapshot's query
- a SELECT statement with the DISTINCT operator
- a SELECT statement with a GROUP BY or ORDER BY clause
- a SELECT statement that is combined with another SELECT statement with the UNION, INTERSECT, or MINUS set operator
- the WHERE clause of a SELECT statement
- DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement
Also, within a single SQL statement, all referenced LONG columns, updated tables, and locked tables must be located on the same database.
- the condition of a CHECK constraint
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 .
This example selects the current value of the employee sequence:
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)
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)
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 .
For each row in the database, the ROWID pseudocolumn returns a row's address. ROWID values contain information necessary to locate a row:
- which data block in the data file
- which row in the data block (first row is 0)
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.
- which data file (first file is 1)
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:
- They are the fastest way to access a single row.
- They can show you how a table's rows are stored.
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.
- They are unique identifiers for rows in a table.
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.
This statement selects the address of all rows that contain data for employees in department 20:
SELECT ROWID, ename
WHERE deptno = 20
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:
WHERE ROWNUM < 10
You can also use ROWNUM to assign unique values to each row of a table, as in this example:
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.
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:
- Begin the comment with /*. Proceed with the text of the comment. This text can span multiple lines. End the comment with */. The opening and terminating characters need not be separated from the text by a space or a line break.
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.
- Begin the comment with -- (two hyphens). Proceed with the text of the comment. This text cannot extend to a new line. End the comment with a line break.
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.
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 */
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
WHERE emp.deptno = dept.deptno
AND sal + NVL(comm, 0) > -- whose compensation
-- is greater than
(SELECT sal + NVL(comm,0) -- the compensation
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.