Explain how to debug PL/SQL program.

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: - Because of the block nature, multiple statements are processed at once thereby improving performance - PL/SQL handles exceptions on catching which, action can be taken - PL/SQL is highly portable as it works with all procedural languages and is highly secured because of privileges

Correct Answer: PL/SQL is Procedural Language SQL that is an extension of SQL that results in a more structural language composed of blocks It is mainly used in writing applications that needs to be structured and has error handling

Correct Answer: In a correlated query, the outer query is related to the inner query This means one or more columns in the outer query are referenced It?s used when the outer queries value is being used by inner query For example, we need to find which employee had more perks in the current month than they did in the previous month The correlated subquery is executed for each row of perks information in the parent query to first determine what the perks were for each employee in the previous month This data, in turn, is compared to perks for each employee in the current month, and only those employees whose perks in the current month were greater that their previous month's perks are returned

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: EXECUTE IMMEDIATE command can be used to call DDL statements Example: BEGIN EXECUTE IMMEDIATE 'TRUNCATE TABLE employee'; END;

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