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
Use Bulk Binds If SQL statements execute inside a loop using the collection elements as bind variables, context switching between the PL/SQL & SQL engines can slow down the ex

Anatomy of a Table: Figure shows the terminology used in SQL to refer to parts of the structure of a table. As you can see, SQL has no official terms for its counterpa

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

Example of Null operator - NiNo Rule If we wanted to make HIGHER_OF adhere to "NULL in, NULL out"-let's call it the NiNo rule-we would have to write something like what is sho

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

i NEED THE QURIES TO SOME OF THE PROBLEMS USING plsql

Important Distinctions The list of important distinctions are given below: Value versus variable Syntax versus semantics Variable versus variable reference

Inner Join We have learned how to retrieve data from one table by using SELECT statement. But, as we have learned, normalized relational databases mean the data is spread betw

SQL outer join SELECT * FROM IS_CALLED NATURAL LEFT JOIN IS_ENROLLED_ON Note that adding LEFT to an invocation of CROSS JOIN has no effect unless the right-hand operand

Using Pragma RESTRICT_REFERENCES: The function called from the SQL statements should obey certain rules meant to control the side effects. To check for violation of the rules,