These PL / SQL interview questions are specifically designed to let you know the nature of the questions you may encounter during a PL / SQL interview.
In my experience, good interlocutors hardly plan to ask a specific question during an interview, usually, the questions start with the concept of the main topic and continue on the basis of further discussion and what you are answering.
What is PL / SQL? (Basic PL SQL Interview Questions )
PL / SQL (Procedural Language / SQL) is essentially a procedural Oracle – SQL extension. PL / SQL helps the user to build complex database applications using control structures, procedures, functions, modules and more.
Best PL SQL Interview Questions in India 2020
How to compile PL / SQL code?
The syntax check is performed first. When the developer corrects all syntax errors, Oracle associates all variables that contain data with the store address. Finally, the p code generation process takes place.
Explain exception handling in PL / SQL.
PL / SQL offers custom exception handling. When an error occurs, the error handling code is included in the program itself. There are 3 types of exceptions:
Pre-defined exceptions: common errors are already defined. Example: NO_DATA_FOUND
Undefined exceptions: errors without predefined names.
User-defined exceptions – handled by user-written code.
What is a PL / SQL package?
Packages are schema objects that hold functions, procedures, variables, and more in one place. Packages should contain –
List some schema objects created using PL / SQL.
Database links, activators, stored procedures, functions and packages, views, synonyms, external procedure libraries, sequences, and so on.
Explains different types of PL / SQL blocks.
A block is any group of PL / SQL code, such as SQL statements, loops, variables, constants, and so on. There are 2 block types:
Anonymous blocks: they do not have a title or name.
Named Blocks: These blocks have a header or label. They can be stored procedures, functions, activators or packages.
Explain PL / SQL records.
Records contain a set of different types of data that can be related to each other as fields. The three types of records supported by PL / SQL are table records, programmer records, and cursor records.
Explain the difference between a commitment and a savings point.
COMMIT – used to make database changes permanent. All save points are deleted and the operation ends. Once a transaction is completed, it cannot be canceled.
SAVEPOINT – Used to set points during an operation that the programmer can return to later. this is useful when there are many operations that can be grouped with savings points.
Explain the use of a database activator. ( IMP PL SQL Interview Questions)
A unit of PL / SQL program associated with a particular database table is called a database trigger. It is used:
1) Changes in audit data.
2) Record events transparently.
3) Apply complex business rules.
4) Maintain replication tables
5) Get the values of the columns
6) Implement complex security permits
What are the two types of exceptions.
The error handling part of the PL / SQL block is called an exception. They have two types: user_defined and predefined.
Show some predefined exceptions.
DATA NOT FOUND
This is a DBMS_STANDARD package procedure that allows you to pass user-defined error messages from a database activator or stored subroutine.
Show how functions and procedures are called PL SQL block.
A function is called a part of an expression.
total: = calculate_sal (‘b644’)
The procedure is called a PL / SQL statement.
calculate the bonus (‘b644’);
Explain the two virtual tables present at the time the database activator is run.
The columns in the table are named THEN.column_name and NOW.column_name.
Only NOW.column_name values can be used with INSERT-related activators.
Only THEN.column_name values can be used with DELETE-related activators.
For UPDATE-related links, both Table columns are available.
What is the most important feature of PL / SQL?
A list of some well-known features:
PL / SQL is a structured language.
It is portable in all environments that support Oracle.
PL / SQL is integrated into the Oracle data dictionary.
Stored methods will help in better application sharing.
What is the PL / SQL table? Why is it used? ( Application, PL SQL Interview Questions)
Table type objects are called PL / SQL tables modeled as database tables. We can also say that PL / SQL tables are a way to provide arrays. Arrays are like temporary memory tables that are processed very fast. PL / SQL tables are used to transfer bulk data. They simplify moving data collections.
What datatypes are available in PL / SQL?
There are two types of datatypes in PL / SQL:
Examples of scalar datatypes are NUMBER, VARCHAR2, DATE, CHAR, LONG, BOOLEAN etc.
Examples of mixed datatypes are RECORD, TABLE etc.
What is the basic structure of PL / SQL?
PL / SQL uses the BLOCK structure as its primary structure. Each PL / SQL program consists of a SQL statement and PL / SQL forming a PL / SQL block.
The PL / SQL block contains 3 sections.
The Expression Section (optional)
The Implementation Section (mandatory)
Exception handling section (optional)
What is the difference between FUNCTION, PROCEDURE, AND PACKAGE in PL / SQL?
Functionality: The main purpose of a PL / SQL function, in general, is to calculate and restore a single value. A function has a return type in its detail and must return the value specified in that type.
Procedure: A procedure has no return type and should not return any value but it can have a return statement that simply stops its implementation and returns to the caller. A method is used to return large amounts otherwise it is generally similar to a function.
Package: A package is the object of a scheme in which groups are logically associated with PL / SQL types, items, and subprograms. You can also say that it is a group of functions, methods, variables, and statements of the record type. It provides modularity, as this facility assists in application development. It is used to hide information from unauthorized users.
What is the exception? What are the types of exceptions?
The exception is a component in PL / SQL error handling. There are two types of exceptions: predefined exception and user_defined exclusion.
How to write a single statement that connects words? How are you? at? World? and assign it to a variable named Greeting?
Greetings: = ‘Hello’ || ‘World’;
What are the advantages of the stored method?
Modularity, extensibility, reusability, Maintainability, and one-time compilation.
What are the cursor properties used in PL / SQL?
% ISOPEN: it checks whether the cursor is open or not.
% ROWCOUNT: will return the number of rows affected by DML operations: INSERT, DELETE, UPDATE, SELECT.
% Found: it checks if the cursor has captured any row. If yes – TRUE.
% NOTFOUND: it checks if the cursor has captured any row. If not – REAL.
What is the difference between syntax error and runtime error?
A syntax error is easily detected by a PL / SQL compiler. For example misspelling etc. while, a runtime error can be handled with the help of the exception handling section in a PL / SQL block. Example: SELECT THE STATEMENT, which does not return any rows. Its practical Que. in Best PL SQL Interview Questions in India 2020.