Oracle interview questions /Answer Set-3

Explain the attributes of implicit cursor

a. %FOUND - True, if the SQL statement has changed any rows.
b. %NOTFOUND - True, if record was not fetched successfully.
c. %ROWCOUNT - The number of rows affected by the SQL statement.
d. %ISOPEN - True, if there is a SQL statement being associated to the cursor or the cursor is open.


Explain the attributes of explicit cursor.

a. %FOUND - True, if the SQL statement has changed any rows.
b. %NOTFOUND - True, if record was not fetched successfully.
c. %ROWCOUNT - The number of rows affected by the SQL statement.
d. %ISOPEN - True, if there is a SQL statement being associated to the cursor or the cursor is open.


What is the ref cursor in Oracle?

REF_CURSOR allows returning a recordset/cursor from a Stored procedure.
It is of 2 types:
Strong REF_CURSOR: Returning columns with datatype and length need to be known at compile time.
Weak REF_CURSOR: Structured does not need to be known at compile time.
Syntax till Oracle 9i
create or replace package REFCURSOR_PKG as
TYPE WEAK8i_REF_CURSOR IS REF CURSOR;
TYPE STRONG REF_CURSOR IS REF CURSOR RETURN EMP%ROWTYPE;
end REFCURSOR_PKG;
Procedure returning the REF_CURSOR:
create or replace procedure test( p_deptno IN number , p_cursor OUT
REFCURSOR_PKG.WEAK8i_REF_CURSOR)
is
begin
open p_cursor FOR
select *
from emp
where deptno = p_deptno;
end test;
Since Oracle 9i we can use SYS_REFCURSOR
create or replace procedure test( p_deptno IN number,p_cursor OUT SYS_REFCURSOR)
is
begin
open p_cursor FOR
select *
from emp
where deptno = p_deptno;
end test;
For Strong
create or replace procedure test( p_deptno IN number,p_cursor OUT REFCURSOR_PKG.STRONG
REF_CURSOR)
is
begin
open p_cursor FOR
select *
from emp
where deptno = p_deptno;
end test;


What are the drawbacks of a cursor?

Cursors allow row by row processing of recordset. For every row, a network roundtrip is made unlike in a Select query where there is just one network roundtrip. Cursors need more I/O and temp storage resources, thus it is slower.


What is a cursor variable?

In case of a cursor, Oracle opens an anonymous work area that stores processing information. This area can be accessed by cursor variable which points to this area. One must define a REF CURSOR type, and then declare cursor variables of that type to do so.
E.g.:
/* Create the cursor type. */
TYPE company_curtype IS REF CURSOR RETURN company%ROWTYPE;

/* Declare a cursor variable of that type. */
company_curvar company_curtype;


What is implicit cursor in Oracle?

PL/SQL creates an implicit cursor whenever an SQL statement is executed through the code, unless the code employs an explicit cursor. The developer does not explicitly declare the cursor, thus, known as implicit cursor.
E.g.:
In the following UPDATE statement, which gives everyone in the company a 20% raise, PL/SQL creates an implicit cursor to identify the set of rows in the table which would be affected.
UPDATE emp
SET salary = salary * 1.2;


Can you pass a parameter to a cursor? Explain with an explain

Parameterized cursor:
/*Create a table*/
create table Employee(
ID VARCHAR2(4 BYTE)NOT NULL,
First_Name VARCHAR2(10 BYTE)
);
/*Insert some data*/
Insert into Employee (ID, First_Name) values (‘01’,’Harry’);
/*create cursor*/
declare
cursor c_emp(cin_No NUMBER)is select count(*) from employee where id=cin_No;
v_deptNo employee.id%type:=10;
v_countEmp NUMBER;
begin
open c_emp (v_deptNo);
fetch c_emp into v_countEmp;
close c_emp;
end;

/*Using cursor*/
Open c_emp (10);





What is a package cursor?

A Package that returns a Cursor type is a package cursor.
Eg:
Create or replace package pkg_Util is
cursor c_emp is select * from employee;
r_emp c_emp%ROWTYPE;
end;
/*Another package using this package*/
Create or replace package body pkg_aDifferentUtil is
procedure p_printEmps is
begin
open pkg_Util.c_emp;
loop
fetch pkg_Util.c_emp into pkg_Util.r_emp;
exit when pkg_Util.c_emp%NOTFOUND;
DBMS_OUTPUT.put_line(pkg_Util.r_emp.first_Name);
end loop;
close pkg_Util.c_emp;
end;
end;


Explain why cursor variables are easier to use than cursors.

Cursor variables are preferred over a cursor for following reasons:
A cursor variable is not tied to a specific query.
One can open a cursor variable for any query returning the right set of columns. Thus, more flexible than cursors.
A cursor variable can be passed as a parameter.
A cursor variable can refer to different work areas.


What is locking, advantages of locking and types of locking in oracle?

Locking is a mechanism to ensure data integrity while allowing maximum concurrent access to data. It is used to implement concurrency control when multiple users access table to manipulate its data at the same time.

Advantages of locking:
a. Avoids deadlock conditions
b. Avoids clashes in capturing the resources
Types of locks:
a. Read Operations: Select
b. Write Operations: Insert, Update and Delete