Which is not a DDL command in SQL below
This document discusses SQL commands used by Oracle Database Lite. Topics include: Show
4.1 SQL Command TypesThe 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 4.2 SQL Commands OverviewOracle 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) CommandsData definition language (DDL) commands enable you to perform the following tasks.
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) CommandsData 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 CommandsTransaction control commands manage changes made by DML commands. 4.2.4 ClausesClauses are subsets of commands that modify the command. 4.2.5 PseudocolumnsPseudocolumns 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 ConventionsThe 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.
4.3 SQL Commands Alphabetical ListingThis section lists Oracle Database Lite SQL commands, clauses, and pseudocolumns in alphabetical order and discusses each. This discussion includes the following.
4.3.1 ALTER SEQUENCESyntax The syntax for the ALTER SEQUENCE command is displayed in Figure 4-1. 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.
The arguments for the ALTER SEQUENCE command are listed in Table 4-6. Table 4-6 Arguments Used with the ALTER SEQUENCE Command
Usage Notes
Example This statement sets a new maximum value for the ESEQ sequence. ALTER SEQUENCE eseq MAXVALUE 1500ODBC 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 SESSIONSyntax The syntax for the ALTER SESSION command is displayed in Figure 4-2. 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-7.
Table 4-7 Arguments Used with the ALTER SESSION Command
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:564.3.3 ALTER TABLESyntax The syntax for ALTER TABLE is displayed in Figure 4-3. 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 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 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 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:
The arguments for the ALTER TABLE command are listed in Table 4-8. Table 4-8 Arguments Used with the ALTER TABLE Command
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 TRIGGERSyntax The syntax for the ALTER TRIGGER command is displayed in Figure 4-7. 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-9. Table 4-9 Parameters of the ALTER TRIGGER Command
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 USERSyntax The syntax for ALTER USER is displayed in Figure 4-8. 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.
Purpose Changes a database user's password. The arguments for the ALTER USER command are listed in Table 4-10. Table 4-10 Arguments Used with the ALTER USER Command
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 VIEWSyntax The syntax for the ALTER VIEW command is displayed in Figure 4-9. 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-11. Table 4-11 Arguments Used with the ALTER VIEW Command
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 COMMITSyntax The syntax for COMMIT is displayed in Figure 4-10. 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-12. Table 4-12 Arguments Used with the Commit Command
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 clauseSyntax The syntax for the COLUMN CONSTRAINT clause is displayed in Figure 4-11. 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 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-13. Table 4-13 Arguments Used with the Constraint Clause
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 DATABASESyntax The syntax for CREATE DATABASE is displayed in Figure 4-13. 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 BNF Notation {|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-14. Table 4-14 Arguments Used with the CREATE DATABASE Command
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. See the Oracle Lite User's Guide for more information about creating a DSN or using the ODBC Administrator. Unlike other DDL statements, Oracle Lite autocommits the CREATE DATABASE command. You cannot undo the CREATE DATABASE command with a ROLLBACK statement. If the POLITE.INI parameter NLS_SORT has been set to enable one of the collation sequences, such as FRENCH, all databases are created with that collation sequence. The default is BINARY. For more information see the Oracle Database Lite Developer’s 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 FUNCTIONSyntax The syntax for CREATE FUNCTION is displayed in Figure 4-15. 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-16. Figure 4-16 The call_spec Expression BNF Notation LANGUAGE Java_declarationJava_declaration::= The syntax for the Java_declaration expression is displayed in Figure 4-17. Figure 4-17 The Java_declaration Expression BNF Notation JAVA NAME . string .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-15. Table 4-15 Arguments Used with the CREATE FUNCTION Command
Usage Notes User-defined functions cannot be used in situations that require an unchanging definition. You cannot use user-defined functions.
In addition, when a function is called from within a query or DML statement, the function cannot.
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.
Related Topics DROP FUNCTION 4.3.11 CREATE GLOBAL TEMPORARY TABLESyntax The syntax for the CREATE GLOBAL TEMPORARY TABLE command is displayed in Figure 4-18. 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-16. Table 4-16 Arguments Used with CREATE GLOBAL TEMPORARY TABLE
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 INDEXSyntax The syntax for the CREATE INDEX command is displayed in Figure 4-19. 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-17. Table 4-17 Arguments Used with the CREATE INDEX Command
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 D, "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 JAVASyntax The syntax for CREATE JAVA is displayed in Figure 4-20. 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 for Java.The arguments for the CREATE JAVA command are listed in Table 4-18. Table 4-18 Arguments Used with the CREATE JAVA Command
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 Lite Developer’s Guide for Java 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 PROCEDURESyntax The syntax for CREATE PROCEDURE is displayed in Figure 4-21. 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 BNF Notation LANGUAGE Java_declarationJava_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 BNF Notation JAVA NAME . string .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 stand alone stored procedure. A call specification ("call spec") declares a Java method so that it can be called from SQL. The call spec tells Oracle which Java method to invoke when a call is made. It also tells Oracle Database Lite what type conversions to make for the arguments and return value. Stored procedures offer advantages in the areas of development, integrity, security, and memory allocation. For more information on stored procedures, including how to call stored procedures, see the Oracle Database Lite Developer’s Guide for Java. 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-19. Table 4-19 Arguments Used with the Create Procedure Command
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.
Related Topics DROP PROCEDURE 4.3.15 CREATE SCHEMASyntax The syntax for the CREATE SCHEMA command is displayed in Figure 4-24. 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-20. Table 4-20 Arguments Used with the CREATE SCHEMA Command
Usage Notes
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 SEQUENCESyntax The syntax for CREATE SEQUENCE is displayed in Figure 4-25. 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-21. Table 4-21 Arguments Used with the CREATE SEQUENCE Command
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 SYNONYMSyntax The syntax for CREATE SYNONYM is displayed in Figure 4-26. 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-22. Table 4-22 Arguments Used with the CREATE SYNONYM Command
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 TABLESyntax The syntax for the CREATE TABLE command is displayed in Figure 4-27. 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 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-23. Table 4-23 Arguments Used with the CREATE TABLE Command
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 the column_list is omitted.
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.
The table has the following integrity constraints.
Related Topics CONSTRAINT clause, DROP TABLE, Transaction Control Commands, SELECT 4.3.19 CREATE TRIGGERSyntax The syntax for CREATE TRIGGER is displayed in Figure 4-29. 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-24. Table 4-24 Arguments Used with the CREATE TRIGGER Command
Example The following example provides you with instructions for creating and testing a trigger.
Related Topics ALTER TRIGGER, ALTER VIEW, CREATE VIEW, DROP TRIGGER 4.3.20 CREATE USERSyntax The syntax for CREATE USER is displayed in Figure 4-30. Figure 4-30 The CREATE USER Command BNF Notation CREATE USER user IDENTIFIED BY passowrd ;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-25. Table 4-25 Arguments Used with the CREATE USER Command
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. This user has all database privileges and is not assigned a password. You can assign a password to SYSTEM, if required. You can use SYSTEM as the default user name until you establish user names of your own as needed. 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:
Example CREATE USER SCOTT IDENTIFIED BY TIGER;Related Topics ALTER USER, GRANT 4.3.21 CREATE VIEWSyntax The syntax for CREATE VIEW is displayed in Figure 4-31. 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-26. Table 4-26 Arguments Used with the CREATE VIEW Command
Usage Notes A view is updatable if:
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:
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.22 CURRVAL and NEXTVAL pseudocolumnsPurpose 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.NEXTVALTo 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.NEXTVALYou can use CURRVAL and NEXTVAL in:
You cannot use CURRVAL and NEXTVAL in:
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.23 DELETESyntax The syntax for DELETE is displayed in Figure 4-32. 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-27. Table 4-27 Arguments Used with the DELETE Command
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_nameRelated Topics UPDATE 4.3.24 DROP clauseSyntax The syntax for the DROP clause is displayed in Figure 4-33. 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-28. Table 4-28 Arguments Used with the DROP Clause
Example ALTER TABLE EMP DROP COLUMN COMM;Related Topics ALTER TABLE, CONSTRAINT clause 4.3.25 DROP FUNCTIONSyntax The syntax for the DROP function is displayed in Figure 4-34. 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:
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-29. Table 4-29 Arguments Used with the DROP 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.26 DROP INDEXSyntax The syntax for DROP INDEX is displayed in Figure 4-35. 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-30. Table 4-30 Arguments Used with the DROP INDEX Command
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.27 DROP JAVASyntax The syntax for DROP JAVA is displayed in Figure 4-36. 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:
Purpose To drop a Java class or resource schema object. For more information on resolving Java classes, and resources, see the Oracle Database Lite Java Developer's Guide. The arguments for the DROP JAVA command are listed in Table 4-31. Table 4-31 Arguments Used with the DROP JAVA Command
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.28 DROP PROCEDURESyntax The syntax for DROP PROCEDURE is displayed in Figure 4-37. 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-32. Table 4-32 Arguments Used with the DROP PROCEDURE Command
Example The following statement drops the procedure TRANSFER owned by the user KERNER and invalidates all objects that depend on TRANSFER: DROP PROCEDURE kerner.transferRelated Topics CREATE PROCEDURE 4.3.29 DROP SCHEMASyntax The syntax for DROP SCHEMA is displayed in Figure 4-38. 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-33. Table 4-33 Arguments Used with the DROP SCHEMA Command
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.30 DROP SEQUENCESyntax The syntax for DROP SEQUENCE is displayed in Figure 4-39. 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-34. Table 4-34 Arguments Used with the DROP SEQUENCE Command
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:
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.31 DROP SYNONYMSyntax The syntax for DROP SYNONYM is displayed in Figure 4-40. 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-35. Table 4-35 Arguments Used with the DROP SYNONYM Command
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.32 DROP TABLESyntax The syntax for DROP TABLE is displayed in Figure 4-41. 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-36. Table 4-36 Arguments Used with the DROP TABLE Command
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.33 DROP TRIGGERSyntax The syntax for DROP TRIGGER is displayed in Figure 4-42. 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-37. Table 4-37 Arguments Used with the DROP TRIGGER Command
Example The following statement drops the SAL_CHECK trigger, which you created in the CREATE TRIGGER example: DROP TRIGGER ruth.reorderRelated Topics CREATE TRIGGER 4.3.34 DROP USERSyntax The syntax for DROP USER is displayed in Figure 4-43. 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-38. Table 4-38 Arguments Used with the DROP USER Command
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 The following statement drops the user Michael: DROP USER MICHAEL;Related Topics CREATE USER 4.3.35 DROP VIEWSyntax The syntax for DROP VIEW is displayed in Figure 4-44. 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:
Purpose Removes a view from the database. The arguments for the DROP VIEW command are listed in Table 4-39. Table 4-39 Arguments Used with the DROP VIEW Command
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.36 EXPLAIN PLANSyntax The syntax for EXPLAIN PLAN is displayed in Figure 4-45. 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-40. Table 4-40 Arguments Used with the EXPLAIN PLAN Command
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-41. Table 4-41 Parameters of the EXPLAIN PLAN Output
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.37 GRANTSyntax The syntax for GRANT is displayed in Figure 4-46. 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-42.
Table 4-42 Arguments Used with the GRANT Command
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-43: Table 4-43 Predefined Roles in Oracle Database Lite
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.38 INSERTSyntax The syntax for INSERT is displayed in Figure 4-47. 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-44. Table 4-44 Arguments Used with the INSERTCommand
Usage Notes
Example INSERT INTO EMP (EMPNO, ENAME, DEPTNO) VALUES ('7010', 'VINCE', '20');Related Topics DELETE, UPDATE 4.3.39 LEVEL pseudocolumnPurpose 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, OL__ROW_STATUS pseudocolumn, ROWID pseudocolumn, ROWNUM pseudocolumn, 4.3.40 OL__ROW_STATUS pseudocolumnPurpose 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-45. Table 4-45 OL__ROW_STATUS Results
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.41 REVOKESyntax The syntax for REVOKE is displayed in Figure 4-48. 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-46. Table 4-46 Arguments Used with the REVOKE Command
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 ROLLBACKSyntax The syntax for ROLLBACK is displayed in Figure 4-49. 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-47. Table 4-47 Arguments Used with the ROLLBACK Command
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 BOSTONODBC 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 pseudocolumnPurpose 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:
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, OL__ROW_STATUS pseudocolumn 4.3.44 ROWNUM pseudocolumnPurpose 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, OL__ROW_STATUS pseudocolumn 4.3.45 SAVEPOINTSyntax The syntax for SAVEPOINT is displayed in Figure 4-50. 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 TOTo remove a savepoint use the statement: REMOVE SAVEPOINTWhen 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 SELECTSyntax select::= The syntax for SELECT is displayed in Figure 4-51. Figure 4-51 The SELECT Command BNF Notation subquery [order_by_clause] [ for_update_clause] ;subquery::= The syntax for the subquery expression is displayed in Figure 4-52. 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 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] } ]for_update_clause::= The syntax for the update_clause expression is displayed in Figure 4-54. Figure 4-54 The for_update_clause Expression BNF Notation FOR UPDATE[OF [[schema .] { table | view } .] column [, [[schema .] { table | view } .] column]...]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 BNF Notation ORDER BY { expr | position | c_alias } [ ASC | DESC ] [, { expr | position | c_alias } [ ASC | DESC ] ]...table_reference::= The syntax for the table_reference expression is displayed in Figure 4-56. Figure 4-56 The table_reference Expression BNF Notation { [schema .] {table | view} | "("subquery [order_by_clause] ")"} [[AS] t_alias]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 BNF Notation "{" OJ joined_table "}"joined_table::= The syntax for the joined_table expression is displayed in Figure 4-58. Figure 4-58 The join_table Expression BNF Notation "{" { table_reference | OJ table_refernce { LEFT | RIGHT } [OUTER] JOIN joined_table ON conditon }"}"hint::= The syntax for the hint expression is displayed in Figure 4-59. Figure 4-59 The hint Expression BNF Notation { // hint // | /* hint */ | /*% hint %*/}Prerequisite 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. Purpose Retrieves data from one or more tables or views. You can also use the select statement to invoke Java stored procedures. The arguments for the SELECT command are listed in Table 4-48. Table 4-48 Arguments Used with the SELECT Command
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 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. 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 10Example 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 SMITHHints Usage You can use special text in a SQL statement to pass instructions, or hints, to the Oracle Lite database optimizer. The optimizer uses these hints as suggestions for choosing an execution plan for the statement. The hint text is delimited by the following sets of symbols depending on the applications in use. All of the sample syntaxes in the diagram work from a user written application. The /*% ... %*/ syntax is treated by the Oracle database optimizer as a comment whereas /*+ ORDERED +*/ is processed by Oracle Database optimizer. 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 /*% ORDERED %*/. To give hints to both Oracle Database Lite and Oracle optimizers, use the syntax /*+ ORDERED +*/. The hint text must follow the SELECT keyword. The hint text is not case sensitive. 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%')Related Topics CONSTRAINT clause, DELETE, UPDATE 4.3.47 SET TRANSACTIONSyntax The syntax for SET TRANSACTION is displayed in Figure 4-60. Figure 4-60 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-49. Table 4-49 Arguments Used with the SET TRANSACTION Command
Usage Notes None. Example SET TRANSACTION ISOLATION LEVEL SINGLEUSER;Related Topics COMMIT, ROLLBACK 4.3.48 TRUNCATE TABLESyntax The syntax for TRUNCATE TABLE is displayed in Figure 4-61. Figure 4-61 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-50. Table 4-50 Arguments Used with the TRUNCATE TABLE Command
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 UPDATESyntax The syntax for UPDATE is displayed in Figure 4-62. Figure 4-62 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-51. Table 4-51 Arguments Used with the UPDATE Command
Usage Notes
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_nameRelated Topics DELETE, INSERT Which is not a DDL statement?DELETE is not a ddl statement. DDL stands for Data Definition Language in SQL. The DDL commands are used to construct and change database and database object structures. DML stands for Data Manipulation Language in SQL.
What are the 4 commands of DDL?Data Definition Language (DDL) commands:. CREATE to create a new table or database.. ALTER for alteration.. Truncate to delete data from the table.. DROP to drop a table.. RENAME to rename a table.. What are the DDL commands in SQL?Following are the five DDL commands in SQL:. CREATE Command.. DROP Command.. ALTER Command.. TRUNCATE Command.. RENAME Command.. Which one of this is not a type of DDL command?ALTER are the major DDL commands. Answer C, B, and D are incorrect. DROP, ALTER, and CREATE are valid DDL commands.
|