Oracle Apps PL-SQL Interview Questions – Set 6

Oracle Apps PL-SQL Interview Questions – Set 6

Explain the difference between trigger and stored procedure.

Trigger in act which is performed automatically before or after a event occur

Stored procedure is a set of functionality which is executed when it is explicitly invoked.

Explain Row level and statement level trigger.

Row-level: – They get fired once for each row in a table affected by the statements.

Statement: – They get fired once for each triggering statement.

Advantage of a stored procedure over a database trigger

Firing of a stored procedure can be controlled whereas on the other hand trigger will get fired whenever any modification takes place on the table.

What are cascading triggers?

A Trigger that contains statements which cause invoking of other Triggers are known as cascading triggers. Here’s the order of execution of statements in case of cascading triggers:

  • Execute all BEFORE statement triggers that apply to the current statement.
  • Loop for each row affected statement.
  • Execute all BEFORE row triggers that apply to the current statement in the loop.
  • Lock and change row, perform integrity constraints check; release lock.
  • Execute all AFTER row triggers that apply to the current statement.
  • Execute all AFTER statement triggers that apply to the current statement.

What is a JOIN? Explain types of JOIN in oracle.

A JOIN is used to match/equate different fields from 2 or more tables using primary/foreign keys. Output is based on type of Join and what is to be queries i.e. common data between 2 tables, unique data, total data, or mutually exclusive data.

Types of JOINS:

JOIN Type Example Description
Simple JOIN SELECT p.last_name, t.deptName
FROM person p, dept t
WHERE p.id = t.id;
Find name and department name of students who have been allotted a department
Inner/Equi/Natural JOIN

 

SELECT * from Emp INNER JOIN Dept WHERE Emp.empid=Dept.empid Extracts data that meets the JOIN conditions only. A JOIN is by default INNER unless OUTER keyword is specified for an OUTER JOIN.
Outer Join

 

SELECT distinct * from Emp LEFT OUTER JOIN Dept Where Emp.empid=Dept.empid It includes non matching rows also unlike Inner Join.
Self JOIN

 

SELECT a.name,b.name from emp a, emp b WHERE a.id=b.rollNumber Joining a Table to itself.

What is object data type in oracle?

New/User defined objects can be created from any database built in types or by their combinations. It makes it easier to work with complex data like images, media (audio/video). An object types is just an abstraction of the real world entities. An object has:

  • Name
  • Attributes
  • Methods

Example:

Create type MyName as object (first varchar2(20), second varchar2(20));

Now you can use this datatype while defining a table below:

Create table Emp (empno number(5),Name MyName);

One can access the Atributes as Emp.Name.First and Emp.Name.Second

What is composite data type?

Composite data types are also known as Collections .i.e RECORD, TABLE, NESTED TABLE, VARRAY.

Composite data types are of 2 types:

PL/SQL RECORDS

PL/SQL Collections- Table, Varray, Nested Table

Differences between CHAR and NCHAR in Oracle

NCHAR allow storing of Unicode data in the database. One can store Unicode characters regardless of the setting of the database characterset

Differences between CHAR and VARCHAR2 in Oracle

CHAR is used to store fixed length character strings where as Varchar2 can store variable length character strings. However, for performance sake Char is quit faster than Varchar2.

If we have char name[10] and store “abcde”, then 5 bytes will be filled with null values, whereas in case of varchar2 name[10] 5 bytes will be used and other 5 bytes will be freed.

Differences between DATE and TIMESTAMP in Oracle

Date is used to store date and time values including month, day, year, century, hours, minutes and seconds. It fails to provide granularity and order of execution when finding difference between 2 instances (events) having a difference of less than a second between them.

TimeStamp datatype stores everything that Date stores and additionally stores fractional seconds.

Date: 16:05:14Timestamp: 16:05:14:000

Define CLOB and NCLOB datatypes.

CLOB: Character large object. It is 4GB in length.

NCLOB: National Character large object. It is CLOB datatype for multiple character sets , upto 4GB in length.

What is the BFILE datatypes?

It refers to an external binary file and its size is limited by the operating system.

What is Varrays?

Varrays are one-dimensional, arrays. The maximum length is defined in the declaration itself. These can be only used when you know in advance about the maximum number of items to be stored.

For example: One person can have multiple phone numbers. If we are storing this data in the tables, then we can store multiple phone numbers corresponding to single Name. If we know the maximum number of phone numbers, then we can use Varrays, else we use nested tables.

What is a cursor? What are its types? 

Cursor is used to access the access the result set present in the memory. This result set contains the records returned on execution of a query.

They are of 2 types:

  1. Explicit
  2. Implicit

Explain the attributes of implicit cursor

 

  1. %FOUND – True, if the SQL statement has changed any rows.
  2. %NOTFOUND – True, if record was not fetched successfully.
  3. %ROWCOUNT – The number of rows affected by the SQL statement.
  4. %ISOPEN – True, if there is a SQL statement being associated to the cursor or the cursor is open.

 

 

Explain the attributes of explicit cursor.

 

  1. %FOUND – True, if the SQL statement has changed any rows.
  2. %NOTFOUND – True, if record was not fetched successfully.
  3. %ROWCOUNT – The number of rows affected by the SQL statement.
  4. %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 asTYPE 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)isbeginopen p_cursor FOR select *from empwhere 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)isbeginopen p_cursor FOR select *from empwhere deptno = p_deptno;end test;

For Strong

create or replace procedure test( p_deptno IN number,p_cursor OUT REFCURSOR_PKG.STRONG REF_CURSOR)isbeginopen p_cursor FOR select *from empwhere 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 empSET 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.

 

Visit Us On FacebookVisit Us On Google PlusVisit Us On TwitterVisit Us On YoutubeVisit Us On LinkedinCheck Our Feed