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
Declaring Cursor Variables Once a REF CURSOR type is define by you, and then you can declare the cursor variables of that type in any PL/SQL block or subprogram. In the exampl

MILLER-UREY' S EXPERIMENTAL PROCEDURES - They recreated the probable conditions on the primitive earth in the laboratory. An atmosphere containing hydrogen, ammonia, me

Using Operator REF: You can retrieve refs by using the operator REF that, like VALUE, takes as its argument a correlation variable. In the illustration below, you retrieve one

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

I need a query for PL/SQL, selecting names with cursor, goes down the list, assigns usernames (initials001) based on initials in the name. If two names have same initials the user

Using FIRST and LAST FIRST and LAST return the first and last (minimum and maximum) index numbers in a collection. When the collection is empty, the FIRST and LAST return NULL

Project Description: I have two types of data sources. One that is a list in SharePoint and another that is an access desktop database. The access desktop database is fairly com

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

Committing and Rolling Back The COMMIT and ROLLBACK end the active autonomous transaction but do not exit the autonomous routine. As the figure shows, if one transaction ends,

Conditionals - SQL At first sight SQL does not appear to have a single operator for expressing logical implication. In this respect it would be in common with most programming