Explain how PL/SQL exceptions are raised.

Correct Answer: 1)Divide by zero ? This is raised when any number is attempted to divide by zero 2)TOO MANY ROWS - A SELECT INTO statement returns more than one row 3)CASE_NOT_FOUND - No choice in the WHEN clause of a case statement is selected 4)LOGIN_DENIED - An attempt to login with an invalid username or password 5)PROGRAM_ERROR - An internal PL/SQL problem

Correct Answer: - %FOUND This is a Boolean variable which evaluates to TRUE if the last row is successfully fetched - %NOTFOUND This is a Boolean variable which evaluates to TRUE if the last row is not successfully fetched This means there are no more rows to fetch - %ROWCOUNT Returns the number of rows fetched by the cursor - %ISOPEN If the cursor is open, it evaluates to TRUE else FALSE

Correct Answer: A record data type represents a data type for that row in a database table It lets u define your own records and not your own fields

Correct Answer: A collection just like an array is an ordered group of elements of the same type Each elements position is determined by a unique subscript Index by tables:- They are similar to hash arrays that allows to search for subscript values using arbitrary numbers and strings They can be declared as: TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY [BINARY_INTEGER | PLS_INTEGER | VARCHAR2(size_limit)]; INDEX BY key_type; Example: TYPE studenttyp IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER; stud_tab studenttyp; Nested tables:- they hold random number of elements and use sequential numbers as sub scripts They can be declared as: TYPE type_name IS TABLE OF element_type [NOT NULL]; Example: TYPE employee_type IS TABLE OF NUMBER INDEX BY VARCHAR2(64); Varrays: Holds a fixed number of elements which can be changed in run time They can be declared as: TYPE type_name IS {VARRAY | VARYING ARRAY} (size_limit) OF element_type [NOT NULL]; Example: TYPE Calendar IS VARRAY(366) OF DATE;

Correct Answer: EXECUTE IMMEDIATE command can be used to call DDL statements Example: BEGIN EXECUTE IMMEDIATE 'TRUNCATE TABLE employee'; END;

Correct Answer: A package is a collection of related PL/SQL objects The package contains a body and a specification The package specification has the declaration which is public and can be used in the PL/SQL sub programs inside the package The package body holds the implementation of all the PL/SQL objects declared in the specification Example of a PL/SQL Package CREATE OR REPLACE PACKAGE emp_data AS PROCEDURE add_employee ( ename VARCHAR2, job VARCHAR2, mgr NUMBER, sal NUMBER, deptno NUMBER); END emp_actions; CREATE OR REPLACE PACKAGE BODY emp_data AS PROCEDURE add_employee ( ename VARCHAR2, job VARCHAR2, mgr NUMBER, sal NUMBER, deptno NUMBER) IS BEGIN INSERT INTO emp VALUES (empno_seqNEXTVAL, ename, job, mgr, SYSDATE, comm, deptno); END add_employee; END emp_data; Advantages of PL/SQL packages : Packages are easier for application designing, encapsulating data, additional functionality and better performance An application has various modules which can be placed in packages and handled easier

Correct Answer: A sequence is a database object that is used to generate sequential number CREATE SEQUENCE seqname [increment] [minimum value][maximum value][start][cache][cycle] Nextval and currval lets us get the next value and current value from the sequence

Correct Answer: The CACHE option means how many sequences will be stored in memory for access by the application objects The performance is faster However in case of the database is down the data is memory is lost for the sequence The NO CACHE option means values are not stored in memory So there might be some performance issue

Correct Answer: An index is a object which is used to improve performance during retrieval of records CREATE [UNIQUE] INDEX index_name ON employee[emp_id, emp_name,dept_id] [COMPUTE STATISTICS] The UNIQUE keyword is used when combined values of the index should be unique The COMPUTE STATISTICS during the creation of index optimizes the plan of execution of the SQL statement and improves performance

Correct Answer: We need to first login to the data base as: SQLPLUS followed by user_name/password Then we need to execute the following query: CREATE USER user_name IDENTIFIED BY abcd; in this query a user_name suggest the user that is created which has a password abcd which is required for login in database