Interview Questions.

PL-SQL Interview Questions and Answers

fluid

PL-SQL Interview Questions and Answers

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:

SQL PL/SQL
SQL is a query language to interact with the database. It is an extension of SQL which supports procedures, functions and many more features.
Supports only simple queries that can perform insert, update, delete on tables. Can perform complex tasks like a high-level programming language, for example, while loop, if-else statements, etc…
SQL statements can be executed only one at a time, thereby making it a time-consuming process. The entire block of statements is sent to the database server at once to be executed, saving time and increasing efficiency.
No provision for error handling. Customized error handling is possible.

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:

%TYPE %ROWTYPE
The attribute that declares a variable of the same data type as of a table column. The attribute that declares a variable of type RECORD having the same structure as a table row. The row is the RECORD that contains fields having the same data types and names as the columns of a table or view.
Example –
DECLARE
studentId
students.student_id%TYPE;

 

Example –
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:

Syntax error Runtime error
These are compile-time errors found by the compiler. These are not detected by the compiler and cause the program to give an incorrect result.
The code doesn't build and run until these issues are resolved. The code is compiled and run, and if an error occurs, the program stops halfway.
Some examples are missing semicolons or brackets (;, {}), incorrect spelling of classes, keywords etc. Examples are null pointer exceptions, dividing a number by zero, array index out of bounds, etc.
int x = 9 String name = null; In the first line, a semicolon is missing which the compiler will catch String name = null; if(name.equals(“hackr.io”)){….} Since name is null, the exception will be caught during runtime when the code is executed

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

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

DBMS_ALERT alert an application using triggers when particular database values change. The alerts are transaction-based and asynchronous.
DBMS_OUTPUT display output from PL/SQL blocks, packages, subprograms and triggers. Mostly used for displaying PL/SQL debugging information.
DBMS_PIPE different sessions communicate over named pipes using this package. The procedures PACK_MESSAGE and SEND_MESSAGE pack a message into a pipe, then send it to another session.
HTF and HTP allow PL/SQL programs to generate HTML tags.
UTL_FILE lets PL/SQL programs read and write OS text files.
UTL_HTTP allows your PL/SQL programs to make hypertext transfer protocol (HTTP) callouts. The package has two entry points, each of which accepts a URL (uniform resource locator) string, contacts the specified site, and returns the requested data, which is usually in HTML format.
UTL_SMTP allows PL/SQL programs to send emails over SMTP.

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:

LEFT Returns the mentioned number of characters from left of a string. LEFT(value, NoOfChars). Example LEFT('Hackr', 4) will give Hack.
RIGHT return specified number of characters from right. RIGHT(value, NoOfChars). Example RIGHT('banker', 2) will return er.
SUBSTRING selects data from any part of the string. SUBSTRING(value, StartPosition, NoOfChars). Example SUBSTRING('hackr.io',0,4) will return hackr.
LTRIM trims white spaces from the left. Example LTRIM(' hackr.io') will return hackr.io.
RTRIM trims white spaces from the right. Example RTRIM('hackr.io ') will return hackr.io.
UPPER converts all the characters to uppercase. Example UPPER('hackr.io') returns HACKR.IO.
LOWER converts all the characters to lowercase. Example LOWER('HACKR.IO') returns hackr.io.

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:

SGA PGA
System Global Area Program Global Area
Contains data and control information for one Oracle database instance Contains data and control information exclusively for a single Oracle process
Shared memory region for components Non-shared memory region
example: cached data blocks and SQL areas Example: session memory, SQL work area

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:

Function Procedure
The function is compiled every time

it is called for execution.

Procedures are pre-compiled and saved. They execute the pre-compiled code whenever called.
Can be called from SQL statements. Can not be called from SQL statements.
The function has to return a value. Need not return any value.
Generally used for computation purpose. Used for executing complex business logic.
Can return multiple values using other methods, otherwise, return only a single value. Can return multiple values
Returns scalar data types. Returns an int by default.
A stored procedure can not be called from a function The procedure can call any function
Functions can be embedded in a select statement Inside a select statement, a procedure cannot be called.
Exception handling is not possible Try/catch block can be defined inside a procedure

Question: Explain the difference between process and cause.

Answer:

PROCEDURE TRIGGER
Called explicitly by a user, trigger or an application Executed by the DBMS whenever an event occurs in the database.
Can have parameters Doesn’t have parameters
Cannot be inactive Can be enabled or disabled on need basis
Creation – CREATE PROCEDURE Creation – CREATE TRIGGER

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:

TRIGGERS CONSTRAINTS
Stored as separate objects A constraint on a table is stored along with the table definition
Triggers are fired upon an event; hence they are fired after constraints Constraints are fired as soon as the

the table is used.

Perform table to table

comparison, hence faster

Performs memory location to table the comparison which is slow leading to low performance.
Trigger is for the entire table The constraint is for a column of the table
They are just stored procedures that get automatically executed, hence don’t check for data integrity. Prevent duplicate and invalid data entries

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.




CFG