PL/SQL Interview Questions Part 2

PL/SQL Interview Questions Part 2

1.) Explain Raise_application_error?
It is a procedure of package DBMS_STANDARD that allows issuing of user_defined error messages from database trigger or stored sub-program.

2.) Explain two virtual tables available at the time of database trigger execution?
Table columns are referred as THEN.column_name and NOW.column_name.

•    For INSERT related triggers, NOW.column_name values are available only.
•    For DELETE related triggers, THEN.column_name values are available only.
•    For UPDATE related triggers, both Table columns are available.

3.) What are the rules to be applied to NULLs whilst doing comparisons?
•    NULL is never TRUE or FALSE
•    NULL cannot be equal or unequal to other values
•    If a value in an expression is NULL, then the expression itself evaluates to NULL except for concatenation operator (||)

4.) Does SQL*Plus also have a PL/SQL Engine?
No, SQL*Plus does not have a PL/SQL Engine embedded in it. Thus, all PL/SQL code is sent directly to database engine. It is much more efficient as each statement is not individually stripped off.

5.) Explain 3 basic parts of a trigger.
•    A triggering statement or event.
•    A restriction
•    An action

6.) What are character functions?
INITCAP, UPPER, SUBSTR, LOWER and LENGTH are all character functions. Group functions give results based on groups of rows, as opposed to individual rows. They are MAX, MIN, AVG, COUNT and SUM.

7.) Explain TTITLE and BTITLE.

TTITLE and BTITLE commands that control report headers and footers.

8.) What is an Intersect?

Intersect is the product of two tables and it lists only matching rows.

9.) What are sequences?

Sequences are used to generate sequence numbers without an overhead of locking. Its drawback is that the sequence number is lost if the transaction is rolled back.

10.) What is a cursor for loop?

Cursor for loop implicitly declares %ROWTYPE as loop index, opens a cursor, fetches rows of values from active set into fields in the record and closes when all the records have been processed.

11.) Explain the usage of WHERE CURRENT OF clause in cursors?

WHERE CURRENT OF clause in an UPDATE, DELETE statement refers to the latest row fetched from a cursor.

12.) Is it possible to use Transaction control Statements such a ROLLBACK or COMMIT in Database Trigger? Why?

It is not possible. As triggers are defined for each table, if you use COMMIT of ROLLBACK in a trigger, it affects logical transaction processing.

13.) What is Raise_application_error?

Raise_application_error is a procedure of package DBMS_STANDARD which allows to issue a user_defined error messages from stored sub-program or database trigger.

14.) What is the difference between 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.

15.) Explain how procedures and functions are called in a PL/SQL block?

•    Function is called as part of an expression.
•    Procedure is called as a PL/SQL statement

16.) What is Overloading of procedures?

The Same procedure name is repeated with parameters of different data types and parameters in different positions, varying number of parameters is called overloading of procedures.
e.g. DBMS_OUTPUT put_line

17.) 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 Application Design, and Information. Hiding, Reusability and Better Performance.

18.) 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.

19.) 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 cannot be accessed by other procedures.

20.) How would you reference column values BEFORE and AFTER you have inserted and deleted triggers?

Using the keyword “new.column name”, the triggers can reference column values by new collection. By using the keyword “old.column name”, they can reference column values by old collection.

21.) What are the uses of SYSDATE and USER keywords?

SYSDATE refers to the current server system date. It is a pseudo column. USER is also a pseudo column but refers to current user logged onto the session. They are used to monitor changes happening in the table.

22.) How does ROWID help in running a query faster?

ROWID is the logical address of a row; it is not a physical column. It composes of data block number; file number and row number in the data block. Thus, I/O time gets minimized retrieving the row, and results in a faster query.

23.) What is SPOOL?

Spool command can print the output of Sql statements in a file.

24.) What does fetching a cursor do?

Fetching a cursor reads Result Set row by row.

25.) What does closing a cursor do?

Closing a cursor clears the private SQL area as well as de-allocates memory.

26.) Explain autonomous transaction.

An autonomous transaction is an independent transaction of the main or parent transaction. It is not nested if it is started by another transaction.

There are several situations to use autonomous transactions like event logging and auditing.

27.) What is out parameter used for even though return statement can also be used in pl/Sql?

Out parameters allows more than one value in the calling program. Out parameter is not recommended in functions. Procedures can be used instead of functions if multiple values are required. Thus, these procedures are used to execute out parameters.

28.) Mention what does the hierarchical profiler does?

The hierarchical profiler could profile the calls made in PL/SQL, apart from filling the gap between the loopholes and the expectations of performance tracing. The efficiencies of the hierarchical profiler includes

•    Distinct reporting for SQL and PL/SQL time consumption
•    Reports count of distinct sub-programs calls made in the PL/SQL, and the time spent with each subprogram call
•    Multiple interactive analytics reports in HTML format by using the command line utility
•    More effective than conventional profiler and other tracing utilities

29.) Mention what does PLV msg allows you to do?

The PLV msg enables you to

•    Assign individual text message to specified row in the PL/SQL table
•    It retrieves the message text by number
•    It substitutes automatically your own messages for standard Oracle error messages with restrict toggle
•    Batch load message numbers and text from a database table directly PLV msg PL/SQL table

30.) Mention what is the PLV (PL/Vision) package offers?

•    Null substitution value
•    Set of assertion routines
•    Miscellaneous utilities
•    Set of constants used throughout PL vision
•    Pre-defined data types

31.) Mention what is the use of PLVprs and PLVprsps?

PLVprs: It is an extension for string parsing for PL/SQL, and it is the lowest level of string parsing functionality
PLVprsps: It is the highest level package to parse PL/SQL source code into separate atomics. It relies on other parsing packages to get work done.

32.)  Explain how you can copy a file to file content and file to PL/SQL table in advance PL/SQL?

With a single program call – “fcopy procedure”, you can copy the complete contents of one file into another file.  While to copy the contents of a file directly into a PL/SQL table, you can use the program “file2pstab”.

33.) Mention what PLVcmt and PLVrb does in PL/SQL?

PL/Vision offers two packages that help you manage transaction processing in PL/SQL application. It is PLVcmt and PLVrb.

•    PLVcmt: PLVcmt package wraps logic and complexity for dealing with commit processing
•    PLVrb: It provides a programmatic interface to roll-back activity in PL/SQL


Post a Comment