PL SQL interview Question and Answers
ANS: SQL is a structured Query Language designed fro communicating with databases. SQL provide with a simple ans efficiency way to read data from a database.
2. Advantages of PL/SQL.
ANS: * Block Structures: PL SQL consists of blocks of code, which can be nested within each other. Each block forms a unit of a task or a logical module. PL/SQL Blocks can be stored in the database and reused.
* Procedural Language Capability: PL SQL consists of procedural language constructs such as conditional statements (if else statements) and loops like (FOR loops).
* Better Performance: PL SQL engine processes multiple SQL statements simultaneously as a single block, thereby reducing network traffic.
* Error Handling: PL/SQL handles errors or exceptions effectively during the execution of a PL/SQL program. Once an exception is caught, specific actions can be taken depending upon the type of the exception or it can be displayed to the user with a message.
3. Describe the use of PL/SQL tables?
ANS: PL/SQL tables are scalar arrays that can be referenced by a binary integer. They can be used to hold values for use in later queries or calculations. In Oracle they will be able to be of the %ROWTYPE designation, or RECORD.
4. How can you generate debugging output from PL/SQL?
ANS: Use the DBMS_OUTPUT package. The DBMS_OUTPUT package can be used to show intermediate results from loops and the status of variables as the procedure is executed, however output only occurs after processing is finished, which might not be useful if processing takes a long time. The package UTL_FILE can be used to write to a file, but one must have write access to the output directory. A third possibility is to create a log table and have the procedure write to the table. This will give you debugging information in real time.
5. Explain how procedures and functions are called in a PL/SQL block?
ANS: Function is called as part of an expression and sql query. Procedure is called as a PL/SQL statement
6. What are the types of triggers?
ANS: There are 12 types of triggers in PL/SQL that consist of combinations of the Before, After, Row, Table, Insert, Update, Delete And All Key Words: Before All Row Insert After All Row Insert Before Insert After Insert etc.
7. When is a declare statement needed?
ANS: The DECLARE statement is used in PL/SQL anonymous blocks such as with stand alone, non-stored PL/SQL procedures. It must come first in a PL/SQL stand alone file if it is used.
8. 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.
9. 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
10. What is a cursor?
ANS: Name or handle to a private SQL area where Oracle parses and fetches query results.
11. What is shared SQL?
ANS: Oracle recognizes similar statements. The SQL area is used many times for similar statements.
What is Parsing?
ANS: Syntax checking, privileges checking, allocating Private SQL Area.
12. What is the difference between anonymous blocks and stored procedures?
ANS: Anonymous block is compiled only when called.
– Stored procedure is compiled and stored in database with the dependency information as well.
– Former is PL/SQL code directly called from an application. Latter is stored in database.
– Former has declare statement. Latter doesnt.
13. How is a PL/SQL program stored in database?
ANS: Parsed code is stored. It’s called P-code
14. How is a PL/SQL program executed?
– PL/SQL engine is the main component that executes procedural stmt and passes the SQL to the SQL statement executor.
15. How to know PL/SQL compile parameters ?
ANS: SHOW PARAMETERS PLSQL
16. What are the most important DDL statements in SQL?
ANS : CREATE TABLE – creates a new database table
ALTER TABLE – alters (changes) a database table
DROP TABLE – deletes a database table
CREATE INDEX – creates an index (search key)
DROP INDEX – deletes an index
17. What command is used to encrypt a PL/SQL application?
18. What is the data type of Null?
ANS: Data type of NULL is any scalar variable i.e varchar2, char, number, date etc.