Interview Questions

AJAX Interview Questions Android Interview Questions Angular 2 Interview Questions AngularJs Interview Questions Apache Presto Interview Questions Apache Tapestry Interview Questions Arduino Interview Questions ASP.NET MVC Interview Questions Aurelia Interview Questions AWS Interview Questions Blockchain Interview Questions Bootstrap Interview Questions C Interview Questions C Programming Coding Interview Questions C# Interview Questions Cakephp Interview Questions Cassandra Interview Questions CherryPy Interview Questions Clojure Interview Questions Cobol Interview Questions CodeIgniter interview Questions CoffeeScript Interview Questions Cordova Interview Questions CouchDB interview questions CSS Buttons Interview Questions CSS Interview Questions D Programming Language Interview Questions Dart Programming Language Interview Questions Data structure & Algorithm Interview Questions DB2 Interview Questions DBMS Interview Questions Django Interview Questions Docker Interview Questions DOJO Interview Questions Drupal Interview Questions Electron Interview Questions Elixir Interview Questions Erlang Interview Questions ES6 Interview Questions and Answers Euphoria Interview Questions ExpressJS Interview Questions Ext Js Interview Questions Firebase Interview Questions Flask Interview Questions Flex Interview Questions Fortran Interview Questions Foundation Interview Questions Framework7 Interview Questions FuelPHP Framework Interview Questions Go Programming Language Interview Questions Google Maps Interview Questions Groovy interview Questions GWT Interview Questions Hadoop Interview Questions Haskell Interview Questions Highcharts Interview Questions HTML Interview Questions HTTP Interview Questions Ionic Interview Questions iOS Interview Questions IoT Interview Questions Java BeanUtils Interview Questions Java Collections Interview Questions Java Interview Questions Java JDBC Interview Questions Java Multithreading Interview Questions Java OOPS Interview Questions Java Programming Coding Interview Questions Java Swing Interview Questions JavaFX Interview Questions JavaScript Interview Questions JCL (Job Control Language) Interview Questions Joomla Interview Questions jQuery Interview Questions js Interview Questions JSF Interview Questions JSP Interview Questions KnockoutJS Interview Questions Koa Interview Questions Laravel Interview Questions Less Interview Questions LISP Interview Questions Magento Interview Questions MariaDB Interview Questions Material Design Lite Interview Questions Materialize CSS Framework Interview Questions MathML Interview Questions MATLAB Interview Questions Meteor Interview Questions MongoDB interview Questions Moo Tools Interview Questions MySQL Interview Questions NodeJS Interview Questions OpenStack Interview Questions Oracle DBA Interview Questions Pascal Interview Questions Perl interview questions Phalcon Framework Interview Questions PhantomJS Interview Questions PhoneGap Interview Questions Php Interview Questions PL/SQL Interview Questions PostgreSQL Interview Questions PouchDB Interview Questions Prototype Interview Questions Pure CSS Interview Questions Python Interview Questions R programming Language Interview Questions React Native Interview Questions ReactJS Interview Questions RequireJs Interview Questions RESTful Web Services Interview Questions RPA Interview Questions Ruby on Rails Interview Questions SAS Interview Questions SASS Interview Questions Scala Interview Questions Sencha Touch Interview Questions SEO Interview Questions Servlet Interview Questions SQL Interview Questions SQL Server Interview Questions SQLite Interview Questions Struts Interview Questions SVG Interview Questions Swift Interview Questions Symfony PHP Framework Interview Questions T-SQL(Transact-SQL) Interview Questions TurboGears Framework Interview Questions TypeScript Interview Questions UiPath Interview Questions VB Script Interview Questions VBA Interview Questions WCF Interview Questions Web icon Interview Questions Web Service Interview Questions Web2py Framework Interview Questions WebGL Interview Questions Website Development Interview Questions WordPress Interview Questions Xamarin Interview Questions XHTML Interview Questions XML Interview Questions XSL Interview Questions Yii PHP Framework Interview Questions Zend Framework Interview Questions Network Architect Interview Questions

Top 25 PL/SQL Interview question for 2020

1) What is PL/SQL?

It is defined as SQL having Procedural features of Programming Language i.e. Procedural Language extension of SQL.

2) Enlist the section of PL/SQL block.

It consist of three block
  • Declaration Section
  • Execution Section
  • Exception Handling or Error Section

3) Give the advantage of PL/SQL.

  • Structured Block
  • Better Performance
  • Error Handling
  • Procedural Language Capability

4) Enlist the types of Data types in PL/SQL.

  • Scalar Data types: e.g. number, date, char, long, Boolean etc.
  • Composite Data types: e.g. Record, Table etc.

5) Write syntax to declare the variable?

Syntax:
DECLARE  
Var_name datatype [NOT NULL  := value]  
e.g. tut_example varchar2(10) NOT NULL  :="Best tutorial"
Syntax: to directly assign values to variable from database.
SELECT column_name  
INTO variable_name   
FROM table_name   
[WHERE condition];

6) Write some programming constructs supported by PL/SQL.

  • Variable and Constant
  • Flow Control
  • Cursor Management
  • Exception Handling
  • Triggers

7) Write single statement to concatenate Tutorials and Examples.

Website: = 'Tutorials' || 'Examples';

8) Give the precedence order among (AND, OR, NOT, NULL).

Highest order: NOT Lowest order: OR

9) What is Trigger in PL/SQL?

Trigger is initiated when query is fetched for any INSERT, UPDATE, DELETE etc where there is change in the structure of Database. It maintains the "Referral Integrity" of the PL/SQL.

10) Enlist the command that is use to create PL/SQL Packages.

  • CREATE PACKAGE command is used for creating the specification part.
  • BODY command is used for creating the body part.

11) What are the uses of Trigger?

  • Creating validation mechanisms
  • Creating logs to register the use of a table
  • Update other tables

12) Differentiate between Execution of Trigger and Stored Procedures ?

Trigger is executed automatically whereas Stored Procedures has to be invoked.

13) Which command is used to delete the package?

DELETE PACKAGE is used to delete the package.

14) Define CURSOR in PL/SQL.

It is a temporary area which is generated at the time of SQL statement execution in the system memory. It contains information about select statement and row data affected in database.

15) Enlist the types of cursor.

  • Implicit Cursor: It is created when DML statement is executed e.g. INSERT, UPDATE AND DELETE.
  • Explicit Cursor: It is created when we execute SELECT Statement which returns more then one rows.

16) Explain Raise_application_error.

It is a procedure of package DBMS_STANDARD that allows issuing of user_defined error messages from database trigger or stored sub-program.

17) When is a declare statement required?

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.

18) What is the importance of SQLCODE and SQLERRM?

SQLCODE returns the value of the number of error for the last encountered error whereas SQLERRM returns the message for the last error.

19) What packages are available to PL SQL developers?

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.

20) What is SQL*Loader?

SQL*Loader is a product for moving data in external files into tables in an Oracle database. To load data from external files into an Oracle database, two types of input must be provided to SQL*Loader : the data itself and the control file.

21) Explain the concept of exception.

An exception occurs when unwanted situation arises. The situation can be exceptional to normal functioning of the program. It can occur due to system error, user error and application error.

22) Define user defined exceptions.

We use user defined exception only when oracle doesn't raise its own exception. In this procedure we raise an exception by using RAISE command.

23) Define Row level trigger.

Row level trigger is fired each time a row is affected by DML statements like Insert, Update and Delete. When no rows affected, the trigger is not executed at all.

24) Define Statement level triggers.

It is fired when statement affects rows in a table but the processing required is completely independent of the number of rows affected.

25) Explain how can you save or place your messages in a table?

To save msg in a table, you can do it in two ways:
  • Load individual messages with calls to the add_text procedure.
  • Load sets of messages from a database table with the load_from_dbms procedure.