Tuesday, April 12, 2011

PL/SQL -Q&A2


What is Raise_application_error ?

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.


What are the return values of functions SQLCODE and SQLERRM ?
SQLCODE returns the latest code of the error that has occured.
SQLERRM returns the relevant error message of the SQLCODE.



Where the Pre_defined_exceptions are stored ?

In the standard package.
Procedures, Functions & Packages ;



What is difference between a PROCEDURE & FUNCTION ?

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.



What are advantages of Stored Procedures?

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



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

IN,OUT,IN-OUT parameters.

What are the two parts of a Pakage?
Pakage Specification and Pakage Body.



Give the structure of the procedure ?

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

Give the structure of the function ?
FUNCTION name (argument list .....) Return datatype is
local variable declarations
Begin
executable statements
Exception
execution handlers
End;



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');
26. What is Overloading of procedures ?
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 ?

Package is a database object that groups logically related procedures.
The advantages of packages are Modularity, Easier Applicaton Design, Information.
Hiding,. reusability and Better Performance.



What are two parts of package ?

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.


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

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.
30. Name the tables where characteristics of Package, procedure and functions are stored ?
User_objects, User_Source and User_error.


What is an Exception ? What are types of Exception ?

Exception is the error handling part of PL/SQL block. The types are Predefined and
user_defined. Some of Predefined execptions are.

CURSOR_ALREADY_OPEN
DUP_VAL_ON_INDEX
NO_DATA_FOUND
TOO_MANY_ROWS
INVALID_CURSOR<>INVALID_NUMBER
LOGON_DENIED
NOT_LOGGED_ON
PROGRAM-ERROR
STORAGE_ERROR
TIMEOUT_ON_RESOU RCE
VALUE_ERROR
ZERO_DIVIDE
OTHERS.


People who read this post also read :


No comments:

Post a Comment

Thanks for your comments!