PLSQL interview questions - part5

PL/SQL Interview Questions

oracle pl sql interview questions for 3+ years experience

66.Describe hit ratio as it pertains to the database buffers. What is the difference between instantaneous and cumulative hit ratio and which should be used for tuning?
Ans: The hit ratio is a measure of how many times the database was able to read a value from the buffers verses how many times it had to re-read a data value from the disks. A value greater than 80-90% is good, less could indicate problems. If you simply take the ratio of existing parameters this will be a cumulative value since the database started. If you do a comparison between pairs of readings based on some arbitrary time span, this is the instantaneous ratio for that time span. An instantaneous reading gives more valuable data since it will tell you what your instance is doing for the time it was generated over.
67.What is a Cartesian product?
Ans: A Cartesian product is the result of an unrestricted join of two or more tables. The result set of a three table Cartesian product will have x * y * z number of rows where x, y, z correspond to the number of rows in each table involved in the join.
68.What is a mutating table error and how can you get around it?
Ans: This happens with triggers. It occurs because the trigger is trying to update a row it is currently using. The usual fix involves either use of views or temporary tables so the database is selecting from one while updating the other.
69.What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?
Ans: SQLCODE returns the value of the error number for the last error encountered. The SQLERRM returns the actual error message for the last error encountered. They can be used in exception handling to report, or, store in an error log table, the error that occurred in the code. These are especially useful for the WHEN OTHERS exception.
70.What are Transactional Triggers ? Give the uses of Transational Trigger ?
Ans: Transactional Triggers fire in response to transaction processing events. These events represent points during application processing at which Oracle Forms needs to interact with the data source. Examples of such events include updating records, rolling back to savepoints, and committing transactions. By default, Oracle Forms assumes that the data source is an ORACLE database, and issues the appropriate SQL statements to optimize transaction processing accordingly. However, by defining
transactional triggers and user exits, you can build a form to interact with virtually any data source, including even non-relational databases and flat files. Calling User Exits When you define transactional triggers to interact with a non-ORACLE data source, you will usually include a call to a user exit in the appropriate triggers. The code in your user exit interacts with the non-ORACLE data source. Once the user exit has performed the appropriate function (as indicated by the trigger from which it was called), it returns control to Oracle Forms for subsequent processing. For example, a user exit called from an On-Fetch trigger might be responsible for retrieving the appropriate number of records from the non-ORACLE data source. Once the records are retrieved, Oracle Forms takes over the display and management of those records in the form interface, just as it would if the records had been fetched from an ORACLE database. Uses for Transactional Triggers • Transactional triggers, except for the commit triggers, are primarily intended to access certain data sources other than Oracle. • The logon and logoff transactional triggers can also be used with Oracle databases to change connections at run time.
71.What is Autonomous transaction ? Where do we use it?
Ans: In Oracle's database products, an autonomous transaction is an independent transaction that is initiated by another transaction. It must contain at least one Structured Query Language (SQL) statement. Autonomous transactions allow a single transaction to be subdivided into multiple commit/rollback transactions, each of which will be tracked for auditing purposes. When an autonomous transaction is called, the original transaction (calling transaction) is temporarily suspended. The autonomous transaction must commit or roll back before it returns control to the calling transaction. Once changes have been made by an autonomous transaction, those changes are visible to other transactions in the database. Autonomous transactions can be nested. That is, an autonomous transaction can operate as a calling transaction, initializing other autonomous transactions within itself.
72.What is a package, procedure and function?
Ans: Package : A package is a group of related program objects stored together as a unit in the database. A package is an encapsulated collection of related program objects stored together in the database. Program objects are: procedures, functions, variables, constants, cursors, exceptions. Procedure/Function : A procedure or function is a set of SQL and PL/SQL statements grouped together as an executable unit to perform a specific task. The main difference between a procedure and function is functions return a single variable by value whereas procedures do not return any variable by value. Rather they return multiple variables by passing variables by reference through their OUT parameter.
73.What do u mean by overloading?
Ans: Function Overloading : Packages allow you to overload procedures or functions. Overloading a procedure means creating multiple procedures with the same name in the same package, each taking arguments of different number or datatype.
74.What are the constructs of a procedure, function or a package ?
Ans: The constructs of a procedure, function or a package are : • variables and constants • cursors • exceptions
75.What are cascading triggers? What is the maximum no of cascading triggers at a time?
Ans: When a statement in a trigger body causes another trigger to be fired, the triggers are said to be cascading. Max = 32
76.What is the significance of the & and && operators in PL/SQL ?
Ans: The & operator means that the PL SQL block requires user input for a variable. The && operator means that the value of this variable should be the same as inputted by the user previously for this same variable.
77.If all the values from a cursor have been fetched and another fetch is issued, the output will be?
Ans: Last Record
78.What is a forward declaration ? What is its use ?
Ans: PL/SQL requires that you declare an identifier before using it. Therefore, you must declare a subprogram before calling it. This declaration at the start of a subprogram is called forward declaration. A forward declaration consists of a subprogram specification terminated by a semicolon.
79.Any three PL/SQL Exceptions?
Ans: Too_many_rows, No_Data_Found, Value_Error, Zero_Error, Others
80.Describe the use of %ROWTYPE and %TYPE in PL/SQL
Ans: %ROWTYPE allows you to associate a variable with an entire table row. The %TYPE associates a variable with a single column type.
81.How can you call a PL/SQL procedure from SQL?
Ans: By use of the EXECUTE (short form EXEC) command.
82.What are the various types of Exceptions ?
Ans: User defined and Predefined Exceptions.
Ans: DBMS_STANDARD provides a procedure named raise_application_error, which lets you issue user-defined error messages. That way, you can report errors to an application and avoid returning unhandled exceptions. The calling syntax is : raise_application_error(error_number, error_message); where error_number is a negative integer in the range -20000...-20999 and error_message is a character string up to 2048 bytes in length. An application can call raise_application_error only from an executing stored subprogram. When called, raise_application_error ends the subprogram, rolls back any database changes it made, and returns a user-defined error number and message to the application. The error number and message can be trapped like any ORACLE error. The calling application gets a PL/SQL exception, which it can process using the error-reporting functions SQLCODE and SQLERRM in an OTHERS handler. • The statement Raise_Application_Error can be called either from a procedure body or from an exception handler. • Irrespective of whether an error occurred or not, a raise_application_error command always raises an exception in the calling program (eg a forms trigger). If an exception handler is not written in that forms trigger, then a forms error occurs.

Part1                     Part2                     Part3                     Part4                     Part5