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
Cursor Variables As Parameters You can declare the cursor variables as the formal parameters of the functions and procedures. In the illustration below, you define the REF CUR

Projection and Existential Quantification - SQL Intuitively it might seem that projection in SQL is simply a matter of specifying the required columns in the SELECT clause, a

Count Operator in SQL Example: Counting the students who have scored more than 50 in some exam (SELECT COUNT (*) FROM (SELECT DISTINCT StudentId FROM EXAM_MARK WHE

ROWID The ROWID returns the rowid (binary address) of a row in the database table. You can use the variables of the type UROWID to store rowids in a readable format. In the il

Recursive Subprograms The recursive subprogram is the one that calls itself. Think of a recursive call as a call to a few other subprograms that does the similar task as your

Write a stored procedure that accepts the post code in which the customer resides as the input parameter. The procedure should then use an explicit cursor to display comprehensive

Using a join on 2 tables, select all columns and 10 rows from the 2 tables without the use of a Cartesian product. Query: SELECT * FROM EMPLOYEE1 E JOIN STAFF S ON E.EMP_

Obtaining a natural join by specifying the common columns Synatax: SELECT * FROM IS_CALLED JOIN IS_ENROLLED_ON USING ( StudentId ) However, a named columns join doe

Logical Connectives - SQL SQL's extended truth tables in which the symbol, for unknown, appears along with the usual T and F. Negation (NOT, ¬) Conjunction (

Operators on Tables and Rows Row Extraction TUPLE FROM r, SQL has row subqueries. These are just like scalar subqueries except that they may specify more than one column.