Q6

WHAT ARE THE TYPES OF CURSORS?

Explicit Cursors
When you need precise control over query processing, you can explicitly declare a cursor in the declarative part of any PL/SQL block, subprogram, or package.
You use three commands to control a cursor: OPEN, FETCH, and CLOSE. First, you initialize the cursor with the OPEN statement, which identifies the result set. Then, you can execute FETCH repeatedly until all rows have been retrieved, or you can use the BULK COLLECT clause to fetch all rows at once. When the last row has been processed, you release the cursor with the CLOSE statement.
This technique requires more code than other techniques such as the implicit cursor FOR loop. Its advantage is flexibility. You can:
• Process several queries in parallel by declaring and opening multiple cursors.
• Process multiple rows in a single loop iteration, skip rows, or split the processing into more than one loop
Implicit Cursors
Implicit cursors are managed automatically by PL/SQL so you are not required to write any code to handle these cursors. However, you can track information about the execution of an implicit cursor through its cursor attributes.

WHAT ARE CURSOR ATTRIBUTES?
Every explicit cursor and cursor variable has four attributes: %FOUND, %ISOPEN %NOTFOUND, and %ROWCOUNT. When appended to the cursor or cursor variable, these attributes return useful information about the execution of a data manipulation statement. You can use cursor attributes in procedural statements but not in SQL statements.
Overview of Explicit Cursor Attributes
Explicit cursor attributes return information about the execution of a multi-row query. When an explicit cursor or a cursor variable is opened, the rows that satisfy the associated query are identified and form the result set. Rows are fetched from the result set.
%FOUND Attribute: Has a Row Been Fetched?
After a cursor or cursor variable is opened but before the first fetch, %FOUND yields NULL. Thereafter, it yields TRUE if the last fetch returned a row, or FALSE if the last fetch failed to return a row. In the following example, you use %FOUND to select an action:
LOOP
FETCH c1 INTO my_ename, my_sal, my_hiredate;
IF c1%FOUND THEN -- fetch succeeded
...
ELSE -- fetch failed, so exit loop
EXIT;
END IF;
END LOOP;

If a cursor or cursor variable is not open, referencing it with %FOUND raises the predefined exception INVALID_CURSOR.
%ISOPEN Attribute: Is the Cursor Open?
%ISOPEN yields TRUE if its cursor or cursor variable is open; otherwise, %ISOPEN yields FALSE. In the following example, you use %ISOPEN to select an action:
IF c1%ISOPEN THEN -- cursor is open
...
ELSE -- cursor is closed, so open it
OPEN c1;
END IF;

%NOTFOUND Attribute: Has a Fetch Failed?
%NOTFOUND is the logical opposite of %FOUND. %NOTFOUND yields FALSE if the last fetch returned a row, or TRUE if the last fetch failed to return a row. In the following example, you use %NOTFOUND to exit a loop when FETCH fails to return a row:
LOOP
FETCH c1 INTO my_ename, my_sal, my_hiredate;
EXIT WHEN c1%NOTFOUND;
...
END LOOP;

Before the first fetch, %NOTFOUND evaluates to NULL. So, if FETCH never executes successfully, the loop is never exited. That is because the EXIT WHEN statement executes only if its WHEN condition is true. To be safe, you might want to use the following EXIT statement instead:
EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL;

If a cursor or cursor variable is not open, referencing it with %NOTFOUND raises INVALID_CURSOR.
%ROWCOUNT Attribute: How Many Rows Fetched So Far?
When its cursor or cursor variable is opened, %ROWCOUNT is zeroed. Before the first fetch, %ROWCOUNT yields 0. Thereafter, it yields the number of rows fetched so far. The number is incremented if the last fetch returned a row. In the next example, you use %ROWCOUNT to take action if more than ten rows have been fetched:
LOOP
FETCH c1 INTO my_ename, my_deptno;
IF c1%ROWCOUNT > 10 THEN
...
END IF;
...
END LOOP;
If a cursor or cursor variable is not open, referencing it with %ROWCOUNT raises INVALID_CURSOR.
Implicit
Attributes of Implicit Cursors
Implicit cursor attributes return information about the execution of DML and DDL statements, such INSERT, UPDATE, DELETE, SELECT INTO, COMMIT, or ROLLBACK statements. The cursor attributes are %FOUND, %ISOPEN %NOTFOUND, and %ROWCOUNT. The values of the cursor attributes always refer to the most recently executed SQL statement. Before Oracle opens the SQL cursor, the implicit cursor attributes yield NULL.
The SQL cursor has another attribute, %BULK_ROWCOUNT, designed for use with the FORALL statement. For more information, see "Counting Rows Affected by FORALL with the %BULK_ROWCOUNT Attribute".
%FOUND Attribute: Has a DML Statement Changed Rows?
Until a SQL data manipulation statement is executed, %FOUND yields NULL. Thereafter, %FOUND yields TRUE if an INSERT, UPDATE, or DELETE statement affected one or more rows, or a SELECT INTO statement returned one or more rows. Otherwise, %FOUND yields FALSE. In Example 6-7, you use %FOUND to insert a row if a delete succeeds.
Example 6-7 Using SQL%FOUND
CREATE TABLE dept_temp AS SELECT * FROM departments;
DECLARE
dept_no NUMBER(4) := 270;
BEGIN
DELETE FROM dept_temp WHERE department_id = dept_no;
IF SQL%FOUND THEN -- delete succeeded
INSERT INTO dept_temp VALUES (270, 'Personnel', 200, 1700);
END IF;
END;
/

%ISOPEN Attribute: Always FALSE for Implicit Cursors
Oracle closes the SQL cursor automatically after executing its associated SQL statement. As a result, %ISOPEN always yields FALSE.
%NOTFOUND Attribute: Has a DML Statement Failed to Change Rows?
%NOTFOUND is the logical opposite of %FOUND. %NOTFOUND yields TRUE if an INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement returned no rows. Otherwise, %NOTFOUND yields FALSE.
%ROWCOUNT Attribute: How Many Rows Affected So Far?
%ROWCOUNT yields the number of rows affected by an INSERT, UPDATE, or DELETE statement, or returned by a SELECT INTO statement. %ROWCOUNT yields 0 if an INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement returned no rows. In Example 6-8, %ROWCOUNT returns the number of rows that have been deleted.
Example 6-8 Using SQL%ROWCOUNT
CREATE TABLE employees_temp AS SELECT * FROM employees;
DECLARE
mgr_no NUMBER(6) := 122;
BEGIN
DELETE FROM employees_temp WHERE manager_id = mgr_no;
DBMS_OUTPUT.PUT_LINE('Number of employees deleted: ' || TO_CHAR(SQL%ROWCOUNT));
END;
/

If a SELECT INTO statement returns more than one row, PL/SQL raises the predefined exception TOO_MANY_ROWS and %ROWCOUNT yields 1, not the actual number of rows that satisfy the query.
The value of the SQL%ROWCOUNT attribute refers to the most recently executed SQL statement from PL/SQL. To save an attribute value for later use, assign it to a local variable immediately.
The SQL%ROWCOUNT attribute is not related to the state of a transaction. When a rollback to a savepoint is performed, the value of SQL%ROWCOUNT is not restored to the old value before the savepoint was taken. Also, when an autonomous transaction is exited, SQL%ROWCOUNT is not restored to the original value in the parent transaction.
Guidelines for Using Attributes of Implicit Cursors
The following are considerations when using attributes of implicit cursors:
• The values of the cursor attributes always refer to the most recently executed SQL statement, wherever that statement is. It might be in a different scope (for example, in a sub-block). To save an attribute value for later use, assign it to a local variable immediately. Doing other operations, such as procedure calls, might change the value of the variable before you can test it.
• The %NOTFOUND attribute is not useful in combination with the SELECT INTO statement:
o If a SELECT INTO statement fails to return a row, PL/SQL raises the predefined exception NO_DATA_FOUND immediately, interrupting the flow of control before you can check %NOTFOUND.
o A SELECT INTO statement that calls a SQL aggregate function always returns a value or a null. After such a statement, the %NOTFOUND attribute is always FALSE, so checking it is unnecessary.
what is the difference between cursor and pl/sql table? FETCH ROW BY ROW IN CURSOR,MOVE ANY WHERE IN
PL/SQL TABLE


what is parameterized cursor?

A cursor can take parameters, which can appear in the associated query wherever constants can appear. The formal parameters of a cursor must be IN parameters; they supply values in the query, but do not return any values from the query. You cannot impose the constraint NOT NULL on a cursor parameter.
As the following example shows, you can initialize cursor parameters to default values. You can pass different numbers of actual parameters to a cursor, accepting or overriding the default values as you please. Also, you can add new formal parameters without having to change existing references to the cursor.
DECLARE
CURSOR c1 (low NUMBER DEFAULT 0, high NUMBER DEFAULT 99) IS
SELECT * FROM departments WHERE department_id > low
AND department_id < high;

Cursor parameters can be referenced only within the query specified in the cursor declaration. The parameter values are used by the associated query when the cursor is opened.
write the syntax of pl/sql table?

DECLARE
TYPE EmpTabTyp IS TABLE OF employees%ROWTYPE
INDEX BY PLS_INTEGER;
emp_tab EmpTabTyp;
BEGIN
/* Retrieve employee record. */
SELECT * INTO emp_tab(100) FROM employees WHERE employee_id = 100;
END;
/



what is the difference between pl/sql table and record group?

what is TKPROF ? for what purpose will u use it?

















WHAT IS BULK COLLECT ? WHERE WILL U USE?

Selecting Multiple Rows: BULK COLLECT Clause
If you need to bring a large quantity of data into local PL/SQL variables, rather than looping through a result set one row at a time, you can use the BULK COLLECT clause. When you query only certain columns, you can store all the results for each column in a separate collection variable. When you query all the columns of a table, you can store the entire result set in a collection of records, which makes it convenient to loop through the results and refer to different columns
This technique can be very fast, but also very memory-intensive. If you use it often, you might be able to improve your code by doing more of the work in SQL:
• If you only need to loop once through the result set, use a FOR loop as described in the following sections. This technique avoids the memory overhead of storing a copy of the result set.
• If you are looping through the result set to scan for certain values or filter the results into a smaller set, do this scanning or filtering in the original query instead. You can add more WHERE clauses in simple cases, or use set operators such as INTERSECT and MINUS if you are comparing two or more sets of results.
• If you are looping through the result set and running another query or a DML statement for each result row, you can probably find a more efficient technique. For queries, look at including subqueries or EXISTS or NOT EXISTS clauses in the original query. For DML statements, look at the FORALL statement, which is much faster than coding these statements inside a regular loop.
OverLoading

PL/SQL lets you overload subprogram names and type methods. You can use the same name for several different subprograms as long as their formal parameters differ in number, order, or datatype family. For an example of an overloaded procedure in a package,
Example 8-8 Overloading a Subprogram Name
DECLARE
TYPE DateTabTyp IS TABLE OF DATE INDEX BY PLS_INTEGER;
TYPE NumTabTyp IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
hiredate_tab DateTabTyp;
sal_tab NumTabTyp;
PROCEDURE initialize (tab OUT DateTabTyp, n INTEGER) IS
BEGIN
FOR i IN 1..n LOOP
tab(i) := SYSDATE;
END LOOP;
END initialize;
PROCEDURE initialize (tab OUT NumTabTyp, n INTEGER) IS
BEGIN
FOR i IN 1..n LOOP
tab(i) := 0.0;
END LOOP;
END initialize;
BEGIN
initialize(hiredate_tab, 50); -- calls first (DateTabTyp) version
initialize(sal_tab, 100); -- calls second (NumTabTyp) version
END;
/


1.1.1 Guidelines for Overloading with Numeric Types
You can overload two subprograms if their formal parameters differ only in numeric datatype. This technique might be useful in writing mathematical application programming interfaces (APIs), where several versions of a function could use the same name, each accepting a different numeric type. For example, a function accepting BINARY_FLOAT might be faster, while a function accepting BINARY_DOUBLE might provide more precision.
To avoid problems or unexpected results passing parameters to such overloaded subprograms:
• Make sure to test that the expected version of a subprogram is called for each set of expected parameters. For example, if you have overloaded functions that accept BINARY_FLOAT and BINARY_DOUBLE, which is called if you pass a VARCHAR2 literal such as '5.0'?
• Qualify numeric literals and use conversion functions to make clear what the intended parameter types are. For example, use literals such as 5.0f (for BINARY_FLOAT), 5.0d (for BINARY_DOUBLE), or conversion functions such as TO_BINARY_FLOAT(), TO_BINARY_DOUBLE(), and TO_NUMBER().
PL/SQL looks for matching numeric parameters starting with PLS_INTEGER or BINARY_INTEGER, then NUMBER, then BINARY_FLOAT, then BINARY_DOUBLE. The first overloaded subprogram that matches the supplied parameters is used. A VARCHAR2 value can match a NUMBER, BINARY_FLOAT, or BINARY_DOUBLE parameter.
For example, consider the SQRT function, which takes a single parameter. There are overloaded versions that accept a NUMBER, a BINARY_FLOAT, or a BINARY_DOUBLE parameter. If you pass a PLS_INTEGER parameter, the first matching overload (using the order given in the preceding paragraph) is the one with a NUMBER parameter, which is likely to be the slowest. To use one of the faster versions, use the TO_BINARY_FLOAT or TO_BINARY_DOUBLE functions to convert the parameter to the right datatype.
For another example, consider the ATAN2 function, which takes two parameters of the same type. If you pass two parameters of the same type, you can predict which overloaded version is used through the same rules as before. If you pass parameters of different types, for example one PLS_INTEGER and one BINARY_FLOAT, PL/SQL tries to find a match where both parameters use the higher type. In this case, that is the version of ATAN2 that takes two BINARY_FLOAT parameters; the PLS_INTEGER parameter is converted upwards.
The preference for converting upwards holds in more complicated situations. For example, you might have a complex function that takes two parameters of different types. One overloaded version might take a PLS_INTEGER and a BINARY_FLOAT parameter. Another overloaded version might take a NUMBER and a BINARY_DOUBLE parameter. What happens if you call this procedure name and pass two NUMBER parameters? PL/SQL looks upward first to find the overloaded version where the second parameter is BINARY_FLOAT. Because this parameter is a closer match than the BINARY_DOUBLE parameter in the other overload, PL/SQL then looks downward and converts the first NUMBER parameter to PLS_INTEGER.
1.1.2 Restrictions on Overloading
Only local or packaged subprograms, or type methods, can be overloaded. You cannot overload standalone subprograms.
You cannot overload two subprograms if their formal parameters differ only in name or parameter mode. For example, you cannot overload the following two procedures:
Example 8-9 Restrictions on Overloading PL/SQL Procedures
DECLARE
PROCEDURE balance (acct_no IN INTEGER) IS
BEGIN NULL; END;
PROCEDURE balance (acct_no OUT INTEGER) IS
BEGIN NULL; END;
BEGIN
DBMS_OUTPUT.PUT_LINE('The following procedure call raises an error.');
-- balance(100); raises an error because the procedure declaration is not unique
END;
/

You cannot overload subprograms whose parameters differ only in subtype. For example, you cannot overload procedures where one accepts an INTEGER parameter and the other accepts a REAL parameter, even though INTEGER and REAL are both subtypes of NUMBER and so are in the same family.
You cannot overload two functions that differ only in the datatype of the return value, even if the types are in different families. For example, you cannot overload two functions where one returns BOOLEAN and the other returns INTEGER.
Example shows how you can define two subprograms with the same name. The procedures initialize different types of collections. Because the processing in these two procedures is the same, it is logical to give them the same name.
You can place the two overloaded initialize procedures in the same block, subprogram, package, or object type. PL/SQL determines which procedure to call by checking their formal parameters. The version of initialize that PL/SQL uses depends on whether you call the procedure with a DateTabTyp or NumTabTyp parameter.
Advantage of Package

A package is a schema object that groups logically related PL/SQL types, variables, and subprograms. Packages usually have two parts, a specification (spec) and a body; sometimes the body is unnecessary. The specification is the interface to the package. It declares the types, variables, constants, exceptions, cursors, and subprograms that can be referenced from outside the package. The body defines the queries for the cursors and the code for the subprograms.

Advantages of PL/SQL Packages
Packages have a long history in software engineering, offering important features for reliable, maintainable, reusable code, often in team development efforts for large systems.
Modularity
Packages let you encapsulate logically related types, items, and subprograms in a named PL/SQL module. Each package is easy to understand, and the interfaces between packages are simple, clear, and well defined. This aids application development.
Easier Application Design
When designing an application, all you need initially is the interface information in the package specs. You can code and compile a spec without its body. Then, stored subprograms that reference the package can be compiled as well. You need not define the package bodies fully until you are ready to complete the application.
Information Hiding
With packages, you can specify which types, items, and subprograms are public (visible and accessible) or private (hidden and inaccessible). For example, if a package contains four subprograms, three might be public and one private. The package hides the implementation of the private subprogram so that only the package (not your application) is affected if the implementation changes. This simplifies maintenance and enhancement. Also, by hiding implementation details from users, you protect the integrity of the package.
Added Functionality
Packaged public variables and cursors persist for the duration of a session. They can be shared by all subprograms that execute in the environment. They let you maintain data across transactions without storing it in the database.
Better Performance
When you call a packaged subprogram for the first time, the whole package is loaded into memory. Later calls to related subprograms in the package require no disk I/O.
Packages stop cascading dependencies and avoid unnecessary recompiling. For example, if you change the body of a packaged function, Oracle does not recompile other subprograms that call the function; these subprograms only depend on the parameters and return value that are declared in the spec, so they are only recompiled if the spec changes.
Mutating Error and How to Solve this error?

Too Many Rows and No data Found

NO_DATA_FOUND 01403 +100 A SELECT INTO statement returns no rows, or your program references a deleted element in a nested table or an uninitialized element in an index-by table.
Because this exception is used internally by some SQL functions to signal completion, you should not rely on this exception being propagated if you raise it within a function that is called as part of a query.
TOO_MANY_ROWS 01422 -1422 A SELECT INTO statement returns more than one row.


When Others

The optional OTHERS handler catches all exceptions that the block does not name specifically.

Data Base Triggers

Type of Pragma and what is the use of this Pargma

Type of Tables

Delete and Truncate Differences

Raise Error

Bulk Collections and Bulk Collection Excpetions

Indices of Use in the Pl sql tables

Collections

Trunc and Trim Differences

To_Char vs To_Date

Tuning

P/L Sql Table Exception

What are the Exception has found in SQL Query?

Sql Loader?

If loading the Data through Sql Loader, How to avoid the exisiting Trigger Fire?

One Time Only Procedure?


Tuning Hints

What is Trigger?

The table is available columns Qty,Price and Amount? Qty and Price is coming from Form

We insert the amount column (qty * amount) by the way of trigger.

What is the advantage of the Package and Procedure?


Dbms_FlashBack

Dbms_job

Dbms_Utility

Dbms_Output

Dbms_Sql

Dbms_Ddl

Dbms_Mview

Dbms_File

Dbms_Session

Dbms_Debug

Dbms_Types

Dbms_Pcofiler

Dbms_DataPump

Dbms_Metadata


What are the hire Key Query? (Prior like)

How to use the variable across the procedure in the packages?

Set Serveroutput On (Backround Process Details)?

Entity Relationship diagram?

What is Entity?

How to Print the Bind Variable in Sql Prompt?

What is the different between Sql & Sql * Plus?

What is the different between Bind Variable Vs Lexcial Variable?

Entity Result Set?

How to select the data from the Nestle Table?

What is mean by Nested Table?

Implicit Cursor Vs Explicit Cursor and Examples?

What are the Advantages of Packages?

Emp table having 10 Record, if u select 2 from emp?

Select count(2) from emp;

Select count(2) ,Count(*), Count(rowid) from emp?

Select sum(2) from emp;

Select rownum,.* from emp;

Select rownum,sal from emp;

Select rownum,sal from emp order by sal;

Select c1,c2,c3
what is the difference between IN and EXISTS ?
what is MERGE statement?
what is explain plan?

what is the difference between merge join ,hash join?
Different Type of Constraints?

Outer Join Vs Inner Join

What is the use of the Save Exceptions?

Level of Constraints ( Column Level and Table Level)

Primary Key Vs Composite Primary Key

Roll Up and Cube
Oracle 9i Vs Oracle 10G

Type of Locks and what is the use of this Locks

Type of Views

Type of Index

Bit Map Index and Hash Index

Materalised View