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

Cursor attributes in pl sql, Cursor Attributes   The Cursors and curso... Cursor Attributes   The Cursors and cursor variables have 4 attributes which give you helpful information about the execution of a data manipulation statement. Syntax:

Implicit rollbacks, Implicit Rollbacks Before execute the INSERT, UPDA... Implicit Rollbacks Before execute the INSERT, UPDATE, or DELETE statement, the Oracle marks an implicit savepoint . When the statement fails, the Oracle rolls back to the save

Parameter and keyword description - open-for statement, Parameter and Keywo... Parameter and Keyword Description: cursor_variable_name: This identifies a cursor variable or the parameter formerly declared within the present scope. host_cursor_va

Raise_application_error, Raise_application_error -  procedure of package D... Raise_application_error -  procedure of package DBMS_STANDARD , allows to issue an user_defined error messages by stored sub-program or database trigger.

I need sql to infopath data connection, I need SQL to infopath data connect... I need SQL to infopath data connection Project Description: Want data retrieval connection from SQL to SharePoint infopath Skills required are Sharepoint, SQL

Using operator ref - manipulating objects pl sql, Using Operator REF: ... Using Operator REF: You can retrieve refs by using the operator REF that, like VALUE, takes as its argument a correlation variable. In the illustration below, you retrieve one

Use the returning clause -improve performance of application, Use the RETUR... Use the RETURNING Clause Frequently, the application requires information about the row affected by a SQL operation, for illustration, to produce a report or take a subsequent

%rowtype - cursors, %ROWTYPE: This attribute gives a record type which ... %ROWTYPE: This attribute gives a record type which represents a row in the database table or a row fetched from a formerly declared cursor. The Fields in the record and corresp

Parameter modes, Parameter Modes   To define the behavior of formal pa... Parameter Modes   To define the behavior of formal parameters you use the parameter modes. The 3 parameter modes, IN, OUT, & IN OUT, can be used with any subprogram. Though, a

Sql, If two relations R and S are joined, then the non matching tuples of b... If two relations R and S are joined, then the non matching tuples of both R and S are ignored in __________________.