Set - 5

Question 91 :

What is difference between a Cursor declared in a procedure and Cursor declared in a package specification ?

Answer :

A cursor declared in a package specification is global and can be accessed by other procedures or procedures in a package.
A cursor declared in a procedure is local to the procedure that can not be accessed by other procedures.


Question 92 :

What are the cursor attributes used in PL/SQL ?

Answer :

%ISOPEN - to check whether cursor is open or not
% ROWCOUNT - number of rows fetched/updated/deleted.
% FOUND - to check whether cursor has fetched any row. True if rows are fetched.
% NOT FOUND - to check whether cursor has fetched any row. True if no rows are featched.
These attributes are proceeded with SQL for Implicit Cursors and with Cursor name for Explicit Cursors.


Question 93 :

What are % TYPE and % ROWTYPE ? What are the advantages of using these over datatypes?

Answer :

% TYPE provides the data type of a variable or a database column to that variable.
% ROWTYPE provides the record type that represents a entire row of a table or view or columns selected in the cursor.
The advantages are :
I. Need not know about variable's data type
ii. If the database definition of a column in a table changes, the data type of a variable changes accordingly.


Question 94 :

What is difference between % ROWTYPE and TYPE RECORD ?

Answer :

% ROWTYPE is to be used whenever query returns a entire row of a table or view.
TYPE rec RECORD is to be used whenever query returns columns of different table or views and variables.
E.g. TYPE r_emp is RECORD (eno emp.empno% type,ename emp ename %type );
e_rec emp% ROWTYPE
cursor c1 is select empno,deptno from emp;
e_rec c1 %ROWTYPE.


Question 95 :

What are the different types of PL/SQL program units that can be defined and stored in ORACLE database ?

Answer :

Procedures and Functions,Packages and Database Triggers.