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

Using default-declarations in sql, Using DEFAULT You can use the keyword... Using DEFAULT You can use the keyword DEFAULT rather than that of the assignment operator to initialize the variables. For e.g. the declaration blood_type CHAR := ’O’; it can b

Scope rules - user-defined exceptions, Scope Rules You cannot declare ... Scope Rules You cannot declare an exception twice in the similar block. Though, you can, declare the similar exception in 2 different blocks. The Exceptions declared in a bloc

Row operators - sql operators, Row Operators The Row operators return ... Row Operators The Row operators return or reference the particular rows. ALL retains the duplicate rows in the result of a query or in an aggregate expression. The DISTINCT el

PROCEDURES AND FUNCTIONS QURIES, i NEED THE QURIES TO SOME OF THE PROBLEMS ... i NEED THE QURIES TO SOME OF THE PROBLEMS USING plsql

Components of an object type - attributes in pl/sql, Attributes: Just ... Attributes: Just similar to variable, an attribute is declared with a name and datatype. The name should be exclusive within the object type. The datatype can be any Oracle ty

Origin of earth - big bang hypothesis, ORIGIN OF EARTH - BIG-BAN G HY... ORIGIN OF EARTH - BIG-BAN G HYPOTHESIS - Origin of life is linked to origin of earth. Cosmos, the Universe originated 10-20 billion years ago by Big Bang (thermonu

Merge and truncate in sql, MERGE and TRUNCATE in SQL SQL has two more ... MERGE and TRUNCATE in SQL SQL has two more table update operators, MERGE and TRUNCATE. MERGE, like INSERT, takes a source table s and uses it to update a target table t. Brief

User-defined exceptions, User-Defined Exceptions The PL/SQL defines the... User-Defined Exceptions The PL/SQL defines the exceptions of your own. Dissimilar to the predefined exceptions, the user-defined exceptions should be declared and should be rai

Rewrite rules - tautology, Equivalences & Rewrite Rules: If notice tha... Equivalences & Rewrite Rules: If notice that as well as allowing us to prove trivial theorems, and tautologies enable us to establish that certain sentences are saying the sam

Forward declarations - subprograms, Forward Declarations The PL/SQL ne... Forward Declarations The PL/SQL needs that you declare an identifier before using it. And hence, you should declare a subprogram before calling it. For illustration, the decla