Difficult Oracle PL-SQL Interview Preparation Guide

Download PDF

SELECT last_name, employee_id, manager_id, LEVEL
FROM employees
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY last_name;

Form
Menu
Library

User_objects, User_Source and User_error.

a. PACKAGE NAME.PROCEDURE NAME (parameters);
variable := PACKAGE NAME.FUNCTION NAME (arguments);
EXEC SQL EXECUTE
b.
BEGIN
PACKAGE NAME.PROCEDURE NAME (parameters)
variable := PACKAGE NAME.FUNCTION NAME (arguments);
END;
END EXEC;
c. EXECUTE PACKAGE NAME.PROCEDURE if the procedures does not have any out/in-out parameters. A function can not be called.

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.

The two parts of package are PACKAGE SPECIFICATION & PACKAGE BODY. Package Specification contains declarations that are global to the packages and local to the schema.
Package Body contains actual procedures and local declaration of the procedures and cursor declarations.

The Same procedure name is repeated with parameters of different datatypes and parameters in different positions, varying number of parameters is called overloading of procedures.
e.g. DBMS_OUTPUT put_line
What is a package ? What are the advantages of packages ?

Function is called as part of an expression.
sal := calculate_sal ('a822');
procedure is called as a PL/SQL statement
calculate_bonus ('A822');

FUNCTION name (argument list .....) Return datatype is
local variable declarations
Begin
executable statements
Exception
execution handlers
End;

PROCEDURE name (parameter list.....)
is
local variable declarations
BEGIN
Executable statements.
Exception.
exception handlers
end;

Procedure Specification and Procedure Body.

IN,OUT,IN-OUT parameters.

Extensibility,Modularity, Reusability, Maintainability and one time compilation.

A FUNCTION is always returns a value using the return statement.
A PROCEDURE may return one or more values through parameters or may not return at all.

Objects of type TABLE are called "PL/SQL tables", which are modeled as (but not the same as) database tables, PL/SQL tables use a primary PL/SQL tables can have one column and a primary key.
Cursors

Cursor is a named private SQL area from where information can be accessed. Cursors are required to process rows individually for queries returning multiple rows.

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.

DECLARE CURSOR cursor name, OPEN cursor name, FETCH cursor name INTO or Record types, CLOSE cursor name.

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

Cursor for loop implicitly declares %ROWTYPE as loop index,opens a cursor, fetches rows of values from active set into fields in the record and closes when all the records have been processed.
eg. FOR emp_rec IN C1 LOOP
salary_total := salary_total +emp_rec sal;
END LOOP;

A stored procedure is a sequence of statements that perform specific function.

In the standard package.
Procedures, Functions & Packages ;

SQLCODE returns the latest code of the error that has occurred.
SQLERRM returns the relevant error message of the SQLCODE.

Raise_application_error is a procedure of package DBMS_STANDARD which allows to issue an user_defined error messages from stored sub-program or database
trigger.

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)