Tuesday, December 27, 2016

PL/SQL Interview questions

  1. Explain uses of cursor.
  2. Ans : Cursor is a named private area in SQL from which information can be accessed. They are required to process each row individually for queries which return multiple rows.


2. Explain the uses of database trigger.
Ans: A PL/SQL program unit associated with a particular database table is called a database trigger. It is used for :
1)Audit data modifications.
2)Log events transparently.
3)Enforce complex business rules.
4)Maintain replica tables
5)Derive column values
6)Implement Complex security authorizations


3. What are the two types of exceptions.
Ans : Error handling part of PL/SQL block is called Exception. They have two types : user_defined and predefined.


4. Show how functions and procedures are called in a PL SQL block.
Ans:
      Function is called as a part of an expression.
      total:=calculate_sal(‘b644’)
      Procedure is called  as a statement in PL/SQL.
      calculate_bonus(‘b644’);


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


6. Explain two virtual tables available at the time of database trigger execution.
Ans: 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.


7. How is a process of PL SQL compiled?
Ans: Compilation process includes syntax check, bind and p-code generation processes.
Syntax checking checks the PL SQL codes for compilation errors. When all errors are corrected, a storage address is assigned to the variables that hold data. It is called Binding. P-code is a list of instructions for the PL SQL engine. P-code is stored in the database for named blocks and is used the next time it is executed


8.What is p-code and source code ?
Ans: P-code is Pre-complied code stored in Public cache memory of System Global Area after the Oracle instance is started, whereas source code is a simple code of sp, package, trigger, functions etc which are stored in Oracle system defined data dictionary.


9. Explain mutating table error.
Ans: It occurs when a trigger tries to update a row that it is currently using. It is fixed by using views or temporary tables, so database selects one and updates the other.


10.Explain Commit, Rollback and Savepoint and Auto commit and Set transaction.
 Ans:
  1. COMMIT : COMMIT command to make changes permanent save to a database during the current transaction.
  2. ROLLBACK : ROLLBACK command execute at the end of current transaction and undo/undone any changes made since the begin transaction.
  3. SAVEPOINT : SAVEPOINT command save the current point with the unique name in the processing of a transaction.
  4. AUTOCOMMIT : Set AUTOCOMMIT ON to execute COMMIT Statement automatically.
  5. SET TRANSACTION : PL/SQL SET TRANSACTION command set the transaction properties such as read-write/read only access.
11. Define Implicit and Explicit Cursors.
Ans: A cursor is implicit by default. The user cannot control or process the information in this cursor.
If a query returns multiple rows of data, the program defines an explicit cursor. This allows the application to process each row sequentially as the cursor returns it.


12 How many triggers can be applied to a table?
Ans: A maximum of 12 triggers can be applied to one table.


13. Explain 3 basic parts of a trigger.
Ans: A triggering statement or event.
  • A restriction
  • An action
14. Explain TTITLE and BTITLE.
Ans: TTITLE and BTITLE commands that control report headers and footers.


15. Show the cursor attributes of PL/SQL.
%ISOPEN : Checks if the cursor is open or not
%ROWCOUNT : The number of rows that are updated, deleted or fetched.
%FOUND : Checks if the cursor has fetched any row. It is true if rows are fetched
%NOT FOUND : Checks if the cursor has fetched any row. It is True if rows are not fetched.
Custom Search