Monday, May 17, 2010

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.

Friday, May 14, 2010

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.

Tuesday, May 11, 2010

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.

Thursday, May 6, 2010

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;

Thursday, April 29, 2010

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.