Interview Questions.

Are you preparing for PL/SQL Interviews, if yes then we're listing here a listing of PL/SQL interview questions?

PL (Procedural language) is an extension to SQL (Structured Query Language) where a developer can write complex database interactions the use of approaches, manipulate structures like branching and new release, modules and features. It has tight integration with SQL and helps both dynamic and static SQL.

Amongst different questions during an interview, it is also critical to realize how SQL and PL/SQL are one-of-a-kind. Some benefits of PL/SQL over SQL are:

Supports procedural processing, conditional statements, looping and different features similar to excessive-level languages.

Multiple statements may be despatched to the database server right away within the shape of a method, as a result saving time and community traffic.

Customized errors handling is feasible.

Fully portable.

Apart from the above blessings, PL/SQL has a few more attractive features like capabilities, techniques, triggers, cursors that make it one of the most flexible relational databases.

Introduction

PL/SQL 1.0 became released in 1992 with Oracle 6 as an non-obligatory extension. It become with PL/SQL 2.Zero that stored methods, features, person-described record sorts, tables, applications and extensions like DBMS_* had been introduced. The modern PL/SQL model is eleven.0, which was released with Oracle 11g and got here with superior features inclusive of local compilation, PL/SQL feature end result cache, and minute dependency tracking.

Best PL/SQL Interview Questions & Answers

Below are a few commonplace fundamental and superior pl/square interview questions and answers which can be requested inside the interview by means of the interviewer

Question: What is PL/SQL?

Answer: A procedural language wherein code can be performed in blocks. It is an extension of SQL.

Question: What are the differences among PL/SQL and SQL?

Answer:

Question: What is the basic structure of PL/SQL?

Answer:

[DECLARE]
--declaration statements (optional)
BEGIN
--execution statements
[EXCEPTION]
--exception handling statements
END;

Question: Define cursor and its use.

Answer: A cursor is a pointer to a reminiscence location assigned by means of Oracle to technique SQL statements. The cursor is used to keep statistics again through the SQL question. There are 2 kinds of cursors – implicit and explicit.

Question: Why will we use database triggers? Give the syntax of a trigger.

Answer: The trigger is a saved technique this is automatically invoked when an event takes place. The occasion may be: insert, update, delete, and many others… Syntax –

create trigger [trigger_name]
[before | after]

on [table_name]
[for each row]
[trigger_body]

Question: How do you compile PL/SQL code?

Answer: Firstly, the syntax test is executed. When the developer corrects any syntax mistakes, Oracle binds all the variables holding facts with a garage cope with. Finally, the p-code generation method takes area.

Question: Explain exception managing in PL/SQL.

Answer: PL/SQL gives customized exception managing. When an error takes place, an mistakes coping with code is protected in the application itself. There are 3 styles of exceptions –

Pre-defined exceptions – common mistakes which are already defined. Example – NO_DATA_FOUND

Undefined exceptions – the mistakes that don't have predefined names.

User-defined exceptions – dealt with by using the code written through the user.

Question: Tell about a few information sorts in PL/SQL.

Answer: There are many data sorts –

Scalar sorts – primitive statistics kinds like CHAR, DATE, LONG, VARCHAR2 and so forth…

Composite – these are made from other information sorts and can be without difficulty updated. Example, RECORD, TABLE and so forth…

Reference statistics sorts like CURSOR

Large object sorts – BLOB, CLOB and many others…

Question: What is the distinction among %TYPE and %ROWTYPE? Give an instance.

Answer:

DECLARE
studentId
students.student_id%TYPE;

DECLARE
stud_rec
students.%ROWTYPE;

Question: What constitutes a PL/SQL package deal?

Answer: Packages are schema gadgets that location features, tactics, variables, etc… in a single area. Packages ought to have –

Package specs

Package frame

Question: List some schema gadgets which might be created using PL/SQL.

Answer: Database hyperlinks, triggers, stored tactics, features and programs, views, synonyms, external technique libraries, sequences, and so forth…

Check right here: Basic SQL Commands Checklist

Question: What are the diverse predefined exceptions?

Answer: Predefined exceptions are internally described exceptions that occur all through the execution of a application. For example, PL/SQL increases NO_DATA_FOUND while there aren't any rows again upon a choose operation, and if multiple row is again the use of a pick statement, TOO_MANY_ROWS mistakes is generated. Some extra examples:

COLLECTION_IS_NULL: while a collection is null

CURSOR_ALREADY_OPEN: When a cursor is already open

LOGIN_DENIED: When login is incorrect or permission isn't there

For the complete list of predefined exceptions, take a look at out Oracle docs.

Question: What is the distinction among syntax and runtime mistakes?

Answer:

Question: What are the various packages available for PL-SQL Developers?

Answer: The numerous programs to be had for PL/SQL builders are:

Source: Oracle medical doctors

Question: Explain Character Functions?

Answer: Character functions are functions that control person statistics. These are greater popularly called as string functions. Example:

Question: What is using SYSDATE and USER keywords? Explain with examples.

Answer: SYSDATE: returns the contemporary date and time on the nearby database server. The syntax is SYSDATE. If we ought to extract a part of the date, then we use the TO_CHAR feature. Examples:

SELECT SYSDATE FROM dual;
select customer_id, TO_CHAR(SYSDATE, 'yyyy/mm/dd') from customer where customer_id < 200;

USER: USER returns the user_id of the cutting-edge session.

Example:

select USER from dual;

Question: What is the difference among SGA and PGA?

Answer:

Question: Explain the uses of Merge with Syntax in PL-SQL.

Answer: Merge reduces the variety of desk scans and performs parallel operations if required. MERGE inserts or updates information conditionally from one desk to another. For example,

MERGE INTO orders o
 USING customer c
 ON (o.cust_id = c.cust_id)
 WHEN MATCHED THEN
 UPDATE SET o.del_address = c.address
 WHEN NOT MATCHED THEN
 INSERT (cust_id, address)
VALUES (c.emp_id, c.address);

In this example, if a report with the matching circumstance is found, then the address of the identical report is updated, else a brand new row is inserted.

Question: Explain the advantages of PL-SQL packages.

Answer: There are many benefits of the usage of PL/SQL applications:

Better code management as applications provide a container for sub-programs

Top-down software layout approach with separation of implementation and specs (interface)

If there are any adjustments to the subprograms, there is no need to exchange the dependent objects or recompile the entire bundle

Accessibility may be designated (private/public) for this reason preserving the safety of the code

Data can be maintained throughout transactions for the entire session with out storing in database

At the primary name of the subprogram, the complete bundle is loaded, so there may be no need for disk I/O for subsequent calls, hence giving better overall performance.

Question: Explain the difference between ROLLBACK and ROLLBACK TO statements?

Answer: ROLLBACK command rolls lower back all the changes from the beginning of the transaction. In ROLLBACK TO, the transaction is rolled lower back (or undone) only till a point known as the SAVEPOINT. The transactions earlier than the SAVEPOINT cannot be undone, and the transaction remains active even if the command is given.

Question: Explain the distinction among system and function.

Answer:

Question: Explain the difference between process and cause.

Answer:

Question: What are the special varieties of cursors in PL/SQL?

Answer: There are two varieties of cursors –

Implicit cursor – PL/SQL applies implicit cursors for INSERT, UPDATE, DELETE and SELECT statements returning a unmarried row.

Explicit cursor – created by way of a programmer for queries returning more than one row. Syntax–

CURSOR is
SELECT statement;
OPEN ;
FETCH INTO ;
CLOSE ;

Question: What are the special varieties of constraints?

Answer:

Not NULL

Unique

Primary key

Foreign key

Check

Question: What are the differences between triggers and constraints?

Answer:

Question: Explain the PL/SQL block with an example.

Answer: PL/SQL block includes 3 sections: statement, executable and exception-coping with sections. The executable phase is mandatory. There are  types of blocks: named and nameless.

Named blocks are functions and techniques which might be saved in the database server and may be reused. Anonymous blocks are for one time use and are not saved inside the server. Example:

DECLARE

 message VARCHAR2(255):= 'Welcome to PL/SQL';
 byzero NUMBER;

BEGIN

   DBMS_OUTPUT.put_line (message);
   byzero := 1/0;

   EXCEPTION

  WHEN ZERO_DIVIDE THEN
 DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

Question: Explain PL/SQL Records.

Answer: Records incorporate a fixed of facts of numerous information sorts that may be associated with each different as fields. Three forms of statistics that are supported in PL/SQL are desk-based records, programmer-based totally information, and cursor-based facts.

Question: Explain the difference among commit and savepoint.

Answer:

COMMIT – is used to make the database changes permanent. All the store factors are erased and the transaction ends. Once committed, a transaction can't be rolled back.

SAVEPOINT – is used to set factors at some point of a transaction to which a programmer can roll-returned later. It's far useful while there is a chain of transactions that may be divided into businesses having a savepoint.

Question: What is the difference among real and formal parameters?

Answer: The parameters that are used to name a procedure are known as as actual parameters. Example –

get_student_details(stud_name, dob); -- here stud_name and dob are actual parameters.

The variables declared in a process header used in the body are referred to as formal parameters. Example –

PROCEDURE get_student_details (dob DATE) IS – here stud_name is a formal parameter.

Question: How is a DECLARE announcement used?

Answer: DECLARE is used as the first announcement for stand-on my own documents that include anonymous block of code which are not saved tactics, features or triggers. Example –

DECLARE
num1 NUMBER(2);
num2 NUMBER(3);
BEGIN
-- logic goes here
END;

Question: Tell us approximately SQLCODE and SQLERRM.

Answer: SQLCODE and SQLERRM are used to trace exceptions that aren't explicitly treated within the program. These are globally described variables. SQLCODE returns the mistake code whilst SQLERRM returns the corresponding error message.

Question: What is rollback? How is it special from rollback to announcement?

Answer: Rollback erases all of the database changes which includes all of the savepoints. It ends a transaction.

‘Rollback to’ rollbacks the adjustments as much as the savepoint cited within the code. The transaction will nonetheless be lively.

Question: What is IN OUT parameter?

Answer: IN OUT parameter mode passes a price to a subprogram and returns an updated cost.

Question: Is it feasible to just accept person inputs at some point of runtime? How?

Answer: Yes, it is viable. Use ACCEPT keyword to take inputs from the user. Example –

ACCEPT age quantity activate ‘Enter your age:’

Question: Give a easy way to run a question faster.

Answer: By the use of ROWID. It is not a physical column however the logical cope with of a row. It consists of the block range, record quantity and row number thereby decreasing I/O time as a result making question execution quicker.

Question: What are some of the pre-defined exceptions in PL/SQL?

Answer: ZERO_DIVIDE, NO_DATA_FOUND, TOO_MANY_ROWS, INVALID_CURSOR, DUP_VAL_ON_INDEX etc…

Question: How do you trace the PL/SQL code?

Answer: You can trace through DBMS_* methods like

DBMS_APPLICATION_INFO

DBMS_TRACE

DBMS_SESSION and DBMS_MONITOR

Question: How to restrict string length in PL/SQL?

Answer: Use CHAR (NUMBER) to get constant period for a variable. Example – CHAR (10). If the length of the string is much less than the desired variety, it will be padded with white areas.

Question: What is the reason of the UTL_FILE package in PL/SQL?

Answer: By using this package deal, developers can get the code examine and write files to and from the laptop. For doing this, the developer will need get right of entry to supply from DBA consumer.

Question: What are DBMS_OUTPUT and DBMS_DEBUG?

Answer: Both can be used for debugging the code. DBMS_OUTPUT prints the output to console whereas DBMS_DEBUG prints it to a log file.

Question: List some cursor attributes in PL/SQL.

Answer:

%ISOPEN: Check if the cursor is open

%ROWCOUNT: Get the number of rows which can be updated, deleted or fetched.

%FOUND: Checks if the cursor has fetched any row, returns Boolean.

%NOT FOUND: Checks if the cursor has fetched any row. Returns Boolean.

Question: What is the cause of NVL?

Answer: NVL we could the programmer replacement a value for a NULL price. Example –

NVL (occupation, ‘default’)

Question: On a table, how many triggers can be applied?

Answer: 12 is the most number.

Question: How can we acquire consistency the use of PL/SQL?

Answer: We can attain consistency by placing the best isolation level. For example, to provide study consistency, the isolation degree can be set to READ COMMITTED.

Question: Write a simple process to pick out some information from the database the usage of a few parameters.

Answer: Example code –

CREATE PROCEDURE get_customer_details @age nvarchar(30), @city nvarchar(10)
AS

BEGIN
SELECT * FROM customers WHERE age = @age AND city = @city;
END;

Question: Explain the mistake ORA-03113.

Answer: The mistakes cease-of-record on communication channel ORA-03113 manner that there may be a damaged connection between the consumer and server channels. It may be a timeout due to which the connection become misplaced. You can troubleshoot with the aid of pinging the server and checking the connectivity.

Question: Can you use IF declaration internal a SELECT statement? How?

Answer: Yes, we will achieve this the use of the DECODE keyword in versions nine and above. Example –

SELECT day_of_week,
DECODE (number, 0, 'Sunday',
1, 'Monday',
2, 'Tuesday',
3, 'Wednesday',
4, 'Thursday',
5, 'Friday',
6, 'Saturday',
'No match') result FROM weekdays;

Question: What is SYS.ALL_DEPENDENCIES?

Answer: SYS.ALL_DEPENDENCIES describes all of the dependencies between packages, techniques, features, triggers accessible to the cutting-edge user. It shows columns like name, kind, dependency_type, referenced_owner, and many others…

Conclusion

In this text, we've got blanketed a few most critical pl/sq. Interview questions in an effort to absolutely get you thru the toughest of interviews. If you need to research the ideas in-depth and get a few palms-on enjoy, do take a look at our PL/SQL tutorials.

These interview questions will help you perform better in PL-SQL Interviews. Here are some greater PL-SQL interview questions in this udemy route: 2 hundred+ PL/SQL Interview Questions.

Also, Here is a great PL/SQL Interview questions book to prepare for an upcoming interview.

If do you've got a few other questions, which we've not included on this list, and also you want to recognise the solution to the ones questions, simply feedback under.