7
SQL Statements (continued)

CREATE CONTEXT

Syntax

Purpose

To create a namespace for a context (a set of application-defined attributes that validates and secures an application) and to associate the namespace with the externally created package that sets the context. For a definition and discussion of contexts, refer to Oracle8i Concepts.

Prerequisites

To create a context namespace, you must have CREATE ANY CONTEXT system privilege.

Keywords and Parameters

OR REPLACE

redefines an existing context namespace using a different package.

namespace

is the name of the context namespace to create or modify. Context namespaces are always stored in the schema SYS.

schema

is the schema owning package. If you omit schema, Oracle uses the current schema.

package

is the PL/SQL package that sets or resets the context attributes under the namespace for a user session. For more information on setting the package, see Oracle8i Supplied Packages Reference.

Note: To provide some design flexibility, Oracle does not verify the existence of the schema or the validity of the package at the time you create the context.

Examples

Suppose you have a human resources application (HR) and a PL/SQL package (HR_SECURE_CONTEXT), which validates and secures the HR application. The following statement creates the context namespace HR_CONTEXT and associates it with the package HR_SECURE_CONTEXT:

CREATE CONTEXT hr_context USING hr_secure_context;

You can control data access based on this context using the SYS_CONTEXT function. For example, suppose your HR_SECURE_CONTEXT package has defined an attribute ORG_ID as a particular organization identifier. You can secure a base table HR_ORG_UNIT by creating a view that restricts access based on the value of ORG_ID, as follows:

CREATE VIEW hr_org_secure_view AS
     SELECT * FROM hr_org_unit
     WHERE organization_id = SYS_CONTEXT ('hr_context', 'org_id');

For more information on the SYS_CONTEXT function, see "SYS_CONTEXT".

CREATE CONTROLFILE

Syntax

WARNING:
Oracle recommends that you perform a full backup of all files in the database before using this statement. For more information, see Oracle8i Backup and Recovery Guide.

filespec: See "filespec".

Purpose

To re-create a control file in one of the following cases:

All copies of your existing control files have been lost through media failure.
You want to change the name of the database.
You want to change the maximum number of redo log file groups, redo log file members, archived redo log files, datafiles, or instances that can concurrently have the database mounted and open.

When you issue a CREATE CONTROLFILE statement, Oracle creates a new control file based on the information you specify in the statement. If you omit any clauses, Oracle uses the default values rather than the values for the previous control file. After successfully creating the control file, Oracle mounts the database in the mode specified by the initialization parameter PARALLEL_SERVER. You then must perform media recovery before opening the database. It is recommended that you then shut down the instance and take a full backup of all files in the database.

For more information about using this statement, see Oracle8i Backup and Recovery Guide.

Prerequisites

You must have the OSDBA role enabled. The database must not be mounted by any instance.

If the REMOTE_LOGIN_PASSWORDFILE initialization parameter is set to EXCLUSIVE, Oracle returns an error when you attempt to re-create the control file. To avoid this message, either set the parameter to SHARED, or re-create your password file before re-creating the control file. For more information about the REMOTE_LOGIN_PASSWORDFILE parameter, see Oracle8i Reference.

Keywords and Parameters

REUSE

specifies that existing control files identified by the initialization parameter CONTROL_FILES can be reused, thus ignoring and overwriting any information they may currently contain. If you omit this clause and any of these control files already exists, Oracle returns an error.

DATABASE database

specifies the name of the database. The value of this parameter must be the existing database name established by the previous CREATE DATABASE statement or CREATE CONTROLFILE statement.

SET DATABASE database

changes the name of the database. The name of a database can be as long as eight bytes.

LOGFILE

specifies the redo log files for your database. You must list all members of all redo log file groups. See the syntax description of filespec in "filespec".

GROUP integer

specifies logfile group. If you specify GROUP values, Oracle verifies these values with the GROUP values when the database was last open.

RESETLOGS

ignores the contents of the files listed in the LOGFILE clause. These files do not have to exist. Each filespec in the LOGFILE clause must specify the SIZE parameter. Oracle assigns all online redo log file groups to thread 1 and enables this thread for public use by any instance. After using this clause, you must open the database using the RESETLOGS clause of the ALTER DATABASE statement.

NORESETLOGS

specifies that all files in the LOGFILE clause should be used as they were when the database was last open. These files must exist and must be the current online redo log files rather than restored backups. Oracle reassigns the redo log file groups to the threads to which they were previously assigned and reenables the threads as they were previously enabled.

DATAFILE

specifies the datafiles of the database. You must list all datafiles. These files must all exist, although they may be restored backups that require media recovery. See the syntax description of filespec in "filespec".

MAXLOGFILES integer

specifies the maximum number of online redo log file groups that can ever be created for the database. Oracle uses this value to determine how much space in the control file to allocate for the names of redo log files. The default and maximum values depend on your operating system. The value that you specify should not be less than the greatest GROUP value for any redo log file group.

MAXLOGMEMBERS integer

specifies the maximum number of members, or identical copies, for a redo log file group. Oracle uses this value to determine how much space in the control file to allocate for the names of redo log files. The minimum value is 1. The maximum and default values depend on your operating system.

MAXLOGHISTORY integer

specifies the maximum number of archived redo log file groups for automatic media recovery of the Oracle Parallel Server. Oracle uses this value to determine how much space in the control file to allocate for the names of archived redo log files. The minimum value is 0. The default value is a multiple of the MAXINSTANCES value and depends on your operating system. The maximum value is limited only by the maximum size of the control file. This parameter is useful only if you are using Oracle with the Parallel Server option in both parallel mode and archivelog mode.

MAXDATAFILES integer

specifies the initial sizing of the datafiles section of the control file at CREATE DATABASE or CREATE CONTROLFILE time. An attempt to add a file whose number is greater than MAXDATAFILES, but less than or equal to DB_FILES, causes the Oracle control file to expand automatically so that the datafiles section can accommodate more files.

The number of datafiles accessible to your instance is also limited by the initialization parameter DB_FILES.

MAXINSTANCES integer

specifies the maximum number of instances that can simultaneously have the database mounted and open. This value takes precedence over the value of the initialization parameter INSTANCES. The minimum value is 1. The maximum and default values depend on your operating system.

ARCHIVELOG

establishes the mode of archiving the contents of redo log files before reusing them. This clause prepares for the possibility of media recovery as well as instance or crash recovery.

NOARCHIVELOG

If you omit both the ARCHIVELOG clause and NOARCHIVELOG clause, Oracle chooses NOARCHIVELOG mode by default. After creating the control file, you can change between ARCHIVELOG mode and NOARCHIVELOG mode with the ALTER DATABASE statement.

CHARACTER SET character_set

optionally reconstructs character set information in the control file. In case media recovery of the database is required, this information will be available before the database is open, so that tablespace names can be correctly interpreted during recovery. This clause is useful only if you are using a character set other than the default US7ASCII.
If you are re-creating your control file and you are using Recovery Manager for tablespace recovery, and if you specify a different character set from the one stored in the data dictionary, then tablespace recovery will not succeed. (However, at database open, the control file character set will be updated with the correct character set from the data dictionary.) For more information on tablespace recovery, see Oracle8i Backup and Recovery Guide

Note: You cannot modify the character set of the database with this clause.

Example

This statement re-creates a control file. In this statement, database ORDERS_2 was created with the F7DEC character set.

CREATE CONTROLFILE REUSE 
   DATABASE orders_2 
   LOGFILE GROUP 1 ('diskb:log1.log', 'diskc:log1.log') SIZE 50K, 
           GROUP 2 ('diskb:log2.log', 'diskc:log2.log') SIZE 50K 
   NORESETLOGS 
   DATAFILE 'diska:dbone.dat' SIZE 2M 
                              MAXLOGFILES 5 
                              MAXLOGHISTORY 100 
                              MAXDATAFILES 10 
                              MAXINSTANCES 2 
                              ARCHIVELOG
   CHARACTER SET F7DEC;

CREATE DATABASE

Syntax

WARNING:
This statement prepares a database for initial use and erases any data currently in the specified files. Use this statement only when you understand its ramifications.

autoextend_clause::=

maxsize_clause::=

filespec: See "filespec".

Purpose

To create a database, making it available for general use.

This statement erases all data in any specified datafiles that already exist in order to prepare them for initial database use. If you use the statement on an existing database, all data in the datafiles is lost.

After creating the database, this statement mounts it in either exclusive or parallel mode (depending on the value of the PARALLEL_SERVER initialization parameter) and opens it, making it available for normal use. You can then create tablespaces and rollback segments for the database. For information on these tasks, see "CREATE ROLLBACK SEGMENT" and "CREATE TABLESPACE".

For more information on modifying a database, see "ALTER DATABASE".

Prerequisites

You must have the OSDBA role enabled.

If the REMOTE_LOGIN_PASSWORDFILE initialization parameter is set to EXCLUSIVE, Oracle returns an error when you attempt to re-create the database. To avoid this message, either set the parameter to SHARED, or re-create your password file before re-creating the database. For more information about the REMOTE_LOGIN_PASSWORDFILE parameter, see Oracle8i Reference.

Keyword and Parameters

Examples

The following statement creates a small database using defaults for all arguments:

CREATE DATABASE;

The following statement creates a database and fully specifies each argument:

CREATE DATABASE newtest 
     
CONTROLFILE REUSE 
LOGFILE
   GROUP 1 ('diskb:log1.log', 'diskc:log1.log') SIZE 50K, 
   GROUP 2 ('diskb:log2.log', 'diskc:log2.log') SIZE 50K 
MAXLOGFILES 5 
MAXLOGHISTORY 100 
DATAFILE 'diska:dbone.dat' SIZE 2M 
MAXDATAFILES 10 
MAXINSTANCES 2 
ARCHIVELOG 
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET JA16SJISFIXED
DATAFILE  
'disk1:df1.dbf' AUTOEXTEND ON
'disk2:df2.dbf' AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

CREATE DATABASE LINK

Syntax

authenticated_clause::=

Purpose

To create a database link. A database link is a schema object in the local database that allows you to access objects on a remote database. The remote database need not be an Oracle system.

Once you have created a database link, you can use it to refer to tables and views on the remote database. You can refer to a remote table or view in a SQL statement by appending @dblink to the table or view name. You can query a remote table or view with the SELECT statement. If you are using Oracle with the distributed option, you can also access remote tables and views using any of the following statements:

For information about accessing remote tables or views with PL/SQL functions, procedures, packages, and datatypes, see Oracle8i Application Developer's Guide - Fundamentals. For information on distributed database systems, see Oracle8i Distributed Database Systems.

Prerequisites

To create a private database link, you must have CREATE DATABASE LINK system privilege. To create a public database link, you must have CREATE PUBLIC DATABASE LINK system privilege.

You must have CREATE SESSION privilege on the remote Oracle database.

Net8 must be installed on both the local and remote Oracle databases.

To access non-Oracle systems you must use the Oracle Heterogeneous Services.

Keyword and Parameters

SHARED

uses a single network connection to create a public database link that can be shared between multiple users. This clause is available only with the multi-threaded server configuration. For more information about shared database links, see Oracle8i Distributed Database Systems.

PUBLIC

creates a public database link available to all users. If you omit this clause, the database link is private and is available only to you.

dblink

is the complete or partial name of the database link. For guidelines for naming database links, see "Referring to Objects in Remote Databases".

Restrictions:

You cannot create a database link in another user's schema, and you cannot qualify dblink with the name of a schema. (Periods are permitted in names of database links, so Oracle interprets the entire name, such as RALPH.LINKTOSALES, as the name of a database link in your schema rather than as a database link named LINKTOSALES in the schema RALPH.)

The number of different database links that can appear in a single statement is limited to the value of the initialization parameter OPEN_LINKS.

CONNECT TO

enables a connection to the remote database.

CURRENT_USER

creates a current user database link. The current user must be a global user with a valid account on the remote database for the link to succeed.
If the database link is used directly, that is, not from within a stored object, then the current user is the same as the connected user.

When executing a stored object (such as a procedure, view, or trigger) that initiates a database link, CURRENT_USER is the username that owns the stored object, and not the username that called the object. For example, if the database link appears inside procedure SCOTT.P (created by SCOTT), and user JANE calls procedure SCOTT.P, the current user is SCOTT.

However, if the stored object is an invoker-rights function, procedure, or package, the invoker's authorization ID is used to connect as a remote user. For example, if the privileged database link appears inside procedure SCOTT.P (an invoker-rights procedure created by SCOTT), and user JANE calls procedure SCOTT.P, then CURRENT_USER is JANE and the procedure executes with JANE's privileges. For more information on invoker-rights functions, see "CREATE FUNCTION".

user IDENTIFIED BY password

is the username and password used to connect to the remote database (fixed user database link). If you omit this clause, the database link uses the username and password of each user who is connected to the database (connected user database link).

authenticated_clause

specifies the username and password on the target instance. This clause authenticates the user to the remote server and is required for security. The specified username and password must be a valid username and password on the remote instance. The username and password are used only for authentication. No other operations are performed on behalf of this user.

You must specify this clause when using the SHARED clause.

USING 'connect string'

specifies the service name of a remote database. For information on specifying remote databases, see Net8 Administrator's Guide.

Examples

CURRENT_USER Example

The following statement defines a current-user database link:

CREATE DATABASE LINK sales.hq.acme.com
   CONNECT TO CURRENT_USER
   USING 'sales';

Fixed User Example

The following statement defines a fixed-user database link named SALES.HQ.ACME.COM:

CREATE DATABASE LINK sales.hq.acme.com 
   CONNECT TO scott IDENTIFIED BY tiger 
   USING 'sales';

Once this database link is created, you can query tables in the schema SCOTT on the remote database in this manner:

SELECT *
   FROM emp@sales.hq.acme.com;

You can also use DML statements to modify data on the remote database:

INSERT INTO accounts@sales.hq.acme.com(acc_no, acc_name, balance)
   VALUES (5001, 'BOWER', 2000);

UPDATE accounts@sales.hq.acme.com 
   SET balance = balance + 500;

DELETE FROM accounts@sales.hq.acme.com 
   WHERE acc_name = 'BOWER';

You can also access tables owned by other users on the same database. This statement assumes SCOTT has access to ADAM's DEPT table:

SELECT *
   FROM adams.dept@sales.hq.acme.com;

The previous statement connects to the user SCOTT on the remote database and then queries ADAM's DEPT table.

A synonym may be created to hide the fact that SCOTT's EMP table is on a remote database. The following statement causes all future references to EMP to access a remote EMP table owned by SCOTT:

CREATE SYNONYM emp 
   FOR scott.emp@sales.hq.acme.com;

PUBLIC Example

The following statement defines a shared public fixed user database link named SALES.HQ.ACME.COM that refers to user SCOTT with password TIGER on the database specified by the string service name 'SALES':

CREATE SHARED PUBLIC DATABASE LINK sales.hq.acme.com 
   CONNECT TO scott IDENTIFIED BY tiger 
   AUTHENTICATED BY anupam IDENTIFIED BY bhide
   USING 'sales';

CREATE DIMENSION

Syntax

level_clause::=

hierarchy_clause::=

join_clause::=

attribute_clause::=

Purpose

To create a dimension. A dimension defines a parent-child relationship between pairs of column sets, where all the columns of a column set must come from the same table. However, columns in one column set (or "level") can come from a different table than columns in another set. The optimizer uses these relationships with materialized views to perform query rewrite. The Summary Advisor uses these relationships to recommend creation of specific materialized views. For more information on materialized views, see "CREATE MATERIALIZED VIEW / SNAPSHOT". For more information on query rewrite, the optimizer and the Summary Advisor, see Oracle8i Tuning.

Prerequisites

To create a dimension in your own schema, you must have the CREATE DIMENSION system privilege. To create a dimension in another user's schema, you must have the CREATE ANY DIMENSION system privilege. In either case, you must have the SELECT object privilege on any objects referenced in the dimension.

Keywords and Parameters

FORCE

creates the dimension even if tables referenced in this statement do not yet exist, or you do not have SELECT object privilege on those tables.

Note: Even if you specify FORCE, the optimizer cannot use this dimension for query rewrite until the tables exist and you have appropriate object privileges on them.

NOFORCE

creates the dimension only if the referenced objects exist and you have appropriate privileges on those objects. This is the default.

schema

is the schema in which the dimension will be created. If you do not specify schema, Oracle creates the dimension in your own schema.

dimension

is the name of the dimension. The name must be unique within its schema.

level_clause

defines a level in the dimension. A level defines dimension hierarchies and attributes.

level

is the name of the level

level_table . level_column

specifies from the columns in the level. You can specify up to 32 columns.
Restrictions:

All of the columns in a level must come from the same table.

The set of columns you specify must be unique to this level.

The columns you specify cannot be specified in any other dimension.

Each level_column must be non-null. (However, these columns need not have NOT NULL constraints.)

hierarchy_clause

defines a linear hierarchy of levels in the dimension. Each hierarchy forms a chain of parent-child relationships among the levels in the dimension. Hierarchies in a dimension are independent of each other. They may (but need not) have columns in common.
Each level in the dimension should be specified at most once in this clause, and each level must already have been named in the level_clause.

hierarchy

is the name of the hierarchy. This name must be unique in the dimension.

child_level

is the name of a level that has an n:1 relationship with a parent level: the level_columns of child_level cannot be null, and each child_level value uniquely determines the value of the next named parent_level.
If the child level_table is different from the parent level_table, you must specify a join relationship between them in the join_clause.

parent_level

is the name of a level.

join_clause

specifies an inner equijoin relationship for a dimension whose columns are contained in multiple tables. This clause is required and permitted only when the columns specified in the hierarchy are not all in the same table.
Restrictions:

The child_key_columns must be non-null and the parent key must be unique and non-null. You need not define constraints to enforce these conditions, but queries may return incorrect results if these conditions are not true.

Each child key must join with a key in the parent_level table.

Self-joins are not permitted (that is, the child_key_columns cannot be in the same table as parent_level).

child_key_column

specifies one or more columns that are join-compatible with columns in the parent level.
If you do not specify the schema and table of each child_column, the schema and table are inferred from the CHILD OF relationship in the hierarchy_clause. If you do specify the schema and column of a child_key_column, the schema and table must match the schema and table of columns that comprise the child of parent_level in the hierarchy_clause.

Restrictions:

All of the child-key columns must come from the same table.

The number of child-key columns must match the number of columns in parent_level, and the columns must be joinable.

Do not specify multiple child key columns unless the parent level consists of multiple columns.

You can specify only one join_clause for a given pair of levels in the same hierarchy.

parent_level

is the name of a level.

attribute_clause

specifies the columns that are uniquely determined by a hierarchy level. The columns in level must all come from the same table as the dependent_columns. The dependent_columns need not have been specified in the level_clause.
For example, if the hierarchy levels are city, state, and country, then city might determine mayor, state might determine governor, and country might determine president.

Examples

This statement creates a TIME dimension on table TIME_TAB, and creates a GEOG dimension on tables CITY, STATE, and COUNTRY.

CREATE DIMENSION time
   LEVEL curDate         IS time_tab.curDate
   LEVEL month           IS time_tab.month
   LEVEL qtr             IS time_tab.qtr
   LEVEL year            IS time_tab.year
   LEVEL fiscal_week     IS time_tab.fiscal_week
   LEVEL fiscal_qtr      IS time_tab.fiscal_qtr
   LEVEL fiscal_year     IS time_tab.fiscal_year
   HIERARCHY month_rollup (
      curDate            CHILD OF
      month              CHILD OF
      qtr                CHILD OF
      year)
   HIERARCHY fiscal_year_rollup (
      curDate            CHILD OF
      fiscal_week        CHILD OF
      fiscal_qtr         CHILD OF
      fiscal_year )
   ATTRIBUTE curDate     DETERMINES (holiday, dayOfWeek)
   ATTRIBUTE month       DETERMINES (yr_ago_month, qtr_ago_month)
   ATTRIBUTE fiscal_qtr  DETERMINES yr_ago_qtr
   ATTRIBUTE year        DETERMINES yr_ago ;

CREATE DIMENSION geog
   LEVEL cityID         IS (city.city, city.state)
   LEVEL stateID        IS state.state
   LEVEL countryID      IS country.country
   HIERARCHY political_rollup (
      cityID            CHILD OF
      stateID           CHILD OF
      countryID 
         JOIN KEY city.state REFERENCES stateID
         JOIN KEY state.country REFERENCES countryID);

CREATE DIRECTORY

Syntax

Purpose

To create a directory object. A directory object specifies an alias for a directory on the server's file system where external binary file LOBs (BFILEs) are located. You can use directory names when referring to BFILEs in your PL/SQL code and OCI calls, rather than hard-coding the operating system pathname, thereby allowing greater file management flexibility. For more information on BFILE objects, see "Large Object (LOB) Datatypes".

All directories are created in a single namespace and are not owned by an individual's schema. You can secure access to the BFILEs stored within the directory structure by granting object privileges on the directories to specific users. for more information on granting object privileges, see "Large Object (LOB) Datatypes".

When you create a directory, you are automatically granted the READ object privilege and can grant READ privileges to other users and roles. The DBA can also grant this privilege to other users and roles.

Prerequisites

You must have CREATE ANY DIRECTORY system privileges to create directories.

You must also create a corresponding operating system directory for file storage. Your system or database administrator must ensure that the operating system directory has the correct read permissions for Oracle processes.

Privileges granted for the directory are created independently of the permissions defined for the operating system directory. Therefore, the two may or may not correspond exactly. For example, an error occurs if user SCOTT is granted READ privilege on the directory schema object, but the corresponding operating system directory does not have READ permission defined for Oracle processes.

Keywords and Parameters

OR REPLACE

re-creates the directory database object if it already exists. You can use this clause to change the definition of an existing directory without dropping, re-creating, and regranting database object privileges previously granted on the directory.
Users who had previously been granted privileges on a redefined directory can still access the directory without being regranted the privileges
For information on removing a directory from the database, see "DROP DIRECTORY".

directory

is the name of the directory object to be created. The maximum length of directory is 30 bytes. You cannot qualify a directory object with a schema name.

Note: Oracle does not verify that the directory you specify actually exists. Therefore, take care that you specify a valid directory in your operating system. In addition, if your operating system uses case-sensitive pathnames, be sure you specify the directory in the correct format. (However, you need not include a trailing slash at the end of the pathname.)

'path_name'

is the full pathname of the operating system directory on the server where the files are located. The single quotes are required, with the result that the path name is case sensitive.

Example

The following statement redefines directory database object BFILE_DIR to enable access to BFILEs stored in the operating system directory /PRIVATE1/LOB/FILES:

CREATE OR REPLACE DIRECTORY bfile_dir AS '/private1/LOB/files';

CREATE FUNCTION

Syntax

invoker_rights_clause::=

call_spec::=

Java_declaration::=

C_declaration::=

Purpose

To create a stored function or a call specification.

A stored function (also called a user function) is a set of PL/SQL statements you can call by name. Stored functions are very similar to procedures, except that a function returns a value to the environment in which it is called. User functions can be used as part of a SQL expression. For a general discussion of procedures and functions, see "CREATE PROCEDURE". For examples of creating functions, see "Examples".

A call specification declares a Java method or a third-generation language (3GL) routine so that it can be called from SQL and PL/SQL. The call specification tells Oracle which Java method, or which named function in which shared library, to invoke when a call is made. It also tells Oracle what type conversions to make for the arguments and return value.

The CREATE FUNCTION statement creates a function as a standalone schema object. You can also create a function as part of a package. For information on creating packages, see "CREATE PACKAGE".

For information on modifying a function, see "ALTER FUNCTION". For information on shared libraries, see "CREATE LIBRARY". For information on dropping a standalone function, see "DROP FUNCTION". For more information about registering external functions, see Oracle8i Application Developer's Guide - Fundamentals.

Prerequisites

Before a stored function can be created, the user SYS must run the SQL script DBMSSTDX.SQL. The exact name and location of this script depend on your operating system.

To create a function in your own schema, you must have the CREATE PROCEDURE system privilege. To create a function in another user's schema, you must have the CREATE ANY PROCEDURE system privilege. To replace a function in another user's schema, you must have the ALTER ANY PROCEDURE system privilege.

To invoke a call specification, you may need additional privileges (for example, EXECUTE privileges on C library for a C call specification). For more information on such prerequisites, refer to PL/SQL User's Guide and Reference or Oracle8i Java Stored Procedures Developer's Guide.

To embed a CREATE FUNCTION statement inside an Oracle precompiler program, you must terminate the statement with the keyword END-EXEC followed by the embedded SQL statement terminator for the specific language.

Keywords and Parameters

OR REPLACE

re-creates 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. If you redefine a function, Oracle recompiles it. For information on recompiling functions, see "ALTER 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 marks the indexes DISABLED.

schema

is the schema to contain the function. If you omit schema, Oracle creates the function in your current schema.

function

is the name of the function to be created. If creating the function results in compilation errors, Oracle returns an error. You can see the associated compiler error messages with the SHOW ERRORS command.

Restrictions on User-Defined Functions
User-defined functions cannot be used in situations that require an unchanging definition. Thus, 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 enforces these restrictions not only for the function called directly from the SQL statement, but also for any functions that function calls, and on any functions called from the SQL statements executed by that function or any function it calls.

argument

is 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 the function will set 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.

NOCOPY

instructs Oracle to pass this argument as fast as possible. This clause can significantly enhance performance when passing a large value like a record, a PL/SQL table, or a varray to an OUT or IN OUT parameter. (IN parameter values are always passed NOCOPY.)

When you specify NOCOPY, assignments made to a package variable may show immediately in this parameter (or assignments made to this parameter may show immediately in a package variable) if the package variable is passed as the actual assignment corresponding to this parameter.

Similarly, 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 would not matter.

datatype

is the datatype of an argument. An argument can have any datatype supported by PL/SQL.

The datatype cannot specify a length, precision, or scale. Oracle 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 PL/SQL.

The datatype cannot specify a length, precision, or scale. Oracle derives the length, precision, or scale of the return value from the environment from which the function is called. For information on PL/SQL datatypes, see PL/SQL User's Guide and Reference.

invoker_rights_clause

lets you specify whether the function executes with the privileges and in the schema of the user who owns it or with the privileges and in the schema of CURRENT_USER. For information on how CURRENT_USER is determined, see Oracle8i Concepts and Oracle8i Application Developer's Guide - Fundamentals.
This clause also determines how Oracle resolves external names in queries, DML operations, and dynamic SQL statements in the function. For more information refer to PL/SQL User's Guide and Reference.

AUTHID CURRENT_USER

specifies that the function executes with the privileges of CURRENT_USER. This clause creates an "invoker-rights function."
This clause also specifies that external names in queries, DML operations, and dynamic SQL statements resolve in the schema of CURRENT_USER. External names in all other statements resolve in the schema in which the function resides.

AUTHID DEFINER

specifies that the function executes with the privileges of the owner of the schema in which the function resides, and that external names resolve in the schema where the function resides. This is the default.

DETERMINISTIC

is an optimization hint that allows the system to use a saved copy of the function's return result (if such a copy is available). The saved copy could come from a materialized view, a function-based index, or a redundant call to the same function in the same SQL statement. The query optimizer can choose whether to use the saved copy or re-call the function.

The function should reliably return the same result value whenever it is called with the same values for its arguments. Therefore, do not define the function to use package variables or to access the database in any way that might affect the function's return result, because the results of doing so will not be captured if the system chooses not to call the function.

A function must be declared DETERMINISTIC in order to be called in the expression of a function-based index, or from the query of a materialized view if that view is marked REFRESH FAST or ENABLE QUERY REWRITE.

For information on materialized views, see Oracle8i Tuning. For information on function-based indexes, see "CREATE INDEX".

PARALLEL_ENABLE

is an optimization hint indicating that the function can be executed from a parallel execution server of a parallel query operation. The function should not use session state, such as package variables, as those variables may not be shared among the parallel execution servers. For more information on these concepts, see Oracle8i Application Developer's Guide - Fundamentals.

pl/sql_subprogram_body

declares the function in a PL/SQL subprogram body. For more information on PL/SQL subprograms, see Oracle8i Application Developer's Guide - Fundamentals.

call_spec

maps a Java or C method name, parameter types, and return type to their SQL counterparts.

In Java_declaration, 'string' identifies the Java implementation of the method. For more information, see Oracle8i Java Stored Procedures Developer's Guide.

For an explanation of the parameters and semantics of the C_declaration, see Oracle8i Application Developer's Guide - Fundamentals.

AS EXTERNAL

is an alternative way of declaring a C method. This clause has been deprecated and is supported for backward compatibility only. Oracle Corporation recommends that you use the AS LANGUAGE C syntax.

Examples

The following statement creates the function GET_BAL.

CREATE FUNCTION get_bal(acc_no IN NUMBER) 
   RETURN NUMBER 
   IS acc_bal NUMBER(11,2);
   BEGIN 
      SELECT balance 
      INTO acc_bal 
      FROM accounts 
      WHERE account_id = acc_no; 
      RETURN(acc_bal); 
    END;

The GET_BAL function returns the balance of a specified account.

When you call the function, you must specify the argument ACC_NO, the number of the account whose balance is sought. The datatype of ACC_NO is NUMBER.

The function returns the account balance. The RETURN clause of the CREATE FUNCTION statement specifies the datatype of the return value to be NUMBER.

The function uses a SELECT statement to select the BALANCE column from the row identified by the argument ACC_NO in the ACCOUNTS table. The function uses a RETURN statement to return this value to the environment in which the function is called.

The function created above can be used in a SQL statement. For example:

SELECT get_bal(100) FROM DUAL;

The following statement creates PL/SQL standalone function GET_VAL that registers the C routine C_GET_VAL as an external function. (The parameters have been omitted from this example.)

CREATE FUNCTION get_val
     
( x_val IN NUMBER,
y_val IN NUMBER,
image IN LONG RAW )
RETURN BINARY_INTEGER AS LANGUAGE C
   NAME "c_get_val"
   LIBRARY c_utils
   PARAMETERS (...);

CREATE INDEX

Syntax

cluster_index_clause::=

table_index_clause::=

index_expr_list::=

index_attributes::=

physical_attributes_clause::=

domain_index_clause::=

global_index_clause::=

local_index_clauses::=

on_range_partitioned_table_clause::=

segment_attributes_clause::=

on_hash_partitioned_table_clause::=

on_composite_partitioned_table_clause::=

index_subpartition_clause::=

global_partition_clause::=

parallel_clause::=

storage_clause: See "storage_clause".

Purpose

To create an index on

One or more columns of a table, a partitioned table, an index-organized table, or a cluster
One or more scalar typed object attributes of a table or a cluster
A nested table storage table for indexing a nested table column

To create a domain index, which is an instance of an application-specific index of type indextype.

An index is a schema object that contains an entry for each value that appears in the indexed column(s) of the table or cluster and provides direct, fast access to rows. A partitioned index consists of partitions containing an entry for each value that appears in the indexed column(s) of the table. A function-based index is an index on expressions. It enables you to construct queries that evaluate the value returned by an expression, which in turn may include functions (built-in or user-defined).

For a discussion of indexes, see Oracle8i Concepts. For information on modifying an index, see "ALTER INDEX".

Prerequisites

To create an index in your own schema, one of the following conditions must be true:

The table or cluster to be indexed must be in your own schema.
You must have INDEX privilege on the table to be indexed.
You must have CREATE ANY INDEX system privilege.

To create an index in another schema, you must have CREATE ANY INDEX system privilege. Also, the owner of the schema to contain the index must have either space quota on the tablespaces to contain the index or index partitions, or UNLIMITED TABLESPACE system privilege.

To create a domain index in your own schema, you must also have EXECUTE privilege on the indextype. If you are creating a domain index in another user's schema, the index owner also must have EXECUTE privilege on the indextype and its underlying implementation type. Before creating a domain index, you should first define the indextype. See "CREATE INDEXTYPE".

To create a function-based index in your own schema on your own table, you must have the QUERY REWRITE system privilege. To create the index in another schema or on another schema's table, you must have the GLOBAL QUERY REWRITE privilege. The table owner must also have the EXECUTE object privilege on the function(s) used in the function-based index.

Keywords and Parameters

UNIQUE

specifies that the value of the column (or columns) upon which the index is based must be unique. If the index is local nonprefixed (see local_index_clause below), then the index key must contain the partitioning key.

Oracle recommends that you do not explicitly define UNIQUE indexes on tables. Uniqueness is strictly a logical concept and should be associated with the definition of a table. Therefore, define UNIQUE integrity constraints on the desired columns. For more information on constraints, see "constraint_clause".

Restrictions:

You cannot specify both UNIQUE and BITMAP.

You cannot specify UNIQUE for a domain index.

BITMAP

specifies that index is to be created as a bitmap, rather than as a B-tree. Bitmap indexes store the rowids associated with a key value as a bitmap. Each bit in the bitmap corresponds to a possible rowid, and if the bit is set, it means that the row with the corresponding rowid contains the key value. The internal representation of bitmaps is best suited for applications with low levels of concurrent transactions, such as data warehousing. See Oracle8i Concepts and Oracle8i Tuning for more information about using bitmap indexes.
Restrictions:

You cannot specify BITMAP when creating a global partitioned index or an index-organized table.

You cannot specify both UNIQUE and BITMAP.

You cannot specify BITMAP for a domain index.

schema

is the schema to contain the index. If you omit schema, Oracle creates the index in your own schema.

index

is the name of the index to be created. An index can contain several partitions.

cluster_index_clause

specifies the cluster for which a cluster index is to be created. If you do not qualify cluster with schema, Oracle assumes the cluster is in your current schema. You cannot create a cluster index for a hash cluster. For more information on clusters, see "CREATE CLUSTER".

table_index_clause

specifies table (and its attributes) on which you are defining the index. If you do not qualify table with schema, Oracle assumes the table is contained in your own schema.

You create an index on a nested table column by creating the index on the nested table storage table. Include the NESTED_TABLE_ID pseudocolumn of the storage table to create a UNIQUE index, which effectively ensures that the rows of a nested table value are distinct.

Restrictions:

If the index is local, then table must be partitioned.

If the table is index-organized, this statement creates a secondary index. You cannot specify BITMAP or REVERSE for this secondary index, and the combined size of the index key and the logical rowid should be less than half the block size.

If table is a temporary table, the index will also be temporary with the same scope (session or transaction) as table. The following restrictions apply to indexes on temporary table:

- The index cannot be a partitioned index or a domain index.

- You cannot specify the physical_attributes_clause or the parallel_clause.

- You cannot specify LOGGING, NOLOGGING, or TABLESPACE.

For more information on temporary tables, see "CREATE TABLE" and Oracle8i Concepts.

t_alias

specifies a correlation name (alias) for the table upon which you are building the index.

Note: This alias is required if the index_expression_list references any object type attributes or object type methods. See "Function-based Index on Type Method Example".

index_expr_list

lets you specify the column or column expression upon which the index is based.

column

is the name of a column in the table. A bitmap index can have a maximum of 30 columns. Other indexes can have as many as 32 columns.
Restriction: You cannot create an index on columns or attributes whose type is user-defined, LONG, LONG RAW, LOB, or REF, except that Oracle supports an index on REF type columns or attributes that have been defined with a SCOPE clause.

You can create an index on a scalar object attribute column or on the system-defined NESTED_TABLE_ID column of the nested table storage table. If you specify an object attribute column, the column name must be qualified with the table name. If you specify a nested table column attribute, it must be qualified with the outermost table name, the containing column name, and all intermediate attribute names leading to the nested table column attribute.

column_expression

is an expression built from columns of table, constants, SQL functions, and user-defined functions. When you specify column_expression, you create a function-based index.
Name resolution of the function is based on the schema of the index creator. User-defined functions used in column_expression are fully name resolved during the CREATE INDEX operation.

After creating a function-based index, collect statistics on both the index and its base table using the ANALYZE statement (see "ANALYZE"). Oracle cannot use the function-based index until these statistics have been generated.

When you subsequently query a table that uses a function-based index, you must ensure in the query that column_expression is not null. See the Function-Based Index Example .

If the function on which the index is based becomes invalid or is dropped, Oracle marks the index DISABLED. Queries on a DISABLED index fail if the optimizer chooses to use the index. DML operations on a DISABLED index fail unless

The index is also marked UNUSABLE and

The parameter SKIP_UNUSABLE is set to true (see "ALTER SESSION" and "ALTER SYSTEM" for more information on this parameter).

Oracle's use of function-based indexes is also affected by the setting of the QUERY_REWRITE_ENABLED session parameter. For more information, see "ALTER SESSION".

Restrictions on function-based indexes:

Any user-defined function referenced in column_expression must be DETERMINISTIC. For more information, see "CREATE FUNCTION" and PL/SQL User's Guide and Reference.

For a function-based globally partitioned index, the column_expression cannot be the partitioning key.

All functions must be specified with parentheses, even if they have no parameters. Otherwise Oracle interprets them as column names.

Any function you specify in column_expression must return a repeatable value. For example, you cannot specify the SYSDATE or USER function or the ROWNUM pseudocolumn.

You cannot build a function-based index on LOB, REF, nested table, or varray columns. In addition, the function in column_expression cannot take as arguments any objects with attributes of type LOB, REF, nested table, or varray.

The column_expression cannot contain any aggregate functions.

Note: If a public synonym for a function, package, or type is used in column_expression, and later an actual object with the same name is created in the table owner's schema, then Oracle will disable the function-based index. When you subsequently enable the function-based index using ALTER INDEX ... ENABLE or ALTER INDEX ... REBUILD, the function, package, or type used in the column_expression will continue to resolve to the function, package, or type to which the public synonym originally pointed. It will not resolve to the new function, package, or type.

ASC | DESC

specifies whether the index should be created in ascending or descending order. Indexes on character data are created in ascending or descending order of the character values in the database character set.
Oracle treats descending indexes as if they were function-based indexes. You do not need the QUERY REWRITE or GLOBAL QUERY REWRITE privileges to create them, as you do with other function-based indexes. However, as with other function-based indexes, Oracle does not use descending indexes until you first analyze the index and the table on which the index is defined. See the column_expression clause of this statement.
Restriction: You cannot specify either of these clauses for a domain index. You cannot specify DESC for a bitmapped index or a reverse index.

index_attributes

physical_attributes_clause

establishes values for physical and storage characteristics for the index. See "CREATE TABLE".
Restriction: You cannot specify the PCTUSED parameter for an index.

PCTFREE

is the percentage of space to leave free for updates and insertions within each of the index's data blocks.

storage_clause

establishes the storage characteristics for the index. See the "

OR REPLACE	redefines an existing context namespace using a different package.
namespace	is the name of the context namespace to create or modify. Context namespaces are always stored in the schema SYS.
schema	is the schema owning package. If you omit schema, Oracle uses the current schema.
package	is the PL/SQL package that sets or resets the context attributes under the namespace for a user session. For more information on setting the package, see Oracle8i Supplied Packages Reference.
Note: To provide some design flexibility, Oracle does not verify the existence of the schema or the validity of the package at the time you create the context.

REUSE	specifies that existing control files identified by the initialization parameter CONTROL_FILES can be reused, thus ignoring and overwriting any information they may currently contain. If you omit this clause and any of these control files already exists, Oracle returns an error.
DATABASE database	specifies the name of the database. The value of this parameter must be the existing database name established by the previous CREATE DATABASE statement or CREATE CONTROLFILE statement.
SET DATABASE database	changes the name of the database. The name of a database can be as long as eight bytes.
LOGFILE	specifies the redo log files for your database. You must list all members of all redo log file groups. See the syntax description of filespec in "filespec".
	GROUP integer	specifies logfile group. If you specify GROUP values, Oracle verifies these values with the GROUP values when the database was last open.
RESETLOGS	ignores the contents of the files listed in the LOGFILE clause. These files do not have to exist. Each filespec in the LOGFILE clause must specify the SIZE parameter. Oracle assigns all online redo log file groups to thread 1 and enables this thread for public use by any instance. After using this clause, you must open the database using the RESETLOGS clause of the ALTER DATABASE statement.
NORESETLOGS	specifies that all files in the LOGFILE clause should be used as they were when the database was last open. These files must exist and must be the current online redo log files rather than restored backups. Oracle reassigns the redo log file groups to the threads to which they were previously assigned and reenables the threads as they were previously enabled.
DATAFILE	specifies the datafiles of the database. You must list all datafiles. These files must all exist, although they may be restored backups that require media recovery. See the syntax description of filespec in "filespec".
MAXLOGFILES integer	specifies the maximum number of online redo log file groups that can ever be created for the database. Oracle uses this value to determine how much space in the control file to allocate for the names of redo log files. The default and maximum values depend on your operating system. The value that you specify should not be less than the greatest GROUP value for any redo log file group.
MAXLOGMEMBERS integer	specifies the maximum number of members, or identical copies, for a redo log file group. Oracle uses this value to determine how much space in the control file to allocate for the names of redo log files. The minimum value is 1. The maximum and default values depend on your operating system.
MAXLOGHISTORY integer	specifies the maximum number of archived redo log file groups for automatic media recovery of the Oracle Parallel Server. Oracle uses this value to determine how much space in the control file to allocate for the names of archived redo log files. The minimum value is 0. The default value is a multiple of the MAXINSTANCES value and depends on your operating system. The maximum value is limited only by the maximum size of the control file. This parameter is useful only if you are using Oracle with the Parallel Server option in both parallel mode and archivelog mode.
MAXDATAFILES integer	specifies the initial sizing of the datafiles section of the control file at CREATE DATABASE or CREATE CONTROLFILE time. An attempt to add a file whose number is greater than MAXDATAFILES, but less than or equal to DB_FILES, causes the Oracle control file to expand automatically so that the datafiles section can accommodate more files.
	The number of datafiles accessible to your instance is also limited by the initialization parameter DB_FILES.
MAXINSTANCES integer	specifies the maximum number of instances that can simultaneously have the database mounted and open. This value takes precedence over the value of the initialization parameter INSTANCES. The minimum value is 1. The maximum and default values depend on your operating system.
ARCHIVELOG	establishes the mode of archiving the contents of redo log files before reusing them. This clause prepares for the possibility of media recovery as well as instance or crash recovery.
NOARCHIVELOG	If you omit both the ARCHIVELOG clause and NOARCHIVELOG clause, Oracle chooses NOARCHIVELOG mode by default. After creating the control file, you can change between ARCHIVELOG mode and NOARCHIVELOG mode with the ALTER DATABASE statement.
CHARACTER SET character_set	optionally reconstructs character set information in the control file. In case media recovery of the database is required, this information will be available before the database is open, so that tablespace names can be correctly interpreted during recovery. This clause is useful only if you are using a character set other than the default US7ASCII. If you are re-creating your control file and you are using Recovery Manager for tablespace recovery, and if you specify a different character set from the one stored in the data dictionary, then tablespace recovery will not succeed. (However, at database open, the control file character set will be updated with the correct character set from the data dictionary.) For more information on tablespace recovery, see Oracle8i Backup and Recovery Guide
	Note: You cannot modify the character set of the database with this clause.

database	is the name of the database to be created and can be up to 8 bytes long. The database name can contain only ASCII characters. Oracle writes this name into the control file. If you subsequently issue an ALTER DATABASE statement that explicitly specifies a database name, Oracle verifies that name with the name in the control file. Database names should also adhere to the rules described in "Schema Object Naming Rules".
	Note: You cannot use special characters from European or Asian character sets in a database name. For example, characters with umlauts are not allowed.
	If you omit the database name from a CREATE DATABASE statement, Oracle uses the name specified by the initialization parameter DB_NAME. If the DB_NAME initialization parameter has been set, and you specify a different name from the value of that parameter, Oracle returns an error.
CONTROLFILE REUSE	reuses existing control files identified by the initialization parameter CONTROL_FILES, thus ignoring and overwriting any information they currently contain. Normally you use this clause only when you are re-creating a database, rather than creating one for the first time. You cannot use this clause if you also specify a parameter value that requires that the control file be larger than the existing files. These parameters are MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, MAXDATAFILES, and MAXINSTANCES.
	If you omit this clause and any of the files specified by CONTROL_FILES already exist, Oracle returns an error.
LOGFILE	specifies one or more files to be used as redo log files. Each filespec specifies a redo log file group containing one or more redo log file members (copies). For the syntax of filespec, see "filespec". All redo log files specified in a CREATE DATABASE statement are added to redo log thread number 1.
	GROUP integer	uniquely identifies a redo log file group and can range from 1 to the value of the MAXLOGFILES parameter. A database must have at least two redo log file groups. You cannot specify multiple redo log file groups having the same GROUP value. If you omit this parameter, Oracle generates its value automatically. You can examine the GROUP value for a redo log file group through the dynamic performance table V$LOG.
	If you omit the LOGFILE clause, Oracle creates two redo log file groups by default. The names and sizes of the default files depend on your operating system.
MAXLOGFILES integer	specifies the maximum number of redo log file groups that can ever be created for the database. Oracle uses this value to determine how much space in the control file to allocate for the names of redo log files. The default, minimum, and maximum values depend on your operating system.
MAXLOGMEMBERS integer	specifies the maximum number of members, or copies, for a redo log file group. Oracle uses this value to determine how much space in the control file to allocate for the names of redo log files. The minimum value is 1. The maximum and default values depend on your operating system.
MAXLOGHISTORY integer	specifies the maximum number of archived redo log files for automatic media recovery with Oracle Parallel Server. Oracle uses this value to determine how much space in the control file to allocate for the names of archived redo log files. The minimum value is 0. The default value is a multiple of the MAXINSTANCES value and depends on your operating system. The maximum value is limited only by the maximum size of the control file.
	Note: This parameter is useful only if you are using Oracle with the Parallel Server option in parallel mode, and archivelog mode enabled.
MAXDATAFILES integer	specifies the initial sizing of the datafiles section of the control file at CREATE DATABASE or CREATE CONTROLFILE time. An attempt to add a file whose number is greater than MAXDATAFILES, but less than or equal to DB_FILES, causes the Oracle control file to expand automatically so that the datafiles section can accommodate more files.
	The number of datafiles accessible to your instance is also limited by the initialization parameter DB_FILES.
MAXINSTANCES integer	specifies the maximum number of instances that can simultaneously have this database mounted and open. This value takes precedence over the value of initialization parameter INSTANCES. The minimum value is 1. The maximum and default values depend on your operating system.
ARCHIVELOG	specifies that the contents of a redo log file group must be archived before the group can be reused. This clause prepares for the possibility of media recovery.
NOARCHIVELOG	specifies that the contents of a redo log file group need not be archived before the group can be reused. This clause does not allow for the possibility of media recovery.
	The default is NOARCHIVELOG mode. After creating the database, you can change between ARCHIVELOG mode and NOARCHIVELOG mode with the ALTER DATABASE statement.
CHARACTER SET	specifies the character set the database uses to store data. You cannot change the database character set after creating the database. The supported character sets and default value of this parameter depend on your operating system.
	Restriction: You cannot specify any fixed-width multibyte character sets as the database character set. For more information about character sets, see Oracle8i National Language Support Guide.
NATIONAL CHARACTER SET	specifies the national character set used to store data in columns specifically defined as NCHAR, NCLOB, or NVARCHAR2. If not specified, the national character set defaults to the database character set. See Oracle8i National Language Support Guide for valid character set names.
DATAFILE	specifies one or more files to be used as datafiles. See the syntax description of filespec in "filespec". All these files become part of the SYSTEM tablespace. If you omit this clause, Oracle creates one datafile by default. The name and size of this default file depend on your operating system.
	Note: Oracle recommends that the total initial space allocated for the SYSTEM tablespace be a minimum of 5 megabytes.
*autoextend_clause*	enables or disables the automatic extension of a datafile. If you do not specify this clause, datafiles are not automatically extended.
	OFF	disables autoextend if it is turned on. NEXT and MAXSIZE are set to zero. Values for NEXT and MAXSIZE must be respecified in ALTER DATABASE AUTOEXTEND or ALTER TABLESPACE AUTOEXTEND statements.
	ON	enables autoextend.
	NEXT	specifies the size in bytes of the next increment of disk space to be allocated to the datafile automatically when more extents are required. Use K or M to specify this size in kilobytes or megabytes. The default is the size of one data block.
	MAXSIZE	specifies the maximum disk space allowed for automatic extension of the datafile.
	UNLIMITED	sets no limit on the allocation of disk space to the datafile.

SHARED	uses a single network connection to create a public database link that can be shared between multiple users. This clause is available only with the multi-threaded server configuration. For more information about shared database links, see Oracle8i Distributed Database Systems.
PUBLIC	creates a public database link available to all users. If you omit this clause, the database link is private and is available only to you.
dblink	is the complete or partial name of the database link. For guidelines for naming database links, see "Referring to Objects in Remote Databases".
	Restrictions: You cannot create a database link in another user's schema, and you cannot qualify dblink with the name of a schema. (Periods are permitted in names of database links, so Oracle interprets the entire name, such as RALPH.LINKTOSALES, as the name of a database link in your schema rather than as a database link named LINKTOSALES in the schema RALPH.) The number of different database links that can appear in a single statement is limited to the value of the initialization parameter OPEN_LINKS.
CONNECT TO	enables a connection to the remote database.
CURRENT_USER	creates a current user database link. The current user must be a global user with a valid account on the remote database for the link to succeed. If the database link is used directly, that is, not from within a stored object, then the current user is the same as the connected user.
	When executing a stored object (such as a procedure, view, or trigger) that initiates a database link, CURRENT_USER is the username that owns the stored object, and not the username that called the object. For example, if the database link appears inside procedure SCOTT.P (created by SCOTT), and user JANE calls procedure SCOTT.P, the current user is SCOTT.
	However, if the stored object is an invoker-rights function, procedure, or package, the invoker's authorization ID is used to connect as a remote user. For example, if the privileged database link appears inside procedure SCOTT.P (an invoker-rights procedure created by SCOTT), and user JANE calls procedure SCOTT.P, then CURRENT_USER is JANE and the procedure executes with JANE's privileges. For more information on invoker-rights functions, see "CREATE FUNCTION".
user IDENTIFIED BY password	is the username and password used to connect to the remote database (fixed user database link). If you omit this clause, the database link uses the username and password of each user who is connected to the database (connected user database link).
*authenticated_clause*	specifies the username and password on the target instance. This clause authenticates the user to the remote server and is required for security. The specified username and password must be a valid username and password on the remote instance. The username and password are used only for authentication. No other operations are performed on behalf of this user.
	You must specify this clause when using the SHARED clause.
USING 'connect string'	specifies the service name of a remote database. For information on specifying remote databases, see Net8 Administrator's Guide.

FORCE	creates the dimension even if tables referenced in this statement do not yet exist, or you do not have SELECT object privilege on those tables.
	Note: Even if you specify FORCE, the optimizer cannot use this dimension for query rewrite until the tables exist and you have appropriate object privileges on them.
NOFORCE	creates the dimension only if the referenced objects exist and you have appropriate privileges on those objects. This is the default.
schema	is the schema in which the dimension will be created. If you do not specify schema, Oracle creates the dimension in your own schema.
dimension	is the name of the dimension. The name must be unique within its schema.
*level_clause*	defines a level in the dimension. A level defines dimension hierarchies and attributes.
	level	is the name of the level
	level_table . level_column	specifies from the columns in the level. You can specify up to 32 columns. Restrictions: All of the columns in a level must come from the same table. The set of columns you specify must be unique to this level. The columns you specify cannot be specified in any other dimension. Each level_column must be non-null. (However, these columns need not have NOT NULL constraints.)
*hierarchy_clause*	defines a linear hierarchy of levels in the dimension. Each hierarchy forms a chain of parent-child relationships among the levels in the dimension. Hierarchies in a dimension are independent of each other. They may (but need not) have columns in common. Each level in the dimension should be specified at most once in this clause, and each level must already have been named in the level_clause.
	hierarchy	is the name of the hierarchy. This name must be unique in the dimension.
	child_level	is the name of a level that has an n:1 relationship with a parent level: the level_columns of child_level cannot be null, and each child_level value uniquely determines the value of the next named parent_level. If the child level_table is different from the parent level_table, you must specify a join relationship between them in the join_clause.
	parent_level	is the name of a level.
*join_clause*	specifies an inner equijoin relationship for a dimension whose columns are contained in multiple tables. This clause is required and permitted only when the columns specified in the hierarchy are not all in the same table. Restrictions: The child_key_columns must be non-null and the parent key must be unique and non-null. You need not define constraints to enforce these conditions, but queries may return incorrect results if these conditions are not true. Each child key must join with a key in the parent_level table. Self-joins are not permitted (that is, the child_key_columns cannot be in the same table as parent_level).
	child_key_column	specifies one or more columns that are join-compatible with columns in the parent level. If you do not specify the schema and table of each child_column, the schema and table are inferred from the CHILD OF relationship in the hierarchy_clause. If you do specify the schema and column of a child_key_column, the schema and table must match the schema and table of columns that comprise the child of parent_level in the hierarchy_clause.
		Restrictions: All of the child-key columns must come from the same table. The number of child-key columns must match the number of columns in parent_level, and the columns must be joinable. Do not specify multiple child key columns unless the parent level consists of multiple columns. You can specify only one join_clause for a given pair of levels in the same hierarchy.
	parent_level	is the name of a level.
*attribute_clause*	specifies the columns that are uniquely determined by a hierarchy level. The columns in level must all come from the same table as the dependent_columns. The dependent_columns need not have been specified in the level_clause. For example, if the hierarchy levels are city, state, and country, then city might determine mayor, state might determine governor, and country might determine president.

OR REPLACE	re-creates the directory database object if it already exists. You can use this clause to change the definition of an existing directory without dropping, re-creating, and regranting database object privileges previously granted on the directory. Users who had previously been granted privileges on a redefined directory can still access the directory without being regranted the privileges For information on removing a directory from the database, see "DROP DIRECTORY".
directory	is the name of the directory object to be created. The maximum length of directory is 30 bytes. You cannot qualify a directory object with a schema name.
	Note: Oracle does not verify that the directory you specify actually exists. Therefore, take care that you specify a valid directory in your operating system. In addition, if your operating system uses case-sensitive pathnames, be sure you specify the directory in the correct format. (However, you need not include a trailing slash at the end of the pathname.)
'path_name'	is the full pathname of the operating system directory on the server where the files are located. The single quotes are required, with the result that the path name is case sensitive.

OR REPLACE	re-creates 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. If you redefine a function, Oracle recompiles it. For information on recompiling functions, see "ALTER 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 marks the indexes DISABLED.
schema	is the schema to contain the function. If you omit schema, Oracle creates the function in your current schema.
function	is the name of the function to be created. If creating the function results in compilation errors, Oracle returns an error. You can see the associated compiler error messages with the SHOW ERRORS command.
	Restrictions on User-Defined Functions User-defined functions cannot be used in situations that require an unchanging definition. Thus, 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 enforces these restrictions not only for the function called directly from the SQL statement, but also for any functions that function calls, and on any functions called from the SQL statements executed by that function or any function it calls.
argument	is 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 the function will set 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.
NOCOPY	instructs Oracle to pass this argument as fast as possible. This clause can significantly enhance performance when passing a large value like a record, a PL/SQL table, or a varray to an OUT or IN OUT parameter. (IN parameter values are always passed NOCOPY.)
	When you specify NOCOPY, assignments made to a package variable may show immediately in this parameter (or assignments made to this parameter may show immediately in a package variable) if the package variable is passed as the actual assignment corresponding to this parameter. Similarly, 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 would not matter.
datatype	is the datatype of an argument. An argument can have any datatype supported by PL/SQL.
	The datatype cannot specify a length, precision, or scale. Oracle 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 PL/SQL.
	The datatype cannot specify a length, precision, or scale. Oracle derives the length, precision, or scale of the return value from the environment from which the function is called. For information on PL/SQL datatypes, see PL/SQL User's Guide and Reference.
*invoker_rights_clause*	lets you specify whether the function executes with the privileges and in the schema of the user who owns it or with the privileges and in the schema of CURRENT_USER. For information on how CURRENT_USER is determined, see Oracle8i Concepts and Oracle8i Application Developer's Guide - Fundamentals. This clause also determines how Oracle resolves external names in queries, DML operations, and dynamic SQL statements in the function. For more information refer to PL/SQL User's Guide and Reference.
	AUTHID CURRENT_USER	specifies that the function executes with the privileges of CURRENT_USER. This clause creates an "invoker-rights function." This clause also specifies that external names in queries, DML operations, and dynamic SQL statements resolve in the schema of CURRENT_USER. External names in all other statements resolve in the schema in which the function resides.
	AUTHID DEFINER	specifies that the function executes with the privileges of the owner of the schema in which the function resides, and that external names resolve in the schema where the function resides. This is the default.
DETERMINISTIC	is an optimization hint that allows the system to use a saved copy of the function's return result (if such a copy is available). The saved copy could come from a materialized view, a function-based index, or a redundant call to the same function in the same SQL statement. The query optimizer can choose whether to use the saved copy or re-call the function.
	The function should reliably return the same result value whenever it is called with the same values for its arguments. Therefore, do not define the function to use package variables or to access the database in any way that might affect the function's return result, because the results of doing so will not be captured if the system chooses not to call the function.
	A function must be declared DETERMINISTIC in order to be called in the expression of a function-based index, or from the query of a materialized view if that view is marked REFRESH FAST or ENABLE QUERY REWRITE.
	For information on materialized views, see Oracle8i Tuning. For information on function-based indexes, see "CREATE INDEX".
PARALLEL_ENABLE	is an optimization hint indicating that the function can be executed from a parallel execution server of a parallel query operation. The function should not use session state, such as package variables, as those variables may not be shared among the parallel execution servers. For more information on these concepts, see Oracle8i Application Developer's Guide - Fundamentals.
pl/sql_subprogram_body	declares the function in a PL/SQL subprogram body. For more information on PL/SQL subprograms, see Oracle8i Application Developer's Guide - Fundamentals.
*call_spec*	maps a Java or C method name, parameter types, and return type to their SQL counterparts. In Java_declaration, 'string' identifies the Java implementation of the method. For more information, see Oracle8i Java Stored Procedures Developer's Guide. For an explanation of the parameters and semantics of the C_declaration, see Oracle8i Application Developer's Guide - Fundamentals.
	AS EXTERNAL	is an alternative way of declaring a C method. This clause has been deprecated and is supported for backward compatibility only. Oracle Corporation recommends that you use the AS LANGUAGE C syntax.

UNIQUE	specifies that the value of the column (or columns) upon which the index is based must be unique. If the index is local nonprefixed (see local_index_clause below), then the index key must contain the partitioning key.
	Oracle recommends that you do not explicitly define UNIQUE indexes on tables. Uniqueness is strictly a logical concept and should be associated with the definition of a table. Therefore, define UNIQUE integrity constraints on the desired columns. For more information on constraints, see "constraint_clause".
	Restrictions: You cannot specify both UNIQUE and BITMAP. You cannot specify UNIQUE for a domain index.
BITMAP	specifies that index is to be created as a bitmap, rather than as a B-tree. Bitmap indexes store the rowids associated with a key value as a bitmap. Each bit in the bitmap corresponds to a possible rowid, and if the bit is set, it means that the row with the corresponding rowid contains the key value. The internal representation of bitmaps is best suited for applications with low levels of concurrent transactions, such as data warehousing. See Oracle8i Concepts and Oracle8i Tuning for more information about using bitmap indexes. Restrictions: You cannot specify BITMAP when creating a global partitioned index or an index-organized table. You cannot specify both UNIQUE and BITMAP. You cannot specify BITMAP for a domain index.
schema	is the schema to contain the index. If you omit schema, Oracle creates the index in your own schema.
index	is the name of the index to be created. An index can contain several partitions.
*cluster_index_clause*	specifies the cluster for which a cluster index is to be created. If you do not qualify cluster with schema, Oracle assumes the cluster is in your current schema. You cannot create a cluster index for a hash cluster. For more information on clusters, see "CREATE CLUSTER".
*table_index_clause*	specifies table (and its attributes) on which you are defining the index. If you do not qualify table with schema, Oracle assumes the table is contained in your own schema.
	You create an index on a nested table column by creating the index on the nested table storage table. Include the NESTED_TABLE_ID pseudocolumn of the storage table to create a UNIQUE index, which effectively ensures that the rows of a nested table value are distinct.
	Restrictions: If the index is local, then table must be partitioned. If the table is index-organized, this statement creates a secondary index. You cannot specify BITMAP or REVERSE for this secondary index, and the combined size of the index key and the logical rowid should be less than half the block size.
	If table is a temporary table, the index will also be temporary with the same scope (session or transaction) as table. The following restrictions apply to indexes on temporary table: - The index cannot be a partitioned index or a domain index. - You cannot specify the physical_attributes_clause or the parallel_clause. - You cannot specify LOGGING, NOLOGGING, or TABLESPACE. For more information on temporary tables, see "CREATE TABLE" and Oracle8i Concepts.
t_alias	specifies a correlation name (alias) for the table upon which you are building the index.
	Note: This alias is required if the index_expression_list references any object type attributes or object type methods. See "Function-based Index on Type Method Example".
*index_expr_list*	lets you specify the column or column expression upon which the index is based.
column	is the name of a column in the table. A bitmap index can have a maximum of 30 columns. Other indexes can have as many as 32 columns. Restriction: You cannot create an index on columns or attributes whose type is user-defined, LONG, LONG RAW, LOB, or REF, except that Oracle supports an index on REF type columns or attributes that have been defined with a SCOPE clause.
	You can create an index on a scalar object attribute column or on the system-defined NESTED_TABLE_ID column of the nested table storage table. If you specify an object attribute column, the column name must be qualified with the table name. If you specify a nested table column attribute, it must be qualified with the outermost table name, the containing column name, and all intermediate attribute names leading to the nested table column attribute.
column_expression	is an expression built from columns of table, constants, SQL functions, and user-defined functions. When you specify column_expression, you create a function-based index. Name resolution of the function is based on the schema of the index creator. User-defined functions used in column_expression are fully name resolved during the CREATE INDEX operation.
	After creating a function-based index, collect statistics on both the index and its base table using the ANALYZE statement (see "ANALYZE"). Oracle cannot use the function-based index until these statistics have been generated.
	When you subsequently query a table that uses a function-based index, you must ensure in the query that column_expression is not null. See the Function-Based Index Example .
	If the function on which the index is based becomes invalid or is dropped, Oracle marks the index DISABLED. Queries on a DISABLED index fail if the optimizer chooses to use the index. DML operations on a DISABLED index fail unless The index is also marked UNUSABLE and The parameter `SKIP_UNUSABLE` is set to true (see "ALTER SESSION" and "ALTER SYSTEM" for more information on this parameter).
	Oracle's use of function-based indexes is also affected by the setting of the `QUERY_REWRITE_ENABLED` session parameter. For more information, see "ALTER SESSION".
	Restrictions on function-based indexes: Any user-defined function referenced in column_expression must be DETERMINISTIC. For more information, see "CREATE FUNCTION" and PL/SQL User's Guide and Reference. For a function-based globally partitioned index, the column_expression cannot be the partitioning key. All functions must be specified with parentheses, even if they have no parameters. Otherwise Oracle interprets them as column names. Any function you specify in column_expression must return a repeatable value. For example, you cannot specify the SYSDATE or USER function or the ROWNUM pseudocolumn. You cannot build a function-based index on LOB, REF, nested table, or varray columns. In addition, the function in column_expression cannot take as arguments any objects with attributes of type LOB, REF, nested table, or varray. The column_expression cannot contain any aggregate functions.
	Note: If a public synonym for a function, package, or type is used in column_expression, and later an actual object with the same name is created in the table owner's schema, then Oracle will disable the function-based index. When you subsequently enable the function-based index using ALTER INDEX ... ENABLE or ALTER INDEX ... REBUILD, the function, package, or type used in the column_expression will continue to resolve to the function, package, or type to which the public synonym originally pointed. It will not resolve to the new function, package, or type.
ASC \| DESC	specifies whether the index should be created in ascending or descending order. Indexes on character data are created in ascending or descending order of the character values in the database character set. Oracle treats descending indexes as if they were function-based indexes. You do not need the QUERY REWRITE or GLOBAL QUERY REWRITE privileges to create them, as you do with other function-based indexes. However, as with other function-based indexes, Oracle does not use descending indexes until you first analyze the index and the table on which the index is defined. See the column_expression clause of this statement. Restriction: You cannot specify either of these clauses for a domain index. You cannot specify DESC for a bitmapped index or a reverse index.
*index_attributes*
physical_attributes_clause	establishes values for physical and storage characteristics for the index. See "CREATE TABLE". Restriction: You cannot specify the PCTUSED parameter for an index.
	PCTFREE	is the percentage of space to leave free for updates and insertions within each of the index's data blocks.
	storage_clause	establishes the storage characteristics for the index. See the "

7SQL Statements (continued)

CREATE CONTEXT

Syntax

Purpose

Prerequisites

Keywords and Parameters

Examples

CREATE CONTROLFILE

Syntax

Purpose

Prerequisites

Keywords and Parameters

Example

CREATE DATABASE

Syntax

Purpose

Prerequisites

Keyword and Parameters

Examples

CREATE DATABASE LINK

Syntax

Purpose

Prerequisites

Keyword and Parameters

Examples

CURRENT_USER Example

Fixed User Example

PUBLIC Example

CREATE DIMENSION

Syntax

Purpose

Prerequisites

Keywords and Parameters

Examples

CREATE DIRECTORY

Syntax

Purpose

Prerequisites

Keywords and Parameters

Example

CREATE FUNCTION

Syntax

Purpose

Prerequisites

Keywords and Parameters

Examples

CREATE INDEX

Syntax

Purpose

Prerequisites

Keywords and Parameters

7
SQL Statements (continued)