Oracle Interview Questions - PL/SQL

Types of Triggers in Oracle

Triggers can be classified based on the following parameters.

• Classification based on the timing
• BEFORE Trigger: It fires before the specified event has occurred.
• AFTER Trigger: It fires after the specified event has occurred.
• INSTEAD OF Trigger: A special type.  It is used only in DML triggers. It is used when any DML event is going to occur on the complex view.

• Classification based on the level
  • STATEMENT level Trigger: It fires one time for the specified event statement.
  • ROW level Trigger: It fires for each record that got affected in the specified event. (only for DML)
• Classification based on the Event
• DML Trigger: It fires when the DML event is specified (INSERT/UPDATE/DELETE)
• DDL Trigger: It fires when the DDL event is specified (CREATE/ALTER)
• DATABASE Trigger: It fires when the database event is specified (LOGON/LOGOFF/STARTUP/SHUTDOWN)

Cursor Attributes  

Every explicit cursor and cursor variable has four attributes: %FOUND, %ISOPEN %NOTFOUND, and %ROWCOUNT. The SQL cursor has additional attributes, %BULK_ROWCOUNT and %BULK_EXCEPTIONS, designed for use with the FORALL statement.

S.No	Attribute & Description
1	%FOUND Returns TRUE if an INSERT, UPDATE, or DELETE statement affected one or more rows or a SELECT INTO statement returned one or more rows. Otherwise, it returns FALSE.
2	%NOTFOUND The logical opposite of %FOUND. It returns TRUE if an INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement returned no rows. Otherwise, it returns FALSE.
3	%ISOPEN Always returns FALSE for implicit cursors, because Oracle closes the SQL cursor automatically after executing its associated SQL statement.
4	%ROWCOUNT Returns the number of rows affected by an INSERT, UPDATE, or DELETE statement, or returned by a SELECT INTO statement.

Loop  : 

S.No	Loop Type & Description
1	PL/SQL Basic LOOP In this loop structure, sequence of statements is enclosed between the LOOP and the END LOOP statements. At each iteration, the sequence of statements is executed and then control resumes at the top of the loop. An EXIT statement or an EXIT WHEN statement is required to break the loop.
2	PL/SQL WHILE LOOP Repeats a statement or group of statements while a given condition is true. It tests the condition before executing the loop body.
3	PL/SQL FOR LOOP Execute a sequence of statements multiple times and abbreviates the code that manages the loop variable.
4	Nested loops in PL/SQL You can use one or more loop inside any another basic loop, while, or for loop.

More PL/Sql Questions : 

https://www.guru99.com/pl-sql-interview-questions-answers.html

What is BULK COLLECT?

BULK COLLECT reduces context switches between SQL and PL/SQL engine and allows SQL engine to fetch the records at once.

Oracle PL/SQL provides the functionality of fetching the records in bulk rather than fetching one-by-one. This BULK COLLECT can be used in 'SELECT' statement to populate the records in bulk or in fetching the cursor in bulk. Since the BULK COLLECT fetches the record in BULK, the INTO clause should always contain a collection type variable. The main advantage of using BULK COLLECT is it increases the performance by reducing the interaction between database and PL/SQL engine.

Syntax:

SELECT <columnl> BULK COLLECT INTO bulk_varaible FROM <table name>;

FETCH <cursor_name> BULK COLLECT INTO <bulk_varaible >;

In the above syntax, BULK COLLECT is used in collect the data from 'SELECT' and 'FETCH' statement.

The FORALL allows to perform the DML operations on data in bulk. It is similar to that of FOR loop statement except in FOR loop things happen at the record-level whereas in FORALL there is no LOOP concept. Instead the entire data present in the given range is processed at the same time.

Syntax:

FORALL <loop_variable>in<lower range> .. <higher range> 

<DML operations>;

In the above syntax, the given DML operation will be executed for the entire data that is present between lower and higher range.

LIMIT Clause

The bulk collect concept loads the entire data into the target collection variable as a bulk i.e. the whole data will be populated into the collection variable in a single-go. But this is not advisable when the total record that needs to be loaded is very large, because when PL/SQL tries to load the entire data it consumes more session memory. Hence, it is always good to limit the size of this bulk collect operation.

Procedure Vs Function :

Procedure	Function
Used mainly to a execute certain process	Used mainly to perform some calculation
Cannot call in SELECT statement	A Function that contains no DML statements can be called in SELECT statement
Use OUT parameter to return the value	Use RETURN to return the value
It is not mandatory to return the value	It is mandatory to return the value
RETURN will simply exit the control from subprogram.	RETURN will exit the control from subprogram and also returns the value
Return datatype will not be specified at the time of creation	Return datatype is mandatory at the time of creation

What is CAST function ? 

The purpose of the Oracle CAST function is to convert one data type to another.

Case Vs Decode 

• Everything DECODE can do, CASE can. There is a lot else CASE can do though, which DECODE cannot.  
• DECODE performs an equality check only. CASE is capable of other logical comparisons such as < > etc. 

Difference between char, varchar and varchar2 in oracle : 

CHAR : 

CHAR should be used for storing fixed length character strings. String values will be space/blank padded before stored on disk. If this type is used to store variable length strings, it will waste a lot of disk space.

VARCHAR : 

Currently VARCHAR behaves exactly the same as VARCHAR2. However, this type should not be used as it is reserved for future usage. Internally oracle creates VARCHAR2 type even when you use VARCHAR in table DDL.

VARCHAR2 : 

VARCHAR2 is used to store variable length character strings. The string value's length will be stored on disk with the value itself.

What is the difference between normal cursor and ref cursor ?

The main difference between a normal cursor and a ref cursor is that a ref cursor can be passed to another PL/SQL routine or returned to a client. A normal cursor cannot be passed to a client application and must be consumed within the same routine.

What is the difference between packages and procedures ?

A package is a higher-level organizational unit that contains related procedures, functions, variables, and other program elements grouped together.

A procedure is a named block of code that performs a specific task or a sequence of actions.

What is pipelined table function ?

what is dbms_lock used for?

The DBMS_LOCK package in a database management system (DBMS) provides a way to manage locks at a more granular level than the built-in locking mechanisms of the DBMS. It allows you to explicitly control locks on arbitrary resources within a database.

What package dbms_application_info is used for?