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
query to Find the account numbers of all customers whose balance is more than 10,000 $

%NOTFOUND The %NOTFOUND is the logical opposite of the %FOUND. The %NOTFOUND yields TRUE when an INSERT, UPDATE, or DELETE statement affected no rows, or the SELECT INTO state

Other monadic - SQL In 2VL there are just 4 (2 2 ) monadic operators, of which negation is really the only "useful" one. When a third truth value is introduced we have 27 (3 3

What are 3 good practices of modeling and/or implementing data warehouses?

Datatypes Every constant and variable has a datatype that specifies the storage format, constraints, and the valid range of values. The PL/SQL gives a variety of predefined dat

Find the account numbers of all customers whose balance is more than 10,000 $

Nested Tables versus Index-by Tables The Index-by tables and nested tables are just similar. For e.g.  They have similar structure and their individual elements are accessed in

ALTER TABLE bb_basketitem ADD CONSTRAINT bitems_qty_ck CHECK (quantity BEGIN INSERT INTO bb_basketitem VALUES (88,8,10.8,21,16,2,3); END; Brewbean’s wants to add a check

Architecture The PL/SQL run-time system and compilation is a technology, not an independent product. Consider this technology as an engine that compiles and executes the PL/SQL

I have a Pascal Source file that needs to be compiled into a Service. In addition, there are various functions (Pascal Procedures I guess) that need to be created to Read and Write