ORA-00117: PROTOCOL, ADDRESS or DESCRIPTION must be specified

ORA-00117

ORA-00117: PROTOCOL, ADDRESS or DESCRIPTION must be specified Cause: PROTOCOL, ADDRESS or DESCRIPTION was not specified. Action: Use one of the attributes: PROTOCOL, ADDRESS or DESCRIPTION to specify the listening address for dispatchers.

ORA-00904: string: invalid identifier

ORA-00904

ORA-00904: string: invalid identifier Cause: The column name entered is either missing or invalid. Action: Enter a valid column name. A valid column name must begin with a letter, be less than or equal to 30 characters, and consist of only alphanumeric characters and the special characters $, _, and #. If it contains other characters, then it must be enclosed in double quotation marks. It may not be a reserved word.

Cursor attributes

Cursor attributes are variables that take some value about the status of the cursor. These values are automatically set by Oracle and the programmer can read them not write values for them. There are four cursor attributes. They are




 %FOUND

 %ISOPEN

 %NOTFOUND

 %ROWCOUNT



 %FOUND:

After a cursor is opened before the first fetch, the value of this variable is null. After the first fetch, if the query returns one or more rows as result set, this variable is set to TRUE. When a fetch is made after the last row of the result set is reached, this variable is set to FALSE.

This variable is extensively used to in stored procedures to handle exceptions when a query returns no data set. If this variable is referenced before the cursor is opened, an exception INVALID_CURSOR is raised.

 %ISOPEN

This variable is set to TRUE if a cursor is opened and false when the cursor is closed.

 %NOTFOUND

This variable is a logical opposite of %FOUND. This variable is set to TRUE if the last fetch returns no rows an FALSE when the last fetch returns a row. This can also be used in exception handing when a query returns no rows.

 %ROWCOUNT

This variable acts like a counter. It is set to zero when a cursor is opened. Thereafter, with each fetch, the value of this variable is incremented by 1 if the fetch returns a row. This variable is handy when processing needs to be done for only a few rows of the result set.

SERIALLY_REUSABLE Pragma in Oracle

The pragma SERIALLY_REUSABLE lets you mark a package as serially reusable. You can so mark a package if its state is needed only for the duration of one call to the server (for example, an OCI call to the server or a server-to-server RPC).


Syntax

PRAGMA SERIALLY_REUSABLE;

Keyword and Parameter Description

PRAGMA

This keyword signifies that the statement is a pragma (compiler directive). Pragmas are processed at compile time, not at run time. They do not affect the meaning of a program; they simply convey information to the compiler.

Usage Notes

You can mark a bodiless package as serially reusable. If a package has a spec and body, you must mark both. You cannot mark only the body.

The global memory for serially reusable packages is pooled in the System Global Area (SGA), not allocated to individual users in the User Global Area (UGA). That way, the package work area can be reused. When the call to the server ends, the memory is returned to the pool. Each time the package is reused, its public variables are initialized to their default values or to NULL.

Serially reusable packages cannot be accessed from database triggers. If you try, Oracle generates an error.

Examples

In the following example, you create a serially reusable package:

CREATE PACKAGE pkg_pragma IS

PRAGMA SERIALLY_REUSABLE;

num NUMBER := 0;

PROCEDURE init_pkg_state(n NUMBER);

PROCEDURE print_pkg_state;

END pkg_pragma;

ORA-00078 cannot dump variables by name

Cause: An attempt was made to dump a variable by name on a system that

does not support this feature.

Action: Try the PEEK command.

ORA-00401 the value for parameter string is not supported by this release

Cause: The value specified cannot be supported by this release of the software.

Action: Choose an appropriate value, or remove the parameter value to use the

default value.

ORA-00361 cannot remove last log member string for group string

Cause: An attempt has been made to remove the last member of a log file

group.

Action: If desired, delete the entire log, by using DROP LOGFILE.

ORA-00304 requested INSTANCE_NUMBER is busy

Cause: An instance tried to start by using a value of the initialization parameter

INSTANCE_NUMBER that is already in use.

Action: Either:

Specify another INSTANCE_NUMBER,

Shut down the running instance with this number, or

Wait for instance recovery to complete on the instance with this number.

ORA-00301 error in adding log file 'string' - file cannot be created

Cause: The creation of the redo log file failed

Action: Check whether:

1. There is enough storage space on the device

2. The name of the file is valid

3. The device is online

4. An I/O error occurred

Also, it is possible REUSE was specified on the command line and a file of the

incorrect size exists. Either do not specify REUSE or use a file of the correct size.

ORA-00257 archiver error. Connect internal only, until freed.

Cause: The archiver process received an error while trying to archive a redo

log. If the problem is not resolved soon, the database will stop executing

transactions. The most likely cause of this message is the destination device is

out of space to store the redo log file.

Action: Check the archiver trace file for a detailed description of the problem.

Also, verify that the device specified in the initialization parameter ARCHIVE_

LOG_DEST is set up properly for archiving.

ORA-00201 controlfile version string incompatible with ORACLE version string

Cause: The control file was created by incompatible software.

Action: Either restart with a compatible software release or use CREATE
 
CONTROLFILE to create a new control file that is compatible with this release

ROLLBACK in Oracle

The ROLLBACK statement is the inverse of the COMMIT statement. It undoes some or all database changes made during the current transaction.
Syntax:

ROLLBACK [WORK] [TO [SAVEPOINT] savepoint_name];

When a parameterless ROLLBACK statement is executed, all database changes made during the current transaction are undone.
ROLLBACK TO
This statement undoes all database changes (and releases all locks acquired) since the savepoint identified by savepoint_name was marked.
SAVEPOINT
This keyword is optional and has no effect except to improve readability.
savepoint_name
This is an undeclared identifier, which marks the current point in the processing of a transaction. For naming conventions, see "Identifiers".
WORK
This keyword is optional and has no effect except to improve readability.

COMMIT in Oracle

Use the COMMIT statement to end your current transaction and make permanent all changes performed in the transaction. A transaction is a sequence of SQL statements that Oracle Database treats as a single unit. This statement also erases all savepoints in the transaction and releases transaction locks.

If the transaction is commited, the database ensures that the modified data can always be found, even if the database or the hard disk crashes. Of course, for the latter to be true, a decent backup and recovery concept must be in place.

Until you commit a transaction:

You can see any changes you have made during the transaction by querying the modified tables, but other users cannot see the changes. After you commit the transaction, the changes are visible to other users' statements that execute after the commit.

You can roll back (undo) any changes made during the transaction with the ROLLBACK statement (see ROLLBACK.

Oracle Database issues an implicit COMMIT before and after any data definition language (DDL) statement.

You can also use this statement to

Commit an in-doubt distributed transaction manually

Terminate a read-only transaction begun by a SET TRANSACTION statement

Oracle recommends that you explicitly end every transaction in your application programs with a COMMIT or ROLLBACK statement, including the last transaction, before disconnecting from Oracle Database. If you do not explicitly commit the transaction and the program terminates abnormally, then the last uncommitted transaction is automatically rolled back.

A normal exit from most Oracle utilities and tools causes the current transaction to be committed. A normal exit from an Oracle precompiler program does not commit the transaction and relies on Oracle Database to roll back the current transaction.

Syntax

COMMIT

The following statements permanently save all analytic workspace changes made so far in your session. The COMMIT command also saves database changes made in your session outside Oracle OLAP.

COMMIT

CHR in Oracle

The CHR function converts an integer value (or any value that can be implicitly converted to an integer value) into a character.

Return Value

A text value. For single-byte character sets, if number > 256, the function returns the binary equivalent of number MOD 256. For multibyte character sets, number must resolve to one entire code point. Invalid code points are not validated, and the result of specifying invalid code points is indeterminate.

Syntax

CHR(number [ USING NCHAR_CS ])


number

An integer value, or any value that can be implicitly converted to an integer value.

IF THEN ELSE statment in Oracle

The IF...THEN...ELSE command executes one or more statements in a program when a specified condition is met. Optionally, it also executes an alternative statement or group of statements when the condition is not met. You can use IF only within programs.


Syntax

IF boolean-expression

THEN statement1

[ELSE statement2]

Arguments

boolean-expression

Any valid Boolean expression that returns either TRUE or FALSE.

THEN statement

Oracle OLAP executes the statement1 argument when the Boolean expression is TRUE. The statement1 must be on the same line as THEN.

ELSE statement

Oracle OLAP executes the statement2 argument when the Boolean expression is FALSE. The statement2 must be on the same line as ELSE. When you omit the ELSE phrase, execution continues with the statement after the whole IF...THEN... statement in the program.

example:

IF grade='E' THEN

dbms_output.put_line("excellent");

ELSE

dbms_output.put_line("AVERAGE");

END IF;

CASE Statement in Oracle

The CASE statement selects one sequence of statements to execute. However, to select the sequence, the CASE statement uses a selector rather than multiple Boolean expressions.CASE statements is same as of IF statment except that it improves the readability of your PL/SQL code.


example

Notice the five Boolean expressions. In each instance, we test whether the same variable, grade, is equal to one of five values: 'A', 'B', 'C', 'D', or 'F'. Let us rewrite the preceding code using the CASE statement, as follows:

CASE category

WHEN 'M' THEN dbms_output.put_line('MANAGER');

WHEN 'P' THEN dbms_output.put_line('PROGRAMMER');

WHEN 'C' THEN dbms_output.put_line('CEO');

WHEN 'D' THEN dbms_output.put_line('DBA');

ELSE dbms_output.put_line('No such grade');

END CASE;



The CASE statement is more readable and more efficient. So, when possible, rewrite lengthy IF-THEN-ELSIF statements as CASE statements.

The CASE statement begins with the keyword CASE. The keyword is followed by a selector, which is the variable grade in the last example. The selector expression can be arbitrarily complex. For example, it can contain function calls. Usually, however, it consists of a single variable. The selector expression is evaluated only once. The value it yields can have any PL/SQL datatype other than BLOB, BFILE, an object type, a PL/SQL record, an index-by-table, a varray, or a nested table.

The selector is followed by one or more WHEN clauses, which are checked sequentially. The value of the selector determines which clause is executed. If the value of the selector equals the value of a WHEN-clause expression, that WHEN clause is executed. For instance, in the last example, if grade equals 'C', the program outputs 'Good'. Execution never falls through; if any WHEN clause is executed, control passes to the next statement.

The ELSE clause works similarly to the ELSE clause in an IF statement. In the last example, if the grade is not one of the choices covered by a WHEN clause, the ELSE clause is selected, and the phrase 'No such grade' is output. The ELSE clause is optional. However, if you omit the ELSE clause, PL/SQL adds the following implicit ELSE clause:

ELSE RAISE CASE_NOT_FOUND;



If the CASE statement selects the implicit ELSE clause, PL/SQL raises the predefined exception CASE_NOT_FOUND. So, there is always a default action, even when you omit the ELSE clause.

The keywords END CASE terminate the CASE statement. These two keywords must be separated by a space. The CASE statement has the following form:

[<>]

CASE selector

WHEN expression1 THEN sequence_of_statements1;

WHEN expression2 THEN sequence_of_statements2;

...

WHEN expressionN THEN sequence_of_statementsN;

[ELSE sequence_of_statementsN+1;]

END CASE [label_name];

CASE statements can be labeled.Exceptions raised during the execution of a CASE statement are handled in the usual way. That is, normal execution stops and control transfers to the exception-handling part of your PL/SQL block or subprogram.

An alternative to the CASEstatement is the CASE expression, where each WHEN clause is an expression.
Searched CASE Statement

PL/SQL also provides a searched CASE statement, which has the form:

[<>]

CASE

WHEN search_condition1 THEN sequence_of_statements1;

WHEN search_condition2 THEN sequence_of_statements2;
...

WHEN search_conditionN THEN sequence_of_statementsN;

[ELSE sequence_of_statementsN+1;]

END CASE [label_name];

The searched CASE statement has no selector. Also, its WHEN clauses contain search conditions that yield a Boolean value, not expressions that can yield a value of any type. An example follows:

CASE

WHEN grade = 'M' THEN dbms_output.put_line('MANAGER');

WHEN grade = 'P' THEN dbms_output.put_line('PROGRAMMER');

WHEN grade = 'C' THEN dbms_output.put_line('CEO');

WHEN grade = 'D' THEN dbms_output.put_line('DBA');

ELSE dbms_output.put_line('No such grade');

END CASE;



The search conditions are evaluated sequentially. The Boolean value of each search condition determines which WHEN clause is executed. If a search condition yields TRUE, its WHEN clause is executed. If any WHEN clause is executed, control passes to the next statement, so subsequent search conditions are not evaluated.

If none of the search conditions yields TRUE, the ELSE clause is executed. The ELSE clause is optional. However, if you omit the ELSE clause, PL/SQL adds the following implicit ELSE clause:

ELSE RAISE CASE_NOT_FOUND;

Exceptions raised during the execution of a searched CASE statement are handled in the usual way. That is, normal execution stops and control transfers to the exception-handling part of your PL/SQL block or subprogram

NULL statement IN Oracle

The NULL statement explicitly specifies inaction; it does nothing other than pass control to the next statement. In a construct allowing alternative actions, the NULL statement serves as a placeholder.


The NULL statement improves readability by making the meaning and action of conditional statements clear. It tells readers that the associated alternative has not been overlooked, but that indeed no action is necessary.

Each clause in an IF statement must contain at least one executable statement. The NULL statement meets this requirement. So, you can use the NULL statement in clauses that correspond to circumstances in which no action is taken. The NULL statement and Boolean value NULL are unrelated.

Examples

IF EMP_CODE = 'M' THEN

compute_salary_manager(emp_id);

ELSIF EMP_CODE = 'P' THEN

compute_salary_programmer(emp_id);

ELSE

NULL;

END IF;



In the next example, the NULL statement shows that no action is taken for unnamed exceptions:

EXCEPTION

WHEN NO_DATA_FOUND THEN

SQLERRM:='NO DATA FOUND';

...

WHEN OTHERS THEN

NULL;

Exceptions in Oracle

An exception is a runtime error or warning condition, which can be predefined or user-defined. Predefined exceptions are raised implicitly (automatically) by the runtime system. User-defined exceptions must be raised explicitly by RAISE statements
An exception declaration can appear only in the declarative part of a block, subprogram, or package. The scope rules for exceptions and variables are the same. But, unlike variables, exceptions cannot be passed as parameters to subprograms.

Predefined Exceptions
Some exceptions are predefined by PL/SQL. For a list of these exceptions, see "Predefined PL/SQL Exceptions". PL/SQL declares predefined exceptions globally in package STANDARD, so you need not declare them yourself.
Redeclaring predefined exceptions is error prone because your local declaration overrides the global declaration. In such cases, you must use dot notation to specify the predefined exception, as follows:
EXCEPTION
WHEN invalid_number OR STANDARD.INVALID_NUMBER THEN ...

The exception-handling part of a PL/SQL block is optional. Exception handlers must come at the end of the block.

User-defined Exceptions

A User-defined exception has to be defined by the programmer. User-defined exceptions are declared in the declaration section with their type as exception. They must be raised explicitly using RAISE statement, unlike pre-defined exceptions that are raised implicitly. RAISE statement can also be used to raise internal exceptions.

Declaring Exception:

DECLARE
my_exception EXCEPTION;
BEGIN
------

Raising Exception:

BEGIN
RAISE my_exception;
-------

Handling Exception:

BEGIN
------
----
EXCEPTION
WHEN my_exception THEN
Statements;
END;

Named System Exception in Oracle

Oracle Exception Name	Oracle Error	Explanation
DUP_VAL_ON_INDEX	ORA-00001	You tried to execute an INSERT or UPDATE statement that has created a duplicate value in a field restricted by a unique index.
TIMEOUT_ON_RESOURCE	ORA-00051	You were waiting for a resource and you timed out.
TRANSACTION_BACKED_OUT	ORA-00061	The remote portion of a transaction has rolled back.
INVALID_CURSOR	ORA-01001	You tried to reference a cursor that does not yet exist. This may have happened because you've executed a FETCH cursor or CLOSE cursor before OPENing the cursor.
NOT_LOGGED_ON	ORA-01012	You tried to execute a call to Oracle before logging in.
LOGIN_DENIED	ORA-01017	You tried to log into Oracle with an invalid username/password combination.
NO_DATA_FOUND	ORA-01403	You tried one of the following: You executed a SELECT INTO statement and no rows were returned. You referenced an uninitialized row in a table. You read past the end of file with the UTL_FILE package.
TOO_MANY_ROWS	ORA-01422	You tried to execute a SELECT INTO statement and more than one row was returned.
ZERO_DIVIDE	ORA-01476	You tried to divide a number by zero.
INVALID_NUMBER	ORA-01722	You tried to execute an SQL statement that tried to convert a string to a number, but it was unsuccessful.
STORAGE_ERROR	ORA-06500	You ran out of memory or memory was corrupted.
PROGRAM_ERROR	ORA-06501	This is a generic "Contact Oracle support" message because an internal problem was encountered.
VALUE_ERROR	ORA-06502	You tried to perform an operation and there was a error on a conversion, truncation, or invalid constraining of numeric or character data.
CURSOR_ALREADY_OPEN	ORA-06511	You tried to open a cursor that is already open.

CLOB datatype in Oracle

You use the CLOB datatype to store large blocks of character data in the database, in-line or out-of-line. Both fixed-width and variable-width character sets are supported. Every CLOB variable stores a locator, which points to a large block of character data. The size of a CLOB cannot exceed four gigabytes.

CLOBs participate fully in transactions, are recoverable, and can be replicated. Changes made by package DBMS_LOB can be committed or rolled back. CLOB locators can span transactions (for reads only), but they cannot span sessions.

BLOB datatype in Oracle

You use the BLOB datatype to store large binary objects in the database, in-line or out-of-line. Every BLOB variable stores a locator, which points to a large binary object. The size of a BLOB cannot exceed four gigabytes.

BLOBs participate fully in transactions, are recoverable, and can be replicated. Changes made by package DBMS_LOB can be committed or rolled back. BLOB locators can span transactions (for reads only), but they cannot span sessions.

BFILE datatype in Oracle

You use the BFILE datatype to store large binary objects in operating system files outside the database. Every BFILE variable stores a file locator, which points to a large binary file on the server. The locator includes a directory alias, which specifies a full path name (logical path names are not supported).

BFILEs are read-only, so you cannot modify them. The size of a BFILE is system dependent but cannot exceed four gigabytes (2**32 - 1 bytes). Your DBA makes sure that a given BFILE exists and that Oracle has read permissions on it. The underlying operating system maintains file integrity.

BFILEs do not participate in transactions, are not recoverable, and cannot be replicated. The maximum number of open BFILEs is set by the Oracle initialization parameter SESSION_MAX_OPEN_FILES, which is system dependent.

NVARCHAR2 datatype in Oracle

You use the NVARCHAR2 datatype to store variable-length Unicode character data. How the data is represented internally depends on the national character set specified when the database was created, which might use a variable-width encoding (UTF8) or a fixed-width encoding (AL16UTF16). Because this type can always accommodate multibyte characters, you can use it to hold any Unicode character data.

The NVARCHAR2 datatype takes a required parameter that specifies a maximum size in characters. The syntax follows:

NVARCHAR2(maximum_size)

Because the physical limit is 32767 bytes, the maximum value you can specify for the length is 32767/2 in the AL16UTF16 encoding, and 32767/3 in the UTF8 encoding.

You cannot use a symbolic constant or variable to specify the maximum size; you must use an integer literal.

The maximum size always represents the number of characters, unlike VARCHAR2 which can be specified in either characters or bytes.

my_string NVARCHAR2(200); -- maximum size is 200 characters

The maximum width of a NVARCHAR2 database column is 4000 bytes. Therefore, you cannot insert NVARCHAR2 values longer than 4000 bytes into a NVARCHAR2 column

NCHAR datatype in Oracle

You use the NCHAR datatype to store fixed-length (blank-padded if necessary) national character data. How the data is represented internally depends on the national character set specified when the database was created, which might use a variable-width encoding (UTF8) or a fixed-width encoding (AL16UTF16). Because this type can always accommodate multibyte characters, you can use it to hold any Unicode character data.

The NCHAR datatype takes an optional parameter that lets you specify a maximum size in characters. The syntax follows:

NCHAR[(maximum_size)]



Because the physical limit is 32767 bytes, the maximum value you can specify for the length is 32767/2 in the AL16UTF16 encoding, and 32767/3 in the UTF8 encoding.

You cannot use a symbolic constant or variable to specify the maximum size; you must use an integer literal.

If you do not specify a maximum size, it defaults to 1. The value always represents the number of characters, unlike CHAR which can be specified in either characters or bytes.

VARCHAR2 Datatype in Oracle

 Variable length character string having maximum length size bytes.
 VARCHAR2 is used to store variable length character strings. The string value's length will be stored on   disk with the value itself.

usage:variablename varchar2(size)

eg:
password varchar2(8)

You use the VARCHAR2 datatype to store variable-length character data. How the data is represented internally depends on the database character set. The VARCHAR2 datatype takes a required parameter that specifies a maximum size up to 32767 bytes. The syntax follows:


VARCHAR2(maximum_size [CHAR
BYTE])



You cannot use a symbolic constant or variable to specify the maximum size; you must use an integer literal in the range 1 .. 32767.

Small VARCHAR2 variables are optimized for performance, and larger ones are optimized for efficient memory use. The cutoff point is 2000 bytes. For a VARCHAR2 that is 2000 bytes or longer, PL/SQL dynamically allocates only enough memory to hold the actual value. For a VARCHAR2 variable that is shorter than 2000 bytes, PL/SQL preallocates the full declared length of the variable. For example, if you assign the same 500-byte value to a VARCHAR2(2000 BYTE) variable and to a VARCHAR2(1999 BYTE) variable, the former takes up 500 bytes and the latter takes up 1999 bytes.

CHAR Datatype in Oracle

The CHAR datatype stores fixed-length character strings. When you create a table with a CHAR column, you must specify a string length (in bytes or characters) between 1 and 2000 bytes for the CHAR column width. The default is 1 byte. Fixed length character data of length size bytes. This should be used for fixed length data. Such as codes A100, B102...

Oracle then guarantees that:


When you insert or update a row in the table, the value for the CHAR column has the fixed length.

If you give a shorter value, then the value is blank-padded to the fixed length.

If a value is too large, Oracle Database returns an error.

Oracle Database compares CHAR values using blank-padded comparison semantics.

usage: variablename char(size)
eg:
 
user_id char(8)

ORA-00108 failed to set up dispatcher to accept connection asynchronously

Cause: The network protocol used by the dispatcher does not support

asynchronous operations.

Action: Contact Oracle Support Services.

ORA-00107 failed to connect to ORACLE listener process

Cause: Most likely due to the fact that the network listener process has not

been started.

Action: Check for the following:

The network configuration file is not set up correctly.

The client side address is not specified correctly.

The listener initialization parameter file is not set up correctly.

ORA-00106 cannot startup/shutdown database when connected to a dispatcher

Cause: An attempt was made to start or shut down an instance while

connected to a shared server via a dispatcher.

Action: Reconnect as user INTERNAL without going through the dispatcher.

For most cases, this can be done by connect to INTERNAL without specifying a

network connect string.

ORA-00105 dispatching mechanism not configured for network protocol string

Cause: An attempt was made to alter the dispatcher’s configuration for a

network protocol that was not specified in the initialization parameter file.

Action: Do one of the following:

1. Include the network protocol in the definition for the DISPATCHERS

initialization parameter and restart the system.

 2.Use the ALTER SYSTEM SET DISPATCHERS statement with a network

protocol that exists in the initialization parameter file.

ORA-00104 deadlock detected; all public servers blocked waiting for resources

Cause: All available public servers are servicing requests that require resources

locked by a client which is unable to get a public server to release the resources.

Action: Increase the limit for the system parameter MAX_SHARED_SERVERS as

the system will automatically start new servers to break the deadlock until the

number of servers reaches the value specified in MAX_SHARED_SERVERS.

ORA-00103 invalid network protocol; reserved for use by dispatchers

Cause: The network specified in the Net8 connect string is reserved for use by

the dispatchers.

Action: Specify other network protocols in the connection string.

ORA-00102 network protocol string cannot be used by dispatchers

Cause: The network specified in DISPATCHERS does not have the functionality

required by the dispatchers.

Action: Refer to your Oracle operating system-specific documentation for

network protocols supported by the dispatchers.

ORA-00101 invalid specification for system parameter DISPATCHERS

Cause: The syntax for the DISPATCHERS initialization parameter is incorrect.

Action: Enter the DISPATCHERS initialization parameter into the initialization

parameter file with the correct syntax and then shut down and restart the

instance.

ORA-00100 no data found

Cause: An application made a reference to unknown or inaccessible data.

Action: Handle this condition within the application or make appropriate

modifications to the application code.

ORA-00099 timed out while waiting for resource, potential PDML deadlock

Cause: The resource needed by the transaction was busy. The PDML

transaction could not acquire the resource within the specified amount of time.

This indicates potential deadlock involving this PDML transaction and other

transactions currently running in the system.

Action: Increase the value of the PARALLEL_TRANSACTION_RESOURCE_

TIMEOUT parameter; then retry the operation.

ORA-00097 use of Oracle SQL feature not in SQL92 string Level

Cause: An attempt was made to use an Oracle SQL feature that is not

compliant with the SQL92 standard.

Action: Do not use the feature or use the ALTER SESSION SET FLAGGER

command to set the appropriate level of SQL92 compliance.

ORA-00096 invalid value string for parameter string, must be from among string

Cause: The value for the initialization parameter is invalid.

Action: Choose a value as indicated by the message.

ORA-00094 string requires an integer value

Cause: The parameter value is not an integer.

Action: Modify the parameter value to be an integer.

ORA-00093 string must be between string and string

Cause: The parameter value is not in a valid range.

Action: Modify the parameter value to be within the specified range

ORA-00092 LARGE_POOL_SIZE must be greater than LARGE_POOL_MIN_

Cause: The value of LARGE_POOL_SIZE is less than the value of LARGE_

POOL_MIN_ALLOC.

Action: Increase the value of LARGE_POOL_SIZE past the value of LARGE_

POOL_MIN_ALLOC.

ORA-00091 LARGE_POOL_SIZE must be at least string

Cause: The value of LARGE_POOL_SIZE is below the minimum size.

Action: Increase the value of LARGE_POOL_SIZE past the minimum size.

ORA-00090 failed to allocate memory for cluster database ORADEBUG command

Cause: Could not allocate memory needed to execute cluster database

oradebug.

Action: Reissue the command on each instance with single-instance

ORADEBUG.

ORA-00089 invalid instance number in ORADEBUG command

Cause: An invalid instance number was specified in a cluster database

ORADEBUG command.

Action: Reissue the command with valid instance numbers.

ORA-00088 command cannot be executed by shared server

Cause: A debug command was issued on a shared server.

Action: Reissue the command using a dedicated server.

ORA-00087 command cannot be executed on remote instance

Cause: Cluster database command issued for non cluster database

ORADEBUG command.

Action: Issue the command without the cluster database syntax.

ORA-00086 user call does not exist

Cause: An invalid attempt was made to dump the user call heap.

Action: Wait until the process starts a call.

ORA-00085 current call does not exist

Cause: An invalid attempt was made to dump the current call heap.

Action: Wait until the process starts a call.

ORA-00084 global area must be PGA, SGA, or UGA

Cause: An attempt was made to dump an invalid global area.

Action: Specify either PGA, SGA, or UGA.

ORA-00083 warning: possibly corrupt SGA mapped

Cause: Even though there may be SGA corruptions, the SGA was mapped.

Action: Use the DUMPSGA command to dump the SGA.

ORA-00082 memory size of string is not in valid set of [1], [2]

Cause: An invalid length was specified for the POKE command.

Action: Use a valid length (either 1, 2, 4, or possibly 8).

ORA-00081 address range [string, string) is not readable

Cause: An attempt was made to read/write an invalid memory address range.

Action: Try another address or length.

ORA-00080 invalid global area specified by level string

Cause: An attempt was made to dump an invalid global area.

Action: Use level 1 for the PGA, 2 for the SGA, and 3 for the UGA. Use

extra + level to dump global area level as well as extra bytes for every pointer;

extra must be a multiple of 4.

ORA-00079 variable string not found

Cause: An attempt was made to dump a variable that does not exist.

Action: Use a valid variable name.

ORA-00077 dump string is not valid

Cause: An attempt was made to invoke an invalid dump.

Action: Try another dump.

ORA-00076 dump string not found

Cause: An attempt was made to invoke a dump that does not exist.

Action: Type DUMPLIST to see the list of available dumps.

ORA-00075 process "string" not found in this instance

Cause: The specified process was not logged on to the current instance.

Action: Specify a valid process.

ORA-00074 no process has been specified

Cause: No debug process has been specified.

Action: Specify a valid process.

ORA-00073 command string takes between string and string argument(s)

Cause: An incorrect number of arguments was specified.

Action: Specify the correct number of arguments. Type HELP to see the list of

commands and their syntax.

ORA-00072 process "string" is not active

Cause: An invalid process was specified.

Action: Specify a valid process.

ORA-00071 process number must be between 1 and string

Cause: An invalid process number was specified.

Action: Specify a valid process number.

ORA-00070 command string is not valid

Cause: An invalid debugger command was specified.

Action: Type HELP to see the list of available commands.

ORA-00069 cannot acquire lock -- table locks disabled for string

Cause: A command was issued that tried to lock the table indicated in the

message. Examples of commands that can lock tables are: LOCK TABLE,

ALTER TABLE... ADD (...), and so on.

Action: Use the ALTER TABLE... ENABLE TABLE LOCK command, and retry

the command.

ORA-00068 invalid value string for parameter string, must be between string and

Cause: The value for the initialization parameter is invalid.

Action: Choose a value as indicated by the message.

ORA-00067 invalid value string for parameter string; must be at least string

Cause: The value for the initialization parameter is invalid.

Action: Choose a value as indicated by the message.

ORA-00066 LOG_FILES is string but needs to be string to be compatible

Cause: The maximum number of log files supported by this instance is not the

same as for the other instances. All instances must be able to open all the files

any instance can open.

Action: Change the value of the LOG_FILES initialization parameter to be

compatible.

ORA-00065 initialization of FIXED_DATE failed

Cause: The FIXED_DATE string was not in date format

yyyy-mm-dd:hh24:mi:ss.

Action: Make sure the initialization parameter is in the correct date format.

ORA-00064 object is too large to allocate on this O/S (string,string)

Cause: An initialization parameter was set to a value that required allocating

more contiguous space than can be allocated on this operating system.

Action: Reduce the value of the initialization parameter.

ORA-00063 maximum number of LOG_FILES exceeded

Cause: The value of the LOG_FILES initialization parameter was exceeded.

Action: Increase the value of the LOG_FILES initialization parameter and

restart Oracle. The value of the parameter needs to be as large as the highest

number of log files that currently exist rather than just the count of logs that

exist.

ORA-00062 DML full-table lock cannot be acquired; DML_LOCKS is 0

Cause: The instance was started with the value of DML_LOCKS set to zero,

and the statement being executed needs a full-table lock (S, X, or SSX).

Action: Restart the instance with DML_LOCKS not equal to zero, and

re-execute the statement.

ORA-00061 another instance has a different DML_LOCKS setting

Cause: The shared instance being started is using DML locks, and the running

instances are not, or vice-versa.

Action:
Either:

 Do not use DML_LOCKS by setting DML_LOCKS to zero at all instances or

 Use DML_LOCKS by setting DML_LOCKS to a positive integer at all

instances.

ORA-00060 deadlock detected while waiting for resource

Cause: Your session and another session are waiting for a resource locked by

the other. This condition is known as a deadlock. To resolve the deadlock, one

or more statements were rolled back for the other session to continue work.

Action:

Either:

1. Enter a ROLLBACK statement and re-execute all statements since the last

commit or

2. Wait until the lock is released, possibly a few minutes, and then re-execute

the rolled back statements.

ORA-00059 maximum number of DB_FILES exceeded

Cause: The value of the DB_FILES initialization parameter was exceeded.

Action: Increase the value of the DB_FILES parameter and restart Oracle.

ORA-00058 DB_BLOCK_SIZE must be string to mount this database (not string)

Cause: The value of the DB_BLOCK_SIZE initialization parameter used to start

this database does not match the value used when that database was created.

Potential reasons for this mismatch are:

1. mounting the wrong database

2. using the wrong initialization parameter file

3. the value of the DB_BLOCK_SIZE parameter was changed

Action: For one of the above causes, either:

1. mount the correct database

2. use the correct initialization parameter file

3. correct the value of the DB_BLOCK_SIZE parameter

ORA-00057 maximum number of temporary table locks exceeded

Cause: The number of temporary tables equals or exceeds the number of

temporary table locks. Temporary tables are often created by large sorts.

Action: Increase the value of the TEMPORARY_TABLE_LOCKS initialization

parameter and restart Oracle.

ORA-00056 DDL lock on object 'string.string' is already held in an incompatible mode

Cause: The attempted lock is incompatible with the DDL lock already held on

the object. This happens if you attempt to drop a table that has parse locks.

Action: Before attempting to drop a table, check that it has no parse locks. Wait

a few minutes before retrying the operation.

ORA-00055 maximum number of DML locks exceeded

Cause: Ran out of DML lock state objects.

Action: Increase the value of the DML_LOCKS initialization parameter and

restart Oracle.

ORA-00054 resource busy and acquire with NOWAIT specified

Cause: The NOWAIT keyword forced a return to the command prompt

because a resource was unavailable for a LOCK TABLE or SELECT FOR

UPDATE command.

Action: Try the command after a few minutes or enter the command without

the NOWAIT keyword.

ORA-00053 maximum number of enqueues exceeded

Cause: Ran out of enqueue state objects.

Action: Increase the value of the ENQUEUES initialization parameter.

ORA-00052 maximum number of enqueue resources (string) exceeded

Cause: Ran out of enqueue resources.


Action: Increase the value of the ENQUEUE_RESOURCES initialization

parameter.

ORA-00051 timeout occurred while waiting for a resource

Cause: This message is usually caused by an instance that has terminated

abnormally.

Action: Restart any non-recovered instances.

ORA-00050 operating system error occurred while obtaining an enqueue

Cause: Could not obtain the operating system resources necessary to cover an

Oracle enqueue. This is normally the result of an operating system user quota

that is too low.

Action: Look up the operating system error in your system documentation and

perform the needed action.

ORA-00038 Cannot create session: server group belongs to another user

Cause: An attempt was made to create a non-migratable session in a server

group that is owned by a different user.

Action: A server group is owned by the first user who logs into a server in the

server group in non-migratable mode. All subsequent non-migratable mode

logins must be made by the user who owns the server group. To have a

different user log in non-migratable mode, the ownership of the server group

will have to be changed. This can be done by logging off all current sessions

and detaching from all existing servers in the server group and then having the

new user login to become the new owner.

ORA-00037 cannot switch to a session belonging to a different server group

Cause: An attempt was made to switch to a session in a different server group.

This is not allowed.

Action: Make sure the server switches to a session that belongs to its server

group.

ORA-00036 maximum number of recursive SQL levels (string) exceeded

Cause: An attempt was made to go more than the specified number of

recursive SQL levels.

Action: Remove the recursive SQL, possibly a recursive trigger.

ORA-00035 LICENSE_MAX_USERS cannot be less than current number of users

Cause: Specified values for LICENSE_MAX_USERS is less than the current

number of users.

Action: Check the license limit and drop extra users or purchase more licenses.

ORA-00034 cannot string in current PL/SQL session

Cause: An attempt was made to issue a commit or rollback from a PL/SQL

object (procedure, function, package) in a session that has this disabled by a

ALTER SESSION DISABLE COMMIT IN PROCEDURE statement.

Action: Enable commits from PL/SQL in this session, or do not attempt to use

commit or rollback in PL/SQL when they are disabled in the current session.

ORA-00033 current session has empty migration password

Cause: An attempt was made to detach or clone the current session and it has

an empty migration password. This is not allowed.

Action: Create the session with a non-empty migration password

ORA-00032 invalid session migration password

Cause: The session migration password specified in a session creation call was


invalid (probably too long).

Action: Retry with a valid password (less than 30 characters).

ORA-00032 invalid session migration password

Cause: The session migration password specified in a session creation call was

invalid (probably too long).

Action: Retry with a valid password (less than 30 characters).

ORA-00031 session marked for kill

Cause: The session specified in an ALTER SYSTEM KILL SESSION command

cannot be killed immediately because the session is involved in a

non-interruptible operation (for example, rolling back a transaction or being

blocked by a network operation). The session has been marked to be killed as

soon as possible after the current operation is done.

Action: No action is required for the session to be killed, but further executions

of the ALTER SYSTEM KILL SESSION command on this session may cause the

session to be killed sooner

ORA-00030 User session ID does not exist.

Cause: The user session ID no longer exists, probably because the session was

logged out.

Action: Use a valid session ID.

ORA-00028 your session has been killed

Cause: A privileged user killed the session and it is no longer logged in to the


database.

Action: Contact the database administrator. The administrator may be

attempting to perform an operation that requires users to be logged out. When

the database administrator announces that the database is available, log in and

resume work.

ORA-00027 cannot kill current session

Cause: An attempt was made to use ALTER SYSTEM KILL SESSION to kill the

current session.

Action: If it is necessary to kill the current session, do so from another session.

ORA-00026 missing or invalid session ID

Cause: The session ID string specified in the ALTER SYSTEM KILL SESSION

command was invalid, or no string was specified.

Action: Retry the command with a valid session ID.

ORA-00024 logins from more than one process not allowed in single-process

Cause: An attempt was made to log in more than once from different processes

for Oracle started in single-process mode.

Action: Log off from the other process.

ORA-00023 session references process private memory; cannot detach session

Cause: An attempt was made to detach the current session when it contains

references to process private memory.

Action: A session may contain references to process memory (PGA) if it has an

open network connection, a very large context area, or operating system

privileges. To allow the detach, it may be necessary to close the session's

database links and/or cursors. Detaching a session with operating system

privileges is always disallowed.

ORA-00022 invalid session ID; access denied

Cause: Either the session specified does not exist or the caller does not have the

privilege to access it.

Action: Specify a valid session ID that you have privilege to access, that is

either you own it or you have the CHANGE_USER privilege.

ORA-00022 invalid session ID; access denied

Cause: Either the session specified does not exist or the caller does not have the

privilege to access it.

Action: Specify a valid session ID that you have privilege to access, that is

either you own it or you have the CHANGE_USER privilege.

ORA-00021 session attached to some other process; cannot switch session

Cause: The user session is currently used by others.

Action: Do not switch to a session attached to some

ORA-00020 maximum number of processes (string) exceeded

Cause: All process state objects are in use.

Action: Increase the value of the PROCESSES initialization parameter.

ORA-00019 maximum number of session licenses exceeded

Cause: All licenses are in use.

Action: Increase the value of the LICENSE MAX SESSIONS initialization

parameter.

ORA-00018 maximum number of sessions exceeded

Cause: All session state objects are in use.

Action: Increase the value of the SESSIONS initialization parameter.

ORA-00017 session requested to set trace event

Cause: The current session was requested to set a trace event by another

session.

Action: This is used internally; no action is required.

ORA-00001 unique constraint (string.string) violated

Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.

Action: Either remove the unique restriction or do not insert the key.

ORA-00000 normal, successful completion

Cause: An operation has completed normally, having met no exceptions.


Action: No action required.

Assignment statment in Oracle PL/SQL

Assignment Statement


An assignment statement sets the current value of a variable, field, parameter, or element. The statement consists of an assignment target followed by the assignment operator and an expression. When the statement is executed, the expression is evaluated and the resulting value is stored in the target. For more information, see "Variable Assignment".

Syntax



Text description of the illustration assignment_statement.gif

Keyword and Parameter Description

attribute_name

This identifies an attribute of an object type. The name must be unique within the object type (but can be reused in other object types). You cannot initialize an attribute in its declaration using the assignment operator or DEFAULT clause. Also, you cannot impose the NOT NULL constraint on an attribute.

collection_name

This identifies a nested table, index-by table, or varray previously declared within the current scope.

cursor_variable_name

This identifies a PL/SQL cursor variable previously declared within the current scope. Only the value of another cursor variable can be assigned to a cursor variable.

expression

This is an arbitrarily complex combination of variables, constants, literals, operators, and function calls. The simplest expression consists of a single variable. For the syntax of expression, see "Expressions". When the assignment statement is executed, the expression is evaluated and the resulting value is stored in the assignment target. The value and target must have compatible datatypes.

field_name

This identifies a field in a user-defined or %ROWTYPE record.

host_cursor_variable_name

This identifies a cursor variable declared in a PL/SQL host environment and passed to PL/SQL as a bind variable. The datatype of the host cursor variable is compatible with the return type of any PL/SQL cursor variable. Host variables must be prefixed with a colon.

host_variable_name

This identifies a variable declared in a PL/SQL host environment and passed to PL/SQL as a bind variable. Host variables must be prefixed with a colon.

index

This is a numeric expression that must yield a value of type BINARY_INTEGER or a value implicitly convertible to that datatype.

indicator_name

This identifies an indicator variable declared in a PL/SQL host environment and passed to PL/SQL. Indicator variables must be prefixed with a colon. An indicator variable "indicates" the value or condition of its associated host variable. For example, in the Oracle Precompiler environment, indicator variables let you detect nulls or truncated values in output host variables.

object_name

This identifies an object (instance of an object type) previously declared within the current scope.

parameter_name

This identifies a formal OUT or IN OUT parameter of the subprogram in which the assignment statement appears.

record_name

This identifies a user-defined or %ROWTYPE record previously declared within the current scope.

variable_name

This identifies a PL/SQL variable previously declared within the current scope.

Usage Notes

By default, unless a variable is initialized in its declaration, it is initialized to NULL every time a block or subprogram is entered. So, never reference a variable before you assign it a value.

You cannot assign nulls to a variable defined as NOT NULL. If you try, PL/SQL raises the predefined exception VALUE_ERROR.

Only the values TRUE, FALSE, and NULL can be assigned to a Boolean variable. When applied to an expression, the relational operators return a Boolean value. So, the following assignment is legal:

DECLARE

out_of_range BOOLEAN;

...

BEGIN

...

out_of_range := (salary < minimum) OR (salary > maximum);



As the next example shows, you can assign the value of an expression to a specific field in a record:

DECLARE

emp_rec emp%ROWTYPE;

BEGIN

...

emp_rec.sal := current_salary + increase;



Moreover, you can assign values to all fields in a record at once. PL/SQL allows aggregate assignment between entire records if their declarations refer to the same cursor or table. For example, the following assignment is legal:

DECLARE

emp_rec1 emp%ROWTYPE;

emp_rec2 emp%ROWTYPE;

dept_rec dept%ROWTYPE;

BEGIN

...

emp_rec1 := emp_rec2;



Using the following syntax, you can assign the value of an expression to a specific element in a collection:

collection_name(index) := expression;



In the following example, you assign the uppercase value of last_name to the third row in nested table ename_tab:

ename_tab(3) := UPPER(last_name);

Examples

Several examples of assignment statements follow:

wages := hours_worked * hourly_salary;

country := 'France';

costs := labor + supplies;

done := (count > 100);

dept_rec.loc := 'BOSTON';

comm_tab(5) := sales * 0.15;

GOTO statment in Oracle PL/SQL

The GOTO statement branches unconditionally to a statement label or block label. The label must be unique within its scope and must precede an executable statement or a PL/SQL block. The GOTO statement transfers control to the labelled statement or block. For more information, see "GOTO Statement".


Syntax



Text description of the illustration label_declaration.gif

Keyword and Parameter Description

label_name

This is an undeclared identifier that labels an executable statement or a PL/SQL block. You use a GOTO statement to transfer control to the statement or block following <>.

Usage Notes

Some possible destinations of a GOTO statement are not allowed. In particular, a GOTO statement cannot branch into an IF statement, LOOP statement, or sub-block. For example, the following GOTO statement is not allowed:

BEGIN

...

GOTO update_row; -- can't branch into IF statement

...

IF valid THEN

...

<>

UPDATE emp SET ...

END IF;



From the current block, a GOTO statement can branch to another place in the block or into an enclosing block, but not into an exception handler. From an exception handler, a GOTO statement can branch into an enclosing block, but not into the current block.

If you use the GOTO statement to exit a cursor FOR loop prematurely, the cursor is closed automatically. The cursor is also closed automatically if an exception is raised inside the loop.

A given label can appear only once in a block. However, the label can appear in other blocks including enclosing blocks and sub-blocks. If a GOTO statement cannot find its target label in the current block, it branches to the first enclosing block in which the label appears.

Examples

A GOTO label cannot precede just any keyword. It must precede an executable statement or a PL/SQL block. For example, the following GOTO statement is not allowed:

FOR ctr IN 1..50 LOOP

DELETE FROM emp WHERE ...

IF SQL%FOUND THEN

GOTO end_loop; -- not allowed

END IF;

...

<>

END LOOP; -- not an executable statement



To debug the last example, simply add the NULL statement, as follows:

FOR ctr IN 1..50 LOOP

DELETE FROM emp WHERE ...

IF SQL%FOUND THEN

GOTO end_loop;

END IF;

...

<>

NULL; -- an executable statement that specifies inaction

END LOOP;

PL/SQL Naming Conventions

The same naming conventions apply to all PL/SQL program items and units including constants, variables, cursors, cursor variables, exceptions, procedures, functions, and packages. Names can be simple, qualified, remote, or both qualified and remote. For example, you might use the procedure name raise_salary in any of the following ways:

raise_salary(...); -- simple

emp_actions.raise_salary(...); -- qualified

raise_salary@newyork(...); -- remote

emp_actions.raise_salary@newyork(...); -- qualified and remote



In the first case, you simply use the procedure name. In the second case, you must qualify the name using dot notation because the procedure is stored in a package called emp_actions. In the third case, using the remote access indicator (@), you reference the database link newyork because the procedure is stored in a remote database. In the fourth case, you qualify the procedure name and reference a database link.

Synonyms

You can create synonyms to provide location transparency for remote schema objects such as tables, sequences, views, standalone subprograms, packages, and object types. However, you cannot create synonyms for items declared within subprograms or packages. That includes constants, variables, cursors, cursor variables, exceptions, and packaged subprograms.

Scoping

Within the same scope, all declared identifiers must be unique. So, even if their datatypes differ, variables and parameters cannot share the same name. In the following example, the second declaration is not allowed:

DECLARE

valid_id BOOLEAN;

valid_id VARCHAR2(5); -- not allowed duplicate identifier



For the scoping rules that apply to identifiers, see "Scope and Visibility of PL/SQL Identifiers".

Case Sensitivity

Like all identifiers, the names of constants, variables, and parameters are not case sensitive. For instance, PL/SQL considers the following names to be the same:

DECLARE

zip_code INTEGER;

Zip_Code INTEGER; -- same as zip_code

Name Resolution

In potentially ambiguous SQL statements, the names of database columns take precedence over the names of local variables and formal parameters. For example, the following DELETE statement removes all employees from the emp table, not just 'KING', because Oracle assumes that both enames in the WHERE clause refer to the database column:

DECLARE

ename VARCHAR2(10) := 'KING';

BEGIN

DELETE FROM emp WHERE ename = ename;

...



In such cases, to avoid ambiguity, prefix the names of local variables and formal parameters with my_, as follows:

DECLARE

my_ename VARCHAR2(10);



Or, use a block label to qualify references, as in

<

>

DECLARE

ename VARCHAR2(10) := 'KING';

BEGIN

DELETE FROM emp WHERE ename = main.ename;

...



The next example shows that you can use a subprogram name to qualify references to local variables and formal parameters:

FUNCTION bonus (deptno IN NUMBER, ...) RETURN REAL IS

job CHAR(10);

BEGIN

SELECT ... WHERE deptno = bonus.deptno AND job = bonus.job;

...

Using Aliases

Table aliases (formerly known as correlation names) are "short names" for each table in your FROM clause meant to uniquely identify the data source, but to act as an abbreviation.

When displaying the column name, Oracle uses the uppercase version of the column name as the header.
When using an expression, Oracle strips out the spaces, and uses the expression as the header.
You can provide your own column name using an alias.
Column Aliases
eg:
select sal*2 double_sal from emp;

here double_sal is the alisa name given for sal*2

it shows the output as
double_sal
-----------
16000
32000
45678
Table Aliases

eg:
select t.first_name,t.last_name,t.amount
    from transaction t;
here t is the alias name for  table transaction.
note that the fileld names are specified using alias name
 

Using DEFAULT

You can use the keyword DEFAULT instead of the assignment operator to initialize variables. For example, the declaration

id  CHAR := '0000';



can be rewritten as follows:

id CHAR DEFAULT '0000';

Use DEFAULT for variables that have a typical value. Use the assignment operator for variables (such as counters and accumulators) that have no typical value. A couple of examples follow:

basic_salary INTEGER DEFAULT 5000;

employee_count INTEGER := 0;

You can also use DEFAULT to initialize subprogram parameters, cursor parameters, and fields in a user-defined record.

Declarations in Oracle PL/SQL

Your program stores values in variables and constants. As the program executes, the values of variables can change, but the values of constants cannot.

You can declare variables and constants in the declarative part of any PL/SQL block, subprogram, or package. Declarations allocate storage space for a value, specify its datatype, and name the storage location so that you can reference it.

A couple of examples follow:

crea_date DATE;

word_count SMALLINT := 0;



The first declaration names a variable of type DATE. The second declaration names a variable of type SMALLINT and uses the assignment operator to assign an initial value of zero to the variable.

The next examples show that the expression following the assignment operator can be arbitrarily complex and can refer to previously initialized variables:

pi REAL := 3.14159;

radius REAL := 1;

area REAL := pi * radius**2;

By default, variables are initialized to NULL. So, these declarations are equivalent:

birthday DATE;

birthday DATE := NULL;

In the declaration of a constant, the keyword CONSTANT must precede the type specifier, as the following example shows:

credit_limit CONSTANT REAL := 5000.00;

This declaration names a constant of type REAL and assigns an initial (also final) value of 5000 to the constant. A constant must be initialized in its declaration. Otherwise, you get a compilation error when the declaration is elaborated.

Restrictions on Declarations


PL/SQL does not allow forward references. You must declare a variable or constant before referencing it in other statements, including other declarative statements. For example, the following declaration of maxi is not allowed:

maxi INTEGER := 2 * mini; -- not allowed

mini INTEGER := 15;



However, PL/SQL does allow the forward declaration of subprograms. For more information, see "Declaring PL/SQL Subprograms".

Some languages allow you to declare a list of variables that have the same datatype. PL/SQL does not allow this. For example, the following declaration is not allowed:

i, j, k SMALLINT; -- not allowed



You must declare each variable separately:

i SMALLINT;

j SMALLINT;

k SMALLINT;


Using default           Using aliases                                                

Literals in Oracle PL/SQL

A literal is an explicit numeric, character, string, or Boolean value not represented by an identifier.

Numeric Literals

Two kinds of numeric literals can be used in arithmetic expressions:
1 Integers
2.Reals.
An integer literal is an  whole number without a decimal point.it may or may not have a sign Some examples follow:

03 60 -236 0 +32767

A real literal is an whole or fractional number with a decimal point(may or may not be signed). Several examples follow:

6.6667 0.0 -12.0 3.14159 +8300.00 .5 25.



PL/SQL considers numbers such as 12.0 and 25. to be reals even though they have integral values.

Numeric literals cannot contain dollar signs or commas, but can be written using scientific notation. Simply suffix the number with an E (or e) followed by an optionally signed integer. A few examples follow:

2E5 1.0E-7 3.14159e0 -1E38 -9.5e-3



E stands for "times ten to the power of." As the next example shows, the number after E is the power of ten by which the number before E must be multiplied (the double asterisk (**) is the exponentiation operator):

5E3 = 5 * 10**3 = 5 * 1000 = 5000



The number after E also corresponds to the number of places the decimal point shifts. In the last example, the implicit decimal point shifted three places to the right. In this example, it shifts three places to the left:

5E-3 = 5 * 10**-3 = 5 * 0.001 = 0.005



As the following example shows, if the value of a numeric literal falls outside the range 1E-130 .. 10E125, you get a compilation error:

DECLARE

n NUMBER;

BEGIN

n := 10E127; -- causes a 'numeric overflow or underflow' error

Character Literals

A character literal is an individual character enclosed by single quotes (apostrophes). Character literals include all the printable characters in the PL/SQL character set: letters, numerals, spaces, and special symbols. Some examples follow:

'Z' '%' '7' ' ' 'z' '('



PL/SQL is case sensitive within character literals. For example, PL/SQL considers the literals 'Z' and 'z' to be different. Also, the character literals '0'..'9' are not equivalent to integer literals but can be used in arithmetic expressions because they are implicitly convertible to integers.

String Literals

A character value can be represented by an identifier or explicitly written as a string literal, which is a sequence of zero or more characters enclosed by single quotes. Several examples follow:

'Hello, world!'

'XYZ Corporation'

'10-NOV-91'

'He said "Life is like licking honey from a thorn."'

'$1,000,000'



All string literals except the null string ('') have datatype CHAR.

Given that apostrophes (single quotes) delimit string literals, how do you represent an apostrophe within a string? As the next example shows, you write two single quotes, which is not the same as writing a double quote:

'Don''t leave without saving your work.'



PL/SQL is case sensitive within string literals. For example, PL/SQL considers the following literals to be different:

'baker'

'Baker'

Boolean Literals

Boolean literals are the predefined values TRUE, FALSE, and NULL (which stands for a missing, unknown, or inapplicable value). Remember, Boolean literals are values, not strings. For example, TRUE is no less a value than the number 25.

Datetime Literals

Datetime literals have various formats depending on the datatype. For example:

DECLARE

d1 DATE := DATE '1998-12-25';

t1 TIMESTAMP := TIMESTAMP '1997-10-22 13:01:01';

t2 TIMESTAMP WITH TIME ZONE := TIMESTAMP '1997-01-31 09:26:56.66

+02:00';

-- Three years and two months

-- (For greater precision, we would use the day-to-second interval)

i1 INTERVAL YEAR TO MONTH := INTERVAL '3-2' YEAR TO MONTH;

-- Five days, four hours, three minutes, two and 1/100 seconds

i2 INTERVAL DAY TO SECOND := INTERVAL '5 04:03:02.01' DAY TO SECOND;

...
You can also specify whether a given interval value is YEAR TO MONTH or DAY TO SECOND. For example, current_timestamp - current_timestamp produces a value of type INTERVAL DAY TO SECOND by default. You can specify the type of the interval using the formats:

• (interval_expression) DAY TO SECOND

• (interval_expression) YEAR TO MONTH

Identifiers in Oracle PL/SQL

We can use identifiers to name PL/SQL program items and units, which include constants, variables, exceptions, cursors, cursor variables, subprograms, and packages. Some examples of identifiers are given below:
a
x2
new#
debit_limit
firstName
ora$number

An identifier consists of a letter optionally followed by more letters, numerals, dollar signs, underscores, and number signs. Other characters such as hyphens, slashes, and spaces are not allowed, as the following examples show:
me&you -- not allowed because of ampersand
credit-limit -- not allowed because of hyphen
in/out -- not allowed because of slash
user name -- not allowed because of space


You can use upper, lower, or mixed case to write identifiers. PL/SQL is not case sensitive except within string and character literals. So, if the only difference between identifiers is the case of corresponding letters, PL/SQL considers the identifiers to be the same, as the following example shows:
username -- same as USERNAME
SSN -- same as ssn

The size of an identifier cannot exceed 30 characters. But, every character, including dollar signs, underscores, and number signs, is significant. For example, PL/SQL considers the following identifiers to be different:
newpassword
new_password

Identifiers should be descriptive. So, avoid obscure names and use meaningful names.

Quoted Identifiers

For flexibility, PL/SQL lets you enclose identifiers within double quotes. Quoted identifiers are seldom needed, but occasionally they can be useful. They can contain any sequence of printable characters including spaces but excluding double quotes. Thus, the following identifiers are valid:
"X+Y"
"last name"
"on/off switch"
"employee(s)"
"*** header info ***"

The maximum size of a quoted identifier is 30 characters not counting the double quotes. Though allowed, using PL/SQL reserved words as quoted identifiers is a poor programming practice.
Some PL/SQL reserved words are not reserved by SQL. For example, you can use the PL/SQL reserved word TYPE in a CREATE TABLE statement to name a database column. But, if a SQL statement in your program refers to that column, you get a compilation error, as the following example shows:
SELECT acct, type, bal INTO ... -- causes compilation error

To prevent the error, enclose the uppercase column name in double quotes, as follows:
SELECT acct, "TYPE", bal INTO ...

The column name cannot appear in lower or mixed case (unless it was defined that way in the CREATE TABLE statement). For example, the following statement is invalid:
SELECT acct, "type", bal INTO ... -- causes compilation error

Alternatively, you can create a view that renames the troublesome column, then use the view instead of the base table in SQL statements.

Predefined Identifiers

Identifiers globally declared in package STANDARD, such as the exception INVALID_NUMBER, can be redeclared. However, redeclaring predefined identifiers is error prone because your local declaration overrides the global declaration.

Delimiters in Oracle PL/SQL

A delimiter is a simple or compound symbol that has a special meaning to PL/SQL
Simple symbols consist of one character

Symbol	Meaning
+	addition operator
%	attribute indicator
'	character string delimiter
.	component selector
/	division operator
(	expression or list delimiter
)	expression or list delimiter
:	host variable indicator
,	item separator
*	multiplication operator
"	quoted identifier delimiter
=	relational operator
<	relational operator
>	relational operator
@	remote access indicator
;	statement terminator
-	subtraction/negation operator

Compound symbols consist of two characters. the following list shows
Compound symbols delimiters

:=	assignment operator
=>	association operator
||	concatenation operator
**	exponentiation operator
'<<'	label delimiter(Begin)
'>>'	label delimiter(End)
/*	multi-line comment delimiter (Begin)
*/	multi-line comment delimiter (end)
. .	range operator
<>	relational operator
!=	relational operator
~=	relational operator
^=	relational operator
<=	relational operator
>=	relational operator
--	single-line comment indicator

Comments in PL/SQL

The PL/SQL compiler ignores comments, but you should not. Adding comments to your program promotes readability and aids understanding. Generally, you use comments to describe the purpose and use of each code segment. PL/SQL supports two comment styles: single-line and multi-line.
In Oracle, comments may be introduced in two ways:


With /*...*/, as in C.

With a line that begins with two dashes --.

Thus:

-- This is a comment

SELECT * /* and so is this */

FROM R;

Comments can be of two types


Single-Line Comments

Single-line comments begin with a double hyphen (--) anywhere on a line and extend to the end of the line. A few examples follow:

-- begin Process
SELECT name INTO employee FROM emp -- get first name
WHERE empno = emp_id;

Notice that comments can appear within a statement at the end of a line.

While testing or debugging a program, you might want to disable a line of code. The following example shows how you can "comment-out" the line:

-- DELETE FROM emp WHERE comm IS NULL;

Multi-line Comments

Multi-line comments begin with a slash-asterisk (/*), end with an asterisk-slash (*/), and can span multiple lines. Some examples follow:



BEGIN

...

/* Compute a 15% bonus for top-rated employees. */

IF rating > 90 THEN

bonus := salary * 0.15 /* bonus is based on salary */

ELSE

bonus := 0;

END IF;

...

/* The following line computes the area of a

circle using pi, which is the ratio between

the circumference and diameter. */

area := pi * radius**2;

END;

Restrictions on Comments

You cannot nest comments. Also, you cannot use single-line comments in a PL/SQL block that will be processed dynamically by an Oracle Precompiler program because end-of-line characters are ignored. As a result, single-line comments extend to the end of the block, not just to the end of a line. So, use multi-line comments instead.

Installing Oracle 10g on Windows

1.For this installation you need either the DVDs or a downloaded version of the DVDs


Insert Oracle CD , the autorun window opens automatically. If you are installing from network or hard disk, click setup.exe in the installation folder.

2.The Oracle Universal Installer (OUI) will run

3.Then display the Select Installation Method Window.we can perform a basic installation with a starter database.

Choose Basic Installation:

Select this option to quickly install Oracle Database 10g. This method requires minimal user input. It installs the software and optionally creates a general-purpose database based on the information you provide.

For basic installation, you specify the following:

Oracle Home Location — Enter the directory in which to install the Oracle Database 10g software. You must specify a new Oracle home directory for each new installation of Oracle Database 10g. Use the default value, which is :

c:\oracle\product\10.2.0\db_1

Installation Type — Select Enterprise Edition :

If you have limited space, select standard edition. Personal edition installs the same software as the Enterprise Edition, but supports only a single-user development and deployment environment.

Create Starter Database — Check this box to create a database during installation. Oracle recommends that you create a starter database for first Create Starter Database — time installations. Choose a Global Database Name, like cs157b, or just use the default value.

Type a password. Don’t lose this password, since you will need it to connect to the database server.

Click next

4.The installer now verifies that the system meets all the minimum requirements for installing and configuring the chosen product. Please correct any reported errors (warnings are OK) before continuing. When the check successfully completes (with or without warnings), click Next.

5.A summary screen appears showing information such as your global settings, space requirements and the new products to be installed. Click Install to start the installation..

6.The progress window appears.

7.At the end of the installation phase, the Configuration Assistants window appears. This window shows the configuration assistants that are started automatically.

If you are creating a new database, then the DCA(Database Configuration Assistant) starts automatically in a separate window.

8.Your database is now being created.The progress bar shows the progress of your database creation

9.When the database has been created, you can unlock the users you want to use. Click Password Management.

10.Unlock SH, OE and HR users by clicking on the check mark in the Lock Account? column. Enter the same name as the user in the New Password and Confirm Password fields. For example, to unlock SH user, enter SH in the New Password and Confirm Password fields. Then, click OK.

11.Click OK again.

 
 
 
 
 
 
 
 
 
 
 
 
 
 
12.click exit button
 

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
13.Click Yes to confirm exit.