Skip Headers

Oracle® Database Lite SQL Reference Release 10.3 Part Number E12092-02 |
Home | Book List | Contents | Index | Contact Us

Previous | Next
| View PDF

# 4 SQL Commands

This document discusses SQL commands used by Oracle Database Lite. Topics include:

- Section 4.1, "SQL Command Types"

- Section 4.2, "SQL Commands Overview"

- Section 4.3, "SQL Commands Alphabetical Listing"

## 4.1 SQL Command Types

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".

SQL Commands

Table 4-1 Data Definition Language (DDL) Commands

DDL | DDL | DDL
--- | --- | ---
ALTER SEQUENCE | CREATE PROCEDURE | DROP INDEX
ALTER SESSION | CREATE SCHEMA | DROP JAVA
ALTER TABLE | CREATE SEQUENCE | DROP PROCEDURE
ALTER TRIGGER | CREATE SYNONYM | DROP SCHEMA
ALTER USER | GRANT | DROP SEQUENCE
ALTER VIEW | REVOKE | DROP SYNONYM
CREATE DATABASE | CREATE TABLE | DROP TABLE
CREATE FUNCTION | CREATE TRIGGER | DROP TRIGGER
CREATE GLOBAL TEMPORARY TABLE | CREATE USER | DROP USER
CREATE INDEX | CREATE VIEW | DROP VIEW
CREATE JAVA | DROP FUNCTION | TRUNCATE TABLE

Table 4-2 Data Manipulation Language (DML)

DML | DML
--- | ---
DELETE | SELECT
EXPLAIN PLAN | subquery::=
INSERT | UPDATE

Table 4-3 Transaction Control Commands

Command | Command
--- | ---
COMMIT | SAVEPOINT
ROLLBACK | SET TRANSACTION

Table 4-4 Clauses

Clause | Clause
--- | ---
CONSTRAINT clause | DROP clause

Table 4-5 Pseudocolumns

Pseudocolumns | Pseudocolumns
--- | ---
CURRVAL and NEXTVAL pseudocolumns | CREATE SNAPSHOT TABLE
ROWNUM pseudocolumn | ROWID pseudocolumn
LEVEL pseudocolumn |

## 4.2 SQL Commands Overview

Oracle Database Lite uses several different types of SQL commands. This section discusses the different types of SQL commands.

### 4.2.1 Data Definition Language (DDL) 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.

### 4.2.2 Data Manipulation Language (DML) Commands

Data manipulation language (DML) commands query and manipulate data in existing schema objects. These commands do not implicitly commit the current transaction.

### 4.2.3 Transaction Control Commands

Transaction control commands manage changes made by DML commands.

### 4.2.4 Clauses

Clauses are subsets of commands that modify the command.

### 4.2.5 Pseudocolumns

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.

### 4.2.6 BNF Notation Conventions

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.

## 4.3 SQL Commands Alphabetical Listing

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 the `ROLLBACK` command after each DDL example that you try in the database.

Table 4-6

SQL Commands | In Alphabetical | Order |  |
--- | --- | --- | --- | ---
ALTER SEQUENCE | ALTER SESSION | ALTER TABLE | ALTER TRIGGER | ALTER USER
ALTER VIEW | COMMIT | CONSTRAINT clause | CREATE DATABASE | CREATE FUNCTION
CREATE GLOBAL TEMPORARY TABLE | CREATE INDEX | CREATE JAVA | CREATE PROCEDURE | CREATE SCHEMA
CREATE SEQUENCE | CREATE SYNONYM | CREATE TABLE | CREATE SNAPSHOT TABLE | CREATE TRIGGER
CREATE USER | CREATE VIEW | CURRVAL and NEXTVAL pseudocolumns | DELETE | DROP clause
DROP FUNCTION | DROP INDEX | DROP JAVA | DROP PROCEDURE | DROP SCHEMA
DROP SEQUENCE | DROP SYNONYM | DROP TABLE | DROP TRIGGER | DROP USER
DROP VIEW | EXPLAIN PLAN | GRANT | INSERT | LEVEL pseudocolumn
REVOKE | ROLLBACK | ROWID pseudocolumn | ROWNUM pseudocolumn | SAVEPOINT
SELECT | SET TRANSACTION | TRUNCATE TABLE | UPDATE |

### 4.3.1 ALTER SEQUENCE

Syntax

The syntax for the `ALTER SEQUENCE` command is displayed in Figure 4-1.

Figure 4-1 The ALTER SEQUENCE Command

Description of "Figure 4-1 The ALTER SEQUENCE Command"

BNF Notation

    ALTER SEQUENCE [schema .] sequence  [(INCREMENT BY "integer"   | (MAXVALUE "integer" | NOMAXVALUE)   | (MINVALUE "integer" | NOMINVALUE)
      ]
    ;

Prerequisite

The sequence must be in your own schema.

Purpose

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` and `MINVALUE`. If you omit the `INCREMENT BY` clause, the default is 1.
MAXVALUE | Specifies the maximum value the sequence can generate. This integer value can have 10 or fewer digits. `MAXVALUE` must be greater than `MINVALUE`.
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. `MINVALUE` must be less than `MAXVALUE`.
NOMINVALUE | Specifies a minimum value of 1 for an ascending sequence or –2147483647 for a descending sequence.

Usage Notes

- 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.

Example

This statement sets a new maximum value for the ESEQ sequence.

    ALTER SEQUENCE eseq MAXVALUE 1500

ODBC 2.0

Although the `ALTER SEQUENCE` command is not part of ODBC SQL; ODBC passes the command through to your database.

Related Topics

CREATE SEQUENCE, DROP SEQUENCE

### 4.3.2 ALTER SESSION

Syntax

The syntax for the `ALTER SESSION` command is displayed in Figure 4-2.

Figure 4-2 The ALTER SESSION Command

Description of "Figure 4-2 The ALTER SESSION Command"

BNF Notation

    ALTER SESSION SET nls_date_format = nls_date_value ;

Prerequisite

None

Purpose

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 `ALTER SESSION` command has only one parameter name: `NLS_DATE_FORMAT`.
parameter_value | The NLS date format. For example: `YYYY MM DD HH24:MI:SS`.

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

### 4.3.3 ALTER TABLE

Syntax

The syntax for `ALTER TABLE` is displayed in Figure 4-3.

Figure 4-3 The ALTER TABLE Command

Description of "Figure 4-3 The ALTER TABLE Command"

BNF Notation

    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
    }
    ;

add_column_list::=

The syntax for the `add_column_list` expression is displayed in Figure 4-4.

Figure 4-4 The add_column_list Expression

Description of "Figure 4-4 The add_column_list Expression"

BNF Notation

    [COLUMN] "("column datatype [DEFAULT expr] [column_constraint]
    [, column_constraint]...")" [, [COLUMN] "("column datatype [DEFAULT expr]
    [column_constraint] [, column_constraint]...")"]...

modify_column_option::=

The syntax for `modify_column_option` expression is displayed in Figure 4-5.

Figure 4-5 The modify_column_option Expression

Description of "Figure 4-5 The modify_column_option Expression"

BNF Notation

    column [datatype] [DEFAULT { literal | USER | SYSDATE }] [ NULL | NOT NULL ]
          [,  column [ [datatype] [DEFAULT { literal | USER | SYSDATE }]
           [ NULL | NOT NULL ] ] ]...

constraint_state::=

The syntax for `constraint_state` expression is displayed in Figure 4-6.

Figure 4-6 The constraint_state Expression

Description of "Figure 4-6 The constraint_state Expression"

BNF Notation

    ([ENABLE | DISABLE] [VALIDATE | NOVALIDATE])

Prerequisite

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.

Purpose

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: `DEFAULT NULL`, `DEFAULT USER` (the user name when the table is created), `DEFAULT` literal `ODBC FUNCTIONS` - `TIMESTAMPADD`, `TIMESTAMPDIFF`, `DATABASE`, `USER` `SQL FUNCTIONS` - `CURRENT_DATE`, `CURRENT_TIME`, `CURRENT_TIMESTAMP`, `SYSDATE` For more information about expressions, see Section 1.8, "Specifying Expressions".
expr | A valid expression. Expressions are evaluated when `ALTER TABLE` is executed, not when a row is inserted with a default value. For more information, see Section 1.8, "Specifying Expressions".
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".
`ATTACH JAVA` | Attaches a Java class or source file to the database table.
`IN` | Indicates that the Java class or source file must be attached in either a database, Java class, or source path.
`DATABASE` | The database in which you attach the Java class or source path.
`DETACH` | Detaches a Java class from the database table.
`CLASS` | Specifies a Java class.
`SOURCE` | 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.
`WITH CONSTRUCTOR ARGS` | 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.
`AND DELETE` | Deletes the Java class from the database.
class_name | The name of a fully qualified Java class.
`ENABLE ALL TRIGGERS` | Enables all triggers associated with the table. The triggers are fired whenever their triggering condition is satisfied. To enable a single trigger, use the `ENABLE` clause of `ALTER TRIGGER`. See ALTER TRIGGER.
`DISABLE ALL TRIGGERS` | 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 `DISABLE` clause of `ALTER TRIGGER`. See ALTER TRIGGER.
`MODIFY` | This specifies a new default for an existing column. Oracle Database Lite assigns this value to the column if a subsequent `INSERT` statement omits a value for the column. The datatype of the default value must match the datatype specified for the column. The column must also be long enough to hold the default value.
`modify_column_option` | This modifies the definition of an existing column. Any of the optional parts of the column definition, datatype, default value (literal, `USER`, or `SYSDATE`) or column constraint state (`NULL`, `NOT NULL`) which are omitted remain unchanged. Existing datatypes can be changed to a new datatype as long as the existing data is such that the data conversion does not produce any conversion errors. Increasing the size of a varchar column whose existing size is greater than 15 characters does not require any data conversion. All other changes require a data conversion step. Each column is converted individually. Each datatype change involves a rewrite of all objects and creation of all dependent indexes. A column undergoing datatype alteration which is part of an index created using the `KEY COLUMNS` clause, may cause the `ALTER TABLE MODIFY` command to fail because the index recreation is unable to reestablish the `KEY COLUMNS` option. An index created using `KEY COLUMNS`, should be dropped before modifying the column.
`CONSTRAINT` | Modifies the state of an existing constraint. `ENABLE` specifies that the constraint is applied to all new data in the table. Before a referential integrity constraint can be enabled, its referenced constraint must be enabled.
`ENABLE VALIDATE` | 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 `ENABLE VALIDATE` mode, validation ensures that primary key columns contain no nulls. If `VALIDATE` or `NOVALIDATE` are omitted, the default is `VALIDATE`.
`ENABLE NOVALIDATE` | 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.
`DISABLE VALIDATE` | 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 `VALIDATE` or `NOVALIDATE` are omitted, the default is `NOVALIDATE`.
`DISABLE NOVALIDATE` | 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 `DISABLE NOVALIDATE` state.

Usage Notes

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.

Example

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));

Related Topics

CONSTRAINT clause, CREATE TABLE, CREATE VIEW

### 4.3.4 ALTER TRIGGER

Syntax

The syntax for the `ALTER TRIGGER` command is displayed in Figure 4-7.

Figure 4-7 The ALTER TRIGGER Command

Description of "Figure 4-7 The ALTER TRIGGER Command"

BNF Notation

    ALTER TRIGGER [schema .] trigger { ENABLE | DISABLE };

Prerequisites

To alter a trigger you must have the DBA/DDL privilege.

Purpose

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 the `CREATE 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.
`ENABLE` | Enables the trigger. You can also use the `ENABLE ALL TRIGGERS` clause of `ALTER TABLE` to enable all triggers associated with a table. See ALTER TABLE.
`DISABLE` | Disables the trigger. You can also use the `DISABLE ALL TRIGGERS` clause of `ALTER TABLE` to disable all triggers associated with a table. See ALTER TABLE.

Examples

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.

Related Topics

CREATE TRIGGER

### 4.3.5 ALTER USER

Syntax

The syntax for `ALTER USER` is displayed in Figure 4-8.

Figure 4-8 The ALTER USER Command

Description of "Figure 4-8 The ALTER USER Command"

BNF Notation

    ALTER USER user IDENTIFIED BY password ;

Prerequisite

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.

Purpose

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.
`IDENTIFIED BY` | 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.

Example

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;

Related Topics

CREATE USER, DROP USER

### 4.3.6 ALTER VIEW

Syntax

The syntax for the `ALTER VIEW` command is displayed in Figure 4-9.

Figure 4-9 The ALTER VIEW Command

Description of "Figure 4-9 The ALTER VIEW Command"

BNF Notation

    ALTER VIEW [schema .] view COMPILE ;

Prerequisite

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.

Purpose

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 `COMPILE` keyword is required.

Usage Notes

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.

Example

The following code demonstrates the `ALTER VIEW` SQL command. The `COMPILE` keyword is required.

    ALTER VIEW customer_view COMPILE;

Related Topics

CREATE VIEW, DROP VIEW

### 4.3.7 COMMIT

Syntax

The syntax for `COMMIT` is displayed in Figure 4-10.

Figure 4-10 The COMMIT Command

Description of "Figure 4-10 The COMMIT Command"

BNF Notation

    COMMIT [WORK] ;

Prerequisite

None

Purpose

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
--- | ---
`WORK` | An optional argument with no effect. `WORK` is supported only for compliance with standard SQL. The statements `COMMIT` and `COMMIT WORK` are equivalent.

Usage Notes

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.

Example

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;

ODBC 2.0

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.

Related Topics

ROLLBACK

### 4.3.8 CONSTRAINT clause

Syntax

The syntax for the `COLUMN CONSTRAINT` clause is displayed in Figure 4-11.

Figure 4-11 The COLUMN_CONSTRAINT Clause

Description of "Figure 4-11 The COLUMN_CONSTRAINT Clause"

BNF Notation

    [CONSTRAINT constraint] { [NOT] NULL    | {UNIQUE | PRIMARY KEY}     | REFERENCES [schema .] table ["("column")"] [ON DELETE CASCADE]    | CHECK "(" condition ")" }

Syntax

The syntax for the `TABLE CONSTRAINT` clause is displayed in Figure 4-12.

Figure 4-12 The TABLE CONSTRAINT Clause

Description of "Figure 4-12 The TABLE CONSTRAINT Clause"

BNF Notation

    [CONSTRAINT constraint]{

     { UNIQUE | PRIMARY KEY } "("column [, column] ...")" [ KEY COLUMNS = number ]
     | FOREIGN KEY "("column [, column] ...")" REFERENCES [ schema .] table   "("column [, column] ...")" [ON DELETE CASCADE]
     | CHECK "("condition")"}

Prerequisite

`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.

Purpose

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
--- | ---
`CONSTRAINT` | 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` keyword, Oracle Database Lite generates a name with this form: `POL_SYS_CONS` `n`, where n is an integer that makes the name unique within the database.
constraint | The name of the constraint being added.
`NULL` | Specifies that a column can contain null values.
`NOT NULL` | Specifies that a column cannot contain null values. By default, a column can contain nulls.
`UNIQUE` | Designates a column, or a combination of columns, as a unique key.
`PRIMARY KEY` | Designates a column, or a combination of columns, as the table's primary key.
`KEY COLUMNS =` | 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 `KEY COLUMNS` value, since the database looks up all qualifying rows to find the matching row(s).
number | An integer which specifies the number of `KEY COLUMNS`.
`FOREIGN KEY` | 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.
`REFERENCES` | 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.
`ON DELETE CASCADE` | 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.
`CHECK` | Specifies that a condition be checked for each row in the table. Oracle Database Lite only supports the following operators and functions in `CHECK` conditions. `+ - / * = ! = < > < = > = IS NULL, LIKE, BETWEEN, TO_CHAR` `TO_NUMBER, TO_DATE, TRANSLATE`
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".

Example

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));

Related Topics

ALTER TABLE, CREATE TABLE

### 4.3.9 CREATE DATABASE

Syntax

The syntax for `CREATE DATABASE` is displayed in Figure 4-13.

Figure 4-13 The CREATE DATABASE Command

Description of "Figure 4-13 The CREATE DATABASE Command"

BNF Notation

    CREATE DATABASE database database_parameter [, database_parameter]...;

database_parameters::=

The syntax for the `database_parameters` expression is displayed in Figure 4-14.

Figure 4-14 The database_parameters Expression

Description of "Figure 4-14 The database_parameters Expression"

BNF Notation

    {|USER SYSTEM IDENTIFIED BY password
      |DATABASE_ID database_id
      |DATABASE_SIZE max_bytes
      |EXTENT_SIZE npages
     }
    ;

Prerequisite

None

Purpose

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.
`USER SYSTEM IDENTIFIED BY` | Use this clause to establish a password for the `SYSTEM` user. This clause is not mandatory. By default, the Oracle Database creates the default password as manager for the SYSTEM user. You can modify the passwords using the ALTER USER statement.
`DATABASE_ID` | 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 `POLITE.INI` file indicates the next available database ID. It is possible to create two databases with the same database ID; however, you cannot connect to both databases at the same time.
`DATABASE_SIZE` | 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.
`EXTENT_SIZE` | 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.

Usage Notes

- 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 Appendix E, "POLITE.INI Parameters" in the Oracle Database Lite Administration and Deployment Guide.

Example

To create the data file LIN.ODB in the directory C:\TMP with the .ODB file extension, use.

    CREATE DATABASE "C:\TMP\LIN"

Related Topics

ROLLBACK

### 4.3.10 CREATE FUNCTION

Syntax

The syntax for `CREATE FUNCTION` is displayed in Figure 4-15.

Figure 4-15 The CREATE FUNCTION Command

Description of "Figure 4-15 The CREATE FUNCTION Command"

BNF Notation

    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];

call_spec::=

The syntax for the `call_spec` expression is displayed in Figure 4-22.

Figure 4-16 The call_spec Expression used with CREATE PROCEDURE

Description of "Figure 4-16 The call_spec Expression used with CREATE PROCEDURE"

BNF Notation

    LANGUAGE Declaration

The following defines the Declarations for Java, C++ or .NET:

- Java Declaration::=

- C++ Declaration::=

- .NET Declaration::=

Java Declaration::=

The syntax for the Java `Declaration` expression is displayed in Figure 4-23.

Figure 4-17 The Java Declaration Expression used with CREATE PROCEDURE

Description of "Figure 4-17 The Java Declaration Expression used with CREATE PROCEDURE"

BNF Notation

    JAVA NAME . string .

C++ Declaration::=

The syntax for the C++ `Declaration` expression is as follows:

    CPLUSPLUS NAME <libName>::<funcName>

Where:

- `<libName>` is the name of the library where the procedure is defined—without the `.DLL` or `.SO` extension

- `<funcName>` is the name of the procedure and function

.NET Declaration::=

Before you execute the `CREATE FUNCTION` statement for a .NET class, first create the .Net class by performing the `CREATE DOTNET CLASS` SQL statement. The following is an example of how to create the .NET class with the `CREATE DOTNET CLASS` SQL function where `NSPLib.dll` is the Assembly name and `SPClass` is the class name. The class name can be qualified with namespace names.

    create dotnet class using bfile('NSPLib.dll', 'SPClass');

Then execute the `CREATE FUNCTION` SQL statement for `LANGUAGE DOTNET`.

The syntax for the .Net `Declaration` expression is as follows:

    DOTNET NAME <className>.<methodName>

Where:

- `<className>` is the name of the library where the procedure is defined.

- `<methodName>` is the name of the method within the class.

The following `CREATE FUNCTION` statement creates the .NET PL/SQL procedure where the `NAME` is defined with the concatenation of `<className>.<methodName>`. In this example, the `<className>` is `SPClass` and the `<methodName>` is `bu1`.

    create procedure bu1(oc1 int, nc1 int, oc2 int, nc2 int) as language dotnet name 'SPClass.bu1';

Prerequisite

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.

Purpose

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
--- | ---
`OR REPLACE` | 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 `DISABLED`.
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.
`IN` | Specifies that you must supply a value for the argument when calling the function. This is the default.
`OUT` | Specifies that the function sets the value of the argument.
`IN OUT` | Specifies that a value for the argument can be supplied by you and may be set by the function. 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 function 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 `NOCOPY` only when these effects do not matter.
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.
`RETURN` datatype | 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.
`IS` | Associates the SQL identifier with the Java method.
`AS` | 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.
`LANGUAGE` | Specifies the call_spec language. In Oracle database this can be C or Java. In Oracle Database Lite, this can only be Java, C++ or .NET.
`Declaration` | Specifies the call_spec language. In Oracle database this can be C or Java. In Oracle Database Lite, this can only be Java, C++ or .NET.
`NAME` | The method name
string | Identifies the Java implementation of the method. For more information, see the Oracle Database Lite Developer's Guide.

Usage Notes

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.

Example

The following example provides complete instructions for creating and testing a function.

1. 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 */
       }

2. 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');

3. 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';
       .
       /

4. 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

Related Topics

DROP FUNCTION

### 4.3.11 CREATE GLOBAL TEMPORARY TABLE

Syntax

The syntax for the `CREATE GLOBAL TEMPORARY TABLE` command is displayed in Figure 4-18.

Figure 4-18 The CREATE GLOBAL TEMPORARY TABLE Command

Description of "Figure 4-18 The CREATE GLOBAL TEMPORARY TABLE Command"

BNF Notation

    CREATE GLOBAL TEMPORARY TABLE table
    "("  column datatype [DEFAULT expr] [{ NULL | NOT NULL}]
      [,  column datatype [DEFAULT expr] [ {NULL | NOT NULL} ]... ")"
    ON COMMIT {DELETE | PRESERVE } ROWS ;

Purpose

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.
`DEFAULT` | Specifies a default value expr (expression) for the new column. It can be one of the following: `DEFAULT NULL`, `DEFAULT USER` (the user name when the table is created), `DEFAULT` literal `ODBC FUNCTIONS - TIMESTAMPADD, TIMESTAMPDIFF, DATABASE, USER` `SQL FUNCTIONS - CURRENT_DATE, CURRENT_TIME, CURRRENT_TIMESTAMP, SYSDATE` For more information about expressions, see Section 1.7, "Specifying SQL Conditions".

Usage Notes

Temporary tables cannot be partitioned, organized into an index, or clustered.

You cannot specify any referential integrity (foreign key) constraints on temporary tables.

Examples

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;

### 4.3.12 CREATE INDEX

Syntax

The syntax for the `CREATE INDEX` command is displayed in Figure 4-19.

Figure 4-19 The CREATE INDEX Command

Description of "Figure 4-19 The CREATE INDEX Command"

BNF Notation

    CREATE [ UNIQUE ] INDEX [schema .] index ON
    [schema .] table
    "(" column [ ASC | DESC]
     [, column [ ASC | DESC]]...
     ")"
    [ KEY COLUMNS=number]
    ;

Prerequisite

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.

Purpose

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
--- | ---
`UNIQUE` | Designates the specified column or combination of columns as a unique key.
schema | When it follows `CREATE INDEX`, this is the schema that contains the index. If you omit schema, Oracle Database Lite creates the index in your own schema. When used in the `ON` clause, the schema that contains the table for which the index is created.
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 `LONG` or `LONG RAW`.
`ASC | DESC` | Provided for DB2 compatibility only. Indexes are always created in ascending order.
`KEY COLUMNS =` | 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 `KEY COLUMNS` value. The database looks up all qualifying rows to find the matching row(s).
number | An integer which specifies the number of `KEY COLUMNS`.

Usage Notes

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.

Example

The following example creates an index on the `SAL` column of the `EMP` table.

`CREATE INDEX SAL_INDEX ON EMP(SAL);`

Related Topics

CONSTRAINT clause, CREATE TABLE, DROP INDEX

### 4.3.13 CREATE JAVA

Syntax

The syntax for `CREATE JAVA` is displayed in Figure 4-20.

Figure 4-20 The CREATE JAVA Command

Description of "Figure 4-20 The CREATE JAVA Command"

BNF Notation

    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
     };

Prerequisite

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.

Purpose

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
--- | ---
`OR REPLACE` | 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 `RESOLVE` or `COMPILE`, Oracle Database Lite recognizes but ignores those parameters. Users, previously granted privileges on a redefined function, can still access the function. You do need to re-grant privileges to the users.
`RESOLVE | COMPILE` | 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. When applied to a class, resolution of referenced names to other class schema objects occurs. When applied to a source, source compilation occurs. Restriction: You cannot specify this clause for a Java resource.
`NOFORCE` | Oracle Database Lite recognizes but ignores this parameter. In Oracle `NO FORCE` rolls back the results of this `CREATE` command if you have specified either `RESOLVE OR COMPILE`, and the resolution or compilation fails. If you do not specify this option, Oracle takes no action if the resolution or compilation fails (that is, the created schema object remains).
`CLASS` | Loads a Java class file.
`RESOURCE` | Loads a Java resource file.
`SOURCE` | Loads a Java source file. Requires the use of the AS source_text clause.
`NAMED` | Oracle Database Lite recognizes but ignores this parameter. In Oracle, it is required for a Java source or resource. For a Java source, this clause specifies the name of the schema object in which the source code is held. A successful `CREATE JAVA SOURCE` statement also creates additional schema objects to hold each of the Java classes defined by the source. For a Java resource, this clause specifies the name of the schema object to hold the Java resource. If you do not specify schema,Oracle creates the object in your own schema. Restrictions: You cannot specify `NAMED` for a Java class. The primary_name cannot contain a database link.
`SCHEMA` schema | 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 `SCHEMA` and you do not specify `NAMED` (above), Oracle creates the object in your own schema.
invoker_rights_clause | For compatibility with Oracle, Oracle Database Lite recognizes but does not enforce the invoker_rights_clause.
`RESOLVER` | 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: match_string is either a fully qualified Java name, a wildcard that can match such a Java name, or a wildcard that can match any name. schema_name designates a schema to be searched for the corresponding Java schema object. A dash (-) as an alternative to schema_name indicates that if match_string matches a valid Java name, Oracle can leave the schema unresolved. The resolution succeeds, but the name cannot be used at run time by the class. 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 `ALTER...RESOLVE` statements).
AS source_text | A text of a Java source program.
`USING BFILE` | Identifies the format of the class file. `BFILE` is interpreted as a binary file by the `CREATE JAVA CLASS` or `CREATE JAVA RESOURCE`.

Usage Notes

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.

Java Class Example

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.

Java Source Example

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 keyword `public class` should not be used in a comment before the first public class statement.

Java Resource Example

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.

Related Topics

DROP JAVA

### 4.3.14 CREATE PROCEDURE

A procedure is a group of PL/SQL statements that you can call by name. A call specification (sometimes called call spec) declares a Java, C++, or .Net method so that it can be called from SQL and PL/SQL. The call spec tells Oracle Database which Java, C++, or .Net method to invoke when a call is made. It also tells the database what type conversions to make for the arguments and return value.

Syntax

The syntax for `CREATE PROCEDURE` is displayed in Figure 4-21.

Figure 4-21 The CREATE PROCEDURE Command

Description of "Figure 4-21 The CREATE PROCEDURE Command"

BNF Notation

    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;

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

Description of "Figure 4-22 The call_spec Expression used with CREATE PROCEDURE"

BNF Notation

    LANGUAGE Declaration

The following defines the Declarations for Java, C++ or .NET:

- Java Declaration::=

- C++ Declaration::=

- .NET Declaration::=

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

Description of "Figure 4-23 The Java Declaration Expression used with CREATE PROCEDURE"

BNF Notation

    JAVA NAME . string .

C++ Declaration::=

The syntax for the C++ `Declaration` expression is as follows:

    CPLUSPLUS NAME <libName>::<funcName>

Where:

- `<libName>` is the name of the library where the procedure is defined—without the `.DLL` or `.SO` extension

- `<funcName>` is the name of the procedure and function

.NET Declaration::=

Before you execute the `CREATE PROCEDURE` statement for a .NET class, first create the .Net class by performing the `CREATE DOTNET CLASS` SQL statement. The following is an example of how to create the .NET class with the `CREATE DOTNET CLASS` SQL function where `NSPLib.dll` is the Assembly name and `SPClass` is the class name. The class name can be qualified with namespace names.

    create dotnet class using bfile('NSPLib.dll', 'SPClass');

Then execute the `CREATE PROCEDURE` SQL statement for `LANGUAGE DOTNET`.

The syntax for the .Net `Declaration` expression is as follows:

    DOTNET NAME <className>.<methodName>

Where:

- `<className>` is the name of the library where the procedure is defined.

- `<methodName>` is the name of the method within the class.

The following `CREATE PROCEDURE` statement creates the .NET PL/SQL procedure where the `NAME` is defined with the concatenation of `<className>.<methodName>`. In this example, the `<className>` is `SPClass` and the `<methodName>` is `bu1`.

    create procedure bu1(oc1 int, nc1 int, oc2 int, nc2 int) as language dotnet name 'SPClass.bu1';

Prerequisite

To create a procedure in your own schema, you must be connected to the database as `SYSTEM` or you must have DBA/DDL privileges.

Purpose

To create a call specification for a standalone stored procedure.

A call specification ("call spec") declares a Java, C++, or .NET method so that it can be called from SQL. The call spec tells Oracle which Java, C++, or .NET 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 10, "Using Stored Procedures and Triggers" in the Oracle Database Lite Client 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
--- | ---
`OR REPLACE` | 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 `DISABLED`.
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.
`IN` | Indicates that you must specify a value for the argument when calling the procedure.
`OUT` | Indicates that the procedure passes a value for this argument back to its calling environment after execution.
`IN OUT` | 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 `IN`, `OUT`, and `IN OUT`, the argument defaults to `IN`. 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 `NOCOPY` only when these effects would not matter.
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, `VARCHAR2(10)` is not valid, but `VARACHAR2` is valid. Oracle Database Lite derives the length, precision, and scale of an argument from the environment from which the procedure is called.
invoker_rights_clause | For compatibility with Oracle, Oracle Database Lite recognizes but does not enforce the invoker_rights_clause.
`IS` | Associates the SQL identifier with the Java method.
`AS` | Associates the SQL identifier with the Java method.
call_spec | Maps the Java, C or C++ method name, parameter types, and return type to SQL counterparts.
`LANGUAGE` | Specifies the call_spec language. Specify the language by using the followng key word for Java, C++, or .NET: `JAVA` `CPLUSPLUS` `DOTNET`
Declaration | Identifies the method name in the Java, C++ or .Net class.
`NAME` | The method name.
string | Identifies the Java implementation of the method. For more information, see the Oracle Database Lite Developer's Guide.

Usage Notes

Oracle Database Lite recognizes but does not enforce the <invoker_rights_clause>. Oracle Database Lite always uses `current_user` for `AUTHID`.

Example

The following example creates and compiles a Java procedure and tests it against Oracle Database Lite.

1. Create and compile the following Java `EMPTrigg.java` program:

       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) {}
          }
       }

2. Create the `EMPLOYEE` table with the `NAME` and `SALARY` columns.

       CREATE TABLE EMPLOYEE (NAME VARCHAR(32), SALARY INT);

3. 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);

4. 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');

5. 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)';
        /

6. 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);
       /

7. Select all rows from the `EMPLOYEE` table.

       SELECT * FROM EMPLOYEE;

   Returns the following result:

       NAME                                SALARY
       -------------------------------- ---------
       Alice                                  100
       Bob                                    100
       Oleg                                   100

Related Topics

DROP PROCEDURE

### 4.3.15 CREATE SCHEMA

Syntax

The syntax for the `CREATE SCHEMA` command is displayed in Figure 4-24.

Figure 4-24 The CREATE SCHEMA Command

Description of "Figure 4-24 The CREATE SCHEMA Command"

BNF Notation

    CREATE SCHEMA schema . CREATE TABLE command [ CREATE TABLE command]... ;

Prerequisite

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.

Purpose

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.
`CREATE TABLE` | A `CREATE TABLE` statement to be issued as part of the `CREATE SCHEMA` statement.
command | Contains all the arguments and keywords for a `CREATE TABLE` or `CREATE VIEW` command.

Usage Notes

- 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.

Example 1

To create a sample schema called `HOTEL_OPERATION` use.

    CREATE SCHEMA HOTEL_OPERATION;

Example 2

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);

ODBC 2.0

Although the `CREATE SCHEMA` command is not part of the ODBC SQL syntax, ODBC passes the command through to your database.

Related Topics

GRANT, CREATE SEQUENCE, CREATE VIEW

### 4.3.16 CREATE SEQUENCE

Syntax

The syntax for `CREATE SEQUENCE` is displayed in Figure 4-25.

Figure 4-25 The CREATE SEQUENCE Command

Description of "Figure 4-25 The CREATE SEQUENCE Command"

BNF Notation

    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
     }]...
    ;

Prerequisite

None

Purpose

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.
`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. If you omit the `INCREMENT BY` clause, the default is 1.
`START WITH` | 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).
`MAXVALUE` | Specifies the maximum value the sequence can generate. This integer value can have 9 or fewer digits. `MAXVALUE` must be greater than `MINVALUE`.
`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 9 or fewer digits. `MINVALUE` must be less than `MAXVALUE`.
`NOMINVALUE` | Specifies a minimum value of 1 for an ascending sequence or –2147483647 for a descending sequence.

Usage Notes

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.

Example

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.

ODBC 2.0

Although the `CREATE SEQUENCE` command is not part of the ODBC SQL syntax, ODBC passes the command through to your database.

Related Topics

ALTER SEQUENCE, DROP SEQUENCE

### 4.3.17 CREATE SYNONYM

Syntax

The syntax for `CREATE SYNONYM` is displayed in Figure 4-26.

Figure 4-26 The CREATE SYNONYM Command

Description of "Figure 4-26 The CREATE SYNONYM Command"

BNF Notation

    CREATE [PUBLIC] SYNONYM [schema .] synonym FOR [schema .] object ;

Prerequisite

None

Purpose

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
--- | ---
`PUBLIC` | 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 `PUBLIC`.
synonym | The name of the synonym to be created.
`FOR` object | 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.

Usage Notes

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.

Example

To define the synonym `PROD` for the table `PRODUCT` in the schema `SCOTT`, issue the following statement.

    CREATE SYNONYM PROD FOR SCOTT.PRODUCT;

Related Topics

CREATE TABLE, CREATE VIEW, CREATE SEQUENCE, DROP SYNONYM

### 4.3.18 CREATE TABLE

Syntax

The syntax for the `CREATE TABLE` command is displayed in Figure 4-27.

Figure 4-27 The CREATE TABLE Command

Description of "Figure 4-27 The CREATE TABLE Command"

BNF Notation

    CREATE TABLE [schema .] table
    column_list [column_list ]...
    [AS subquery] ;

column_list::=

The syntax for the `column_list` expression is displayed in Figure 4-28.

Figure 4-28 The column_list Expression

Description of "Figure 4-28 The column_list Expression"

BNF Notation

    "("
    column datatype [DEFAULT expr|AUTO INCREMENT][column_constraint] [column_constraint]...
    [table_constraint]
    [, column datatype [DEFAULT expr|AUTO INCREMENT][column_constraint] [column_constraint]...
    [table_constraint]]...
    ")"

Prerequisite

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.

Purpose

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.
`DEFAULT` | The `DEFAULT` clause enables you to assign a value to the column if a subsequent `INSERT` statement omits a value for the column. The datatype of the expression must match the datatype of the column. To contain this expression, the column size must be increased. The `DEFAULT` expression can include any SQL function provided the function does not return a column reference or a nested function invocation. Restrictions on Default Common Values A `DEFAULT` expression cannot contain references to Java stored procedures, other columns or the psuedo columns named `LEVEL`, `PRIOR`, and `ROWNUM`. A `DEFAULT` expression cannot contain a sub query. 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 `INTEGER`. 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 `SELECT` statement.

Usage Notes

`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);`

Example 1

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)`

Example 2

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.

Related Topics

CONSTRAINT clause, DROP TABLE, Transaction Control Commands, SELECT

### 4.3.19 CREATE SNAPSHOT TABLE

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.1, "Example Creating Snapshot Table"

- 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"

#### 4.3.19.1 Example Creating Snapshot Table

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));

#### 4.3.19.2 Using the OL__ROW_STATUS Pseudocolumn to Select Modified Rows

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);

Purpose

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.

Prerequisite

None.

Usage Notes

`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

Example 1

    Select OL__ROW_STATUS, Emp.* from Employee Emp Where Empno = 7900;

Example 2

    Select Emp. OL__ROW_STATUS, ENAME, DNAME  from EMP,DEPT where
    DEPT.DEPTNO=EMP.DEPTNO AND EMP.EMPNO=7900;

Related Topics

CURRVAL and NEXTVAL pseudocolumns, LEVEL pseudocolumn, ROWID pseudocolumn, ROWNUM pseudocolumn

#### 4.3.19.3 Retrieving Dirty Rows Using The Plus Sign ('+') on the Snapshot Table

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.

#### 4.3.19.4 Reset the Snapshot Table With the RESET SNAPSHOT TABLE Command

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."

### 4.3.20 CREATE TRIGGER

Syntax

The syntax for `CREATE TRIGGER` is displayed in Figure 4-29.

Figure 4-29 The CREATE TRIGGER Command

Description of "Figure 4-29 The CREATE TRIGGER Command"

BNF Notation

    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")"]...
    ;

Prerequisite

None

Purpose

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
--- | ---
`OR REPLACE` | 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.
`BEFORE` | 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.
`AFTER` | 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.
`DELETE` | Specifies that the trigger should be fired whenever a `DELETE` statement removes a row from the table.
`INSERT` | Specifies that the trigger should be fired whenever an `INSERT` statement adds a row to the table.
`UPDATE OF` | Specifies that the trigger should be fired whenever an `UPDATE` statement changes a value in one of the columns specified in the `OF` clause. If you omit the `OF` clause, Oracle Database Lite fires the trigger whenever an `UPDATE` statement changes a value in any column of the table.
col_list | The column(s) that, when updated, cause the trigger to be fired.
`ON` | 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.
`FOR EACH ROW` | 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.

Example

The following example provides you with instructions for creating and testing a trigger.

1. 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);
               }
           }

2. Attach `TriggerExample.java` to the EMP table.

       ALTER TABLE EMP ATTACH JAVA SOURCE "TriggerExample" in '.';

3. Create the Java trigger.

       CREATE TRIGGER SAL_CHECK BEFORE UPDATE OF SAL ON EMP FOR EACH ROW
       EMP_SAL(NEW.SAL);
        .
        /

4. 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

Related Topics

ALTER TRIGGER, ALTER VIEW, CREATE VIEW, DROP TRIGGER

### 4.3.21 CREATE USER

Syntax

The syntax for `CREATE USER` is displayed in Figure 4-30.

Figure 4-30 The CREATE USER Command

Description of "Figure 4-30 The CREATE USER Command"

BNF Notation

    CREATE USER user IDENTIFIED BY password ;

Prerequisite

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.

Purpose

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. The username is not case sensitive. The user name should not contain characters such as ',",@,% or blank spaces and should not be Oracle Database reserve words.
`IDENTIFIED BY` | 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 password should not contain characters such as ',",@,% or blank spaces and should not be Oracle Database reserve words.

Usage Notes

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.

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.

Example

    CREATE USER SCOTT IDENTIFIED BY TIGER;

Related Topics

ALTER USER, GRANT

### 4.3.22 CREATE VIEW

Syntax

The syntax for `CREATE VIEW` is displayed in Figure 4-31.

Figure 4-31 The CREATE VIEW Command

Description of "Figure 4-31 The CREATE VIEW Command"

BNF Notation

    CREATE [OR REPLACE] [[NO] FORCE] VIEW [schema .] view["("alias [, alias]...")"] AS subquery ;

Prerequisite

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.

Purpose

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.

Usage Notes

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:

1. 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.

2. A CREATE FORCE VIEW created with a valid base table is no different than CREATE VIEW.

Example

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.

ODBC 2.0

Although the ODBC SQL syntax for CREATE VIEW does not support the OR REPLACE argument, ODBC passes the command through to your database.

Editing Data in a View

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.

Related Topics

DROP SEQUENCE, CREATE TABLE, DROP VIEW

### 4.3.23 CURRVAL and NEXTVAL pseudocolumns

Purpose

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.

Prerequisite

You must have a sequence object.

Usage Notes

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.

Example 1

This example selects the current value of the employee sequence in the sample schema `hr`:

    SELECT employees_seq.currval
        FROM DUAL;

Example 2

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);

Example 3

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);

Related Topics

LEVEL pseudocolumn, ROWID pseudocolumn, ROWNUM pseudocolumn

### 4.3.24 DELETE

Syntax

The syntax for `DELETE` is displayed in Figure 4-32.

Figure 4-32 The DELETE Command

Description of "Figure 4-32 The DELETE Command"

BNF Notation

    DELETE FROM [schema .] {table|view}[WHERE condition] ;

Prerequisite

You can only delete rows from tables or views in your schema.

Purpose

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".

Usage Notes

If no WHERE clause is specified, then all rows of the table are deleted.

A positioned DELETE requires that the cursor be updatable.

Example

    DELETE FROM PRICE WHERE MINPRICE < 2.4;

ODBC 2.0

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

Related Topics

UPDATE

### 4.3.25 DROP clause

Syntax

The syntax for the `DROP` clause is displayed in Figure 4-33.

Figure 4-33 The DROP Clause

Description of "Figure 4-33 The DROP Clause"

BNF Notation

    DROP
    {PRIMARY KEY
     | [COLUMN] column
     | UNIQUE "("column")" [, "("column")"]...
     |CONSTRAINT constraint }
    [ CASCADE ]  ;

Prerequisite

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.

Purpose

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.

Example

    ALTER TABLE EMP DROP COLUMN COMM;

Related Topics

ALTER TABLE, CONSTRAINT clause

### 4.3.26 DROP FUNCTION

Syntax

The syntax for the `DROP` function is displayed in Figure 4-34.

Figure 4-34 The DROP Function

Description of "Figure 4-34 The DROP Function"

BNF Notation

    DROP FUNCTION [schema .] function_name ;

Prerequisite

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.

Purpose

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.

Example

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;

Related Topics

CREATE FUNCTION

### 4.3.27 DROP INDEX

Syntax

The syntax for `DROP INDEX` is displayed in Figure 4-35.

Figure 4-35 The DROP INDEX Command

Description of "Figure 4-35 The DROP INDEX Command"

BNF Notation

    DROP INDEX [schema .] index ;

Prerequisite

To drop an index, you must be logged into the database as SYSTEM or as a user with DBA/DDL privileges.

Purpose

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.

Example

The following example drops an index on the SAL column of the EMP table:

DROP INDEX SAL_INDEX;

Related Topics

CREATE INDEX

### 4.3.28 DROP JAVA

Syntax

The syntax for DROP JAVA is displayed in Figure 4-36.

Figure 4-36 The DROP JAVA Command

Description of "Figure 4-36 The DROP JAVA Command"

BNF Notation

    DROP JAVA { CLASS | RESOURCE } [schema .] object_name;

Prerequisite

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.

Purpose

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.

Usage Notes

Oracle Lite recognizes schema_name when specified, but does not enforce it.

Example

The following statement drops the Java class `MyClass`:

    DROP JAVA CLASS "MyClass";

Related Topics

CREATE JAVA

### 4.3.29 DROP PROCEDURE

Syntax

The syntax for `DROP PROCEDURE` is displayed in Figure 4-37.

Figure 4-37 The DROP PROCEDURE Command

Description of "Figure 4-37 The DROP PROCEDURE Command"

BNF Notation

    DROP PROCEDURE [schema .] procedure ;

Prerequisite

The procedure must be connected to the database as schema or you must have DBA/DDL privileges.

Purpose

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.

Example

The following statement drops the procedure `TRANSFER` owned by the user `KERNER` and invalidates all objects that depend on `TRANSFER`:

    DROP PROCEDURE kerner.transfer

Related Topics

CREATE PROCEDURE

### 4.3.30 DROP SCHEMA

Syntax

The syntax for DROP SCHEMA is displayed in Figure 4-38.

Figure 4-38 The DROP SCHEMA Command

Description of "Figure 4-38 The DROP SCHEMA Command"

BNF Notation

    DROP SCHEMA schema . [{CASCADE | RESTRICT}] ;

Prerequisite

To drop a schema, you must be logged into the database as SYSTEM or as a user with DBA/DDL or ADMIN privileges.

Purpose

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.

Usage Notes

If no options are specified, the default behavior is determined by the RESTRICT argument.

Example

The following example drops the HOTEL_OPERATION schema you created in the CREATE SCHEMA example:

    DROP SCHEMA HOTEL_OPERATION CASCADE;

Related Topics

CREATE SCHEMA

### 4.3.31 DROP SEQUENCE

Syntax

The syntax for DROP SEQUENCE is displayed in Figure 4-39.

Figure 4-39 The DROP SEQUENCE Command

Description of "Figure 4-39 The DROP SEQUENCE Command"

BNF Notation

    DROP SEQUENCE [schema .] sequence ;

Prerequisite

You must be logged into the database as SYSTEM, or the sequence must be in your schema.

Purpose

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.

Usage Notes

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.

Example

The following example drops the ESEQ sequence you created in the CREATE SEQUENCE example:

    DROP SEQUENCE ESEQ;

ODBC 2.0

Although the DROP SEQUENCE command is not part of the ODBC SQL syntax, ODBC passes the command through to your database.

Related Topics

ALTER SEQUENCE, CREATE SEQUENCE

### 4.3.32 DROP SYNONYM

Syntax

The syntax for DROP SYNONYM is displayed in Figure 4-40.

Figure 4-40 The DROP SYNONYM Command

Description of "Figure 4-40 The DROP SYNONYM Command"

BNF Notation

    DROP [PUBLIC] SYNONYM [schema .] synonym ;

Prerequisite

To drop a synonym from the database, you must be logged into the database as SYSTEM, or the synonym must be in your schema.

Purpose

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.

Example

The following example drops the synonym named PROD, which you created in the CREATE SYNONYM example:

    DROP SYNONYM PROD;

Related Topics

CREATE SYNONYM

### 4.3.33 DROP TABLE

Syntax

The syntax for DROP TABLE is displayed in Figure 4-41.

Figure 4-41 The DROP TABLE Command

Description of "Figure 4-41 The DROP TABLE Command"

BNF Notation

    DROP TABLE [schema .] table [{CASCADE | CASCADE CONSTRAINTS | RESTRICT}] ;

Prerequisite

To drop a table from the database, you must be logged into the database as SYSTEM or as a user with DBA/DDL privileges.

Purpose

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.

Usage Notes

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.

Example

    DROP TABLE EMP;

Related Topics

ALTER TABLE, CREATE TABLE

### 4.3.34 DROP TRIGGER

Syntax

The syntax for DROP TRIGGER is displayed in Figure 4-42.

Figure 4-42 The DROP TRIGGER Command

Description of "Figure 4-42 The DROP TRIGGER Command"

BNF Notation

    DROP TRIGGER [schema .] trigger ;

Prerequisite

You must be logged into the database as SYSTEM or the trigger must be in your schema.

Purpose

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.

Example

The following statement drops the `SAL_CHECK` trigger, which you created in the CREATE TRIGGER example:

    DROP TRIGGER ruth.reorder

Related Topics

CREATE TRIGGER

### 4.3.35 DROP USER

Syntax

The syntax for DROP USER is displayed in Figure 4-43.

Figure 4-43 The DROP USER Command

Description of "Figure 4-43 The DROP USER Command"

BNF Notation

    DROP USER user [CASCADE] ;

Prerequisite

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.

Purpose

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.

Usage Notes

You can drop users if you are connected to the database as SYSTEM, or if you are granted the ADMIN or DBA/DDL role.

Example

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;

Related Topics

CREATE USER

### 4.3.36 DROP VIEW

Syntax

The syntax for DROP VIEW is displayed in Figure 4-44.

Figure 4-44 The DROP VIEW Command

Description of "Figure 4-44 The DROP VIEW Command"

BNF Notation

    DROP [schema .] VIEW view [ {CASCADE | RESTRICT}] ;

Prerequisite

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.

Purpose

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.

Usage Notes

If no options are specified, Oracle Lite drops only this view. Other dependent views are not affected.

Example

The following statement drops the EMP_SAL view you created in the CREATE VIEW example:

    DROP VIEW EMP_SAL;

Related Topics

CREATE SYNONYM, CREATE TABLE, CREATE VIEW

### 4.3.37 EXPLAIN PLAN

Syntax

The syntax for `EXPLAIN PLAN` is displayed in Figure 4-45.

Figure 4-45 The EXPLAIN PLAN Command

Description of "Figure 4-45 The EXPLAIN PLAN Command"

BNF Notation

    EXPLAIN PLAN select_command;

Purpose

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.

Usage Notes

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.

### 4.3.38 GRANT

Syntax

The syntax for `GRANT` is displayed in Figure 4-46.

Figure 4-46 The GRANT Command

Description of "Figure 4-46 The GRANT Command"

BNF Notation

    GRANT {role | privilege_list ON object_name} TO user_list ;

Prerequisite

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.

Purpose

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.

Pre-defined Roles

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.

Usage Notes

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.

Example 1

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;

Example 2

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;

Example 3

The following example grants ALL privileges on the PRODUCT table to the newly created user, MICHAEL:

    GRANT ALL ON PRODUCT TO MICHAEL;

Related Topics

REVOKE

### 4.3.39 INSERT

Syntax

The syntax for `INSERT` is displayed in Figure 4-47.

Figure 4-47 The INSERT Command

Description of "Figure 4-47 The INSERT Command"

BNF Notation

    INSERT INTO [schema .] {table | view }["("column [, column]...")"]{ VALUES  "(" expr [, expr]...")" | subquery} ;

Prerequisite

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.

Purpose

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.

Usage Notes

- 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.

Example

    INSERT INTO EMP (EMPNO, ENAME, DEPTNO) VALUES ('7010', 'VINCE', '20');

Related Topics

DELETE, UPDATE

### 4.3.40 LEVEL pseudocolumn

Purpose

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.

Prerequisites

None.

Usage Notes

The number of levels returned by a hierarchical query is limited to 32.

Example

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.

Related Topics

CURRVAL and NEXTVAL pseudocolumns, CREATE SNAPSHOT TABLE, ROWID pseudocolumn, ROWNUM pseudocolumn,

### 4.3.41 REVOKE

Syntax

The syntax for `REVOKE` is displayed in Figure 4-48.

Figure 4-48 The REVOKE Command

Description of "Figure 4-48 The REVOKE Command"

BNF Notation

    REVOKE  { role | privilige_list ON object_name } FROM user_list ;

Prerequisite

To revoke roles from users, you must be logged into the database as SYSTEM or as a user with DBA or ADMIN privileges.

Purpose

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.

Usage Notes

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.

Example 1

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;

Example 2

The following example revokes the INSERT and DELETE privileges on the EMP table from the user, SCOTT.

    REVOKE INSERT,DELETE ON EMP FROM SCOTT;

Example 3

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;

Related Topics

GRANT

### 4.3.42 ROLLBACK

Syntax

The syntax for `ROLLBACK` is displayed in Figure 4-49.

Figure 4-49 The ROLLBACK Command

Description of "Figure 4-49 The ROLLBACK Command"

BNF Notation

    ROLLBACK [{ WORK | TO savepoint_name }] ;

Prerequisite

None.

Purpose

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.

Usage Notes

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.

Example

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

ODBC 2.0

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.

Related Topics

SAVEPOINT

### 4.3.43 ROWID pseudocolumn

Purpose

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`.

Prerequisite

None.

Usage Notes

`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.

Example 1

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;

Related Topics

CURRVAL and NEXTVAL pseudocolumns, LEVEL pseudocolumn, ROWNUM pseudocolumn, CREATE SNAPSHOT TABLE

### 4.3.44 ROWNUM pseudocolumn

Purpose

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.

Prerequisite

None.

Usage Notes

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.

Example 1

The following example uses ROWNUM to limit the number of rows returned by a query:

    SELECT * FROM emp WHERE ROWNUM < 10;

Example 2

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;

Example 3

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;

Example 4

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.

Example 5

The following statement assigns unique values to each row of a table:

    UPDATE tabx SET col1 = ROWNUM;

Related Topics

CURRVAL and NEXTVAL pseudocolumns, LEVEL pseudocolumn, ROWID pseudocolumn, CREATE SNAPSHOT TABLE

### 4.3.45 SAVEPOINT

Syntax

The syntax for `SAVEPOINT` is displayed in Figure 4-50.

Figure 4-50 The SAVEPOINT Command

Description of "Figure 4-50 The SAVEPOINT Command"

BNF Notation

    SAVEPOINT savepoint_name ;

Purpose

To identify a point in a transaction to which you can later roll back.

Prerequisites

None.

Usage Notes

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.

Example

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;

Related Topics

COMMIT, SAVEPOINT, ROLLBACK

### 4.3.46 SELECT

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.

Syntax

`select::=`

The syntax for `SELECT` is displayed in Figure 4-51.

Figure 4-51 The SELECT Command

Description of "Figure 4-51 The SELECT Command"

BNF Notation

    subquery [order_by_clause] [ for_update_clause] ;

Related Topics

CONSTRAINT clause, DELETE, UPDATE

The following sections describe the different operations you can use within a select statement:

- Section 4.3.46.1, "SELECT Command Arguments"

- Section 4.3.46.2, "The SUBQUERY Expression"

- Section 4.3.46.3, "The FOR_UPDATE Clause"

- Section 4.3.46.4, "The ORDER_BY Clause"

- Section 4.3.46.5, "The TABLE_REFERENCE Expression"

- Section 4.3.46.6, "The ODBC_JOIN_TABLE Expression"

- Section 4.3.46.7, "The HINT Expression"

- Section 4.3.46.8, "The LIMIT and OFFSET Clauses"

- Section 4.3.46.9, "Select Statement Behavior When Retrieving BLOBs in a Read Committed Transaction"

- Section 4.3.46.10, "Examples For the SELECT Command"

#### 4.3.46.1 SELECT Command Arguments

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 Section 4.3.46.7, "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.

Usage Notes

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.

#### 4.3.46.2 The SUBQUERY Expression

subquery::=

The syntax for the `subquery` expression is displayed in Figure 4-52.

Figure 4-52 The subquery Expression

Description of "Figure 4-52 The subquery Expression"

BNF Notation

    {query_spec | "("subquery")" } [{ INTERSECT | INTERSECT ALL | UNION | UNION ALL | MINUS }
      {query_spec |"(" subquery ")" } ]

query_spec::=

The syntax for the `query_spec` expression is displayed in Figure 4-53.

Figure 4-53 The query spec Expression

Description of "Figure 4-53 The query spec Expression"

BNF Notation

    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]  }]

#### 4.3.46.3 The FOR_UPDATE Clause

for_update_clause::=

The syntax for the `update_clause` expression is displayed in Figure 4-54.

Figure 4-54 The for_update_clause Expression

Description of "Figure 4-54 The for_update_clause Expression"

BNF Notation

    FOR UPDATE[OF [[schema .] { table | view } .] column  [, [[schema .] { table | view } .] column]...]

#### 4.3.46.4 The ORDER_BY Clause

order_by_clause::=

The syntax for the `order_by_clause` expression is displayed in Figure 4-55.

Figure 4-55 The order_by_clause Expression

Description of "Figure 4-55 The order_by_clause Expression"

BNF Notation

    ORDER  BY { expr | position | c_alias } [ ASC | DESC ] [, { expr | position | c_alias } [ ASC | DESC ] ]...

#### 4.3.46.5 The TABLE_REFERENCE Expression

table_reference::=

The syntax for the `table_reference` expression is displayed in Figure 4-56.

Figure 4-56 The table_reference Expression

Description of "Figure 4-56 The table_reference Expression"

BNF Notation

    { [schema .] {table | view} | "("subquery [order_by_clause] ")"} [[AS] t_alias]

#### 4.3.46.6 The ODBC_JOIN_TABLE Expression

odbc_join_table::=

The syntax for the `odbc_join_table` expression is displayed in Figure 4-57.

Figure 4-57 The odbc_join_table Expression

Description of "Figure 4-57 The odbc_join_table Expression"

BNF Notation

    "{" OJ joined_table "}"

joined_table::=

The `joined_table` is not supported within Oracle Database Lite; however, you can still use the `odbc_join_table` or the Oracle Database `join`, which has the same syntax. So, this syntax is displayed here ONLY for use within the `odbc_join_table` or `join` expression.

The syntax for the `joined_table` expression is displayed in Figure 4-58.

Figure 4-58 The join_table Expression

Description of "Figure 4-58 The join_table Expression"

BNF Notation

    "{"
      { table_reference   | OJ table_refernce { LEFT | RIGHT } [OUTER] JOIN joined_table ON conditon  }"}"

#### 4.3.46.7 The HINT Expression

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 */`.

BNF Notation

    {
     {DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]]... */ |
     {DELETE|INSERT|SELECT|UPDATE} // hint [text] [hint[text]]... // |
     {DELETE|INSERT|SELECT|UPDATE} /*% hint [text] [hint[text]]... %*/
    }

##### 4.3.46.7.1 ORDERED Hints

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;

##### 4.3.46.7.2 INDEX Hints

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.

#### 4.3.46.8 The LIMIT and OFFSET Clauses

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.

Syntax

    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.12, "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.

#### 4.3.46.9 Select Statement Behavior When Retrieving BLOBs in a Read Committed Transaction

There is a change in behavior for some SELECT statements when all of the following conditions are met:

1. The transaction isolation level is `READ_COMMITED`

2. The SELECT statement does NOT contain the FOR UPDATE clause

3. 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!!!
        . . .
    }

#### 4.3.46.10 Examples For the SELECT Command

The following examples demonstrate how you can use the select command:

- Example 1

- Example 2

- Example 3

- Example 4

- Example 5

- Example 6

Example 1

    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

Example 2

    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.

Example 3

    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.

Example 4

    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

Example 5

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;

Example 6

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%')

### 4.3.47 SET TRANSACTION

Syntax

The syntax for `SET TRANSACTION` is displayed in Figure 4-59.

Figure 4-59 The SET TRANSACTION Command

Description of "Figure 4-59 The SET TRANSACTION Command"

BNF Notation

    SET TRANSACTION ISOLATION LEVEL{ READ COMMITTED | REPEATABLE READ | SERIALIZABLE | SINGLE USER};

Prerequisite

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.

Purpose

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.9, "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.

Usage Notes

None.

Example

    SET TRANSACTION ISOLATION LEVEL SINGLEUSER;

Related Topics

COMMIT, ROLLBACK

### 4.3.48 TRUNCATE TABLE

Syntax

The syntax for `TRUNCATE TABLE` is displayed in Figure 4-60.

Figure 4-60 The TRUNCATE TABLE Command

Description of "Figure 4-60 The TRUNCATE TABLE Command"

BNF Notation

    TRUNCATE TABLE [schema .] table ;

Purpose

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.

Usage Notes

A table cannot be truncated if it has a primary key and there are rows in the dependent tables.

Example

    TRUNCATE TABLE emp;

### 4.3.49 UPDATE

Syntax

The syntax for `UPDATE` is displayed in Figure 4-61.

Figure 4-61 The Update Command

Description of "Figure 4-61 The Update Command"

BNF Notation

    UPDATE [schema .] { table | view} [ alias ] SET column = { expr | subquery }  [, column = { expr | subquery }]...[WHERE condition] ;

Prerequisite

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.

Purpose

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".

Usage Notes

- 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.

Example

    UPDATE EMP SET SAL = SAL * .45 WHERE JOB = 'PRESIDENT';

ODBC 2.0

The ODBC SQL syntax for UPDATE is the same as specified. In addition, the following syntax is supported:

    WHERE CURRENT OF CURSOR cursor_name

Related Topics

DELETE, INSERT