ORACLE SQL Interview Practical Questions with Queries With Answers Part 2

15) What is PL/SQL ?

PL/SQL is a procedural language that has both interactive SQL and procedural programming language constructs such as iteration, conditional branching.

16) Explain how procedures and functions are called in a PL/SQL block ?

Function is called as part of an expression.

sal := calculate_sal ('a822');

procedure is called as a PL/SQL statement

calculate_bonus ('A822');

Function can be called from SQL query + explicitly as well

e.g 1)select empno,salary,fn_comm(salary)from employee;

2)commision=fn_comm(salary);

Procedure can be called from begin-end clause.

e.g.

Begin

(

proc_comm(salary);

)end

17) What is ref cursor?

Ref Cursor is cursor variable. It is a pointer to a result set. It is useful in scenarios when result set is created in one program and the processing of the same in some other, might be written in different language, e.g. firing the select is done in PL/SQL and the processing will be done in Java.

Its a run time query binding with the cursor variable. Normal cursors are static cursors becaz they get acquited of query at the compile time.

Ref cursor is user defined type.Ref cursor allows any type of query for the cursor.Ref cursor is a global declaration of the cursor.Ref cursor is re-usable.

Ref cursor reduces the number of declarations of the cursor in the entire plsql program.Ref cursor is either static or dynamic.

18) What are two parts of package ?

1)package specification
2)package body
package specification :
where the variables r global and all the packages can access with that variable.it contains the declaration of variables.(variables r global)
package body:
where the procedures and functions are collected in that packages.the variable declared with in package body of a function or procedure they are not used outside of that procedure.(the variables are private used by that specific procedure or function.

create or replace package taxes
is
tax number;
----declare all public procedure/funcation
end taxes;

create or replace package body taxes
is
---declare all private variable
---declare all public/private procedure/funcations

begin
select rate_value into tax from tax_rates where rate_name='TAX';
end taxes;

19) What is a stored procedure ?

A stored procedure is a named pl/sql block which performs an action.It is stored in the database as a schema object and can be repeatedly executed.It can be invoked, parameterised and nested.

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

% 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.

---

%TYPE provides datatypes of the particular column of the table.

%ROWTYPE attribute is useful 
When we need to fetch entire row from the table.
secondly if we don't know the data type of some column %rowtype is useful for that.
finally, if we change any datatypes of the column,%rowtype automatically change the datatypes for the variable which is created by user

21) What are the modes of parameters that can be passed to a procedure ?

IN,OUT,IN-OUT parameters.

IN parameter is the default mode which acts as a constant inside calling environment.value passed in this parameter can not be changed.OUT parameter mode is used to pass value from calling environment into main block,here we can change the value.It acts as a variable inside calling environment.

INOUT parameter mode which pass value into calling environment and will get the value back in main block.

IN parameter mode uses call by reference method to pass value from formal parameter to actual parameter.

OUT & INOUT parameter mode uses call by value method to pass values.

22) How we can create a table through procedure ?

create procedure p1 is

begin

EXECUTE IMMEDIATE 'CREATE TABLE temp AS

SELECT * FROM emp ' ;

END;

23) What will happen after commit statement ?

After commit statement,all the transaction will be ended.

all the changes data are parmanently stored in the database.

24) 1)What is the starting "oracle error number"?

2)What is meant by forward declaration in functions?

One must declare an identifier before referencing it. Once it is declared it can be referred even before defining it in the PL/SQL. This rule applies to function and procedures also

---

ORACLE ERROR NO starts with ORA 00001

25) Explain the two type of Cursors ?

There are two types of cursors, Implicit Cursor and Explicit Cursor.

PL/SQL uses Implicit Cursors for queries. User defined cursors are called Explicit Cursors. They can be declared and used.

there are two types of cursor

1.implicit cursor

2. Explicit cursor

26) What is Overloading of procedures ?

Overloading of procedure

name of the procedure is

same but the number of parameters should be different.In that case,procedure will be overloaded.

2. if the number of parameters are same in that case,data type should be different.

if the two rules are satisfied in that case procedure will be overloaded.

Same procedure name with different parameters called procedure overloading, parameters may different by their datatypes, sequence and position.

eg1:

get_employee(Emp_id number);

get_employee(Emp_id Varchar(20));

eg2:

get_employee(ID number, name char(20))

get_employee(ID Number, name char(20), dept char(20))

get_employee(name char(20), dept char(20),ID Number)

27) What is Pragma EXECPTION_INIT ? Explain the usage ?

The PRAGMA EXECPTION_INIT tells the complier to associate an exception with an oracle error. To get an error message of a specific oracle error.

e.g. PRAGMA EXCEPTION_INIT (exception name, oracle error number)

The PRAGMA_EXCEPTION_INIT

tells the compiler to assosiate

an exception with an oracle error.

28) What is pl/sql?what are the advantages of pl/sql?

PL/SQL is Very Usefully Language & Tools of Oracle to Manipulate,Restrict,Validate & Control the Unauthorized Access of Data From the Database.

We Can easily show multiple records of the multiple table at same time.

And Using control statement like Loops & If else & Select case we control the database.

29) What is Rollback?

Rollback causes work in the current transaction to be undone.

30) How to avoid using cursors? What to use instead of cursor and in what cases to do so?

When the number of rows returned by query is small (around 100), then explicit cursor can be avoided safely since in those cases using explicit cursor is performance degrading(CPU overhead).

                 *******Thank you for reading and All the best**************

Your comments will help me a lot...please give your comments which will encourage me to post more.....