Define cursor attributes: %FOUND, %NOTFOUND, %ROWCOUNT, and %ISOPEN

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: The UTL_FILE database package can be used to read and write operating system files You need to have read /write access rights in that directory before the package can be used Example to write file: Fhandler is a variable of type UTL_FILEFILE_TYPE UTL_FILEPUTF(fHandler, 'Im writing to a file\n'); Example to read file: UTL_FILEGET_LINE(fHandler, buf);

Correct Answer: One can debug PL/SQL program by printing the output using DBMS_OUTPUT package Put_line can be used to display a line as shown below: BEGIN dbms_outputput_line(?Sample line'); END;

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: PL/SQL exceptions are raised using the RAISE command This command is used when exceptions are defined by programmer and not implicit exceptions Example: Declare and raising an exception: DECLARE short_of_attendance EXCEPTION; min_attendance NUMBER(4); BEGIN IF min_attendance < 10 THEN RAISE short_of_attendance; END IF; EXCEPTION WHEN short_of_attendance THEN -- handle the error 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