Release 8.0
A58225-01
Library |
Product |
Contents |
Index |
| Oracle8
SQL Reference
Release 8.0 A58225-01 |
|
To re-create a control file in one of the following cases:
See also "Re-creating Control Files".
|
Warning: Oracle recommends that you perform a full backup of all files in the database before using this command. |
You must have the OSDBA role enabled. The database must not
be mounted by any instance.
filespec: See "Filespec".
|
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 option and any of these control files already exists, Oracle returns an error. |
|
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 |
changes the name of the database. The name of a database can be as long as eight bytes. |
|
LOGFILE |
specifies the redo log file groups for your database. You must list all members of all redo log file groups. See the syntax description of filespec in "Filespec". |
|
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 redo log file groups to thread 1 and enables this thread for public use by any instance. After using this option, you must open the database using the RESETLOGS option of the ALTER DATABASE command. |
|
NORESETLOGS |
specifies that all files in the LOGFILE clause should be used as they were when the database was last open. These files must exit and must be the current 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. If you specify GROUP values, Oracle verifies these values with the GROUP values when the database was last open. |
|
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 |
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 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. |
|
|
Note that the number of redo log file groups accessible to your instance is also limited by the initialization parameter LOG_FILES. |
|
MAXLOGMEMBERS |
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 file. The minimum value is 1. The maximum and default values depend on your operating system. |
|
MAXLOGHISTORY |
specifies the maximum number of archived redo log file groups for automatic media recovery of the Oracle8 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 MAXINSTANCE 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 |
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 Oracle8 control file to expand automatically so that the datafiles section can accommodate more files. |
|
|
Note that the number of datafiles accessible to your instance is also limited by the initialization parameter DB_FILES. |
|
MAXINSTANCES |
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 option prepares for the possibility of media recovery as well as instance recovery. |
|
NOARCHIVELOG |
If you omit both the ARCHIVELOG and NOARCHIVELOG options, Oracle chooses noarchivelog mode by default. After creating the control file, you can change between archivelog mode and noarchivelog mode with the ALTER DATABASE command. |
Oracle recommends that you take a full backup of all files
in the database before issuing a CREATE CONTROLFILE statement.
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 of the options from the statement, Oracle uses the default
options, rather than the options 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 shutdown the instance and take a full backup of all files in the database.
For more information about using this command, see the Oracle8
Administrator's Guide.
This example re-creates a control file:
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;
To create a database, making it available for general use, with the following options:
For examples of some of these purposes, see "Examples".
|
warning: This command prepares a database for initial use and erases any data currently in the specified files. Use this command only when you understand its ramifications. |
This command erases all data in any specified datafiles that
already exist to prepare them for initial database use. If you use the
command on an existing database, all data in the datafiles is lost.
After creating the database, this command mounts it in the
mode specified by the PARALLEL_SERVER initialization parameter and opens
it, making it available for normal use.
You must have the OSDBA role enabled.
|
database |
is the name of the database to be created and can be up to eight 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 and 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, the umlaut is not allowed. |
|
|
|
If you omit the database name from a CREATE DATABASE statement, Oracle uses the name specified by the initialization parameter DB_NAME. |
|
|
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 option only when you are re-creating a database, rather than creating one for the first time. You cannot use this option 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 option and any of the files specified by CONTROL_FILES already exist, Oracle returns an error message. |
|
|
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, or copies. See the syntax description of filespec in "Filespec". All redo log files specified in a CREATE DATABASE statement are added to redo log thread number 1. |
|
|
|
GROUP |
uniquely identifies a redo log file group and can range from 1 to the value of the MAXLOGFILES parameter. 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 depends on your operating system. |
|
|
MAXLOGFILES |
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 depends on your operating system. |
|
|
|
The number of redo log file groups accessible to your instance is also limited by the initialization parameter LOG_FILES. |
|
|
MAXLOGMEMBERS |
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 |
specifies the maximum number of archived redo log files for automatic media recovery of Oracle with the Parallel Server option. 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 |
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 Oracle8 control file to expand automatically so that the datafiles section can accommodate more files. |
|
|
|
Note that the number of datafiles accessible to your instance is also limited by the initialization parameter DB_FILES. |
|
|
MAXINSTANCES |
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 |
establishes archivelog mode for redo log file groups. In this mode, the contents of a redo log file group must be archived before the group can be reused. This option prepares for the possibility of media recovery. |
|
|
NOARCHIVELOG |
establishes noarchivelog mode for redo log files groups. In this mode, the contents of a redo log file group need not be archived before the group can be reused. This option does not prepare 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 command. |
|
|
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. |
|
|
|
You can specify any supported character set except the following fixed-width, multibyte character sets, which can be used only as the national character set: For more information about valid character sets, see in the Oracle8 Reference. |
|
|
NATIONAL CHARACTER SET |
specifies the national character set used to store data in columns specifically defined as NCHAR, NCLOB, or NVARCHAR2. You cannot change the national character set after creating the database. If not specified, the national character set defaults to the database character set. See Oracle8 Reference 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 |
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 commands. |
|
|
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. You can also use K or M to specify this size in kilobytes or megabytes. The default is 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. |
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;
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 can be either an Oracle or a non-Oracle system.
See also "Creating Database Links".
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. Also, 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 Oracle8 Heterogeneous Services.
|
SHARED |
uses a single network connection to create a public database link that can be shared between multiple users. This option is available only with the multithreaded server configuration. For more information about shared database links, see Oracle8 Distributed Database Systems. |
|
|
PUBLIC |
creates a public database link available to all users. If you omit this option, 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" and "Current-User Database Links". |
|
|
CONNECT TO |
enables a connection to the remote database. |
|
|
|
CURRENT_USER |
creates a current user database link. To use a current database link, the current user must be a global user authenticated by the Oracle Security Server. See also "Current-User Database Links". |
|
|
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 option. |
|
|
USING 'connect string' |
specifies the service name of a remote database. For information on specifying remote databases, see Net8 Administrator's Guide. |
|
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.
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 command. If you are using Oracle with the distributed option, you can also access remote tables and views using any of the following commands:
See Oracle8
Application Developer's Guide for information about accessing remote
tables or views with PL/SQL functions, procedures, packages, and datatypes.
The number of different database links that can appear in
a single statement is limited to the value of the initialization parameter
OPEN_LINKS.
A current user database link is one that contains no user
credentials and that enables a connection to a remote database as the current
user. To use the link, the current user must be a global user with global
accounts on both the local and remote databases. Both databases must be
members of the same security domain.
To create a global user, see CREATE
USER. For detailed information about current database links, see Oracle8
Distributed Database Systems.
When executing a stored object (such as a procedure, view,
or trigger) that initiates a database link, CURRENT_USER is the username
that created 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.
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.
The following example defines a current-user database link:
CREATE DATABASE LINK sales.hq.acme.com CONNECT TO CURRENT_USER USING 'sales';
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 commands 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 example 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;
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';
The following example creates a current user database link:
CREATE DATABASE LINK sales.hq.acme.com CONNECT TO CURRENT_USER USING 'sales';
Use CREATE DIRECTORY to create a directory object, which
represents an operating system directory for administering access
to, and the use of, BFILEs stored outside the database. A directory
is an alias for a full pathname on the server's file system where the files
are actually located.
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.
|
OR REPLACE |
re-creates the directory database object if it already exists. You can use this option 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. |
|
|
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. See also "Directory Objects". 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. Note that the single quotes are required, with the result that the path name is case sensitive. |
|
A directory object specifies an alias name 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.
The Oracle BFILE datatype provides access to the external
file system. A BFILE column or attribute contains a locator to an external
file on the operating system, rather than the file itself. The locator
maintains the directory alias and the filename.
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. 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.
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.
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';
To create a stored function or to register an external
function.
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"
An external function is a third-generation language (3GL)
routine stored in a shared library that can be called from SQL or PL/SQL.
To call an external function, you must provide information in your PL/SQL
function about where to find the external function, how to call it, and
what to pass to it.
The CREATE FUNCTION command 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 more information about registering external functions,
see the PL/SQL User's Guide and Reference.
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 CREATE
PROCEDURE system privilege. To create a function in another user's schema,
you must have CREATE ANY PROCEDURE system privilege.
To call an external function, you must have EXECUTE privileges
on the callout library in which the function resides.
To create a stored function, you must be using Oracle with
PL/SQL installed. For more information, see PL/SQL
User's Guide and Reference.
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.
|
OR REPLACE |
re-creates the function if it already exists. Use this option 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. |
|
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. |
|
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. |
|
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. |
|
pl/sql_subprogram_body |
is the definition of the function. Function definitions are writing in PL/SQL. For information on PL/SQL, see PL/SQL User's Guide and Reference.
|
|
external_body_clause |
identifies the external function to be registered. |
|
AS EXTERNAL |
identifies an external 3GL function stored in a shareable library. The AS EXTERNAL clause is the interface between PL/SQL and the external function. |
|
LIBRARY |
specifies the shared library in which the external function is stored. You must have EXECUTE privileges on the library. See CREATE LIBRARY for the syntax. |
|
library_name |
is a PL/SQL identifier. Enclosing library_name in double quotes makes it case sensitive, but quotes are not required. |
|
NAME external_function_name |
specifies the external function to be called. Enclosing external_function_name in double quotes makes it case sensitive, but quotes are not required. If you omit the name, it defaults to the PL/SQL subprogram (uppercase) name. |
|
LANGUAGE |
specifies the 3GL in which the external function was written. Currently, the only language name supported is C. If you omit the name, it defaults to C. |
|
CALLING STANDARD |
specifies the calling standard (C or Pascal) under which the external function was compiled. If you omit the calling standard, it defaults to C. |
|
WITH CONTEXT |
specifies that a context pointer will be the first parameter passed to the external function. The context is opaque to the external function but is available to access functions called by the external function. For more information about the WITH CONTEXT clause, see PL/SQL User's Guide and Reference. |
|
PARAMETERS |
specifies the positions and datatypes of parameters passed to the external function. It can also specify parameter properties such as current length and maximum length, and the preferred parameter passing method (by value or by reference). For more information about parameter passing see PL/SQL User's Guide and Reference. |
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:
CREATE FUNCTION get_val ( x_val IN BINARY_INTEGER, y_val IN BINARY_INTEGER, image IN LONG RAW ) RETURN BINARY_INTEGER AS EXTERNAL LIBRARY c_utils NAME "c_get_val" LANGUAGE C;
To create an index on
one or more scalar typed object
attributes of a table or a cluster
a nested table storage table
for indexing a nested table columnAn 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. See also "Creating Indexes".
|
Note: Descriptions of commands and clauses preceded by |
To create an index in your own schema, one of the following conditions must be true:
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.
See also "Index Columns".
parallel_clause: See PARALLEL
clause.
storage_clause: See STORAGE
clause
|
UNIQUE |
specifies that the value of the column (or combination of columns) in the table to be indexed must be unique. |
|
|
If the index is local nonprefixed (see LOCAL clause below), then the index key must contain the partitioning key. |
|
BITMAP |
specifies that index is to be created as a bitmap, rather than as a B-tree. See also "Creating Bitmap Indexes". Note: You cannot use this keyword when creating a global partitioned index. |
|
You can specify either UNIQUE or BITMAP, but you cannot create a unique bitmap 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. (See also "Multiple Indexes Per Table".) An index can contain several partitions. |
|
|
You cannot range partition a cluster index or an index defined on a clustered table. |
|
table |
is the name of the table for which the index is to be created. If you do not qualify table with schema, Oracle assumes the table is contained in your own schema. |
|
|
If the index is LOCAL, then table must be partitioned. |
|
|
You cannot create an index on an index-organized table. |
|
|
|
|
column |
is the name of a column in the table. An index can have as many as 32 columns. A column of an index cannot be of datatype LONG or LONG RAW. See also "Index Columns". |
|
|
See also "Nulls". |
|
ASC / DESC |
are allowed for DB2 syntax compatibility, although indexes are always created in ascending order. Indexes on character data are created in ascending order of the character values in the database character set. |
|
CLUSTER |
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 contained in your current schema. You cannot create a cluster index for a hash cluster. See also "Creating Cluster Indexes". |
|
index_physical_attributes_clause |
establishes values for the INITRANS, MAXTRANS, and PCTFREE parameters and storage characteristics for the index. See CREATE TABLE. |
|
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 STORAGE clause. |
|
TABLESPACE |
is the name of the tablespace to hold the index or index partition. If you omit this option, Oracle creates the index in the default tablespace of the owner of the schema containing the index. |
|
|
For a partitioned index, this is the tablespace name. |
|
|
For a local index, you can specify the keyword DEFAULT in place of tablespace. New partitions added to the local index will be created in the same tablespace(s) as the corresponding partition(s) of the underlying table. |
|
NOSORT |
indicates to Oracle that the rows are stored in the database in ascending order; therefore Oracle does not have to sort the rows when creating the index. You cannot specify REVERSE with this option. See also "The NOSORT Option". |
|
REVERSE |
stores the bytes of the index block in reverse order, excluding the ROWID. You cannot specify NOSORT with this option. |
|
|
You cannot reverse a bitmap index. |
|
LOGGING /NOLOGGING |
specifies that the creation of the index will be logged (LOGGING) or not logged (NOLOGGING) in the redo log file. It also specifies that subsequent Direct Loader (SQL*Loader) and direct-load INSERT operations against the index are logged or not logged. LOGGING is the default. |
|
|
If index is nonpartitioned, this is the logging attribute of the index. |
|
|
For partitioned index, the logging attribute specified is the default physical attribute of the segments associated with the index partitions. The default logging value applies to all partitions specified in the CREATE statement (and on subsequent ALTER TABLE ADD PARTITION statements) unless you specify LOGGING/NOLOGGING in the PARTITION description clause. |
|
|
In NOLOGGING mode, data is modified with minimal logging (to mark new extents invalid and to record dictionary changes). When applied during media recovery, the extent invalidation records mark a range of blocks as logically corrupt, since the redo data is not logged. Thus if you cannot afford to lose this index, it is important to take a backup after the NOLOGGING operation. |
|
|
If the database is run in ARCHIVELOG mode, media recovery from a backup taken before the LOGGING operation will re-create the index. However, media recovery from a backup taken before the NOLOGGING operation will not re-create the index. |
|
|
The logging attribute of the index is independent of that of its base table. |
|
|
If the [NO]LOGGING clause is omitted, the logging attribute of the index defaults to the logging attribute of the tablespace in which it resides. |
|
|
For more information about the LOGGING option and Parallel DML, see "NOLOGGING", Oracle8 Concepts and Oracle8 Parallel Server Concepts & Administration. |
|
GLOBAL |
specifies that the partitioning of the index is user defined and is not equipartitioned with the underlying table. By default, nonpartitioned indexes are global indexes. |
|
PARTITION BY RANGE |
specifies that the global index is partitioned on the ranges of values from the columns specified in column_list. You cannot specify this clause for a LOCAL index. |
|
(column_list) |
is the name of the column(s) of a table on which the index is partitioned. The column_list must specify a left prefix of the index column list. |
|
|
You cannot specify more than 32 columns in column_list, and the columns cannot contain the ROWID pseudocolumn or a column of type ROWID. |
|
LOCAL |
specifies that the index is range partitioned on the same columns, with the same number of partitions, and the same partition bounds as table. Oracle automatically maintains LOCAL index partitioning as the underlying table is repartitioned. |
|
PARTITION partition_name |
describes the individual partitions. The number of clauses determines the number of partitions. If the index is local, the number of index partitions must be equal to the number of the table partitions, and in the same order. |
|
|
The partition_name is the name of the physical index partition. If partition_name is omitted, Oracle generates a name with the form SYS_Pn. |
|
|
For local indexes, if partition_name is omitted, Oracle generates a name that is consistent with the corresponding table partition. If the name conflicts with an existing index partition name, the form SYS_Pn is used. See also "Creating Partitioned Indexes". |
|
VALUES LESS THAN (value_list) |
specifies the (noninclusive) upper bound for the current partition in a global index. The value_list is a comma-separated, ordered list of literal values corresponding to column_list in the PARTITION BY RANGE clause. Always specify MAXVALUE as the value_list of the last partition. |
|
|
You cannot specify this clause for a local index. |
|
parallel_clause |
specifies the degree of parallelism for creating the index. See the PARALLEL clause. |
An index is an ordered list of all the values that reside in a group of one or more columns at a given time. Such a list makes queries that test the values in those columns vastly more efficient. However, indexes take up data storage space and must be changed whenever the data is changed. Therefore, you should make a cost-benefit analysis in each case to determine whether and how indexes should be used. Oracle can use indexes to improve performance when:
When you initially insert rows into a new table, it is generally
faster to create the table, insert the rows, and then create the index.
If you create the index before inserting the rows, Oracle must update the
index for every row inserted.
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. Instead, define UNIQUE integrity
constraints on the desired columns. Oracle enforces UNIQUE integrity constraints
by automatically defining a unique index on the unique key. Exceptions
to this recommendation are usually performance related. For example, using
a CREATE TABLE ... AS SELECT with a UNIQUE constraint is very much slower
than creating the table without the constraint and then manually creating
the UNIQUE index.
If indexes contain NULLs, the NULLS generally are considered
distinct values. There is, however, one exception: if all the non-NULL
values in two or more rows of an index are identical, the rows are considered
identical; therefore, UNIQUE indexes prevent this from occurring. This
does not apply if there are no non-NULL values--in other words, if the
rows are entirely NULL.
An index can contain a maximum of 32 columns. The index entry
becomes the concatenation of all data values from each column. You can
specify the columns in any order. The order you choose is important to
how Oracle uses the index.
When appropriate, Oracle uses the entire index or a leading
portion of the index. Assume an index named IDX1 is created on columns
A, B, and C of table TAB1 (in the order A, B, C). Oracle uses the index
for references to columns A, B, C (the entire index); A, B; or just column
A. References to columns B and C do not use the IDX1 index. Of course,
you can also create another index just for columns B and C.
You can create unlimited indexes for a table provided that the combination of columns differs for each index. You can create more than one index using the same columns provided that you specify distinctly different combinations of the columns. For example, the following statements specify valid combinations:
CREATE INDEX emp_idx1 ON emp (ename, job); CREATE INDEX emp_idx2 ON emp (job, ename);
You cannot create an index that references only one column
in a table if another such index already exists.
Note that each index increases the processing time needed
to maintain the table during updates to indexed data. Thus, updating a
table with a single index will take less time than if the table had five
indexes.
The NOSORT option can substantially reduce the time required
to create an index. Normal index creation first sorts the rows of the table
based on the index columns and then builds the index. The sort operation
is often a substantial portion of the total work involved. If the rows
are already physically stored in ascending order (based on the indexed
column values), then the NOSORT option causes Oracle to bypass the sort
phase of the process.
You cannot use the NOSORT option to create a cluster index,
partitioned index, or a bitmap index.
The NOSORT option also reduces the amount of space required
to build the index. Oracle uses temporary segments during the sort. Since
a sort is not performed, the index is created with much less temporary
space.
To use the NOSORT option, you must guarantee that the rows
are physically sorted in ascending order. However, you run no risk by trying
the NOSORT option. If your rows are not in the ascending order, Oracle
returns an error. You can issue another CREATE INDEX without the NOSORT
option. Because of the physical data independence inherent in relational
database management systems, especially Oracle, there is no way to force
a physical internal order on a table. The CREATE INDEX command with the
NOSORT option should be used immediately after the initial load of rows
into a table.
The NOLOGGING option may substantially reduce the time required
to create a large index. This feature is particularly useful after creating
a large index in parallel. For backup and recovery considerations, see
Oracle8 Backup and Recovery Guide
and Oracle8 Administrator's Guide
To quickly create an index in parallel on a table that was created using a fast parallel load (so all rows are already sorted), you might issue the following statement:
CREATE INDEX i_loc ON big_table (akey) NOSORT NOLOGGING PARALLEL (DEGREE 5);
Oracle does not index table rows in which all key columns
are NULL, except in the case of bitmap indexes.
Consider the following statement:
SELECT ename FROM emp WHERE comm IS NULL;
The above query does not use an index created on the COMM
column unless it is a bitmap index.
Oracle does not automatically create an index for a cluster
when the cluster is initially created. Data manipulation language statements
cannot be issued against clustered tables until a cluster index has been
created.
To create an index for the EMPLOYEE cluster, issue the following statement:
CREATE INDEX ic_emp ON CLUSTER employee
Note that no index columns are specified, because the index
is automatically built on all the columns of the cluster key. For cluster
indexes, all rows are indexed.
Indexes can be local prefixed (unique or nonunique), local
nonprefixed (unique, but only when the partitioning key is a subset of
the index key or nonunique), or global prefixed (unique or nonunique).
Oracle does not support global nonprefixed indexes. Local indexes are always
partitioned. Global indexes can be nonpartitioned or partitioned.
Index partitions must be listed in order. For a global index,
this means that the partition bound of the first partition listed must
be less than the partition bound of the second partition listed,
and the partition bound of the second partition listed must be less
than the third, and so on. For a local index, you must list the partitions
in the same order as the partitions of the underlying table to which they
correspond.
The following statement creates a global prefixed index STOCK_IX on table STOCK_XACTIONS with two partitions, one for each half of the alphabet. The index partition names are system generated:
CREATE INDEX stock_ix ON stock_xactions (stock_symbol, stock_series) GLOBAL PARTITION BY RANGE (stock_symbol) (PARTITION VALUES LESS THAN ('N') TABLESPACE ts3, PARTITION VALUES LESS THAN (MAXVALUE) TABLESPACE ts4);
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 Oracle8 Concepts and Oracle8
Tuning for more information about using bitmap indexes.
To create a bitmap partitioned index on a table with four partitions, issue the following statement:
CREATE BITMAP INDEX partno_ix ON lineitem(partno) TABLESPACE ts1 LOCAL (PARTITION quarter1 TABLESPACE ts2, PARTITION quarter2 STORAGE (INITIAL 10K NEXT 2K), PARTITION quarter3 TABLESPACE ts2, PARTITION quarter4);
You cannot create bitmap indexes, unique bitmap indexes,
or global partitioned indexes.
Creating Indexes on Nested Table ColumnsCreating a table with nested table columns implicitly creates
a storage table for each nested table column. The storage table stores
the rows of the nested table values and the nested table identifier values
assigned to each row. These identifier values are contained in a storage
table pseudocolumn called NESTED_TABLE_ID.
You create an index on a nested table column by creating
the index on the nested table storage table. You can include the NESTED_TABLE_ID
pseudocolumn to create a UNIQUE index, which effectively ensures that the
rows of a nested table value are distinct.
In the following example, UNIQUE index UNIQ_PROJ_INDX is created on storage table NESTED_PROJECT_TABLE. Including pseudocolumn NESTED_TABLE_ID ensures distinct rows in nested table column PROJS_MANAGED:
CREATE TYPE proj_table_type AS TABLE OF proj_type; CREATE TABLE employee ( emp_num NUMBER, emp_name CHAR(31), projs_managed proj_table_type ) NESTED TABLE projs_managed STORE AS nested_project_table; CREATE UNIQUE INDEX uniq_proj_indx ON nested_project_table ( NESTED_TABLE_ID, proj_num);
To create a schema object (library), which represents
an operating-system shared library, from which SQL and PL/SQL can call
external third-generation-language (3GL) functions and procedures. See
"Examples".
You must have CREATE ANY LIBRARY system privileges. To use
the procedures and functions stored in the library, you must have EXECUTE
object privileges on the library.
The CREATE LIBRARY command is valid only on platforms that
support shared libraries and dynamic linking.
filespec: See
"Filespec".
The following statement creates library EXT_LIB:
CREATE LIBRARY ext_lib AS '/OR/lib/ext_lib.so';
The following example re-creates library EXT_LIB:
CREATE OR REPLACE ext_lib IS '/OR/newlib/ext_lib.so';
To create the specification for a stored package. A package
is an encapsulated collection of related procedures, functions, and other
program objects stored together in the database. The specification
declares these objects.
Before a package 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 package in your own schema, you must have CREATE
PROCEDURE system privilege. To create a package in another user's schema,
you must have CREATE ANY PROCEDURE system privilege.
To embed a CREATE PACKAGE 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.
For more information, see PL/SQL
User's Guide and Reference.
|
OR REPLACE |
re-creates the package specification if it already exists. Use this option to change the specification of an existing package without dropping, re-creating, and regranting object privileges previously granted on the package. If you change a package specification, Oracle recompiles it. For information on recompiling package specifications, see ALTER PROCEDURE. |
|
|
Users who had previously been granted privileges on a redefined package can still access the package without being regranted the privileges. |
|
schema |
is the schema to contain the package. If you omit schema, Oracle creates the package in your own schema. |
|
package |
is the name of the package to be created. See also "Packages". |
|
pl/sql_package_spec |
is the package specification. The package specification can declare program objects. Package specifications are written in PL/SQL. For information on PL/SQL, including writing package specifications, see PL/SQL User's Guide and Reference. |
A package is an encapsulated collection of related
program objects stored together in the database. Program objects are: procedures,
functions, variables, constants, cursors, and exceptions.
Using packages is an alternative to creating procedures and functions as standalone schema objects. Packages have many advantages over stand-alone procedures and functions. They:
For more information on these and other benefits of packages,
see Oracle8 Application Developer's
Guide.
To create a package, you must perform two distinct steps:
See CREATE PACKAGE BODY.
Oracle stores the specification and body of a package separately
in the database. Other schema objects that call or reference public program
objects depend only on the package specification, not on the package body.
This distinction allows you to change the definition of a program object
in the package body without causing Oracle to invalidate other schema objects
that call or reference the program object. Oracle invalidates dependent
schema objects only if you change the declaration of the program object
in the package specification.
This SQL statement creates the specification of the EMP_MGMT package:
CREATE PACKAGE emp_mgmt AS FUNCTION hire(ename VARCHAR2, job VARCHAR2, mgr NUMBER, sal NUMBER, comm NUMBER, deptno NUMBER) RETURN NUMBER; FUNCTION create_dept(dname VARCHAR2, loc VARCHAR2) RETURN NUMBER; PROCEDURE remove_emp(empno NUMBER); PROCEDURE remove_dept(deptno NUMBER); PROCEDURE increase_sal(empno NUMBER, sal_incr NUMBER); PROCEDURE increase_comm(empno NUMBER, comm_incr NUMBER); no_comm EXCEPTION; no_sal EXCEPTION; END emp_mgmt;
The specification for the EMP_MGMT package declares the following public program objects:
All of these objects are available to users who have access
to the package. After creating the package, you can develop applications
that call any of the package's public procedures or functions or raise
any of the package's public exceptions.
Before you can call this package's procedures and functions,
you must define these procedures and functions in the package body. For
an example of a CREATE PACKAGE BODY statement that creates the body of
the EMP_MGMT package, see CREATE PACKAGE BODY.
To create the body of a stored package. A package
is an encapsulated collection of related procedures, stored functions,
and other program objects stored together in the database. The body
defines these objects.
Packages are an alternative to creating procedures and functions
as standalone schema objects. For a discussion of packages, including how
to create packages, see CREATE PACKAGE. For some illustrations,
see "Examples".
Before a package 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 package in your own schema, you must have CREATE
PROCEDURE system privilege. To create a package in another user's schema,
you must have CREATE ANY PROCEDURE system privilege.
To embed a CREATE PACKAGE BODY 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.
For more information, see PL/SQL
User's Guide and Reference.
|
OR REPLACE |
re-creates the package body if it already exists. Use this option to change the body of an existing package without dropping, re-creating, and regranting object privileges previously granted on it. If you change a package body, Oracle recompiles it. For information on recompiling package bodies, see ALTER PACKAGE |
|
|
Users who had previously been granted privileges on a redefined package can still access the package without being regranted the privileges. |
|
schema |
is the schema to contain the package. If you omit schema, Oracle creates the package in your current schema. |
|
package |
is the name of the package to be created. |
|
pl/sql_package_body |
is the package body. The package body can declare and define program objects. Package bodies are written in PL/SQL. For information on PL/SQL, including writing package bodies, see PL/SQL User's Guide and Reference. |
This SQL statement creates the body of the EMP_MGMT package:
CREATE PACKAGE BODY emp_mgmt AS tot_emps NUMBER; tot_depts NUMBER; FUNCTION hire (ename VARCHAR2, job VARCHAR2, mgr NUMBER, sal NUMBER, comm NUMBER, deptno NUMBER) RETURN NUMBER IS new_empno NUMBER(4); BEGIN SELECT empseq.NEXTVAL INTO new_empno FROM DUAL; INSERT INTO emp VALUES (new_empno, ename, job, mgr, sal, comm, deptno, tot_emps := tot_emps + 1; RETURN(new_empno); END; FUNCTION create_dept(dname VARCHAR2, loc VARCHAR2) RETURN NUMBER IS new_deptno NUMBER(4); BEGIN SELECT deptseq.NEXTVAL INTO new_deptno FROM dual; INSERT INTO dept VALUES (new_deptno, dname, loc); tot_depts := tot_depts + 1; RETURN(new_deptno); END; PROCEDURE remove_emp(empno NUMBER) IS BEGIN DELETE FROM emp WHERE emp.empno = remove_emp.empno; tot_emps := tot_emps - 1; END; PROCEDURE remove_dept(deptno NUMBER) IS BEGIN DELETE FROM dept WHERE dept.deptno = remove_dept.deptno; tot_depts := tot_depts - 1; SELECT COUNT(*) INTO tot_emps FROM emp; /* In case Oracle deleted employees from the EMP table to enforce referential integrity constraints, reset the value of the variable TOT_EMPS to the total number of employees in the EMP table. */ END; PROCEDURE increase_sal(empno NUMBER, sal_incr NUMBER) IS curr_sal NUMBER(7,2); BEGIN SELECT sal INTO curr_sal FROM emp WHERE emp.empno = increase_sal.empno; IF curr_sal IS NULL THEN RAISE no_sal; ELSE UPDATE emp SET sal = sal + sal_incr WHERE empno = empno; END IF; END; PROCEDURE increase_comm(empno NUMBER, comm_incr NUMBER) IS curr_comm NUMBER(7,2); BEGIN SELECT comm INTO curr_comm FROM emp WHERE emp.empno = increase_comm.empno IF curr_comm IS NULL THEN RAISE no_comm; ELSE UPDATE emp SET comm = comm + comm_incr; END IF; END; END emp_mgmt;
This package body corresponds to the package specification in the example of the CREATE PACKAGE command earlier in this chapter. The package body defines the public program objects declared in the package specification:
These objects are declared in the package specification,
so they can be called by application programs, procedures, and functions
outside the package. For example, if you have access to the package, you
can create a procedure INCREASE_ALL_COMMS separate from the EMP_MGMT package
that calls the INCREASE_COMM procedure.
These objects are defined in the package body, so
you can change their definitions without causing Oracle to invalidate dependent
schema objects. For example, if you subsequently change the definition
of HIRE, Oracle need not recompile INCREASE_ALL_COMMS before executing
it.
The package body in this example also declares private program
objects, the variables TOT_EMPS and TOT_DEPTS. These objects are declared
in the package body rather than the package specification, so they are
accessible to other objects in the package, but they are not accessible
outside the package. For example, you cannot develop an application that
explicitly changes the value of the variable TOT_DEPTS. However, the function
CREATE_DEPT is part of the package, so CREATE_DEPT can change the value
of TOT_DEPTS.
To create a standalone stored procedure or to register an
external procedure. A procedure is a group of PL/SQL statements
that you can call by name. An external procedure is a third-generation
language (3GL) routine stored in a shared library which can be called from
SQL or PL/SQL. To call an external procedure, you must provide information
in your PL/SQL function about where to find the external procedure, how
to call it, and what to pass to it. See also "Using Procedures".
For more information about registering external procedures,
see the PL/SQL User's Guide and Reference.
Before creating a procedure, the user SYS must run the SQL
script DBMSSTDX.SQL. The exact name and location of this script depends
on your operating system.
To create a procedure in your own schema, you must have CREATE
PROCEDURE system privilege. To create a procedure in another schema, you
must have CREATE ANY PROCEDURE system privilege. To replace a procedure
in another schema, you must have ALTER ANY PROCEDURE system privilege.
To call an external procedure, you must have EXECUTE privileges
on the callout library in which the procedure resides.
|
OR REPLACE |
re-creates the procedure if it already exists. Use this option to change the definition of an existing procedure without dropping, re-creating, and regranting object privileges previously granted on it. If you redefine a procedure, Oracle recompiles it. For information on recompiling procedures, see ALTER PROCEDURE. |
|
|
Users who had previously been granted privileges on a redefined procedure can still access the procedure without being regranted the privileges. |
|
schema |
is the schema to contain the procedure. If you omit schema, Oracle creates the procedure in your current schema. |
|
procedure |
is the name of the procedure to be created. |
|
argument |
is the name of an argument to the procedure. If the procedure does not accept arguments, you can omit the parentheses following the procedure name. |
|
IN |
specifies that you must specify a value for the argument when calling the procedure. |
|
OUT |
specifies that the procedure passes a value for this argument back to its calling environment after execution. |
|
IN OUT |
specifies that you must specify a value for the argument when calling the procedure and that the procedure passes a value back to its calling environment after execution. |
|
|
If you omit IN, OUT, and IN OUT, the argument defaults to IN. |
|
datatype |
is the datatype of the argument. As long as no length specifier is used, an argument can have any datatype supported by PL/SQL. For information on PL/SQL datatypes, see PL/SQL User's Guide and Reference. |
|
|
Datatypes are specified without a length, precision, or scale. For example, VARCHAR2(10) is not valid, but VARCHAR2 is valid. Oracle derives the length, precision, and scale of an argument from the environment from which the procedure is called. |
|
IS pl/sql_subprogram_body |
is the definition of the procedure. Procedure definitions are written in PL/SQL. For information on PL/SQL, including how to write a PL/SQL subprogram body, see PL/SQL User's Guide and Reference. |
|
AS external_body |
identifies an external 3GL procedure stored in a sharable library. The AS external_body clause is the interface between PL/SQL and the external procedure. |
|
LIBRARY |
specifies the shared library in which the external procedure is stored. You must have EXECUTE privileges on the library. See CREATE LIBRARY for the syntax. |
|
library_name |
is a PL/SQL identifier. Enclosing library_name in double quotes makes it case sensitive, but quotes are not required. |
|
NAME external_procedure_name |
specifies the external procedure to be called. Enclosing external_procedure_name in double quotes makes it case sensitive, but quotes are not required. If you omit the name, it defaults to the PL/SQL subprogram (uppercase) name. |
|
LANGUAGE |
specifies the 3GL in which the external procedure was written. Currently, the only language name supported is C. If you omit the name, it defaults to C. |
|
CALLING STANDARD |
specifies the calling standard (C or PASCAL) under which the external procedure was compiled. If you omit the calling standard, it defaults to C. |
|
WITH CONTEXT |
specifies that a context pointer will be the first parameter passed to the external procedure. The context is opaque to the external procedure but is available to access functions called by the external procedure. For more information about the WITH CONTEXT clause, see the PL/SQL User's Guide and Reference. |
|
PARAMETERS |
specifies the positions and datatypes of parameters passed to the external procedure. It can also specify parameter properties such as current length and maximum length, and the preferred parameter passing method (by value or by reference). For more information about parameter passing, see the PL/SQL User's Guide and Reference. |
|
To embed a CREATE PROCEDURE 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. |
|
A procedure is a group of PL/SQL statements that you
can call by name. Stored procedures and stored functions are similar in
many ways. This discussion applies to functions as well as to procedures.
For information specific to functions, see CREATE FUNCTION.
With PL/SQL, you can group multiple SQL statements together
with procedural PL/SQL statements similar to those in programming languages
such as Ada and C. With the CREATE PROCEDURE command, you can create a
procedure and store it in the database. You can call a stored procedure
from any environment from which you can issue a SQL statement.
Stored procedures offer advantages in the areas of development,
integrity, security, performance, and memory allocation. For more information
on stored procedures, including how to call stored procedures, see Oracle8
Application Developer's Guide.
The CREATE PROCEDURE command creates a procedure as a standalone
schema object. You can also create a procedure as part of a package. For
information on creating packages, see CREATE FUNCTION.
The following statement creates the procedure CREDIT in the schema SAM:
CREATE PROCEDURE sam.credit (acc_no IN NUMBER, amount IN NUMBER) AS BEGIN UPDATE accounts SET balance = balance + amount WHERE account_id = acc_no; END;
The CREDIT procedure credits a specified bank account with
a specified amount. When you call the procedure, you must specify the following
arguments:
|
ACC_NO |
is the number of the bank account to be credited. The argument's datatype is NUMBER. |
|
AMOUNT |
is the amount of the credit. The argument's datatype is NUMBER. |
The procedure uses an UPDATE statement to increase the value
in the BALANCE column of the ACCOUNTS table by the value of the argument
AMOUNT for the account identified by the argument ACC_NO.
In the following example, external procedure C_FIND_ROOT expects a pointer as a parameter. Procedure FIND_ROOT passes the parameter by reference using the BY REF phrase:
CREATE PROCEDURE ( x IN REAL ) AS EXTERNAL EXTERNAL LIBRARY c_utils NAME "c_find_root" PARAMETERS ( x BY REF );
See the PL/SQL User's Guide
and Reference for information about external procedures.
To create a profile. A profile is a set of limits
on database resources. If you assign the profile to a user, that user cannot
exceed these limits.
You must have CREATE PROFILE system privilege.
|
profile |
is the name of the profile to be created. See also "Using Profiles". |
|
|
SESSIONS_PER_USER |
limits a user to integer concurrent sessions. |
|
|
CPU_PER_SESSION |
limits the CPU time for a session, expressed in hundredth of seconds |
|
|
CPU_PER_CALL |
limits the CPU time for a call (a parse, execute, or fetch), expressed in hundredths of seconds. |
|
|
CONNECT_TIME |
limits the total elapsed time of a session, expressed in minutes. |
|
|
IDLE_TIME |
limits periods of continuous inactive time during a session, expressed in minutes. Long-running queries and other operations are not subject to this limit. |
|
|
LOGICAL_READS_PER_SESSION |
specifies the number of data blocks read in a session, including blocks read from memory and disk. |
|
|
LOGICAL_READS_PER_CALL |
specifies the number of data blocks read for a call to process a SQL statement (a parse, execute, or fetch). |
|
|
PRIVATE_SGA |
specifies the amount of private space a session can allocate in the shared pool of the system global area (SGA), expressed in bytes. You can use K or M to specify this limit in kilobytes or megabytes. This limit applies only if you are using multithreaded server architecture. The private space for a session in the SGA includes private SQL and PL/SQL areas, but not shared SQL and PL/SQL areas. |
|
|
FAILED_LOGIN_ATTEMPTS |
specifies the number of failed attempts to log in to the user account before the account is locked. |
|
|
PASSWORD_LIFE_TIME |
limits the number of days the same password can be used for authentication. The password expires if it is not changed within this period, and further connections are rejected. See also "Fractions in Dates". |
|
|
PASSWORD_REUSE_TIME |
specifies the number of days before which a password cannot be reused. If you set PASSWORD_REUSE_TIME to an integer value, then you must set PASSWORD_REUSE_MAX to UNLIMITED. |
|
|
PASSWORD_REUSE_MAX |
specifies the number of password changes required before the current password can be reused. If you set PASSWORD_REUSE_MAX to an integer value, then you must set PASSWORD_REUSE_TIME to UNLIMITED. |
|
|
PASSWORD_LOCK_TIME |
specifies the number of days an account will be locked after the specified number of consecutive failed login attempts. |
|
|
PASSWORD_GRACE_TIME |
specifies the number of days after the grace period begins during which a warning is issued and login is allowed. If the password is not changed during the grace period, the password expires. |
|
|
PASSWORD_VERIFY_FUNCTION |
allows a PL/SQL password complexity verification script to be passed as an argument to the CREATE PROFILE command. Oracle provides a default script, but you can create your own routine or use third-party software instead. |
|
|
|
function |
is the name of the password complexity verification routine. |
|
|
NULL |
indicates that no password verification is performed. |
|
|
DEFAULT |
omits a limit for this resource in this profile. A user assigned this profile is subject to the limit on the resource specified in the default profile. |
|
COMPOSITE_LIMIT |
specifies the total resources cost for a session, expressed in service units. Oracle calculates the total service units as a weighted sum of CPU_PER_SESSION, CONNECT_TIME, LOGICAL_READS_PER_SESSION, and PRIVATE_SGA. For information on how to specify the weight for each session resource, see ALTER RESOURCE COST. |
|
|
UNLIMITED |
indicates that a user assigned this profile can use an unlimited amount of this resource. |
|
|
DEFAULT |
omits a limit for this resource in this profile. A user assigned this profile is subject to the limit for this resource specified in the DEFAULT profile. See also "The DEFAULT Profile". |
|
A profile is a set of limits on database resources. You can use profiles to limit the database resources available to a user for a single call or a single session. Oracle enforces resource limits in the following ways:
You can use fractions of days for all parameters, with days
as units. Fractions are expressed as x/y. For example, 1 hour is
1/24 and 1 minute is 1/1440.
For a detailed description and explanation of how to use
password management and protection, see the Oracle8
Administrator's Guide.
To specify resource limits for a user, you must perform both
of the following operations:
You can enable resource limits in one of two ways:
To specify a resource limit for a user, you must perform following steps:
Note that you can specify resource limits for users regardless
of whether resource limits are enabled. However, Oracle does not enforce
these limits until you enable them.
Oracle automatically creates a default profile named DEFAULT.
This profile initially defines unlimited resources. You can change the
limits defined in this profile with the ALTER PROFILE command.
Any user who is not explicitly assigned a profile is subject
to the limits defined in the DEFAULT profile. Also, if the profile that
is explicitly assigned to a user omits limits for some resources or specifies
DEFAULT for some limits, the user is subject to the limits on those resources
defined by the DEFAULT profile.
The following statement creates the profile SYSTEM_MANAGER:
CREATE PROFILE system_manager LIMIT SESSIONS_PER_USER UNLIMITED CPU_PER_SESSION UNLIMITED CPU_PER_CALL 3000 CONNECT_TIME 45 LOGICAL_READS_PER_SESSION DEFAULT LOGICAL_READS_PER_CALL 1000 PRIVATE SGA 15K COMPOSITE_LIMIT 5000000;
If you then assign the SYSTEM_MANAGER profile to a user, the user is subject to the following limits in subsequent sessions:
The following example creates the profile PROF:
CREATE PROFILE prof LIMIT PASSWORD_REUSE_MAX DEFAULT PASSWORD_REUSE_TIME UNLIMITED;
The following example creates profile MYPROFILE with password profile limits values set:
CREATE PROFILE myprofile LIMIT FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LIFE_TIME 60 PASSWORD_REUSE_TIME 60 PASSWORD_REUSE_MAX UNLIMITED PASSWORD_VERIFY_FUNCTION verify_function PASSWORD_LOCK_TIME 1/24 PASSWORD_GRACE_TIME 10;
external_body::=
are available only if the Oracle objects option is installed on your database
server.
You can create an index on a nested
table storage table. 
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. See also 