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
Opening a Cursor Variable The OPEN-FOR statement relates a cursor variable with the multi-row query, executes the query, and then identifies the result set. The syntax for ope

Managing Cursors The PL/SQL uses 2 types of cursors: implicit and explicit. The PL/SQL declares a cursor implicitly for all the SQL data manipulation statements, including th

Write a query to find academics that are authors and that have only ever coauthored papers with authors from institutes in the same state as their own. List their academic number,

Processing Transactions This part describes how to do the transaction processing. You learn the fundamental techniques that safeguard the consistency of your database, involvin

Fetching Across Commits The FOR UPDATE clauses acquire exclusive all row locks. All rows are locked when you open the cursor, and when you commit your transaction they are unl

Left and Right Joins LEFT OUTER JOIN can be used when you want to retrieve the data from the main table (table1) even if there is no match in other tables (table_2, table_3...

Example of Using Aggregation on Nested Tables Example: How many students sat each exam WITH C_ER AS (SELECT CourseId, CAST (TABLE (SELECT DISTINCT StudentId, Mark FROM EXAM

Subprograms The PL/SQL has two types of subprograms known as the procedures and functions that can take parameters and be invoked. As the following example represents, a subp

Object Type: The object type is a user-defined composite datatype which encapsulates a data structure along with the functions and procedures required to manipulate the data

I need to write one function and one procedure to query a Oracle 10.1 DB using PL SQL. I have the schema and exact queries...along with work Ive started and a template to put the a