This document discusses SQL commands used by Oracle Database Lite. Topics include:
The following lists the different types of SQL commands including clauses and pseudocolumns. An explanation of each SQL command, clause, and pseudocolumn is provided in "SQL Commands Overview".
Pseudocolumns | Pseudocolumns |
---|---|
Oracle Database Lite uses several different types of SQL commands. This section discusses the different types of SQL commands.
Data definition language (DDL) commands enable you to perform the following tasks.
Create, alter, and drop schema objects
Grant and revoke privileges and roles
Add comments to the data dictionary
The CREATE
, ALTER
, and DROP
commands require exclusive access to the object being acted upon. For example, an ALTER TABLE
command fails if another user has an open transaction on the specified table.
Data manipulation language (DML) commands query and manipulate data in existing schema objects. These commands do not implicitly commit the current transaction.
Transaction control commands manage changes made by DML commands.
Pseudocolumns are values generated from commands that behave like columns of a table, but are not actually stored in the table. Pseudocolumns are supported by Oracle but are not part of SQL-92.
The syntax diagrams in this document use a variation of Backus-Nauer Form (BNF), a convention used to show syntax in many programming languages. Emphasis and symbols have the following meaning in this version of BNF syntax.
Keywords are shown in UPPERCASE
.
Placeholders for which you must substitute an actual value are shown in lowercase. These can include clauses and other expressions.
Vertical (|) bars separate multiple choices. They indicate "or".
Parentheses and other punctuation enclosed in quotes must be typed as shown, for example "(".
Square brackets ( [] ) are not typed. They indicate that the enclosed syntax is optional.
Curly braces ( {} ) usually are not typed. They indicate that you must specify one of the enclosed choices. (The choices are separated by vertical bars.)
Loops or repetitions are indicated by a second, bracketed appearance of the term, set of terms, or expression, followed by ellipsis points. The brackets indicate that the repetition is optional (all repetitions are optional). The ellipsis points indicate that multiple repetitions are allowed. The bracketed appearance of the term begins with a comma if the repetitions are comma delimited.
All other punctuation (quotation marks, commas, semicolons, and so on) must be typed as shown.
This section lists Oracle Database Lite SQL commands, clauses, and pseudocolumns in alphabetical order and discusses each. This discussion includes the following.
Syntax
BNF Notation
Purpose
Prerequisites
Argument and Descriptions
Usage Notes
Examples
Related Topics
ODBC Functionality (where relevant)
Note:
All examples refer to sample database objects supplied with Oracle Database Lite. Some DDL examples may alter the structure and data of the sample database objects. To avoid altering the sample database objects, use theROLLBACK
command after each DDL example that you try in the database.SQL Commands | In Alphabetical | Order | ||
---|---|---|---|---|
The syntax for the ALTER SEQUENCE
command is displayed in Figure 4-1.
ALTER SEQUENCE [schema .] sequence [(INCREMENT BY "integer" | (MAXVALUE "integer" | NOMAXVALUE) | (MINVALUE "integer" | NOMINVALUE) ] ;
The sequence must be in your own schema.
Changes a sequence in one of the following ways.
Changes the increment between future sequence values.
Sets or eliminates the minimum or maximum value.
The arguments for the ALTER SEQUENCE
command are listed in Table 4-7.
Table 4-7 Arguments Used with the ALTER SEQUENCE Command
Argument | Description |
---|---|
schema |
The name of the schema to contain the sequence. If you omit schema, Oracle Database Lite alters the sequence in your own schema. |
sequence |
The name of the sequence to be altered. |
INCREMENT BY |
Specifies the interval between sequence numbers. Can be any positive or negative integer, but cannot be 0. If negative, then the sequence descends. If positive, the sequence ascends. This value can have 10 or fewer digits. The absolute of this value must be less than the difference of |
MAXVALUE |
Specifies the maximum value the sequence can generate. This integer value can have 10 or fewer digits. |
NOMAXVALUE |
Specifies a maximum value of 2147483647 for an ascending sequence or –1 for a descending sequence. |
MINVALUE |
Specifies the minimum value that the sequence can generate. This integer value can have 10 or fewer digits. |
NOMINVALUE |
Specifies a minimum value of 1 for an ascending sequence or –2147483647 for a descending sequence. |
To restart a sequence at a different number, you must drop and recreate the sequence. Only future sequence numbers are affected by the ALTER SEQUENCE
command.
Oracle Database Lite performs some validations. For example, you cannot specify a new MAX VALUE
that is less than the current sequence number, or a new MINVALUE
that is greater than the current sequence number.
This statement sets a new maximum value for the ESEQ sequence.
ALTER SEQUENCE eseq MAXVALUE 1500
Although the ALTER SEQUENCE
command is not part of ODBC SQL; ODBC passes the command through to your database.
The syntax for the ALTER SESSION
command is displayed in Figure 4-2.
ALTER SESSION SET nls_date_format = nls_date_value ;
None
To specify or modify any of the conditions or parameters that affect your connection to the database. Oracle Database Lite only enables you to use the SET
clause of this command to specify or modify the NLS date format. The statement stays in effect until you disconnect from the database.
The arguments for the ALTER SESSION
command are listed in Table 4-8.
Table 4-8 Arguments Used with the ALTER SESSION Command
Argument | Description |
---|---|
parameter_name |
With Oracle Lite, the |
parameter_value |
The NLS date format. For example: |
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY MM DD HH24:MI:SS';
Oracle Lite uses the new default date format.
SELECT TO_CHAR(SYSDATE) Today FROM DUAL; TODAY ------------------- 1997 08 12 14:25:56
The syntax for ALTER TABLE
is displayed in Figure 4-3.
ALTER TABLE [schema .] table { ADD add_column_list |ADD table_constraint |DROP drop_clause |ATTACH JAVA {CLASS | SOURCE} cls_or_src_name IN {DATABASE | cls_or_src_path} [WITH CONSTRUCTOR ARGUMENTS "(" col_name_list ")" |DETACH [AND DELETE] JAVA CLASS class_name |ENABLE ALL TRIGGERS |DISABLE ALL TRIGGERS |MODIFY "(" modify_column_option")" |MODIFY CONSTRAINT constraint_name constraint_state } ;
The syntax for the add_column_list
expression is displayed in Figure 4-4.
Figure 4-4 The add_column_list Expression
[COLUMN] "("column datatype [DEFAULT expr] [column_constraint] [, column_constraint]...")" [, [COLUMN] "("column datatype [DEFAULT expr] [column_constraint] [, column_constraint]...")"]...
The syntax for modify_column_option
expression is displayed in Figure 4-5.
Figure 4-5 The modify_column_option Expression
column [datatype] [DEFAULT { literal | USER | SYSDATE }] [ NULL | NOT NULL ] [, column [ [datatype] [DEFAULT { literal | USER | SYSDATE }] [ NULL | NOT NULL ] ] ]...
The syntax for constraint_state
expression is displayed in Figure 4-6.
Figure 4-6 The constraint_state Expression
([ENABLE | DISABLE] [VALIDATE | NOVALIDATE])
The table must be in your own schema. You must be logged into the database as SYSTEM
or as a user with DBA/DDL privileges.
Changes the definition of a table in one of the following ways:
Adds a column or integrity constraint
Drops a column or integrity constraint
Attaches a Java class
Detaches a Java class
Add, or change default value of a column
Change datatype or size of a column
Disable or enable a constraint
Change nullity property of a column
The arguments for the ALTER TABLE
command are listed in Table 4-9.
Table 4-9 Arguments Used with the ALTER TABLE Command
Argument | Description |
---|---|
schema |
The name of the schema, which is a character string of up to 128 characters. The schema name must be different from any user names since each user name comes with a default schema with the same name. If you create a schema with the same name as a user name, Oracle Lite returns an error. See "CREATE USER" for more information. |
table |
The name of a database table. |
ADD |
Specifies that a column or integrity constraint is added to the database table. |
DROP |
Specifies that a column or integrity constraint is dropped from the database table. |
column |
The name of a database column. |
datatype |
The datatype of the database column. |
DEFAULT |
Specifies a default value expr (expression) for the new column. It can be one of the following:
For more information about expressions, see Section 1.8, "Specifying Expressions". |
expr |
A valid expression. Expressions are evaluated when |
column_constraint |
A column integrity constraint. For more information, see CONSTRAINT clause. You cannot add a column with a not null constraint to a table that already contains data. |
table_constraint |
A table integrity constraint. For more information, see "CONSTRAINT clause". |
drop_clause |
An integrity constraint to be dropped. For more information, see "DROP clause". |
|
Attaches a Java class or source file to the database table. |
|
Indicates that the Java class or source file must be attached in either a database, Java class, or source path. |
|
The database in which you attach the Java class or source path. |
|
Detaches a Java class from the database table. |
|
Specifies a Java class. |
|
Specifies a Java source file. |
cls_or_src_name |
A fully qualified Java class or source file name. |
cls_or_src_path |
The directory containing the specified Java class or source file. |
|
Specifies attributes of the class to be used as arguments to the Java constructor. |
col_name_list |
List of columns (attributes) in the database table. |
|
Deletes the Java class from the database. |
class_name |
The name of a fully qualified Java class. |
|
Enables all triggers associated with the table. The triggers are fired whenever their triggering condition is satisfied. To enable a single trigger, use the |
|
Disables all triggers associated with the table. A disabled trigger is not fired even if the triggering condition is satisfied. To disable a single trigger, use the |
|
This specifies a new default for an existing column. Oracle Database Lite assigns this value to the column if a subsequent |
|
This modifies the definition of an existing column. Any of the optional parts of the column definition, datatype, default value (literal, A column undergoing datatype alteration which is part of an index created using the |
|
Modifies the state of an existing constraint. |
|
This setting specifies that all existing data complies with the constraint. An enabled validated constraint guarantees that all data is and continues to be valid. If a user places a primary key constraint in If |
|
This setting ensures that all new DML operations on the constrained data comply with the constraint, but does not ensure that existing data in the table complies with the constraint. Enabling a primary key constraint automatically creates a primary index to enforce the constraint. This index is converted to an ordinary index if the primary key constraint is subsequently disabled. If the constraint is subsequently re-enabled, the index is checked for any primary key constraints and if no violations are detected, is restored to primary key status. |
|
This setting disables the constraint and converts the index on the primary key constraint to an ordinary index, but keeps the constraint valid. No DML statements are allowed on the table through the SQLRT engine but you may be able to perform a DML statement through Oracle Database Lite Java Access Classes (JAC). If |
|
This setting signifies that Oracle Database Lite makes no effort to maintain the constraint (because it is disabled) and cannot guarantee that the constraint is true (because it is not validated). A primary key constraint index is downgraded to an ordinary index. You cannot drop a table with a primary key that is referenced by a foreign key even if the foreign key constraint is in the |
If you use the ADD
clause to add a new column to the table, then the initial value of each row for the new column is null. You can add a column with a NOT NULL
constraint only when a default value is also specified, regardless of whether or not the table is empty.
If VALIDATE
or NOVALIDATE
are omitted from the ENABLE
argument, the default is NOVALIDATE
.
If VALIDATE
or NOVALIDATE
are omitted from the DISABLE
argument, the default is NOVALIDATE
.
The nullity constraint is the only integrity constraint that can be added to an existing column using the MODIFY
clause with the column constraint syntax. NOT NULL
can be added only if the column contains no nulls. A NULL
can be added provided the column is not a component of a primary key constraint.
The following statement adds the columns THRIFTPLAN
and LOANCODE
to the EMP
table. THRIFTPLAN
has a datatype, NUMBER
, with a maximum of seven digits and two decimal places. LOANCODE
has a datatype, CHAR
, with a size of one and a NOT NULL
integrity constraint:
ALTER TABLE emp ADD (thriftplan NUMBER(7,2), loancode CHAR(1));
The syntax for the ALTER TRIGGER
command is displayed in Figure 4-7.
ALTER TRIGGER [schema .] trigger { ENABLE | DISABLE };
To alter a trigger you must have the DBA/DDL privilege.
To enable or disable a database trigger. For information on creating a trigger, see CREATE TRIGGER. For information on dropping a trigger, see DROP TRIGGER.
Note:
This statement does not change the declaration or definition of an existing trigger. To redeclare or redefine a trigger, use theCREATE TRIGGER
statement with OR REPLACE
.The arguments for the ALTER TRIGGER
command are listed in Table 4-10.
Table 4-10 Parameters of the ALTER TRIGGER Command
Parameter | Description |
---|---|
schema |
The schema containing the trigger. If you omit schema, Oracle Database Lite assumes the trigger is in your own schema. |
trigger |
The name of the trigger to be altered. |
|
Enables the trigger. You can also use the |
|
Disables the trigger. You can also use the |
Consider a trigger named REORDER
created on the INVENTORY
table. The trigger is fired whenever an UPDATE
statement reduces the number of a particular part on hand below the part's reorder point. The trigger inserts into a table of pending orders a row that contains the part number, a reorder quantity, and the current date.
When this trigger is created, Oracle Database Lite enables it automatically. You can subsequently disable the trigger with the following statement.
ALTER TRIGGER reorder DISABLE;
When the trigger is disabled, Oracle Database Lite does not fire the trigger when an UPDATE
statement causes the part's inventory to fall below its reorder point.
After disabling the trigger, you can subsequently enable it with the following statement.
ALTER TRIGGER reorder ENABLE;
After you re-enable the trigger, Oracle Database Lite fires the trigger whenever a part's inventory falls below its reorder point as a result of an UPDATE
statement. It is possible that a part's inventory falls below its reorder point while the trigger was disabled. In that case, when you reenable the trigger, Oracle Database Lite does not automatically fire the trigger for this part until another transaction further reduces the inventory.
The syntax for ALTER USER
is displayed in Figure 4-8.
ALTER USER user IDENTIFIED BY password ;
You can change your user password in the database if you meet one of the following conditions.
You are connected to the database as that user.
You are connected to the database as SYSTEM
or as a user with DBA/DDL or ADMIN
privileges.
You are granted the UNRESOLVED XREF TO ADMIN
or UNRESOLVED XREF TO DBA/DDL
role.
Changes a database user password.
The arguments for the ALTER USER
command are listed in Table 4-11.
Table 4-11 Arguments Used with the ALTER USER Command
Argument | Description |
---|---|
user |
The user to be altered. Here, user is a unique user name with no more than 30 characters, beginning with one character. The first character in user cannot be a blank space. |
|
Indicates how Oracle Database Lite permits user access. |
password |
Specifies a new password for the user which is a name of up to 128 characters. The password does not appear in quotes and is not case-sensitive. |
The following example creates a user named todd
identified by the password, tiger
. It then changes the user's password to lion
.
CREATE USER todd IDENTIFIED BY tiger; ALTER USER todd IDENTIFIED BY lion;
The syntax for the ALTER VIEW
command is displayed in Figure 4-9.
ALTER VIEW [schema .] view COMPILE ;
The view must be in your own schema. You must be logged into the database as SYSTEM
or as a user with DBA/DDL privileges.
Recompiles a view.
The arguments for the ALTER VIEW
command are listed in Table 4-12.
Table 4-12 Arguments Used with the ALTER VIEW Command
Argument | Description |
---|---|
schema |
The schema to contain the view. If you omit schema, Oracle Database Lite alters the view in your own schema. |
view |
The name of the view to be recompiled. |
COMPILE |
Causes Oracle Lite to recompile the view. The |
You can use ALTER VIEW
to explicitly recompile a view that is invalid. Explicit recompilation enables you to locate recompilation errors before run-time. You may want to explicitly recompile a view after altering one of its base tables to ensure that the alteration does not affect the view or other objects that depend on it. When you issue an ALTER VIEW
statement, Oracle Database Lite recompiles the view regardless of whether it is valid or invalid. Oracle Database Lite also invalidates any local objects that depend on the view.
This command does not change the definition of an existing view. To redefine a view, you must use the CREATE VIEW
command with the OR REPLACE
option.
The following code demonstrates the ALTER VIEW
SQL command. The COMPILE
keyword is required.
ALTER VIEW customer_view COMPILE;
The syntax for COMMIT
is displayed in Figure 4-10.
COMMIT [WORK] ;
None
Ends your current transaction, making permanent to the database all its changes.
The arguments for the COMMIT
command are listed in Table 4-13.
Table 4-13 Arguments Used with the Commit Command
Argument | Description |
---|---|
|
An optional argument with no effect. |
Oracle Database Lite does not autocommit any DDL statements except for CREATE DATABASE
. You must commit your current transaction to make permanent all of its changes to the database.
The following code demonstrates the COMMIT
command. This example inserts a row into the DEPT
table and commits the change. The WORK
argument is optional.
INSERT INTO dept VALUES (50, 'Marketing', 'TAMPA'); COMMIT;
Although the COMMIT
command is not part of the ODBC SQL syntax, ODBC passes the command through to your database.
An ODBC program typically uses the API call SQLTransact()
with the SQL_COMMIT
flag.
The syntax for the COLUMN CONSTRAINT
clause is displayed in Figure 4-11.
[CONSTRAINT constraint] { [NOT] NULL | {UNIQUE | PRIMARY KEY} | REFERENCES [schema .] table ["("column")"] [ON DELETE CASCADE] | CHECK "(" condition ")" }
The syntax for the TABLE CONSTRAINT
clause is displayed in Figure 4-12.
[CONSTRAINT constraint]{ { UNIQUE | PRIMARY KEY } "("column [, column] ...")" [ KEY COLUMNS = number ] | FOREIGN KEY "("column [, column] ...")" REFERENCES [ schema .] table "("column [, column] ...")" [ON DELETE CASCADE] | CHECK "("condition")"}
CONSTRAINT
clauses can appear in both the CREATE TABLE and ALTER TABLE commands. To define an integrity constraint, you must be logged into the database as SYSTEM
or as a user with DBA/DDL privileges. Oracle Database Lite only has integrity constraints.
Defines an integrity constraint.
The arguments for the CONSTRAINT
clause are listed in Table 4-14.
Table 4-14 Arguments Used with the Constraint Clause
Argument | Description |
---|---|
|
Identifies the integrity constraint named by the constraint argument. Oracle Database Lite stores the constraint's name and definition in the data dictionary. If you omit the |
constraint |
The name of the constraint being added. |
|
Specifies that a column can contain null values. |
|
Specifies that a column cannot contain null values. By default, a column can contain nulls. |
|
Designates a column, or a combination of columns, as a unique key. |
|
Designates a column, or a combination of columns, as the table's primary key. |
|
This specifies how many columns should be used to create the index. This clause is useful when an index is needed on a large number of columns, since it reduces the size of the index. Query performance may suffer when multiple rows qualify as prefix columns of an index key as given by the |
number |
An integer which specifies the number of |
|
Designates a column, or a combination of columns in the child table, as the foreign key in a referential integrity constraint. |
schema |
The name of the schema, which is a character string up to 128 characters. The schema name must be different from any user names since each user name comes with a default schema with the same name. If you create a schema with the same name as a user name, Oracle Database Lite returns an error. See CREATE USER for more information. |
|
Identifies the primary key or unique key of the parent table that is referenced by a foreign key in a referential integrity constraint. |
table |
Specifies the table on which the constraint is placed. If you specify only table and omit the column argument, the foreign key automatically references the primary key of the table. |
column |
Specifies the column of the table on which the constraint is placed. |
|
Specifies that Oracle Database Lite maintains referential integrity by automatically removing dependent foreign key values when you remove a referenced primary key or unique key value. |
|
Specifies that a condition be checked for each row in the table. Oracle Database Lite only supports the following operators and functions in
|
condition |
Specifies the condition that each row in the table must satisfy. For more information about creating a valid condition, see Section 1.7, "Specifying SQL Conditions". |
The following example creates a table T, with columns A and B. The example uses the PRIMARY KEY
constraint clause to make column A the table's primary key.
CREATE TABLE T (A CHAR(20) PRIMARY KEY, B CHAR(20));
The syntax for CREATE DATABASE
is displayed in Figure 4-13.
CREATE DATABASE database database_parameter [, database_parameter]...;
The syntax for the database_parameters
expression is displayed in Figure 4-14.
Figure 4-14 The database_parameters Expression
{|USER SYSTEM IDENTIFIED BY password |DATABASE_ID database_id |DATABASE_SIZE max_bytes |EXTENT_SIZE npages } ;
None
Creates a database.
The arguments for the CREATE DATABASE
command are listed in Table 4-15.
Table 4-15 Arguments Used with the CREATE DATABASE Command
Argument | Description |
---|---|
database |
A data file name or full path name. Full path names must be enclosed in double quotation marks. If no path name is specified, the data file is created in the directory specified by the data source name (DSN) if connected through ODBC. If neither the full path name nor DSN are valid, the database is created under the current working directory. The length of database is limited by the operating system or file system. If a duplicate database name is used, an error occurs. |
|
Use this clause to establish a password for the |
|
An optional numeric identifier for the database. |
database_id |
A unique identifier for the database. Must be a unique number from 16 to 32765. If omitted, the default initial value is 64. The database_id parameter in the |
|
The database size. |
maxbytes |
The maximum file size to which the database can grow. If omitted, the default value is 256M. The abbreviations K, M, and G may be used for kilobytes, megabytes, and gigabytes, respectively. If an abbreviation is not specified, the default is K. If specifying an abbreviation, you must use an integer value between 250 kilobytes and 4 gigabytes, for example, 256M, 1000K, or 2G. |
|
An incremental amount of pages in a database file. When a database runs out of pages in the current file, it extends the file by this number of pages. |
npages |
The number of 4K (kilobyte) pages which make up an extent (the minimum unit of allocation for a table). A number that is a multiple of 2 is required for npages. The default value is 4. If set to 0, Oracle Database Lite sets npages to the default value. |
The number of pages should be less than or equal to 64.
Keywords may be listed in any order.
Before you can run a newly created database, you must first configure its ODBC data source name (DSN) using the ODBC Administrator.
Unlike other DDL statements, Oracle Lite autocommits the CREATE DATABASE
command. You cannot undo the CREATE DATABASE
command with a ROLLBACK
statement.
If the POLITE.INI parameter NLS_SORT
has been set to enable one of the collation sequences, such as FRENCH
, all databases are created with that collation sequence. The default is BINARY
. For more information, see the POLITE.INI Parameters Appendix in the Oracle Database Lite Administration and Deployment Guide.
To create the data file LIN.ODB in the directory C:\TMP with the .ODB file extension, use.
CREATE DATABASE "C:\TMP\LIN"
The syntax for CREATE FUNCTION
is displayed in Figure 4-15.
CREATE [OR REPLACE] FUNCTION [schema .] function ["(" argument [ IN | OUT | IN OUT ] datatype [, argument [ IN | OUT | IN OUT ] datatype]... ")"] RETURN datatype { IS | AS } [ invoker_rights_clause] [call_spec];
The syntax for the call_spec
expression is displayed in Figure 4-16.
LANGUAGE Java_declaration
The syntax for the Java_declaration
expression is displayed in Figure 4-17.
Figure 4-17 The Java_declaration Expression
JAVA NAME . string .
To create a function in your own schema, you must be connected to the database as SYSTEM
or you must have DBA/DDL privileges.
To invoke a call specification, you must have DBA/DDL privileges.
To create a call specification for a stored function.
A stored function (also called a user function) is a Java stored procedure that returns a value. Stored functions are very similar to procedures, except that a procedure does not return a value to the environment in which it is called. For a general discussion of procedures and functions, see CREATE PROCEDURE. For examples of creating functions, see the CREATE FUNCTION examples.
A call specification declares a Java method so that it can be called from SQL. The call specification tells Oracle Database Lite which Java method to invoke when a call is made. It also tells Oracle Database Lite what type conversions to make for the arguments and return value.
The CREATE FUNCTION
statement creates a function as a standalone schema object. For information on dropping a stand alone function, see DROP FUNCTION.
The arguments for the CREATE FUNCTION
command are listed in Table 4-16.
Table 4-16 Arguments Used with the CREATE FUNCTION Command
Argument | Description |
---|---|
|
Recreates the function if it already exists. Use this clause to change the definition of an existing function without dropping, re-creating, and regranting object privileges previously granted on the function. Users who had previously been granted privileges on a redefined function can still access the function without being regranted the privileges. If any function-based indexes depend on the function, Oracle Database Lite marks the indexes |
schema |
The schema to contain the function. If you omit schema, Oracle Database Lite creates the function in your current schema. |
function |
The name of the function to create. See "Usage Notes". |
argument |
The name of an argument to the function. If the function does not accept arguments, you can omit the parentheses following the function name. |
|
Specifies that you must supply a value for the argument when calling the function. This is the default. |
|
Specifies that the function sets the value of the argument. |
|
Specifies that a value for the argument can be supplied by you and may be set by the function.
These effects may or may not occur on any particular call. You should use |
datatype |
The datatype of an argument. An argument can have any datatype supported by SQL. The datatype cannot specify a length, precision, or scale. Oracle Database Lite derives the length, precision, or scale of an argument from the environment from which the function is called. |
|
Specifies the datatype of the function's return value. Because every function must return a value, this clause is required. The return value can have any datatype supported by SQL. The datatype cannot specify a length, precision, or scale. Oracle Database Lite derives the length, precision, or scale of the return value from the environment from which the function is called. |
|
Associates the SQL identifier with the Java method. |
|
Associates the SQL identifier with the Java method. |
invoker_rights_clause |
For compatibility with Oracle, Oracle Database Lite recognizes but does not enforce the invoker_rights_clause. |
call_spec |
Maps the Java method name, parameter types, and return type to their SQL counterparts. |
|
Specifies the call_spec language. In Oracle database this can be C or Java. In Oracle Database Lite, this can only be Java. |
|
Specifies the call_spec language. In Oracle database this can be C or Java. In Oracle Database Lite, this can only be Java. |
|
The Java method name |
string |
Identifies the Java implementation of the method. For more information, see the Oracle Database Lite Developer's Guide. |
User-defined functions cannot be used in situations that require an unchanging definition. You cannot use user-defined functions.
In a CHECK
constraint clause of a CREATE TABLE
or ALTER TABLE
statement.
In a DEFAULT
clause of a CREATE TABLE
or ALTER TABLE
statement.
In addition, when a function is called from within a query or DML statement, the function cannot.
Have OUT
or IN OUT
parameters.
Commit or roll back the current transaction, create or roll back to a savepoint, or alter the session or the system. DDL statements implicitly commit the current transaction, so a user-defined function cannot execute any DDL statements.
Write to the database, if the function is being called from a SELECT
statement. However, a function called from a subquery in a DML statement can write to the database.
Write to the same table that is being modified by the statement from which the function is called, if the function is called from a DML statement.
Except for the restriction on OUT
and IN OUT
parameters, Oracle Database Lite enforces these restrictions not only for the function called directly from the SQL statement, but also for any functions that the function calls. Oracle Database Lite also enforces these restrictions on any functions called from the SQL statements executed by that function or any function it calls.
The following example provides complete instructions for creating and testing a function.
Create and compile the following Java program and name it Employee.java
.
public class Employee { public static String paySalary (float sal, float fica, float sttax, float ss_pct, float espp_pct) { float deduct_pct; float net_sal; /* compute take-home salary */ deduct_pct = fica + sttax + ss_pct + espp_pct; net_sal = sal * deduct_pct; String returnstmt = "Net salary is " + net_sal; return returnstmt; } /*paySalary */ }
Load the Employee
class into Oracle Database Lite. Once loaded, the Employee
class methods become stored procedures in Oracle Database Lite.
CREATE JAVA CLASS USING BFILE ('C:\', 'Employee.class');
Since the employeeSalary
method returns a value, publish it by using the CREATE FUNCTION
statement.
CREATE FUNCTION PAY_SALARY( sal float, fica float, sttax float, ss_pct float, espp_pct float) return varchar2 as language java name 'Employee.paySalary(float,float,float,float,float)return java.lang.String'; . /
Select the PAY_SALARY stored procedure from dual:
SELECT PAY_SALARY(6000.00, 0.2, 0.0565, 0.0606, 0.1) from dual;
Returns the following result.
PAY_SALARY ----------------------------------------- Net Salary is 2502.6
The syntax for the CREATE GLOBAL TEMPORARY TABLE
command is displayed in Figure 4-18.
Figure 4-18 The CREATE GLOBAL TEMPORARY TABLE Command
CREATE GLOBAL TEMPORARY TABLE table "(" column datatype [DEFAULT expr] [{ NULL | NOT NULL}] [, column datatype [DEFAULT expr] [ {NULL | NOT NULL} ]... ")" ON COMMIT {DELETE | PRESERVE } ROWS ;
The CREATE GLOBAL TEMPORARY TABLE
command creates a temporary table which can be transaction specific or session specific. For transaction-specific temporary tables, data exists for the duration of the transaction. For session-specific temporary table, data exists for the duration of the session. Data in a temporary table is private to the session. Each session can only view and modify its own data. On rollback of a transaction, all modifications made to the global temporary table are lost.
The arguments for the CREATE GLOBAL TEMPORARY TABLE
command are listed in Table 4-17.
Table 4-17 Arguments Used with CREATE GLOBAL TEMPORARY TABLE
Argument | Description |
---|---|
name |
An optionally qualified table name. |
schema |
A schema, which has the same name as the user who owns it. If omitted, the default schema name is used. |
column |
The name of a table column. |
datatype |
The datatype of the column. Cannot be used in subquery. |
|
Specifies a default value expr (expression) for the new column. It can be one of the following:
For more information about expressions, see Section 1.7, "Specifying SQL Conditions". |
Temporary tables cannot be partitioned, organized into an index, or clustered.
You cannot specify any referential integrity (foreign key) constraints on temporary tables.
The following statement creates a temporary table FLIGHT_SCHEDULE
for use in an automated airline reservation scheduling system. Each client has its own session and can store temporary schedules. The temporary schedules are deleted at the end of the session.
CREATE GLOBAL TEMPORARY TABLE flight_schedule ( startdate DATE, enddate DATE, cost NUMBER) ON COMMIT PRESERVE ROWS;
The syntax for the CREATE INDEX
command is displayed in Figure 4-19.
CREATE [ UNIQUE ] INDEX [schema .] index ON [schema .] table "(" column [ ASC | DESC] [, column [ ASC | DESC]]... ")" [ KEY COLUMNS=number] ;
The table to be indexed must be in your own schema. You must be logged into the database as SYSTEM
or as a user with DBA/DDL privileges.
Creates an index on one or more columns of a table.
The arguments for the CREATE INDEX
command are listed in Table 4-18.
Table 4-18 Arguments Used with the CREATE INDEX Command
Argument | Description |
---|---|
|
Designates the specified column or combination of columns as a unique key. |
schema |
When it follows When used in the |
index |
The name of the index to create. You can create any number of indexes for a table, provided you do not use the same columns and column order for more than one index. |
table |
The name of the table for which the index is created. If you do not qualify table with a schema, Oracle Database Lite assumes that the table is contained in your own schema. |
column |
The name of a column in the table. A column of an index cannot be of the datatype |
|
Provided for DB2 compatibility only. Indexes are always created in ascending order. |
|
This specifies how many columns should be used to create the index. This clause is useful when an index is needed on a large number of columns, since it reduces the size of the index. Query performance may suffer when multiple rows qualify as prefix columns of an index key as given by the |
number |
An integer which specifies the number of |
You can use additional index creation options for tuning purposes. However, only use these options when necessary as they may degrade your database performance. See Appendix F, "Index Creation Options" for more information.
CREATE ANY INDEX
can be used to create a index in another schema, but this requires the DBA/DDL role.
The following example creates an index on the SAL
column of the EMP
table.
CREATE INDEX SAL_INDEX ON EMP(SAL);
The syntax for CREATE JAVA
is displayed in Figure 4-20.
CREATE [OR REPLACE] [AND { RESOLVE | COMPILE } NOFORCE ] JAVA { { SOURCE | RESOURCE } NAMED [schema .] primary_name | CLASS [SCHEMA schema .]} [invoker_rights_clause] [RESOLVER "(" "(" match_string [,] { schema_name | - }")" ["(" match_string [,] { schema_name | - }")"]... ")"] { USING BFILE "(" directory_path , server_file_name ")" | AS source_text };
To create or replace a schema object containing a Java source, class, or resource in your own schema, you must be connected to the database as SYSTEM
or you must have DBA/DDL privileges.
To create a schema object containing a Java source, class, or resource.
Note:
For information on Java concepts, including Java stored procedures and JDBC, see the Oracle Database Lite Developer's Guide.The arguments for the CREATE JAVA
command are listed in Table 4-19.
Table 4-19 Arguments Used with the CREATE JAVA Command
Argument | Description |
---|---|
|
Recreates the schema object containing the Java class, source, or resource if it already exists. Use this clause to change the definition of an existing object without dropping, re-creating, and regranting object privileges previously granted. If you redefine a Java schema object and specify Users, previously granted privileges on a redefined function, can still access the function. You do need to re-grant privileges to the users. |
|
Oracle Database Lite recognizes but ignores this parameter. In Oracle, you specify that the database should attempt to resolve the Java schema object that is created if this statement succeeds.
Restriction: You cannot specify this clause for a Java resource. |
|
Oracle Database Lite recognizes but ignores this parameter. In Oracle |
|
Loads a Java class file. |
|
Loads a Java resource file. |
|
Loads a Java source file. Requires the use of the AS source_text clause. |
|
Oracle Database Lite recognizes but ignores this parameter. In Oracle, it is required for a Java source or resource.
If you do not specify schema,Oracle creates the object in your own schema. Restrictions:
|
|
Oracle Database Lite recognizes but ignores this parameter. In Oracle, it applies only to a Java class. This optional clause specifies the schema in which the object containing the Java file resides. If you do not specify |
invoker_rights_clause |
For compatibility with Oracle, Oracle Database Lite recognizes but does not enforce the invoker_rights_clause. |
|
Oracle Database Lite recognizes but ignores this parameter. In Oracle, it specifies a mapping of the fully qualified Java name to a Java schema object, where:
This mapping is stored with the definition of the schema objects created in this command for use in later resolutions (either implicit or in explicit |
AS source_text |
A text of a Java source program. |
|
Identifies the format of the class file. |
When Oracle Database Lite loads a Java class into the database, it does not load dependent classes. Generally, you should use the loadjava
utility to load Java classes into the database. See the Oracle Database documentation for more information about the loadjava
utility.
The following statement creates a schema object and loads the specified Java class into the newly created schema object.
CREATE JAVA CLASS USING BFILE (bfile_dir, 'Agent.class');
This example assumes the directory path bfile_dir
, which points to the operating system directory containing the Java class Agent.class
, already exists. In this example, the name of the class determines the name of the Java class schema object.
The following statement creates a Java source schema object:
CREATE OR REPLACE JAVA SOURCE AS /* This is a class Test */ import java.math.*; /* */ public class Test { public static BigDecimal myfunc(BigDecimal a, BigDecimal b) { return a.add(b); } public static Strin myfunc2(String a, String b) { return (a+b); } };
Note:
The keywordpublic class
should not be used in a comment before the first public class statement.The following statement creates a Java resource schema object named APPTEXT
from a binary file.
CREATE JAVA RESOURCE NAMED "appText" USING BFILE ('C:\TEMP', 'textBundle.dat');
Note:
when embedding any Java statements, the semi-colon character,";"
cannot be the last character in an SQL*Plus statement. If the semi-colon must be the last character in a line, a blank comment line must be added using the following characters: "/* */" . The regular comment symbols, "//" do not work in this context. Placing /* */ at the end of the line prevents SQL*Plus from interpreting the semi-colon as the end of the SQL statement.The syntax for CREATE PROCEDURE
is displayed in Figure 4-21.
CREATE [OR REPLACE] PROCEDURE [schema .] procedure ["(" argument [ IN | OUT | IN OUT ] datatype [, argument [ IN | OUT | IN OUT ] datatype]... ")" ] [invoker_rights_clause] { IS | AS } call_spec;
The syntax for the call_spec
expression is displayed in Figure 4-22.
Figure 4-22 The call_spec Expression used with CREATE PROCEDURE
LANGUAGE Java_declaration
The syntax for the Java_declaration
expression is displayed in Figure 4-23.
Figure 4-23 The Java_declaration Expression used with CREATE PROCEDURE
JAVA NAME . string .
To create a procedure in your own schema, you must be connected to the database as SYSTEM
or you must have DBA/DDL privileges.
To create a call specification for a stand alone stored procedure.
A call specification ("call spec") declares a Java method so that it can be called from SQL. The call spec tells Oracle which Java method to invoke when a call is made. It also tells Oracle Database Lite what type conversions to make for the arguments and return value.
Stored procedures offer advantages in the areas of development, integrity, security, and memory allocation. For more information on stored procedures, including how to call stored procedures, see Chapter 11, "Java Stored Procedures and Triggers" in the Oracle Database Lite Developer's Guide.
Stored procedures and stored functions are similar. While a stored function returns a value to the environment in which it is called, a stored procedure does not. For information specific to functions, see CREATE FUNCTION.
The CREATE PROCEDURE
statement creates a procedure as a stand alone schema object. For information on dropping a stand alone procedure, see DROP PROCEDURE.
The arguments for the Create Procedure
command are listed in Table 4-20.
Table 4-20 Arguments Used with the Create Procedure Command
Argument | Description |
---|---|
|
Recreates the procedure if it already exists. Use this clause to change the definition of an existing procedure without dropping, re-creating, and regranting object privileges previously granted on it. If any function-based indexes depend on the package, Oracle Database Lite marks the indexes |
schema |
The schema to contain the procedure. If you omit schema, Oracle Database Lite creates the procedure in your current schema. |
procedure |
The name of the procedure to create. |
argument |
The name of an argument to the procedure. If the procedure does not accept arguments, you can omit the parentheses following the procedure name. |
|
Indicates that you must specify a value for the argument when calling the procedure. |
|
Indicates that the procedure passes a value for this argument back to its calling environment after execution. |
|
Indicates that you must specify a value for the argument when calling the procedure and that the procedure passes a value back to its calling environment after execution. If you omit Changes made either to this parameter or to another parameter may be visible immediately through both names if the same variable is passed to both. If the procedure is exited with an unhandled exception, any assignment made to this parameter may be visible in the caller's variable. These effects may or may not occur on any particular call. You should use |
datatype |
The datatype of the argument. An argument can have any datatype supported by Oracle Database Lite SQL. Datatypes cannot specify length, precision, or scale. For example, |
invoker_rights_clause |
For compatibility with Oracle, Oracle Database Lite recognizes but does not enforce the invoker_rights_clause. |
|
Associates the SQL identifier with the Java method. |
|
Associates the SQL identifier with the Java method. |
call_spec |
Maps the Java method name, parameter types, and return type to SQL counterparts. |
|
Specifies the call_spec language. In Oracle this can be C or Java. In Oracle Database Lite, this can only be Java. |
Java_declaration |
Identifies the method name in the Java class. |
|
The Java method name. |
string |
Identifies the Java implementation of the method. For more information, see the Oracle Database Lite Developer's Guide. |
Oracle Database Lite recognizes but does not enforce the <invoker_rights_clause>. Oracle Database Lite always uses current_user
for AUTHID
.
The following example creates and compiles a Java procedure and tests it against Oracle Database Lite.
Create and compile the following Java program and name it EMPTrigg.java:
import java.sql.*; public class EMPTrigg { public static final String goodGuy = "Oleg"; public static void NameUpdate(String oldName, String[] newName) { if (oldName.equals(goodGuy)) newName[0] = oldName; } public static void SalaryUpdate(String name, int oldSalary, int newSalary[]) { if (name.equals(goodGuy)) newSalary[0] = Math.max(oldSalary, newSalary[0])*10; } public static void AfterDelete(Connection conn, String name, int salary) { if (name.equals(goodGuy)) try { Statement stmt = conn.createStatement(); stmt.executeUpdate( "insert into employee values('" + name + "', " + salary + ")"); stmt.close(); } catch(SQLException e) {} } }
Create the EMPLOYEE
table with the NAME
and SALARY
columns.
CREATE TABLE EMPLOYEE (NAME VARCHAR(32), SALARY INT);
Insert values into the EMPLOYEE
table by typing the following statements.
INSERT INTO EMPLOYEE VALUES ('Alice', 100); INSERT INTO EMPLOYEE VALUES ('Bob', 100); INSERT INTO EMPLOYEE VALUES ('Oleg', 100);
Load the EMPTrigg
class into Oracle Database Lite. Once loaded, the EMPTrigg
class methods become stored procedures in Oracle Database Lite.
CREATE JAVA CLASS USING BFILE ('c:\', 'EMPTrigg.class');
Use the CREATE PROCEDURE
statement to enable SQL to call the methods in the EMPTrigg
class.
CREATE PROCEDURE name_update( old_name in varchar2, new_name in out varchar2) is language java name 'EMPTrigg.NameUpdate (java.lang.String, java.lang.String[])'; / CREATE PROCEDURE salary_update( ename varchar2, old_salary int, new_salary in out int) as language java name 'EMPTrigg.SalaryUpdate (java.lang.String, int, int[])'; / CREATE PROCEDURE after_delete( ename varchar2, salary int) as language java name 'EMPTrigg.AfterDelete (java.sql.Connection, java.lang.String, int)'; /
Create a trigger for each of the stored procedures.
CREATE TRIGGER NU BEFORE UPDATE OF NAME ON EMPLOYEE FOR EACH ROW name_update (old.name, new.name); / CREATE TRIGGER SU BEFORE UPDATE OF SALARY ON EMPLOYEE FOR EACH ROW salary_update (name, old.salary, new.salary); / CREATE TRIGGER AD AFTER DELETE ON EMPLOYEE FOR EACH ROW after_delete (name, salary); /
Select all rows from the EMPLOYEE
table.
SELECT * FROM EMPLOYEE;
Returns the following result:
NAME SALARY -------------------------------- --------- Alice 100 Bob 100 Oleg 100
The syntax for the CREATE SCHEMA
command is displayed in Figure 4-24.
CREATE SCHEMA schema . CREATE TABLE command [ CREATE TABLE command]... ;
The CREATE SCHEMA
statement can include the CREATE TABLE, CREATE VIEW, and GRANT statements. To issue a CREATE SCHEMA
statement, you must be logged into the database as SYSTEM
or as a user with DBA/DDL or ADMIN
privileges.
Creates a schema or an owner of tables, indexes, and views. CREATE SCHEMA
can also be used to create multiple tables and views in a single transaction.
The arguments for the CREATE SCHEMA
command are listed in Table 4-21.
Table 4-21 Arguments Used with the CREATE SCHEMA Command
Argument | Description |
---|---|
schema |
The name of the schema, which is a character string of up to 128 characters. The schema name must be different from any user names since each user name has a default schema with the same name. If you create a schema with the same name as a user name, Oracle Database Lite returns an error. See CREATE USER for more information. |
|
A |
command |
Contains all the arguments and keywords for a |
Oracle Database Lite treats the schema as the user's private database. Informally, a schema defines a separate name space and a scope of ownership. In other words, two tables may have the same name if they reside in different schemas. All tables and views in the same schema are owned by the owner of that schema. To use a schema different from the one currently in use, you must first disconnect from the current schema, then connect to the new schema.
CREATE SCHEMA
treats a group of separate statements as a single statement; if one of its constituent statements fails, all of its statements are reversed.
The name of the new schema appears in the POL_SCHEMATA
view.
To create a sample schema called HOTEL_OPERATION
use.
CREATE SCHEMA HOTEL_OPERATION;
To create the schema HOTEL_OPERATION
together with the table HOTEL_DIR
and the view LARGE_HOTEL
use.
CREATE SCHEMA HOTEL_OPERATION CREATE TABLE HOTEL_DIR( HOTELNAME CHAR(40) NOT NULL, RATING INTEGER, ROOMRATE FLOAT, LOCATION CHAR(20) NOT NULL, CAPACITY INTEGER);
Although the CREATE SCHEMA
command is not part of the ODBC SQL syntax, ODBC passes the command through to your database.
The syntax for CREATE SEQUENCE
is displayed in Figure 4-25.
CREATE SEQUENCE [schema .] sequence { { INCREMENT BY } integer | { MAXVALUE integer | NOMAXVALUE } | { MINVALUE integer | NOMINVALUE } | { START WITH } integer } [{ { INCREMENT BY } integer | { MAXVALUE integer | NOMAXVALUE } | { MINVALUE integer | NOMINVALUE } | { START WITH } integer }]... ;
None
Creates a sequence.
The arguments for the CREATE SEQUENCE
command are listed in Table 4-22.
Table 4-22 Arguments Used with the CREATE SEQUENCE Command
Argument | Description |
---|---|
schema |
The name of the schema to contain the sequence. If you omit schema, Oracle Database Lite creates the sequence in your own schema. |
sequence |
The name of the sequence to be created. |
|
Specifies the interval between sequence numbers. Can be any positive or negative integer, but cannot be 0. If negative, then the sequence descends. If positive, the sequence ascends. If you omit the |
|
Specifies the first sequence number to be generated. Use this option to start an ascending sequence at a value greater than its minimum (which is the default), or to start a descending sequence at a value less than its maximum (which is the default). |
|
Specifies the maximum value the sequence can generate. This integer value can have 9 or fewer digits. |
|
Specifies a maximum value of 2147483647 for an ascending sequence or –1 for a descending sequence. |
|
Specifies the minimum value that the sequence can generate. This integer value can have 9 or fewer digits. |
|
Specifies a minimum value of 1 for an ascending sequence or –2147483647 for a descending sequence. |
Oracle Database Lite commits sequence numbers when you access the NEXTVAL
function. However, unlike Oracle, Oracle Database Lite does not automatically commit sequences. As a result, you can roll back sequences in Oracle Database Lite. To maintain a sequence when using the ROLLBACK
command, you must commit the sequence after you create it.
The following statement creates the sequence ESEQ
.
CREATE SEQUENCE ESEQ INCREMENT BY 10;
The first reference to ESEQ.NEXTVAL
returns 1. The second returns 11. Each subsequent reference returns a value 10 greater than the previous one.
Although the CREATE SEQUENCE
command is not part of the ODBC SQL syntax, ODBC passes the command through to your database.
The syntax for CREATE SYNONYM
is displayed in Figure 4-26.
CREATE [PUBLIC] SYNONYM [schema .] synonym FOR [schema .] object ;
None
Creates a public or private SQL synonym.
The arguments for the CREATE SYNONYM
command are listed in Table 4-23.
Table 4-23 Arguments Used with the CREATE SYNONYM Command
Argument | Description |
---|---|
|
Creates a public synonym. Public synonyms are accessible to all users. If you omit this option, the synonym is private and is accessible only within its schema. |
schema |
The schema to contain the synonym. If you omit schema, Oracle Database Lite creates the synonym in your own schema. You cannot specify schema if you have specified |
synonym |
The name of the synonym to be created. |
|
Identifies the object for which the synonym is created. If you do not qualify the object with a schema, Oracle Database Lite assumes that the object is in your own schema. The object can be a table, view, sequence, or another synonym. Note that the object need not currently exist and you must have privileges to access the object. |
A private synonym name must be distinct from all other objects in its schema.
You can only use synonyms with the INSERT
, SELECT
, UPDATE
, and DELETE
statements. You cannot use synonyms with the DROP
statement.
To define the synonym PROD
for the table PRODUCT
in the schema SCOTT
, issue the following statement.
CREATE SYNONYM PROD FOR SCOTT.PRODUCT;
The syntax for the CREATE TABLE
command is displayed in Figure 4-27.
CREATE TABLE [schema .] table column_list [column_list ]... [AS subquery] ;
The syntax for the column_list
expression is displayed in Figure 4-28.
"(" column datatype [DEFAULT expr|AUTO INCREMENT][column_constraint] [column_constraint]... [table_constraint] [, column datatype [DEFAULT expr|AUTO INCREMENT][column_constraint] [column_constraint]... [table_constraint]]... ")"
To create a table in your schema or another schema, you must be logged into the database as SYSTEM
or as a user with DBA/DDL privileges.
Creates a database table.
The CREATE TABLE
command creates and populates a database table based on the result of a specified sub-query. The datatypes for the column are derived from the subquery's result set. See Usage Notes for more information.
The arguments for the CREATE TABLE
command are listed in Table 4-24.
Table 4-24 Arguments Used with the CREATE TABLE Command
Argument | Description |
---|---|
schema |
A schema, which has the same name as the user who owns it. If omitted, the default schema name is used. |
table |
The name of a database table. Table names may not contain the period "." character, nor begin with an underscore "_" character. |
column |
The name of a table column. |
datatype |
The datatype of the column. Cannot be used in subquery. |
|
The The Restrictions on Default Common Values A A For more information about expressions, see Chapter 1, "Using SQL", Section 1.8, "Specifying Expressions". |
auto increment |
Set the column to auto increment column. The data type for any auto increment column has to be of the type The value of an auto increment column is auto incremented and inserted, so that the user does not have to provide the value. The value is unique in the table and contains no null value, and thus can be used as a primary key column, when required. The value of the column is determined by the database system and the user does not have means to control the amount incremented, the start value, or the maximum value. The value of the auto increment column starts with 0 and the maximum positive value is the maximum value of a 4-byte integer (2147483647). Once the auto-incremented value reaches the maximum value, the next auto-incremented value starts from the minimum value of the 4-byte integer (-2147483648). |
column_constraint |
Adds a column integrity constraint. For more information, see "CONSTRAINT clause". |
table_constraint |
Adds a table integrity constraint. For more information, see "CONSTRAINT clause". |
AS subquery |
A |
CREATE ANY TABLE
can be used to create a table in another schema, but this requires the DBA/DDL role. Each table can have upto 1000 columns and no more than one primary key constraint.
If the column_list
is omitted.
If table columns are not defined when specifying a sub query, column names are derived from the expressions selected from the sub query.
If an expression in the select list contains an alias, then the alias is used as the column name.
If an expression is a column with no alias name, then its name is used as the column name. An expression is illegal if it is not a column and has no alias. The datatypes for the table's columns are the same as the datatypes for the corresponding expressions in the select list of the sub query.
If the subquery contains UNION
or MINUS
, the first select statement is chosen for this purpose.
If the column_list
is omitted.
The number of columns in the column_list
must equal the number of expressions in the sub query.
The column definitions can specify only column names, default values, and integrity constraints, but not datatypes or auto incremented columns.
A referential integrity constraint cannot be defined using the CREATE TABLE
statement form. Instead, an ALTER TABLE
statement can be used to create the referential integrity constraint at a later point.
If an ORDER BY
clause is used in the sub query, the data is inserted in the specified order into the table. This normaly results in clustering of the data according to the order by columns, but is not guaranteed.
To insert into tables with auto-incremented column(s), since the value of an auto-incremented column is generated automatically by the database system, there is no insert operation allowed on this column. To insert a row into a table that has auto increment column(s), the user has to specify the column list that contains no auto increment column(s) for the insert operation to be successful. For example, assuming that we have the following table defined.
CREATE TABLE t1 (c1 INT AUTO INCREMENT, c2 INT, c3 INT);
To insert into table t1, use the following command.
INSERT INTO T1(c2,c3) values (123, 456);
If the user does not specify the column list, an error message is returned.
To avoid the column list in the insert statement, the auto-incremented column can be hidden before issuing the INSERT
command. For example, if we have the following ALTER COMMAND
issued.
ALTER TABLE T1 HIDE C1;
Then, to insert into table t1, the insert statement can omit the column list as given below.
INSERT INTO T1 VALUES (123,456);
The following statement creates a table named HOTEL_DIR
with two columns. They are: HOTEL_NAME
which is the primary key, and CAPACITY
, which is not nullable and has the default value 0.
CREATE TABLE HOTEL_DIR (HOTEL NAME CHAR(40) PRIMARY KEY, CAPACITY INTEGER DEFAULT 0 NOT NULL)
The following statement creates a table named HOTEL_RESTAURANT
.
CREATE TABLE HOTEL_RESTAURANT(REST_NAME CHAR(50) UNIQUE, HOTEL_NAME CHAR(40) REFERENCES HOTEL_DIR, RATING FLOAT DEFAULT NULL)
The columns include.
REST_NAME
- Restaurant name.
HOTEL_NAME
- Name of the hotel that the restaurant is in.
RATING
- Restaurant rating. The default value is null.
The table has the following integrity constraints.
Two hotels or restaurants cannot have the same name.
HOTEL_NAME
must refer to a hotel in the HOTEL_DIR
table.
CONSTRAINT clause, DROP TABLE, Transaction Control Commands, SELECT
A snapshot table is the table that is created automatically when Oracle Lite database is create on the client. The snapshot tables are created for use for each application whose data is replicated to the back-end Oracle database with the Oracle Database Lite synchronization process. There is a column that enables the Mobile client Sync Agent to determine what information must be uploaded to the Mobile repository.
However, if you do not wish to use the Oracle Database Lite synchronization; but instead, wish to use your own synchronization process, then use the CREATE
SNAPSHOT
TABLE
command enables you to create the snapshot tables for your own synchronization process to determine what data needs replication.
The CREATE
SNAPSHOT
TABLE
command creates the snapshot table on the local Oracle Lite database, which resides on a client device. The snapshot table differs from a regular table in that it contains information regarding the changes of the data, which enables any agent the ability to perform synchronization/replication with the back-end server. This table is the same as a regular table, with the additional column that performs the following:
tracks every INSERT
, UPDATE
and DELETE
operation
reports the status of each row back to the user by issuing a SELECT
statement
The OL__ROW_STATUS
virtual column ( of type INTEGER) displays the row status back to the user. See Section 4.3.19.2, "Using the OL__ROW_STATUS Pseudocolumn to Select Modified Rows" for full details, including the values the shows whether the modification is an insert, update or delete.
The CREATE
SNAPSHOT
TABLE
command has the same syntax as the CREATE
TABLE
command with the addition SNAPSHOT
keywords to indicating that this is a replication table, as follows:
<create snapshot table> ::=
CREATE SNAPSHOT TABLE [<schema>.]<table> [<column list>] [AS <subquery>
]
Refer to Section 4.3.18, "CREATE TABLE" for full details on the syntax and rules in executing this command.
The following sections describe the CREATE SNAPSHOT TABLE command:
Section 4.3.19.2, "Using the OL__ROW_STATUS Pseudocolumn to Select Modified Rows"
Section 4.3.19.3, "Retrieving Dirty Rows Using The Plus Sign ('+') on the Snapshot Table"
Section 4.3.19.4, "Reset the Snapshot Table With the RESET SNAPSHOT TABLE Command"
The following example creates a snapshot table called system.ssl
with both integer and 10 byte character columns:
CREATE SNAPSHOT TABLE system.ssl (c1 INTEGER, c2 CHAR(10));
For the purpose of selecting rows that are inserted, updated or deleted, the virtual column OL__ROW_STATUS
can be used to retrieve all modified rows. The OL__ROW_STATUS
can be used in any place as a regular column.
The following example selects all rows that have been modified with codes 16 (Inserted), 32 (Updated), and 64 (Deleted):
SELECT OL__ROW_STATUS FROM system.ssl WHERE OL__ROW_STATUS IN (16, 32, 64);
For each row in the database, the OL__ROW_STATUS
pseudocolumn returns the status of a row from a snapshot table: new, updated, or clean.
None.
OL__ROW_STATUS
enables you to select the column from any snapshot or regular table, but row status information is only returned for snapshot table rows. Regular table rows return the same value regardless of status.
The OL__ROW_STATUS
pseudocolumn can be qualified with the table name in the same manner as other pseudocolumns. Thus you can determine row status in complex queries involving multiple tables as listed in Table 4-25.
Table 4-25 OL__ROW_STATUS Results
OL__ROW_STATUS value | Value |
---|---|
0 |
Clean |
16 |
Insert |
32 |
Update |
64 |
Delete |
80 |
Ghost |
Select OL__ROW_STATUS, Emp.* from Employee Emp Where Empno = 7900;
Select Emp. OL__ROW_STATUS, ENAME, DNAME from EMP,DEPT where DEPT.DEPTNO=EMP.DEPTNO AND EMP.EMPNO=7900;
CURRVAL and NEXTVAL pseudocolumns, LEVEL pseudocolumn, ROWID pseudocolumn, ROWNUM pseudocolumn
To retrieve rows that are inserted, updated or deleted, append the plus sign ('+') after the table name to indicate that you want only the dirty rows to be selected.
The following example demonstrates how to retrieve the dirty rows from the system.ssl
table:
SELECT c1, c2 FROM system.ssl + WHERE ol__row_status = 64;
This query provides the values of c1
and c2
for any deleted rows.
To reset the row status of each row in a snapshot table, use the RESET
SNAPSHOT
TABLE
command, as follows:
<reset snapshot table> ::= RESET SNAPSHOT TABLE [<schema>.]<table>
The following example resets the system.ssl
table:
RESET SNAPSHOT TABLE system.ssl;
This will clean and reset the row status of each row of the ss1
table to Òclean."
The syntax for CREATE TRIGGER
is displayed in Figure 4-29.
CREATE [OR REPLACE] TRIGGER [schema .] trigger { BEFORE | AFTER } { DELETE | INSERT | UPDATE [OF column [, column]...] } [OR { DELETE | INSERT | UPDATE [OF col_list [, col_list]...] }]... ON { [schema .] table FOR EACH ROW proc_name ["("arg_list")"] ["("arg_list")"]... ;
None
Creates and enables a database trigger.
The arguments for the CREATE TRIGGER
command are listed in Table 4-26.
Table 4-26 Arguments Used with the CREATE TRIGGER Command
Argument | Description |
---|---|
|
Recreates the trigger if it already exists. Creates the trigger if it does not already exist. Used to change the definition of an existing trigger without dropping, recreating, or regranting object privileges previously granted on it. |
schema |
The schema to contain the trigger. If omitted, Oracle Database Lite creates the trigger in your own schema. |
table |
The name of a table in the database. |
trigger |
The name of the trigger to be created. |
|
Specifies that the trigger should be fired before executing the triggering statement. For row triggers, this is a separate firing before each affected row is changed. |
|
Specifies that the trigger should be fired after executing the triggering statement. For row triggers, this is a separate firing after each affected row is changed. |
|
Specifies that the trigger should be fired whenever a |
|
Specifies that the trigger should be fired whenever an |
|
Specifies that the trigger should be fired whenever an |
col_list |
The column(s) that, when updated, cause the trigger to be fired. |
|
Specifies the schema and name of the table on which the trigger is to be created. If omitted, Oracle Database Lite assumes the table is in your own schema. |
|
Designates the trigger to be a row trigger. Oracle Database Lite fires a row trigger once for each row that is affected by the triggering statement. If you omit this clause, the trigger is a statement trigger. Oracle Database Lite fires a statement trigger only once when the triggering statement is issued if the optional trigger constraint is met. |
proc_name |
Name of the Java method Oracle Database Lite executes to fire the trigger. |
arg_list |
Arguments passed to the Java method. |
The following example provides you with instructions for creating and testing a trigger.
Create the following Java program and name it TriggerExample.java.
import java.lang.*; import java.sql.*; class TriggerExample { public void EMP_SAL(Connection conn, int new_sal) { System.out.println("new salary is :"+new_sal); } }
Attach TriggerExample.java
to the EMP table.
ALTER TABLE EMP ATTACH JAVA SOURCE "TriggerExample" in '.';
Create the Java trigger.
CREATE TRIGGER SAL_CHECK BEFORE UPDATE OF SAL ON EMP FOR EACH ROW EMP_SAL(NEW.SAL); . /
Update the EMP table using the Java trigger.
update emp set sal=sal+5000 where sal=70000;
Returns the following result.
new salary is:75000 1 row updated
The syntax for CREATE USER
is displayed in Figure 4-30.
CREATE USER user IDENTIFIED BY password ;
To create users in your schema or other schemas, you must be logged into the database as SYSTEM
or as a user with DBA/DDL privileges.
Creates a database user with no privileges.
The arguments for the CREATE USER
command are listed in Table 4-27.
Table 4-27 Arguments Used with the CREATE USER Command
Argument | Description |
---|---|
user |
The user to be created. Here, user is a unique string, beginning with a letter, with a minimum of one byte and a maximum length of 30 bytes. |
|
Indicates how Oracle Database Lite permits user access. |
password |
Specifies a new password for the user which is a name of up to 128 characters. The password does not appear in quotes and is not case-sensitive. |
You can create multiple users in Oracle Database Lite by using the CREATE USER command. A user is not a schema. When you create a user, Oracle Database Lite creates a schema with the same name and automatically assigns it to the new user as the default schema. The name of the new user appears in the ALL_USERS
view. The new user's default schema appears in the POL_SCHEMATA
view.
When you connect to an Oracle Lite database as a user, the user name becomes the default schema for that session. If there is no schema to match the user name, Oracle Lite refuses the connection. You can access database objects in the default schema without prefixing them with the schema name.
Users with the appropriate privileges can create additional schemas by using the CREATE SCHEMA command, but only the default schema can connect to the database. These schemas are owned by the user who created them and require the schema name prefix to access their objects.
When you create a database using the CREATEDB utility or the CREATE DATABASE command, Oracle Lite creates a special user called SYSTEM with password of MANAGER. This user has all database privileges. You can use SYSTEM as the default user name until you establish user names of your own as needed.
For encrypted databases, all user names and passwords are written to a file named mydbname.opw
. Each user can then use their own password as a key to unlock the .opw
file before the .odb
file is accessed. When you copy or back up the database, you should include the .opw
file and the .plg
file.
Oracle Lite does not permit a user other than SYSTEM to access data or perform operations in a schema that is not its own. Users can only access data and perform operations in a different user's schema if one of the following conditions is met:
The user is granted a pre-defined role in another user's schema, which permits the user to perform the operation.
The user is granted specific privileges in another user's schema.
Note:
The user SYSTEM must grant DBA/DDL or RESOURCE privileges to a new user before the new user can create database objects. The DBA role is recommended as a replacement for the DDL role wherever possible.CREATE USER SCOTT IDENTIFIED BY TIGER;
The syntax for CREATE VIEW
is displayed in Figure 4-31.
CREATE [OR REPLACE] [[NO] FORCE] VIEW [schema .] view["("alias [, alias]...")"] AS subquery ;
You must be logged into the database as SYSTEM or as a user with DBA/DDL privileges.
FORCE creates the view regardless of whether the view's base tables or the referenced object types exist or the owner of the schema containing the view has privileges on them. These conditions must be true before any SELECT, INSERT, UPDATE, or DELETE statements can be issued against the view.
NO FORCE creates the view only if the base tables exist and the owner of the schema containing the view has privileges on them. This is the default.
Creates or replaces a view.
The arguments for the CREATE VIEW
command are listed in Table 4-28.
Table 4-28 Arguments Used with the CREATE VIEW Command
Argument | Description |
---|---|
OR REPLACE |
Recreates the view if it already exists. Used to change the definition of an existing view without dropping, recreating, or re-granting object privileges previously granted. |
FORCE |
Specify FORCE if you want to create the view regardless of whether the view's base tables or the referenced object types exist or the owner of the schema containing the view has privileges on them. These conditions must be true before any SELECT, INSERT, UPDATE, or DELETE statements can be issued against the view. |
NO FORCE |
Specify NO FORCE if you want to create the view only if the base tables exist and the owner of the schema containing the view has privileges on them. This is the default option. |
schema |
The schema to contain the view. If you omit schema, Oracle Lite creates the view in your own schema. |
view |
The name of the view. |
alias |
Specifies names for the expressions selected by the view's query. The number of aliases must match the number of expressions selected by the view. Aliases must follow Oracle Lite's rules for naming schema objects. Each alias must be unique within the view. |
AS subquery |
Identifies columns and rows of the table(s) on which the view is based. A view's query can be any SELECT statement without the ORDER BY or FOR UPDATE clauses. Its select list can contain up to 254 expressions. |
A view is updatable if:
The subquery selects from a single base table or from another updatable view.
Each selected expression is a column reference to that base table or updatable view.
No two column references in the select list reference the same column.
CREATE ANY VIEW can be used to create a view in another schema, but this requires the DBA/DDL role.
The FORCE option of CREATE VIEW behaves differently under Oracle Database Lite. There are two cases:
A command issued to a view created by using CREATE FORCE VIEW without the base table must have the ALTER VIEW view_name COMPILE command issued first, otherwise an error message is thrown.
A CREATE FORCE VIEW created with a valid base table is no different than CREATE VIEW.
The following example creates a view called EMP_SAL which displays the name, job, and salary of each row in the EMP table:
CREATE VIEW EMP_SAL (Name, Job, Salary) AS SELECT ENAME, JOB, SAL FROM EMP; SELECT * FROM EMP_SAL;
Returns the following result:
NAME JOB SALARY ---------- --------- --------- KING PRESIDENT 5000 BLAKE MANAGER 2850 CLARK MANAGER 2450 JONES MANAGER 2975 MARTIN SALESMAN 1250 ALLEN SALESMAN 1600 TURNER SALESMAN 1500 JAMES CLERK 950 WARD SALESMAN 1250 FORD ANALYST 3000 SMITH CLERK 800 SCOTT ANALYST 3000 ADAMS CLERK 1100 MILLER CLERK 1300 14 rows selected.
Although the ODBC SQL syntax for CREATE VIEW does not support the OR REPLACE argument, ODBC passes the command through to your database.
Most ODBC-based tools require a primary key before allowing updates on a view. Oracle Lite does not report primary keys for views, so you must issue SQL commands to perform updates or deletes on views using the WHERE clause to specify the target row or rows.
A sequence is a schema object that can generate unique sequential values. These values are often used for primary and unique keys. You can use the CURRVAL
and NEXTVAL
pseudocolumns to refer to sequence values in SQL statmetments.
You must have a sequence object.
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 qualify the sequence with the schema containing it.
schema.sequence.CURRVAL schema.sequence.NEXTVAL
You can use CURRVAL
and NEXTVAL
in:
The SELECT list of a SELECT
statement that is not contained in a subquery, materialized view, or view.
The SELECT
list of a subquery in an INSERT
statement.
The VALUES
clause of an INSERT
statement.
The SET
clause of an UPDATE
statement.
You cannot use CURRVAL
and NEXTVAL
in:
A query of a view or of a materialized
view.
A SELECT
statement with the DISTINCT operator.
A SELECT
statement with a GROUP BY
clause 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.
The condition of a CHECK
constraint
Also, within a single SQL statement that uses CURRVAL
or NEXTVAL
, 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
. Within a single SQL statement, Oracle Database Lite will increment the sequence only once for each row. If a statement contains more than one reference to NEXTVAL
for a sequence, Oracle increments the sequence once and returns the same value for all occurrences of NEXTVAL
. If a statement contains references to both CURRVAL
and NEXTVAL
, Oracle 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.
This example selects the current value of the employee sequence in the sample schema hr
:
SELECT employees_seq.currval FROM DUAL;
This example increments the employee sequence and uses its value for a new employee inserted into the sample table hr.employees:
INSERT INTO employees VALUES (employees_seq.nextval, 'John', 'Doe', 'jdoe', '555-1212', TO_DATE(SYSDATE), 'PU_CLERK', 2500, null, null, 30);
This example adds a new order with the next order number to the master order table. It then adds suborders with this number to the detail order table:
INSERT INTO orders (order_id, order_date, customer_id) VALUES (orders_seq.nextval, TO_DATE(SYSDATE), 106); INSERT INTO order_items (order_id, line_item_id, product_id) VALUES (orders_seq.currval, 1, 2359); INSERT INTO order_items (order_id, line_item_id, product_id) VALUES (orders_seq.currval, 2, 3290); INSERT INTO order_items (order_id, line_item_id, product_id) VALUES (orders_seq.currval, 3, 2381);
The syntax for DELETE
is displayed in Figure 4-32.
DELETE FROM [schema .] {table|view}[WHERE condition] ;
You can only delete rows from tables or views in your schema.
Removes rows from a table or from a view's base table.
The arguments for the DELETE
command are listed in Table 4-29.
Table 4-29 Arguments Used with the DELETE Command
Argument | Description |
---|---|
schema |
The schema that contains the table or view. If you omit schema, Oracle Lite assumes the table or view is in your own schema. |
table |
The name of a table from which you want to delete rows. |
view |
The name of the view. If you specify view, Oracle Lite deletes rows from the view's base tables. |
WHERE condition |
Deletes only rows that satisfy a condition specified with the condition argument. For more information about creating a valid condition, see Section 1.7, "Specifying SQL Conditions". |
If no WHERE clause is specified, then all rows of the table are deleted.
A positioned DELETE requires that the cursor be updatable.
DELETE FROM PRICE WHERE MINPRICE < 2.4;
The ODBC SQL syntax for DELETE is the same as the SQL syntax. In addition, ODBC syntax includes the CURRENT
OF
cursor_name keyword and argument. These are used in the WHERE clause to specify the cursor where the DELETE operation occurs, as follows:
WHERE CURRENT OF cursor_name
The syntax for the DROP
clause is displayed in Figure 4-33.
DROP {PRIMARY KEY | [COLUMN] column | UNIQUE "("column")" [, "("column")"]... |CONSTRAINT constraint } [ CASCADE ] ;
The DROP clause only appears in an ALTER TABLE statement. To drop an integrity constraint, you must be logged into the database as SYSTEM or as a user with DBA/DDL privileges.
Removes an integrity constraint from the database.
The arguments for the DROP
clause are listed in Table 4-30.
Table 4-30 Arguments Used with the DROP Clause
Argument | Description |
---|---|
PRIMARY KEY |
Drops the table's PRIMARY KEY constraint. |
UNIQUE |
Drops the UNIQUE constraint from the specified columns. |
COLUMN |
Drops a column from the table. |
column |
Specifies the column from which a column constraint is removed, or in the case of DROP COLUMN, specifies the column to be dropped from the table. |
CONSTRAINT |
Drops the integrity constraint named constraint. For more information, see "CONSTRAINT clause". |
constraint |
The name of the integrity constraint to drop. |
RESTRICT |
If any integrity constraints depend on the constraint to drop, the DROP command fails. |
CASCADE |
Drops all other integrity constraints that depend on the constraint specified in the CONSTRAINT clause. |
ALTER TABLE EMP DROP COLUMN COMM;
The syntax for the DROP
function is displayed in Figure 4-34.
DROP FUNCTION [schema .] function_name ;
To drop a function, you must meet one of the following requirements:
The function must be in your own schema.
You must be connected to the database as SYSTEM.
You must have DBA/DDL privileges.
To remove a stand alone stored function from the database. For information on creating a function, see "CREATE FUNCTION".
The arguments for the DROP
function are listed in Table 4-31.
Table 4-31 Arguments Used with the DROP Function
Argument | Description |
---|---|
schema |
The schema containing the function. If you omit schema, Oracle Lite assumes the function is in your own schema. |
function_name |
The name of the function to drop. Oracle Lite invalidates any local objects that depend on, or call, the dropped function. If you subsequently reference one of these objects, Oracle Lite tries to recompile the object and returns an error if you have not recreated the dropped function. |
The following statement drops the PAY_SALARY function, which you created in the CREATE FUNCTION example. When you drop the PAY_SALARY function, you invalidate all objects that depend on PAY_SALARY.
DROP FUNCTION PAY_SALARY;
The syntax for DROP INDEX
is displayed in Figure 4-35.
DROP INDEX [schema .] index ;
To drop an index, you must be logged into the database as SYSTEM or as a user with DBA/DDL privileges.
Removes an index from the database.
The arguments for the DROP INDEX command are listed in Table 4-32.
Table 4-32 Arguments Used with the DROP INDEX Command
Argument | Description |
---|---|
schema |
The schema that contains the index to drop. If you omit the schema, Oracle Lite assumes that the index is in your own schema. |
index |
The name of the index to drop. |
The following example drops an index on the SAL column of the EMP table:
DROP INDEX SAL_INDEX;
The syntax for DROP JAVA is displayed in Figure 4-36.
DROP JAVA { CLASS | RESOURCE } [schema .] object_name;
To drop a class or resource schema object, you must meet the following requirements:
The Java class, or resource must be in your own schema.
You must be connected to the database as SYSTEM or have DBA/DDL privileges.
To drop a Java class or resource schema object.
The arguments for the DROP JAVA command are listed in Table 4-33.
Table 4-33 Arguments Used with the DROP JAVA Command
Argument | Description |
---|---|
JAVA CLASS |
Drops a Java class schema object. |
JAVA RESOURCE |
Drops a Java resource schema object. |
object_name |
Specifies the name of an existing Java class, source, or resource schema object. |
Oracle Lite recognizes schema_name when specified, but does not enforce it.
The following statement drops the Java class MyClass
:
DROP JAVA CLASS "MyClass";
The syntax for DROP PROCEDURE is displayed in Figure 4-37.
DROP PROCEDURE [schema .] procedure ;
The procedure must be connected to the database as schema or you must have DBA/DDL privileges.
To remove a stand alone stored procedure from the database.
For information on creating a procedure, see "CREATE PROCEDURE".
The arguments for the DROP PROCEDURE command are listed in Table 4-34.
Table 4-34 Arguments Used with the DROP PROCEDURE Command
Argument | Description |
---|---|
schema |
The schema containing the procedure. If you omit schema, Oracle Lite assumes the procedure is in your own schema. |
procedure |
The name of the procedure to drop. When you drop a procedure, Oracle Lite invalidates any local objects that depend on the dropped procedure. If you subsequently reference one of these objects, Oracle Lite tries to recompile the object and returns an error message if you have not recreated the dropped procedure. |
The following statement drops the procedure TRANSFER owned by the user KERNER and invalidates all objects that depend on TRANSFER:
DROP PROCEDURE kerner.transfer
The syntax for DROP SCHEMA is displayed in Figure 4-38.
DROP SCHEMA schema . [{CASCADE | RESTRICT}] ;
To drop a schema, you must be logged into the database as SYSTEM or as a user with DBA/DDL or ADMIN privileges.
Removes a schema from the database.
The arguments for the DROP SCHEMA command are listed in Table 4-35.
Table 4-35 Arguments Used with the DROP SCHEMA Command
Argument | Description |
---|---|
schema |
The schema to drop from the database. |
CASCADE |
Specifies that all other objects whose definitions depend on the specified schema are automatically dropped with the schema. |
RESTRICT |
Specifies that if there are other objects whose definitions depend on the specified schema, the DROP SCHEMA operation fails. |
If no options are specified, the default behavior is determined by the RESTRICT argument.
The following example drops the HOTEL_OPERATION schema you created in the CREATE SCHEMA example:
DROP SCHEMA HOTEL_OPERATION CASCADE;
The syntax for DROP SEQUENCE is displayed in Figure 4-39.
DROP SEQUENCE [schema .] sequence ;
You must be logged into the database as SYSTEM, or the sequence must be in your schema.
Removes a sequence from the database.
The arguments for the DROP SEQUENCE command are listed in Table 4-36.
Table 4-36 Arguments Used with the DROP SEQUENCE Command
Argument | Description |
---|---|
schema |
The schema that contains the sequence to drop. If you omit schema, Oracle Lite assumes that the sequence is in your own schema. |
sequence |
The name of the sequence to remove from the database. |
One method for restarting a sequence is to drop and recreate it. For example, if you have a sequence with a current value of 150 and you would like to restart the sequence with a value of 27, you would:
Drop the Sequence.
Create it with the same name and a START WITH value of 27.
The following example drops the ESEQ sequence you created in the CREATE SEQUENCE example:
DROP SEQUENCE ESEQ;
Although the DROP SEQUENCE command is not part of the ODBC SQL syntax, ODBC passes the command through to your database.
The syntax for DROP SYNONYM is displayed in Figure 4-40.
DROP [PUBLIC] SYNONYM [schema .] synonym ;
To drop a synonym from the database, you must be logged into the database as SYSTEM, or the synonym must be in your schema.
Drops a public or private SQL sequence from the database.
The arguments for the DROP SYNONYM command are listed in Table 4-37.
Table 4-37 Arguments Used with the DROP SYNONYM Command
Argument | Description |
---|---|
PUBLIC |
Specifies a public synonym. You must specify PUBLIC to drop a public synonym. |
schema |
The schema to contain the synonym. If you omit schema, Oracle Lite creates the synonym in your own schema. You cannot specify schema if you have specified PUBLIC. |
synonym |
The name of the synonym to be dropped. |
The following example drops the synonym named PROD, which you created in the CREATE SYNONYM example:
DROP SYNONYM PROD;
The syntax for DROP TABLE is displayed in Figure 4-41.
DROP TABLE [schema .] table [{CASCADE | CASCADE CONSTRAINTS | RESTRICT}] ;
To drop a table from the database, you must be logged into the database as SYSTEM or as a user with DBA/DDL privileges.
Removes a table from the database.
The arguments for the DROP TABLE command are listed in Table 4-38.
Table 4-38 Arguments Used with the DROP TABLE Command
Argument | Description |
---|---|
schema |
The schema that contains the table to drop. If you omit schema, Oracle Lite assumes that the table is in your own schema. |
table |
The name of the table to remove from the database. |
CASCADE |
Specifies that, if the table is a base table for views, or if there are referential integrity constraints that refer to primary keys in the table, they are automatically dropped with the table. |
CASCADE CONSTRAINTS |
Specifies that all referential integrity constraints that refer to primary keys in the table are automatically dropped with the table. |
RESTRICT |
Specifies that, if the table is a base table for views, or if the table is referenced in any referential integrity constraints, the DROP TABLE operation fails. |
If no options are specified and there are no referential integrity constraints that refer to the table, Oracle Lite drops the table. If no options are specified and there are referential integrity constraints that refer to the table, Oracle Lite returns an error message.
DROP TABLE EMP;
The syntax for DROP TRIGGER is displayed in Figure 4-42.
DROP TRIGGER [schema .] trigger ;
You must be logged into the database as SYSTEM or the trigger must be in your schema.
Removes a database trigger from the database.
The arguments for the DROP TRIGGER command are listed in Table 4-39.
Table 4-39 Arguments Used with the DROP TRIGGER Command
Argument | Description |
---|---|
schema |
The schema that contains the trigger. If you omit schema, Oracle Lite assumes that the trigger is in your own schema. |
trigger |
The name of the trigger. |
The following statement drops the SAL_CHECK
trigger, which you created in the CREATE TRIGGER example:
DROP TRIGGER ruth.reorder
The syntax for DROP USER is displayed in Figure 4-43.
DROP USER user [CASCADE] ;
To drop a user from the database, you must be logged into the database as SYSTEM, or you must have DBA/DDL or ADMIN privileges.
Removes a user from the database.
The arguments for the DROP USER command are listed in Table 4-40.
Table 4-40 Arguments Used with the DROP USER Command
Argument | Description |
---|---|
user |
Name of the user to be dropped. |
CASCADE |
Drops all objects associated with the user. |
You can drop users if you are connected to the database as SYSTEM, or if you are granted the ADMIN or DBA/DDL role.
To drop a user when the user's schema does not contain any objects, use the syntax:
DROP USER <user>
To drop all objects in the user's schema before dropping the user, use the syntax:
DROP USER <user> CASCADE
The following statement drops the user Michael:
DROP USER MICHAEL;
The syntax for DROP VIEW is displayed in Figure 4-44.
DROP [schema .] VIEW view [ {CASCADE | RESTRICT}] ;
To drop a view from the database, you must be logged into the database and you must meet one of the following requirements:
You must be logged into the database as SYSTEM.
You must have DBA/DDL privileges.
The view must be in your schema.
Removes a view from the database.
The arguments for the DROP VIEW command are listed in Table 4-41.
Table 4-41 Arguments Used with the DROP VIEW Command
Argument | Description |
---|---|
schema |
The schema that contains the view to drop. If you omit schema, Oracle Lite assumes that the view is in your own schema. |
view |
The name of the view to be removed from the database. |
CASCADE |
Specifies that all other views whose definitions depend on the specified view are automatically dropped with the view. |
RESTRICT |
Specifies that if there are other views whose definitions depend on the specified view, the DROP VIEW operation fails. |
If no options are specified, Oracle Lite drops only this view. Other dependent views are not affected.
The following statement drops the EMP_SAL view you created in the CREATE VIEW example:
DROP VIEW EMP_SAL;
The syntax for EXPLAIN PLAN
is displayed in Figure 4-45.
EXPLAIN PLAN select_command;
Displays the execution plan chosen by the Oracle Lite database optimizer for subquery::= statements.
The arguments for the EXPLAIN PLAN
command are listed in Table 4-42.
Table 4-42 Arguments Used with the EXPLAIN PLAN Command
Argument | Description |
---|---|
EXPLAIN PLAN |
Determines an execution plan on a query. |
select_command |
The query for which you determine the execution plan. |
Oracle Lite outputs the execution plan to a file called execplan.txt. Oracle Lite appends each new execution plan to the file.
For every execution of the EXPLAIN PLAN command, Oracle Lite outputs a single line of the EXPLAIN COMMAND followed by one or more lines of the execution plan.
The execution plan contains one line for each query block. A query block begins with a subquery::= keyword.
The plan output is indented to indicate nesting. All siblings of UNION and MINUS are also indented. Each line of the plan output has the following general form:
table-name [(column-name)] [{NL(rows)|IL(rows)} table-name [(column-name)] ]
The parameters for the EXPLAIN PLAN command are listed in Table 4-43.
Table 4-43 Parameters of the EXPLAIN PLAN Output
Parameter | Definition |
---|---|
table-name |
A fully qualified alias or table name. |
column-name |
The name of the first column of an index key. |
NL |
Nested loop join. |
IL |
Index loop join is an index used to join the table following "IL". |
(rows) |
Indicates the optimizer's estimate of rows for the result of the join. |
The tables are executed from left to right. The left-most table forms the outer-most loop of iteration.
Oracle Lite uses row estimates to order tables, however, the actual values are not important. The optimizer estimates the best possible index. The object kernel may choose a different index since it is more accurate at execution time.
The syntax for GRANT
is displayed in Figure 4-46.
GRANT {role | privilege_list ON object_name} TO user_list ;
To grant roles, you must be logged into the database as SYSTEM, or as a user with DBA/DDL and ADMIN privileges, or with RESOURCE privileges to GRANT privilege on your own objects to other users.
Grants the ADMIN, DBA, DDL, or RESOURCE roles to users, or grants privileges on a database object to users. The DBA role is recommended as a replacement for the DDL role wherever possible.
The arguments for the GRANT
command are listed in Table 4-44.
Table 4-44 Arguments Used with the GRANT Command
Argument | Description |
---|---|
role |
The UNRESOLVED XREF TO ADMIN, UNRESOLVED XREF TO DBA/DDL, or UNRESOLVED XREF TO RESOURCE role. |
user_list |
One user, or a comma-separated list of users. |
ON |
Signifies the database object to which you grant roles. |
privilege_list |
Either a comma-separated list of the following privileges or a combination called ALL: INSERT, DELETE, UPDATE (col_list), SELECT, and REFERENCES. |
TO |
Signifies the users or user list to whom you grant roles. |
object_name |
A table name optionally prefixed with a schema name. |
Oracle Lite combines some privileges into pre-defined roles for convenience. In many cases it is easier to grant a user a pre-defined role than to grant specific privileges in another schema. Oracle Lite does not support creating or dropping roles. The Oracle Lite pre-defined roles are listed in Table 4-45:
Table 4-45 Predefined Roles in Oracle Database Lite
Role Name | Privileges Granted To Role |
---|---|
ADMIN |
Enables the user to create other users and grant privileges other than DDL and ADMIN on any object in the schema. The user can execute any of the following commands in a SQL statement: CREATE SCHEMA, CREATE USER, ALTER USER, DROP USER, DROP SCHEMA, GRANT, and REVOKE. |
DBA/DDL |
Enables the user to issue the following DDL statements which otherwise can only be issued by SYSTEM: All ADMIN privileges, CREATE TABLE, CREATE ANY TABLE, CREATE VIEW, CREATE ANY VIEW, CREATE INDEX, CREATE ANY INDEX, ALTER TABLE, ALTER VIEW, DROP TABLE, DROP VIEW, and DROP INDEX. |
RESOURCE |
The RESOURCE role grants the same level of control as the DBA/DDL role, but only over the user's own domain. The user can execute any of the following commands in a SQL statement: CREATE TABLE, CREATE VIEW, CREATE INDEX, CREATE CONSTRAINT, ALTER TABLE, ALTER VIEW, ALTER INDEX, ALTER CONSTRAINT, DROP TABLE, DROP VIEW, DROP INDEX, DROP CONSTRAINT, and GRANT or REVOKE privileges on any object under a user's own schema. |
If privilege_list is ALL, then the user can INSERT, DELETE, UPDATE, or SELECT from the table or view. If privilege_list is either INSERT, DELETE, UPDATE, or SELECT, then the user has that privilege on a table.
When you grant UPDATE on a table to a user and then subsequently alter the table by adding a column, the user is not able to update the new column. The user can only update the new column if you issue a grant statement after creating the new column. For example:
CREATE TABLE t1 (c1 NUMBER c2 INTEGER); CREATE USER a IDENTIFIED BY a; GRANT SELECT, UPDATE ON t1 TO a; ALTER TABLE t1 ADD c3 INT; COMMIT;
In the preceding example, the GRANT statement must be issued after the ALTER TABLE statement or the user cannot update the new column, c3.
The following example creates a user named MICHAEL and grants the user the ADMIN role:
CREATE USER MICHAEL IDENTIFIED BY SWORD; GRANT ADMIN TO MICHAEL;
The following example creates a user named MICHAEL and grants INSERT and DELETE privileges on the EMP table the user.
CREATE USER MICHAEL IDENTIFIED BY SWORD; GRANT INSERT, DELETE ON EMP TO MICHAEL;
The following example grants ALL privileges on the PRODUCT table to the newly created user, MICHAEL:
GRANT ALL ON PRODUCT TO MICHAEL;
The syntax for INSERT
is displayed in Figure 4-47.
INSERT INTO [schema .] {table | view }["("column [, column]...")"]{ VALUES "(" expr [, expr]...")" | subquery} ;
To insert rows into a table or view, you must be logged into the database as SYSTEM, or the table and view must be in your schema.
Adds rows to a table or to a view's base table.
The arguments for the INSERT
command are listed in Table 4-46.
Table 4-46 Arguments Used with the INSERTCommand
Argument | Description |
---|---|
schema |
The schema that contains the table or view. If you omit schema, Oracle Lite assumes that the table or view is in your own schema. |
table |
The name of the table into which you want to insert rows. |
view |
The name of the view into whose base tables you want to insert rows. |
column |
A column of a table or view. In the inserted row, each column listed in this argument is assigned a value from the VALUES clause or from the subquery. If you omit one of the table's columns from this argument, the column's value for the inserted row is the column's default value as specified when the table is created. If you omit the column argument, the VALUES clause or the query must specify values for all columns in the table. |
VALUES |
Specifies a row of values to be inserted into the table or view. You specify in the VALUES clause a value for each column in the column argument. |
expr |
The values assigned to the corresponding column. This can contain host variables. For more information, see Section 1.8, "Specifying Expressions". |
subquery |
A SELECT statement that returns rows that are inserted into the table. The SELECT list of this subquery must have the same number of columns as the column list of the INSERT statement. |
The same column name may not appear more than once in the column argument.
If you omit any columns from the column argument, Oracle Lite assigns the columns the default values specified when the table is created.
The number of columns specified in the column argument must be the same as the number of values provided. If you omit the column argument, the number of values must be equal to the degree of the table.
If a column does not have a user-defined default value, its default value is NULL. This is true even when there is a NOT NULL constraint on the column. If an INSERT statement does not provide an explicit value for such a column, Oracle Lite generates an integrity violation error message.
INSERT INTO EMP (EMPNO, ENAME, DEPTNO) VALUES ('7010', 'VINCE', '20');
The LEVEL pseudocolumn can be used in a SELECT statement that performs a hierarchical query. 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. In a hierarchical query, 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, and a leaf node is any node without children.
None.
The number of levels returned by a hierarchical query is limited to 32.
The following statement returns all employees in hierarchical order. The root row is defined to be the employee whose job is PRESIDENT. The child rows of a parent row are defined to be those who have the employee number of the parent row as their manager number.
SELECT LPAD(' ',2*(LEVEL-1)) || ename org_chart, empno, mgr, job FROM emp START WITH job = 'PRESIDENT' CONNECT BY PRIOR empno = mgr;
Returns the following result:
ORG_CHART EMPNO MGR JOB ------------------ --------- --------- --------- 7839 PRESIDENT JONES 7566 7839 MANAGER SCOTT 7788 7566 ANALYST ADAMS 7876 7788 CLERK FORD 7902 7566 ANALYST SMITH 7369 7902 CLERK CLARK 7782 7839 MANAGER MILLER 7934 7782 CLERK BLAKE 7698 7839 MANAGER WARD 7521 7698 SALESMAN JAMES 7900 7698 CLERK TURNER 7844 7698 SALESMAN ALLEN 7499 7698 SALESMAN MARTIN 7654 7698 SALESMAN 14 rows selected.
CURRVAL and NEXTVAL pseudocolumns, CREATE SNAPSHOT TABLE, ROWID pseudocolumn, ROWNUM pseudocolumn,
The syntax for REVOKE
is displayed in Figure 4-48.
REVOKE { role | privilige_list ON object_name } FROM user_list ;
To revoke roles from users, you must be logged into the database as SYSTEM or as a user with DBA or ADMIN privileges.
Revokes the ADMIN, DBA/DDL, or RESOURCE roles from users, or revokes privileges on a database object from users. The DBA role is recommended as a replacement for the DDL role.
The arguments for the REVOKE
command are listed in Table 4-47.
Table 4-47 Arguments Used with the REVOKE Command
Argument | Description |
---|---|
role |
The UNRESOLVED XREF TO ADMIN, UNRESOLVED XREF TO DBA/DDL, or UNRESOLVED XREF TO RESOURCE role. |
user_list |
One user, or a comma-separated list of users. |
privilege_list |
A comma-separated list of the following privileges or a combination called ALL: INSERT, DELETE, UPDATE (col_list), and SELECT. |
object_name |
A table name prefixed with a schema name. |
If privilege_list contains INSERT, DELETE, UPDATE, or SELECT, then the user has those privileges on a table or view. If privilege_list is ALL, then the user can INSERT, DELETE, UPDATE, or SELECT from the table or view.
The following example creates a user named STEVE and grants the user the ADMIN role. Then, the example revokes the ADMIN role from the user, STEVE.
CREATE USER STEVE IDENTIFIED BY STINGRAY; GRANT ADMIN TO STEVE; REVOKE ADMIN FROM STEVE;
The following example revokes the INSERT and DELETE privileges on the EMP table from the user, SCOTT.
REVOKE INSERT,DELETE ON EMP FROM SCOTT;
The following example creates a user named CHARLES and grants the user the INSERT and DELETE privileges on the PRICE table, and ALL privileges on the ITEM table. Then the example revokes all privileges for the user CHARLES on the PRICE and ITEM tables.
CREATE USER CHARLES IDENTIFIED BY VORTEX; GRANT INSERT, DELETE, UPDATE ON PRICE TO CHARLES; GRANT ALL ON ITEM TO CHARLES; REVOKE ALL ON PRICE FROM CHARLES; REVOKE ALL ON ITEM FROM CHARLES;
The syntax for ROLLBACK
is displayed in Figure 4-49.
ROLLBACK [{ WORK | TO savepoint_name }] ;
None.
Undoes work performed in the current synonym.
The arguments for the ROLLBACK
command are listed in Table 4-48.
Table 4-48 Arguments Used with the ROLLBACK Command
Argument | Description |
---|---|
work |
An optional argument supported to provide ANSI compatibility. |
TO |
An optional argument that enables you to roll back to a savepoint. |
savepoint_name |
The name of the savepoint you roll back to. |
If you are not already in a transaction, Oracle Lite starts one the first time you issue a SQL statement. All the statements you issue are considered part of the transaction until you use a COMMIT or ROLLBACK command.
The COMMIT command makes permanent changes to the data in the database, saving everything up to the start of the transaction. Before changes are committed, both the old and new data exist so that changes can be stored or the data can be restored to its prior state.
The ROLLBACK command discards pending changes made to the data in the current transaction, restoring the database to its state before the start of the transaction. You can ROLLBACK a portion of a transaction by identifying a SAVEPOINT.
Important:
Oracle Lite does not automatically commit DDL commands, except for CREATE DATABASE. DDL commands in Oracle Lite are subject to rollback.The following example inserts a new row into the DEPT table and then rolls back the transaction. This example returns the same results for both ROLLBACK and ROLLBACK WORK.
INSERT INTO DEPT (deptno, dname, loc) VALUES (50, 'Design', 'San Francisco'); SELECT * FROM dept;
Returns the following result:
DEPTNO DNAME LOC --------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 50 DESIGN SAN FRANCISCO ROLLBACK WORK; SELECT * FROM dept;
Returns the following result:
DEPTNO DNAME LOC --------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
Although the ROLLBACK command is not part of the ODBC SQL syntax, ODBC passes the command through to your database.
An ODBC program typically uses the API call SQLTransact()
with the SQL_ROLLBACK
flag.
For each row in the database, the ROWID
pseudocolumn returns a row address. A ROWID
value uniquely identifies a row in the database. Values of the ROWID
pseudocolumn have the datatype ROWID
.
None.
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.
They are unique identifiers for rows in a table.
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, Oracle Database Lite may reassign its ROWID
to a new row inserted later.
Although you can use the ROWID
pseudocolumn in the SELECT
and WHERE
clause 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, last_name FROM employees WHERE department_id = 20;
CURRVAL and NEXTVAL pseudocolumns, LEVEL pseudocolumn, ROWNUM pseudocolumn, CREATE SNAPSHOT TABLE
For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle Lite 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.
None.
If an ORDER BY clause follows ROWNUM in the same subquery, the rows are reordered by the ORDER BY clause. The results can vary depending on the way the rows are accessed. For example, if the ORDER BY clause causes Oracle Lite to use an index to access the data, Oracle Lite may retrieve the rows in a different order than without the index.
If you embed the ORDER BY clause in a subquery and place the ROWNUM condition in the top-level query, you can force the ROWNUM condition to be applied after the ordering of the rows. See Example 3.
The following example uses ROWNUM to limit the number of rows returned by a query:
SELECT * FROM emp WHERE ROWNUM < 10;
The following example follows the ORDER BY clause with ROWNUM in the same query. As a result, the rows are reordered by the ORDER BY clause and do not have the same effect as the preceding example:
SELECT * FROM emp WHERE ROWNUM < 11 ORDER BY empno;
The following query returns the ten smallest employee numbers. This is sometimes referred to as a "top-N query":
SELECT * FROM (SELECT empno FROM emp ORDER BY empno) WHERE ROWNUM < 11;
The following query returns no rows:
SELECT * FROM emp WHERE ROWNUM > 1;
The first fetched row 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, this makes the condition false. All rows subsequently fail to satisfy the condition, so no rows are returned.
The following statement assigns unique values to each row of a table:
UPDATE tabx SET col1 = ROWNUM;
CURRVAL and NEXTVAL pseudocolumns, LEVEL pseudocolumn, ROWID pseudocolumn, CREATE SNAPSHOT TABLE
The syntax for SAVEPOINT
is displayed in Figure 4-50.
SAVEPOINT savepoint_name ;
To identify a point in a transaction to which you can later roll back.
None.
Once you set a savepoint you can either roll back to it or remove it later. To roll back to a savepoint use the statement:
ROLLBACK TO <savepoint_name>
To remove a savepoint use the statement:
REMOVE SAVEPOINT <savepoint_name>
When you roll back to remove a savepoint, all nested savepoints are also rolled back or removed. Savepoints should be removed as soon as possible to reduce memory usage.
A user defined savepoint enables you to name and mark the current point in the processing of a transaction. Used with ROLLBACK, SAVEPOINT lets you undo parts of a transaction instead of the entire transaction. When you roll back to a savepoint, any savepoint marked after that savepoint is erased. The COMMIT statement erases any savepoints marked since the last commit or rollback.
The number of active savepoints you define for each session is unlimited. An active savepoint is one marked since the last commit or rollback.
The following example updates the salary for two employees, Blake and Clark. It then checks the total salary in the EMP table. The example rolls back to savepoints for each employee's salary, and updates Clark's salary.
UPDATE emp SET sal = 2000 WHERE ename = 'BLAKE'; SAVEPOINT blake_sal; UPDATE emp SET sal = 1500 WHERE ename = 'CLARK'; SAVEPOINT clark_sal; SELECT SUM(sal) FROM emp; ROLLBACK TO SAVEPOINT blake_sal; UPDATE emp SET sal = 1300 WHERE ename = 'CLARK'; COMMIT;
The SELECT statement retrieves data from one or more tables or views. You can also use the select statement to invoke Java stored procedures. To select data from a table or view, you must be logged into the database as SYSTEM, or the table(s) and view(s) must be part of your schema.
select::=
The syntax for SELECT
is displayed in Figure 4-51.
subquery [order_by_clause] [ for_update_clause] ;
CONSTRAINT clause, DELETE, UPDATE
The following sections describe the different operations you can use within a select statement:
Section 4.3.46.10, "Select Statement Behavior When Retrieving BLOBs in a Read Committed Transaction"
The arguments for the SELECT
command are listed in Table 4-49.
Table 4-49 Arguments Used with the SELECT Command
Argument | Description |
---|---|
DISTINCT |
Returns only one copy of each set of duplicate rows selected. Duplicate rows are those with matching values for each expression in the select list. |
ALL |
Returns all rows selected, including all copies of duplicates. The default is ALL. |
* |
Selects all columns from all tables, views, or snapshots listed in the FROM clause. |
table.* |
Selects all columns from the selected table. Use the schema qualifier to select from a schema other than your own. |
view.* |
Selects all columns from the selected view. Use the schema qualifier to select from a schema other than your own. |
expr |
Selects an expression, usually based on column values, from one of the tables or views in the FROM clause. A column name in this list can be qualified with a schema only if the table or view that contains the column is itself qualified with a schema in the FROM clause. For more information, see Section 1.8, "Specifying Expressions". |
hint |
Hints are processed by the Oracle Database Lite optimizer to suggest choices for statement execution. See "The HINT Expression" for more information. |
/*+ ... */ |
Hint processed by both Oracle and Oracle Database Lite. |
/*% ...%*/ |
Hint processed as a comment in Oracle, processed by Oracle Database Lite. |
// ... // |
Hint processed by both Oracle and Oracle Database Lite. |
c_alias |
Provides a column alias, which is a different name for the column expression, and causes the column alias to be used in the column heading. A column alias does not affect the actual name of the column. The alias can only be used in the ORDER BY clause. It cannot be used by other clauses in the query. |
schema |
The schema that contains the selected table, view, or snapshot. If you omit schema, Oracle Lite assumes that the table, view, or snapshot resides in your own schema. |
table |
The table from which data is selected. |
view |
The view from which data is selected |
t_alias |
Provides a different name or alias for the table, view, or snapshot, for evaluating the query. Most often used in a correlated query. Other references to the table, view, or snapshot throughout the query must refer to the alias. |
WHERE |
Restricts the rows selected to those for which the specified condition is TRUE. If you omit the WHERE clause, Oracle Lite returns all rows from the tables, views, or snapshots in the FROM clause. WHERE specifies a conditional expression that evaluates to TRUE or FALSE. For more information, see Section 1.8, "Specifying Expressions". |
condition |
A search condition. For more information about creating a valid condition, see Section 1.7, "Specifying SQL Conditions". |
START WITH |
Returns rows in a hierarchical order. |
CONNECT BY |
Specifies the relationship between parent and child rows in a hierarchical query. The condition defines this relationship, and must use the PRIOR operator to refer to the parent row. To find the children of the parent row, Oracle Lite evaluates the PRIOR expression for each row in the table. Rows for which the condition is TRUE are the children of the parent. For more information, see the details of the PRIOR operator in Section 2.7, "Other Operators". |
GROUP BY |
Groups the selected rows based on the value of the expr argument for each row, and returns a single row of summary information for each group. |
HAVING |
Restricts the groups of rows returned to those groups for which the specified condition is TRUE. If you omit this clause, Oracle Lite returns summary rows for all groups. For more information, see Section 1.7, "Specifying SQL Conditions". |
INTERSECT |
Returns all distinct rows selected by both queries. INTERSECT has a higher precedence than UNION. |
INTERSECT ALL |
Returns all distinct rows selected by both queries, the same result as INTERSECT. This syntax is supported, but has no function. |
UNION |
Returns all distinct rows selected by either query. |
UNION ALL |
Returns all rows selected by either query, including duplicates. |
MINUS |
Returns all distinct rows selected by the first query but not the second. |
command |
Refers to all parameters of a SELECT command which is itself a parameter of another SELECT command. When entering parameters for a SELECT command within a SELECT command, you cannot use the WHERE statement. |
ORDER BY |
Orders rows returned by the SELECT statement, according to the following arguments: expr (expression) orders rows based on their value for expr. The expression is based on columns in the select list, or based on columns in the tables, views, or snapshots in the FROM clause. position orders rows based on their value for the expression in this position in the select list. ASC specifies an ascending sort order. ASC is the default. DESC specifies a descending sort order. |
FOR UPDATE |
Locks the selected rows. The column list in the FOR UPDATE clause is ignored. The FOR UPDATE clause can be used either before or after the ORDER BY clause. |
column |
The column to be updated. |
If you do not specify a WHERE clause and there is more than one table in the FROM clause, Oracle Lite computes a Cartesian product of all the tables involved.
You can use the LEVEL pseudocolumn in a SELECT statement to perform a hierarchical query. For more information, see Section 4.3.40, "LEVEL pseudocolumn". A hierarchical query cannot perform a join, nor can it select data from a view.
When you select columns with an expression, those columns must have an alias. An alias specifies names for the column expressions selected by the query. The number of aliases must match the number of expressions selected by the query. Aliases must be unique within the query.
The syntax for the subquery
expression is displayed in Figure 4-52.
{query_spec | "("subquery")" } [{ INTERSECT | INTERSECT ALL | UNION | UNION ALL | MINUS } {query_spec |"(" subquery ")" } ]
The syntax for the query_spec
expression is displayed in Figure 4-53.
SELECT [ hint ] [ { DISTINCT | ALL ]{ * | { [schema.] { table | view } .* | expr [[AS] c_alias] } [, { | [schema .] { table | view } .* | expr [[AS] c_alias] } ]...}FROM [schema .] { "("subquery [order_by_clause] ")" | table | view }[ t_alias ] [ WHERE condition][ { [ START WITH condition ] CONNECT BY condition | GROUP BY expr [, expr]... | [HAVING condition] }]
The syntax for the update_clause
expression is displayed in Figure 4-54.
Figure 4-54 The for_update_clause Expression
FOR UPDATE[OF [[schema .] { table | view } .] column [, [[schema .] { table | view } .] column]...]
The syntax for the order_by_clause
expression is displayed in Figure 4-55.
Figure 4-55 The order_by_clause Expression
ORDER BY { expr | position | c_alias } [ ASC | DESC ] [, { expr | position | c_alias } [ ASC | DESC ] ]...
The syntax for the table_reference
expression is displayed in Figure 4-56.
Figure 4-56 The table_reference Expression
{ [schema .] {table | view} | "("subquery [order_by_clause] ")"} [[AS] t_alias]
The syntax for the odbc_join_table
expression is displayed in Figure 4-57.
Figure 4-57 The odbc_join_table Expression
"{" OJ joined_table "}"
The syntax for the joined_table
expression is displayed in Figure 4-58.
"{" { table_reference | OJ table_refernce { LEFT | RIGHT } [OUTER] JOIN joined_table ON conditon }"}"
You can use comments in a SQL statement to pass instructions, or hints, to the Oracle Database Lite optimizer. The optimizer uses these hints as suggestions for choosing an execution plan for the statement.
A statement block can have only one comment containing hints, and that comment must follow the SELECT, UPDATE, INSERT, or DELETE keyword. The following syntax shows hints contained in the styles of comments that Oracle Database Lite supports within a statement block.
{DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]]... */
or
{DELETE|INSERT|SELECT|UPDATE} // hint [text] [hint[text]]... //
or
{DELETE|INSERT|SELECT|UPDATE} /*% hint [text] [hint[text]]...%*/
Where:
DELETE, INSERT, SELECT or UPDATE is a DELETE, INSERT, SELECT or UPDATE keyword that beings a statement block. Comments containing hints can appear only after these keywords. the /*+
, //
, or /*%
causes Oracle to interpret the comment as a list of hints. The plus sign must follow immediately after the comment delimiter and no space is permitted. However, the space between the plus sign and the hint is optional. If the comment contains multiple hints, then separate the hints by at least one space.
The text is other commenting text that can be interspersed with the hints. Oracle Database Lite treats mis-spelled hints as regular comments and does not return an error.
To share the same code between Oracle Database Lite and Oracle database and to specify a hint to Oracle Database Lite only, use the syntax /*% hint %*/
. To give hints to both Oracle Database Lite and Oracle optimizers, use the syntax /*+ hint */
.
{ {DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]]... */ | {DELETE|INSERT|SELECT|UPDATE} // hint [text] [hint[text]]... // | {DELETE|INSERT|SELECT|UPDATE} /*% hint [text] [hint[text]]... %*/ }
The ORDERED hint causes Oracle Database Lite to join tables in the order in which they appear in the FROM clause. If you omit the ORDERED hint from a SQL statement performing a join, then the optimizer chooses the order in which to join the tables. You can use the ORDERED hint to specify a join order if you know how the number of rows are selected from each table. You can choose an inner and outer table for best performance.
ordered_hint::=/*+ ORDERED */
The following query is an example of the use of the ORDERED hint:
SELECT /*+ORDERED */ o.order_id, c.customer_id, 1.unit_price * 1.quantity FROM customers c, order_items 1, orders o WHERE c.cust_last_name = ? AND o.customer_id = c.customer_id AND o.order_id = 1.order_id;
Index hints behave exactly the same and explicitly choose an index scan for the specified table. The following are Index hints:
INDEX
INDEX_ASC
INDEX_DESC
The keywords are supported for compatibility with Oracle Database Server.
index_hint::= table_name index_name
Where:
index_name
specifies an index name
table_name
specifies the name or alias of the table
Either name cannot be a qualified name, such as SYSTEM.EMP
. Only one index_name
can be provided for a given table_name
. If you provide more than one index name, then only the first one is selected for optimization.
For example:
SELECT /*+ INDEX (employees emp_department_ix)*/ employee_id, department_id FROM employees WHERE department_id > 50;
Note:
For full details on the INDEX hint, see the Oracle Database SQL Reference.Because client devices have software and hardware limitations—such as CPU, memory, screen size, and so on—you may wish to limit the number of rows returned from your SQL query, especially if the returned result set contains a huge number of rows. The retrieval of all rows could take a long time to complete and affect performance. Also, your application may not be able to display all results, due to the limitation of the device, the requirement of the business logic, or the slow response time of the query.
You can limit the number of rows returned by a query, as follows:
LIMIT clause: Enables you to return only a specified number of rows, so that you do not overwhelm the limitations of your device or application.
OFFSET clause: Enables you to start at a certain point within the returned result set.
ORDER BY clauses: Enables you to retrieve rows in a specified order.
Creating indexes: If you create the right indexes, the performance can be improved significantly for small devices.
Cursor_spec::=subquery [order_by_clause][for_update_clause][limit_clause] subquery::= see Section 4.3.46.2, "The SUBQUERY Expression" for more details limit_clause::={LIMIT number [offset_clause] | offset_clause} offset_clause::=OFFSET number
The LIMIT clause can be used to limit the number of rows returned by a query. LIMIT takes an integer constant between 0 and 4294967295, which specifies the maximum number of rows to return. The OFFSET clause takes an integer constant between 0 and 4294967295, which specifies the offset of the first row to return. If OFFSET clause is not present, it defaults to 0.
For example, the following SQL statement retrieves rows from 5 to 9:
SELECT * FROM table LIMIT 5 OFFSET 4;
With only the LIMIT argument, the value specifies the number of rows to return from the beginning of the result set. The following SQL statement retrieves rows from 1 to 5;
SELECT * FROM table LIMIT 5;
If the LIMIT argument is 0, the OFFSET value is ignored even if it was specified. The following SQL statement retrieves nothing:
SELECT * FROM table LIMIT 0 OFFSET 4;
If only the OFFSET clause is present, then there is not a limit on the number of rows returned. The following SQL statement retrieves rows starting from the second row of the result set:
SELECT * FROM table OFFSET 1;
You can use the ORDER BY clause together with LIMIT clause to constrain the order of the output rows. That is, when both the LIMIT and ORDER BY clauses are present in a statement, then the optimizer takes this into account when generating the execution plan. By creating indexes on the ORDER BY column(s), you can avoid inserting the whole result set into a temporary table and performing the sorting just to retrieve a few rows from the query. The EXPLAIN PLAN command can be used to see whether a sorting is performed when LIMIT and ORDER BY are used in a query. See Section 1.11, "Tuning SQL Statement Execution Performance With the EXPLAIN PLAN" for more information on the EXPLAIN PLAN.
Limit and Offset Clause Example
A customer uses an order entry application, where there is a product table with over 3,000 rows with a primary index on the product number. The user can select an individual product by scanning a barcode with a scanner, or by entering a product number manually in a text field. The script opens a cursor to select one product using the barcode or product number as an equality selection (both are indexed). In this case, Oracle Database Lite performs well. However, the database access is very slow in other actions. After a product is selected, the user can click a "next" or "prev" button to find the next or previous product number, with product number being the primary index. This is necessary because the customer often wants to view related items with similar product numbers.
The SQL statement when user clicks a "next" button is as follows:
SELECT * FROM PRODUCT WHERE PARTNUM > partnum ORDER BY PARTNUM;
Where partnum is the product number scanned or entered by the end user.
When the current product is the first one (in the index) doing a "next" takes a long time, since there are more than 3,000 rows that need to be sorted and returned by this query. On the other hand, the actual SQL statement when the user clicks a "prev" button is similar to the one above. In addition, when the current product is the last one or near the end of the product table, the response time is also slow for the same reason.
SELECT * FROM PRODUCT WHERE PARTNUM < partnum ORDER BY PARTNUM DESC;
Where partnum is the product number scanned or entered by the end user.
What the customer wants is a SELECT statement that will do the equivalent of "find the first few products where partnum > [value]
", so it reads a few records using the primary index, not 3000.
With the LIMIT clause, the customer can rewrite the query and use the LIMIT clause to limit the number of rows returned by the query, as follows:
SELECT * FROM PRODUCT WHERE PARTNUM > partnum ORDER BY PARTNUM LIMIT 5;
This limits the number of rows returned by this query to 5 rows. When an ORDER BY clause is used with proper indexes created, the performance is faster than the original query.
There is a change in behavior for some SELECT statements when all of the following conditions are met:
The transaction isolation level is READ_COMMITED
The SELECT statement does NOT contain the FOR UPDATE clause
The result set of the SELECT statement retrieves at least one binary or character large object—also known as BLOBs. The data type for the large object is either BLOB, CLOB, LONG, LONG RAW, LONG VARBINARY or RAW.
You may notice the following changes.
The Oracle Lite database provides a higher concurrency for large objects. That is, multiple READ COMMITTED transactions can simultaneously access the rows that are fetched and updated—as long as the same row is not currently updated by two or more transactions at the same time.
The Oracle Lite database copies every BLOB object selected by a NON UPDATE SELECT statement in a READ COMMITED transaction. It destroys the copy when the result set (or the statement) is closed. This reduces the lifetime of the BLOB objects selected to the lifetime of the result set. Once the result set or the statement is closed, the BLOB references are invalid.
SELECT FOR UPDATE is required for a READ COMMITTED transaction if you want to update BLOB and CLOB columns. If you do not use the FOR UPDATE clause, the transaction fails and no error is returned when you update the BLOB and CLOB columns.
For a non-blob columns such as integer, character, and so on, the SELECT FOR UPDATE is required for the update to work for a read committed transaction.
However, if you previously saved a BLOB reference in your program and subsequently use it to fetch the BLOB content, then this will no longer work in Oracle Database Lite Release 10.2.0.2 and later. For example, the following Java code will no longer work:
public static Connection getConnection()throws Exception { Connection conn; . . . return conn; } // // Assume the table definition to be: My_Blob_Tab (key varchar(50), bin blob); static int BUF_SIZE = 2048 static String SELECT_STATEMENT = ÒSelect * from My_Blob_Table where key = ?Ò; public static void ProcessBlob(String key, String file) throws Exception { Connection conn = getConnection(); PreparedStatement stmt = conn.prepareStatement( SELECT_STATEMENT); stmt.setString(1, key); ResultSet rset = stmt.executeQuery(); if (!rset.next()) return; Blob blob = rset.getBlob(1); stmt.close(); writeToFile(blob, file); // BLOB handle is invalid now!!! // If you move stmt.close() here the program will work conn.close(); } public static void writeToFile(Blob blob, String file) throws Exception { InputStream is = (oracle.lite.poljdbc.BLOB) blob). getBinaryStream(); FileOutputStream os = new FileOutputStream(new File(file)); byte[] buf = new byte[BUF_SIZE]; int len; . . . len = is.read(buf, 0, BUF_SIZE); // This will fail now!!! . . . }
The following examples demonstrate how you can use the select command:
SELECT * FROM EMP WHERE SAL = 1300;
Returns the following result:
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO --------- ---------- --------- --------- --------- --------- --------- --------- 7782 CLARK MANAGER 7839 1981-06-0 1300 10 7934 MILLER CLERK 7782 1982-01-2 1300 10
SELECT 'ID=',EMPNO, 'Name=',ENAME, 'Dept=',DEPTNO FROM EMP ORDER BY DEPTNO;
Returns the following result:
'ID EMPNO 'NAME ENAME 'DEPT DEPTNO --- --------- ----- ---------- ----- --------- ID= 7839 Name= KING Dept= 10 ID= 7934 Name= MILLER Dept= 10 ID= 7782 Name= CLARK Dept= 10 ID= 7566 Name= JONES Dept= 20 ID= 7876 Name= ADAMS Dept= 20 ID= 7788 Name= SCOTT Dept= 20 ID= 7369 Name= SMITH Dept= 20 ID= 7902 Name= FORD Dept= 20 ID= 7521 Name= WARD Dept= 30 ID= 7900 Name= JAMES Dept= 30 ID= 7844 Name= TURNER Dept= 30 ID= 7499 Name= ALLEN Dept= 30 ID= 7654 Name= MARTIN Dept= 30 ID= 7698 Name= BLAKE Dept= 30 14 rows selected.
SELECT 'ID=', EMPNO, 'Name=', ENAME, 'Dept=', DEPTNO FROM EMP WHERE SAL >= 1300;
Returns the following result:
'ID EMPNO 'NAME ENAME 'DEPT DEPTNO --- --------- ----- ---------- ----- --------- ID= 7839 Name= KING Dept= 10 ID= 7698 Name= BLAKE Dept= 30 ID= 7782 Name= CLARK Dept= 10 ID= 7566 Name= JONES Dept= 20 ID= 7499 Name= ALLEN Dept= 30 ID= 7844 Name= TURNER Dept= 30 ID= 7902 Name= FORD Dept= 20 ID= 7788 Name= SCOTT Dept= 20 ID= 7934 Name= MILLER Dept= 10 9 rows selected.
SELECT * FROM (SELECT ENAME FROM EMP WHERE JOB = 'CLERK' UNION SELECT ENAME FROM EMP WHERE JOB = 'ANALYST');
Returns the following result:
ENAME ---------- ADAMS FORD JAMES MILLER SCOTT SMITH
In this example, the "ordered" hint selects the EMP table as the outermost table in the join ordering. The optimizer still attempts to pick the best possible indexes to use for execution. All other optimizations, such as view replacement and subquery unnesting are still attempted.
Select //ordered// Eno, Ename, Loc from Emp, Dept where Dept.DeptNo = Emp.DeptNo and Emp.Sal > 50000;
In this example, the hint joins the tables (Product, Item, and Ord) in the given order: Product, Item, and Ord. The hint is limited only to the subquery.
Select CustId, Name, Phone from Customer Where CustId In ( Select //ordered// Ord.CustId from Product, Item, Ord
Where Ord.OrdId = Item.OrdId And
Item.ProdId = Product.ProdId And Product.Descrip like '%TENNIS%')
The syntax for SET TRANSACTION
is displayed in Figure 4-59.
SET TRANSACTION ISOLATION LEVEL{ READ COMMITTED | REPEATABLE READ | SERIALIZABLE | SINGLE USER};
If you use a SET TRANSACTION statement, it must be the first statement in your transaction. However, a transaction need not have a SET TRANSACTION statement.
Note:
Oracle Lite implicitly commits the current transaction before and after executing a data definition language statement.Establishes the isolation level of the current transaction.
The arguments for the SET TRANSACTION command are listed in Table 4-50.
Table 4-50 Arguments Used with the SET TRANSACTION Command
Argument | Description |
---|---|
SET TRANSACTION |
Establishes the isolation level of the current transaction. The operations performed by a SET TRANSACTION statement affect only your current transaction, not other users or other transactions. Your transaction ends whenever you issue a COMMIT or ROLLBACK statement. |
ISOLATION LEVEL |
Specifies how transactions containing database modifications are handled. |
READ COMMITTED |
An isolation level. The transaction does not take place until rows write locked by other transactions are unlocked. The transaction holds a read lock when it reads the current row and a write lock when it updates or deletes the current row. This prevents other transactions from updating or deleting it. The transaction releases read locks when it moves off the current row, and releases write locks when it is either committed or rolled back. Note: If using any large object, such as a BLOB, see Section 4.3.46.10, "Select Statement Behavior When Retrieving BLOBs in a Read Committed Transaction". |
REPEATABLE READ |
An isolation level. The transaction does not take place until rows write locked by other transactions are unlocked. The transaction maintains read locks on all rows it returns to the application, and maintains write locks on all rows it inserts, updates, or deletes. The transaction only releases its locks when it is committed or rolled back. |
SERIALIZABLE |
An isolation level. The transaction does not take place until rows write locked by other transactions are unlocked. The transaction holds a read lock when it reads a range of rows and a write lock when it updates or deletes a range of rows. This prevents other transactions from updating or deleting the rows. |
SINGLEUSER |
An isolation level. The transaction has no locks and therefore consumes less memory. This is recommended for bulk loading of the database. |
None.
SET TRANSACTION ISOLATION LEVEL SINGLEUSER;
The syntax for TRUNCATE TABLE
is displayed in Figure 4-60.
TRUNCATE TABLE [schema .] table ;
This command deletes all rows from the table. The statement is provided to be compatible with Oracle database. This statement performs the same action as the following:
DELETE FROM table_name ;
The arguments for the TRUNCATE TABLE
command are listed in Table 4-51.
Table 4-51 Arguments Used with the TRUNCATE TABLE Command
Argument | Description |
---|---|
schema |
The schema that contains the table. |
table |
The name of the table to be truncated. |
A table cannot be truncated if it has a primary key and there are rows in the dependent tables.
TRUNCATE TABLE emp;
The syntax for UPDATE
is displayed in Figure 4-61.
UPDATE [schema .] { table | view} [ alias ] SET column = { expr | subquery } [, column = { expr | subquery }]...[WHERE condition] ;
To update existing values in a database table or view, you must be logged into the database as SYSTEM, or the table(s) and view(s) must be part of your schema.
Changes existing values in a table or in a view's base table.
The arguments for the UPDATE
command are listed in Table 4-52.
Table 4-52 Arguments Used with the UPDATE Command
Argument | Description |
---|---|
schema |
The schema that contains the table or view. If you omit schema, Oracle Lite assumes that the table or view resides in your own schema. |
table |
The name of the table to be updated. |
view |
The name of the view whose base tables you want to update. |
alias |
Relabels the name of the table or view in the other clauses of the UPDATE command. |
SET |
Indicates that the columns that follow be set to specific values. |
column |
The name of a column of the table or view to be updated. If you omit one of the table's columns in the SET clause, that column's value remains unchanged. |
expr |
The new values assigned to the corresponding column. This can contain host variables. |
subquery |
The subquery to be updated. |
WHERE |
Restricts the rows updated to those for which the specified condition is TRUE. If you omit the WHERE clause, Oracle Lite updates all rows in the table or view. |
condition |
A search condition. For more information about creating a valid condition, see Section 1.7, "Specifying SQL Conditions". |
The same column name may not appear more than once in the SET clause.
If no WHERE clause is specified, then all rows of the table are updated.
A positioned UPDATE requires that the cursor be updatable.
UPDATE EMP SET SAL = SAL * .45 WHERE JOB = 'PRESIDENT';
The ODBC SQL syntax for UPDATE is the same as specified. In addition, the following syntax is supported:
WHERE CURRENT OF CURSOR cursor_name