What is a Collection? Explain collection types.

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: Even though both PL/SQL and Java (or) NET can be used, PL/SQL stands above these two in terms of integration overhead This is because Java is an open source proprietary and Data manipulation is slightly faster in PL/SQL than in Java

Correct Answer: SQL is a structured query language while PL/SQL is an extension of SQL by introducing a procedural flow PL/SQL has blocks of statements PL/SQL works like other procedural languages and has concepts like control statements, sequential statements, exception handling etc

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