Cursor attributes in dynamic sql - pl sql, PL-SQL Programming

Using Cursor Attributes:

Every cursor has 4 attributes: %NOTFOUND, %FOUND, %ISOPEN, and %ROWCOUNT.

If appended to the cursor name, they return the helpful information about the execution of the static and dynamic SQL statements.

To process the SQL data manipulation statements, the Oracle opens an implicit cursor named SQL. Its attributes returns information about the most newly executed INSERT, DELETE, UPDATE, or single-row SELECT statement. For illustration, the stand-alone function below uses %ROWCOUNT to return the number of rows deleted from the database table:

CREATE FUNCTION rows_deleted (

table_name IN VARCHAR2,

condition IN VARCHAR2) RETURN INTEGER AS

BEGIN

EXECUTE IMMEDIATE

'DELETE FROM ' || table_name || ' WHERE ' || condition;

RETURN SQL%ROWCOUNT; -- return number of rows deleted

END;

Similarly, if appended to a cursor variable name, the cursor attributes returns information about the execution of the multi-row query. 

Posted Date: 10/6/2012 8:31:22 AM | Location : United States







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

Write discussion on Cursor attributes in dynamic sql - pl sql
Your posts are moderated
Related Questions
Magento change address format depending on store Project Description: What I need is that depending on the store in which the customer bought the address should change the fo

V ariables and Constants in PL/SQL The PL/SQL permits you to declare constants and variables, and then use them in SQL and procedural statements anywhere in the expression. Th

Constants and Variables:   You can declare the constants and variables in the declarative section of any PL/SQL subprogram, block, or package. The Declarations allot the stor

Read-Only Operator (+) - SQL The term read-only operator to the mathematical term function. Here I just need to add that the SQL standard reserves the term function for read-

Manipulating Local Collections Within PL/SQL, to manipulate the local collection, by using the  TABLE and CAST operators . The operands of CAST are a collection declared locally

Tautology - Equivalences Rules: If there Tautologies are not all the time as much easy to note as the one above so than we can use these truth tables to be definite that a sta

Project Description: I want a database for large governmental and private data sets on one country that will be easily extended to other countries in the future. Also, the datab

Effects of NULL in Table Literal When a VALUES expression appears as the source value for an SQL INSERT statement, the key word NULL can appear as a field value, such that for

Parameter and Keyword Description: cursor_name: This identifies an explicit cursor formerly declared within the present scope. cursor_variable_name: These identif

Example of ADD CONSTRAINT in SQL Example: Alternative formulation for MAX_ENROLMENTS ALTER TABLE IS_ENROLLED_ON ADD CONSTRAINT MAX_ENROLMENTS CHECK ((SELECT COUNT (*)