Do you get scared by hearing the word “interview”? If you do, then it’s because of lack of preparation and confidence that you have. But now you can boost your confidence level to a certain extent by reading our PL SQL interview questions and answers that are prepared by the greatest minds who have been working in this field for years. Now it’s possible for you to sit in the interview rooms and answer all the questions asked with confidence and crack it by winning the trust of the interviewers.
PL/SQL is the procedural extension created by Oracle Corporation for SQL, and a relational database of Oracle. PL/SQL is present in the database that Oracle has, and in the memory database of TimesTen, as well as in IBM DB2. It usually extends functionality based on SQL with successive releases. PL/SQL stands for Procedural Language extensions to SQL. Most of the companies use this platform for their efficiency and accuracy in writing and executing codes in the procedural format across all the industries. In layman terms, it means an extension of SQL utilized in the databases of Oracle
PL SQL is a procedural language which has interactive SQL, as well as procedural programming language constructs like conditional branching and iteration.
% ROWTYPE is used when a query returns an entire row of a table or view.
TYPE RECORD, on the other hand, is used when a query returns column of different tables or views.
Eg. TYPE r_emp is RECORD (sno smp.smpno%type,sname smp sname %type)
e_rec smp %ROWTYPE
Cursor c1 is select smpno,dept from smp;
e_rec c1 %ROWTYPE
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.
Cursor declares %ROWTYPE as loop index implicitly. It then opens a cursor, gets rows of values from the active set in fields of the record and shuts when all records are processed.
Eg. FOR smp_rec IN C1 LOOP
totalsal=totalsal+smp_recsal;
ENDLOOP;
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
Error handling part of PL/SQL block is called Exception. They have two types : user_defined and predefined.
DUP_VAL_ON_INDEX
ZERO_DIVIDE
NO_DATA_FOUND
TOO_MANY_ROWS
CURSOR_ALREADY_OPEN
INVALID_NUMBER
INVALID_CURSOR
PROGRAM_ERROR
TIMEOUT _ON_RESOURCE
STORAGE_ERROR
LOGON_DENIED
VALUE_ERROR
etc.
It is a procedure of package DBMS_STANDARD that allows issuing of user_defined error messages from database trigger or stored sub-program.
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);
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.
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.
A syntax error can be easily detected by a PL/SQL compiler. For eg, incorrect spelling.
A runtime error is handled with the help of exception-handling section in an PL/SQL block. For eg, SELECT INTO statement, which does not return any rows.
For a COMMIT statement, the following is true:
A ROLLBACK statement gets issued when the transaction ends, and the following is true.
It undoes all the work done by the user in a transaction. With SAVEPOINT, only part of transaction can be undone.
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.
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.
DECLARE statement is used by PL SQL anonymous blocks such as with stand alone, non-stored procedures. If it is used, it must come first in a stand alone file.
A maximum of 12 triggers can be applied to one table.
SQLCODE returns the value of the number of error for the last encountered error whereas SQLERRM returns the message for the last error.
the %ISOPEN cursor status variable can be used.
Cursor_Already_Open
Invaid_cursor
Implicit cursor is implicitly declared by Oracle. This is a cursor to all the DDL and DML commands that return only one row.
Explicit cursor is created for queries returning multiple rows.
The cursor attribute SQL%ROWCOUNT will return the number of rows that are processed by a SQL statement.
It returns the Boolean value TRUE if at least one row was processed.
It returns the Boolean value TRUE if no rows were processed.
A PL/SQL package can be specified as a file that groups functions, cursors, stored procedures, and variables in one place.
NVL converts NULL to another specified value.
var:=NVL(var2,Hi);
IS NULL and IS NOT NULL can be used to check specifically to see whether the value of a variable is NULL or not.
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.
DBMS_ series of packages, such as, DBMS_PIPE, DBMS_DDL, DBMS_LOCK, DBMS_ALERT, DBMS_OUTPUT, DBMS_JOB, DBMS_UTILITY, DBMS_SQL, DBMS_TRANSACTION, UTL_FILE.
When it comes to SQL, most of the tools and applications that you run in your operations division are based on SQL. If you want to pursue a career in the field of PL/SQL, then this might be the best career option available for you. As most of the companies nowadays use this platform for their benefits, they are in need of developers or software architects who are well versed with PL/SQL. Therefore, getting into a job in this field will help you in earning lakhs per month once you gain experience. For a fresher who is willing to start his career in this field can get up to 25,000 to 35,000 dollars per annum, while for an experienced employee, they can earn up to 95,000 to 110,000 dollars per annum easily.
Pursuing a career in this field is the best option for the freshers or currently graduated candidates. With the help of our PL SQL interview questions, it has become easier for the candidates to crack the interviews easily and sit in a dignified position in various companies.