Cursor Attributes

 The Cursors and cursor variables have 4 attributes which give you helpful information about the execution of a data manipulation statement.

Syntax:

Keyword and Parameter Description:

cursor_name:

These identify an explicit cursor formerly declared within the present scope.

cursor_variable_name:

These identify the PL/SQL cursor variable (or parameter) formerly declared within the present scope.

host_cursor_variable_name:

These identify a cursor variable declared in the PL/SQL host atmosphere and passed to the PL/SQL as a bind variable. The host cursor variable datatype is well-suited with the return type of any PL/SQL cursor variable. The host variables should be prefixed with a colon.

SQL:

These are the name of the implicit SQL cursor.

%FOUND:

This is a cursor attribute that can be appended to the name of the cursor or cursor variable. Previous to the first fetch from an open cursor, the cursor_name%FOUND results NULL. Afterward, it results TRUE if the final fetch returned a row, or FALSE if the final fetch failed to return a row. Until the SQL statement is executed, the SQL%FOUND results NULL. Afterward, it results TRUE if the statement affects any rows, or FALSE if it affects no rows.

%ISOPEN:

This is a cursor attribute that can be appended to the name of the cursor or cursor variable. If a cursor is open, the cursor_name%ISOPEN results TRUE; Or else, it results FALSE. The Oracle automatically closes the implicit SQL cursor after executing its related SQL statement, Therefore the SQL%ISOPEN always results FALSE.

%NOTFOUND:

This is a cursor attribute that can be appended to the name of the cursor or cursor variable. Previous to the first fetch from an open cursor, the cursor_name%NOTFOUND results NULL. Afterward, it results FALSE if the last fetch returned a row, or TRUE if the last fetch unsuccessful to return a row.

Until a SQL statement is executed, the SQL%NOTFOUND results NULL. Afterward, it results FALSE if the statement affects any rows, or TRUE if it affects no rows.

%ROWCOUNT:

This is a cursor attribute that can be appended to the name of the cursor or cursor variable. If a cursor is opened, the %ROWCOUNT is zeroed. Before the first fetch, the cursor_name%ROWCOUNT yields to 0. Afterward, it results the number of rows fetch so far. The number is incremented if the newest fetch returned a row. Until the SQL statement is executed, the SQL%ROWCOUNT results NULL. Afterward, it results the number of rows affected by the statement. The SQL%ROWCOUNT results 0 if the statement affect no rows.

Posted Date: 10/6/2012 9:12:17 AM | Location : United States

Ask an Expert

Related Discussions:- Cursor attributes in pl sql, Assignment Help, Ask Question on Cursor attributes in pl sql, Get Answer, Expert's Help, Cursor attributes in pl sql Discussions

Write discussion on Cursor attributes in pl sql
Your posts are moderated

Write your message here..

Related Questions

Advantages of packages, Advantages of Packages The benefits of the Pack... Advantages of Packages The benefits of the Packages are as shown below: Modularity The Packages encapsulate logically associated items, types, and subprograms in the

Dynamic sql - pl sql, Dynamic SQL: The Most PL/SQL programs do a predi... Dynamic SQL: The Most PL/SQL programs do a predictable, specific job. For illustration, a stored procedure may accept an employee number and salary increase, and then update t

Union all - sql, UNION ALL - SQL Further varieties of UNION arise when... UNION ALL - SQL Further varieties of UNION arise when we replace the key word DISTINCT by ALL in any of the foregoing examples, as in Example. ALL specifies that if row r appe

Error handling in pl/sql, Error Handling The PL/SQL makes it easy to de... Error Handling The PL/SQL makes it easy to detect and process the predefined and user-defined error conditions known as exceptions. Whenever an error occurs, an exception is ra

Rowid - sql pseudocolumns, ROWID The ROWID returns the rowid (binary a... ROWID The ROWID returns the rowid (binary address) of a row in the database table. You can use the variables of the type UROWID to store rowids in a readable format. In the il

Understanding varrays, Understanding Varrays The Items of type VARRAY ar... Understanding Varrays The Items of type VARRAY are termed as the varrays. They permit you to relate a single identifier with the whole collection. This relationship lets you man

Introduction to SQl and DQL, which operation is used if we are interested i... which operation is used if we are interested in only certain columns of a table?

Using delete - collection method, Using DELETE This process has three ... Using DELETE This process has three forms. The DELETE removes all elements from the collection. DELETE(n) removes the nth element from the nested table. When n is null, then D

Select query, Query: SELECT * FROM EMPLOYEE1; Select 5 columns and ... Query: SELECT * FROM EMPLOYEE1; Select 5 columns and all rows from one table Query: SELECT C_ID, COMPANY, BUILDING, DEPARTMENT, BRANCH FROM CONTRACT;

Advantages of wrapping, Advantages of Wrapping   The PL/SQL Wrapper co... Advantages of Wrapping   The PL/SQL Wrapper convert the PL/SQL source code into a transitional form of the object code. By hiding the application internals, the Wrapper secure