• phone icon +44 7459 302492 email message icon info@uplatz.com
  • Register

3.5 (166 reviews)
( 723 Students )
8 Hours


Oracle PL/SQL

you will learn about the fundamentals of Oracle PL/SQL programming language.
( add to cart )
Preview Oracle PL/SQL course
View Course Curriculum   Price Match Guarantee   Full Lifetime Access     Access on any Device   Technical Support    Secure Checkout   Course Completion Certificate
Instant access

Students also bought -

Completed the course? Request here for Certificate. ALL COURSES

PL/SQL is simply a Procedural Language extension to the Structured Query Language (SQL). SQL is a powerful language for both querying and updating data in relational databases.


Oracle created PL/SQL that extends some limitations of SQL to provide a more comprehensive solution for building mission-critical applications running on Oracle database. PL/SQL is a block structured language i.e. the programs in PL/SQL are logical blocks that can contain any number of nested sub-blocks.

PL/SQL includes procedural language elements like conditions and loops. It allows declaration of constants and variables, procedures and functions, types and variable of those types and triggers. It can support Array and handle exceptions (runtime errors). You can create PL/SQL units like procedures, functions, packages, types and triggers, etc. which are stored in the database for reuse by applications.

With PL/SQL, you can use SQL statements to manipulate Oracle data and flow of control statements to process the data. PL/SQL is known for its combination of data manipulating power of SQL with data processing power of procedural languages. It inherits the robustness, security, and portability of the Oracle Database.


In this Oracle PL/SQL course by Uplatz you will learn about the fundamentals of Oracle PL/SQL programming language. You will gain a solid understanding of Pl/SQL datatypes and programming constructs like loops, conditional execution, cursors and exception handling, which will enable you to do effective debugging.

Course/Topic 1 - Overview of PL/SQL

  • This video entails the terms and definitions used in Oracle PL/SQL. Here, we will also learn the structured Procedural language which enables the developer to combine the powers of SQL with its procedural statements.

    • 27:21
  • This video entails the terms and definitions used in Oracle PL/SQL. Here, we will also learn the structured Procedural language which enables the developer to combine the powers of SQL with its procedural statements.

    • 27:21

Course/Topic 2 - Declaring PL/SQL Variables

  • In the lecture you will learn about PL/SQL variables and how to use them effectively. In PL/SQL, a variable is named storage location that stores a value of a particular data type. The value of the variable changes through the program. Before using a variable, you must declare it in the declaration section of a block.

    • 13:02
  • In the lecture you will learn about PL/SQL variables and how to use them effectively. In PL/SQL, a variable is named storage location that stores a value of a particular data type. The value of the variable changes through the program. Before using a variable, you must declare it in the declaration section of a block.

    • 13:02

Course/Topic 3 - Writing Executable Statements

  • This video shows how an executable (not declarative) statement that. A sequence of statements can include procedural statements such as RAISE, SQL statements such as UPDATE, and PL/SQL blocks. PL/SQL statements are free format. That is, they can continue from line to line if you do not split keywords, delimiters, or literals across lines. A semicolon (;) serves as the statement terminator.

    • 13:07

Course/Topic 4 - Interacting with Oracle DB Server

  • In this video you will learn Oracle database operations at the most basic level. It illustrates an Oracle database configuration in which the user and associated server process are on separate computers, connected through a network.

    • 9:37

Course/Topic 5 - Writing Control Structure

  • In this video you will learn how to structure the flow of control through a PL/SQL program. You learn how statements are connected by simple but powerful control structures that have a single entry and exit point. Collectively, these structures can handle any situation. Their proper use leads naturally to a well-structured program.

    • 39:35

Course/Topic 6 - Working with Composite Data Types

  • In this video you will learn Composite types have internal components that can be manipulated individually, such as the elements of an array, record, or table. Oracle Times Ten In-Memory Database supports the following composite data types.

    • 43:14

Course/Topic 7 - Using Explicit Cursors

  • In this video, we will discuss the cursors in PL/SQL. Oracle creates a memory area, known as the context area, for processing an SQL statement, which contains all the information needed for processing the statement; for example, the number of rows processed, etc.

    • 22:03

Course/Topic 8 - Handling Exceptions

  • In this video you will learn, Exceptions in PL/SQL. An exception is an error condition during a program execution. PL/SQL supports programmers to catch such conditions using EXCEPTION block in the program and an appropriate action is taken against the error condition.

    • 22:34

Course/Topic 9 - Creating Procedures

  • In this video, we will discuss Procedures in PL/SQL. A subprogram is a program unit/module that performs a particular task. These subprograms are combined to form larger programs. This is basically called the 'Modular design'. A subprogram can be invoked by another subprogram or program which is called the calling program.

    • 15:39

Course/Topic 10 - Creating Functions

  • In this video, we will discuss the functions in PL/SQL. A function is same as a procedure except that it returns a value. Therefore, all the discussions of the previous video are true for functions too.

    • 12:37

Course/Topic 11 - Creating Packages

  • In this tutorial, you will learn how to create, compile, and execute a PL/SQL procedure from the Oracle SQL Developer.

    • 10:02

Course/Topic 12 - Working with Packages

  • In this video you will learn the Packages in PL/SQL. Packages are schema objects that groups logically related PL/SQL types, variables, and subprograms.

    • 10:27

Course/Topic 13 - Using Oracle-supplied Packages in Application Development

  • In this video you will learn about Oracle supplies, many PL/SQL packages with the Oracle server to extend database functionality and provide PL/SQL access to SQL features. You can use the supplied packages when creating your applications or for ideas in creating your own stored procedures.

    • 05:59

Course/Topic 14 - Using Dynamic SQL

  • In this video you will learn about using Dynamic SQL it is a programming methodology for generating and running statements at run-time. It is mainly used to write the general-purpose and flexible programs where the SQL statements will be created and executed at run-time based on the requirement.

    • 11:58

Course/Topic 15 - Design considerations for PL/SQL Code

  • This video explains the implementation and performance characteristics of the Oracle object-relational model. Use this information to map a logical data model into an Oracle physical implementation, and when developing applications that use object-oriented features.

    • 13:35

Course/Topic 16 - Creating Triggers

  • In this video you will learn about creating Triggers in PL/SQL. Triggers are stored programs, which are automatically executed or fired when some events occur.

    • 22:37

Course/Topic 17 - Using the PL/SQL Compiler

  • In this video you will learn about the PL/SQL compiler translates the code, assuming it is valid, into bytecode. Embedded SQL statements are modified before they are sent to the SQL compiler.

    • 16:00

Course/Topic 18 - Managing PL/SQL Code

  • In this video you will learn about the source code, how it is loaded into the data dictionary in a variety of forms (the text of the code, dependency relationships, parameter information, etc.). You can then use SQL statements to retrieve information about those program units, making it easier to understand and manage your application code.

    • 08:55

Course/Topic 19 - Managing Dependencies

  • In this video you will learn in addition to managing dependencies among schema objects, Oracle also manages dependencies of each shared SQL area in the shared pool. If a table, view, synonym, or sequence is created, altered, or dropped, or a procedure or package specification is recompiled, all dependent shared SQL areas are invalidated.

    • 12:35
Objectives Back to Top

Conditionally control code flow (loops, control structures).

Use explicit cursors.

Handle runtime errors.

Describe stored procedures and functions.

Work with composite data types.

Declare PL/SQL variables.

Create anonymous PL/SQL blocks, stored procedures and functions.

Declare identifiers and trap exceptions.

By the end of this course the student will be able to write simple and advanced PL/SQL code blocks, and will be able to use advanced features such as ref cursors and bulk fetches. 

The student will also become familiar with the internals of Oracle9i PL/SQL and will be able to compile PL/SQL for super-fast performance


Syllabus Back to Top

Overview of PL/SQL

1.     What is PL/SQL? What are the benefits?

2.     PL/SQL Block Structure & Types of Blocks

3.     Writing simple anonymous block

4.     Using SQL*plus to run anonymous block

5.     Creating Script and run it


Declaring PL/SQL Variables

1.     About Variables and the syntax

2.     Defining Variables exercise

3.     Guidelines for declaring & initializing variables

4.     Delimiters in string literals (the q' Notation)

5.     Types of variables and data types 1

6.     Types of variables and data types 2

7.     Data Types exercise

8.     Define Variables Using %TYPE

9.     Understating the SELECT statement in PL/SQL

10.  Bind Variables


Writing Executable Statements

1.     Inside PL/SQL Block

2.     Inside PL/SQL Block (exercise)

3.     Nested Blocks

4.     SQL & PL/SQL programming Guidelines


Interacting with Oracle DB Server

1.     SELECT statement & DML in PL/SQL

2.     SELECT statement & DML in PL/SQL exercise

3.     Understanding Implicit SQL cursor/ SQL Attributes

4.     Implicit SQL cursor exercise


Writing Control Structure

1.     IF Statement Part 1

2.     IF Statement Part 2

3.     Handling nulls in IF statement

4.     CASE expression & CASE Statement

5.     Basic Loop

6.     While Loop

7.     FOR Loop

8.     Nested Loops

9.     Nested Loops and Labels

10.  Continue Statement


Working with Composite Data Types

1.     PL/SQL Records: Programmer method part 1

2.     PL/SQL Records: Programmer method part 2

3.     PL/SQL Records: %ROWTYPE method

4.     Nested PL/SQL Records

5.     INDEX BY tables (Associative arrays)

6.     INDEX BY tables Methods

7.     INDEX BY tables of Records

8.     Nested table

9.     VARRAY


Using Explicit Cursors

1.     Understanding Explicit Cursor

2.     Explicit Cursor exercise

3.     Explicit Cursor Attributes

4.     FOR LOOP Cursor

5.     Cursor with Parameters

6.     FOR UPDATE Clause & CURRENT OF Clause


Handling Exceptions

1.     Understanding Exceptions

2.     Predefined Oracle Server Error Exception

3.     Common mistakes when dealing with exceptions

4.     Non-Predefined Oracle Server Error Exception

5.     User-Defined Error Exception

6.     Group Functions and exceptions

7.     Many blocks and many exceptions


Creating Procedures

1.     Understanding Procedures and subprograms

2.     Understanding Procedures Syntax

3.     Creating Procedure (IN parameter)

4.     Creating Procedure (OUT parameter)

5.     Creating Procedure (IN OUT parameter)

6.     Comparing the Parameter Modes

7.     Available notations for passing parameters

8.     Using the default option for Parameters

9.     Exception handling in multiple blocks

10.  Using Boolean and PL/SQL records as parameters


Creating Functions

1.     Understanding user-defined functions

2.     Difference between procedures & functions

3.     Creating function example

4.     No_data_found and functions

5.     Advantages of user-defined functions in SQL Statements

6.     Restrictions when calling functions from SQL part 1

7.     Restrictions when calling functions from SQL part 2


Creating Packages

1.     Understanding packages by nice example

2.     More about packages

3.     Creating package practice

4.     Creating package specification without body

5.     Guidelines for writing packages

6.     The visibility of a package’s components

7.     When & Why to recompile the package?


Working with Packages

1.     Understanding Overloading Subprograms in PL/SQL

2.     Overloading Subprograms practices (procedures)

3.     Overloading Subprograms practices (functions)

4.     Overloading Standard package

5.     Forward Declaration

6.     Persistent State of packages


8.     Persistent State and cursor

9.     using PL/SQL tables in packages


Using Oracle-supplied Packages in Application Development

1.     About oracle-supplied Packages

2.     DBMS_OUTPUT part 1

3.     DBMS_OUTPUT part 2

4.     UTL_FILE Package Part 1 / create directory

5.     UTL_FILE Package Part 2 / get_line example 1

6.     UTL_FILE Package Part 3 / get_line example 2

7.     UTL_FILE Package Part 4 / put_line

8.     UTL_FILE Package Part 5 / exceptions

9.     Mail


Using Dynamic SQL

1.     Execution flow of SQL

2.     What is Dynamic SQL?

3.     Execute immediate example 1 / delete any table

4.     Execute immediate example 2 / DDL in PLSQL

5.     Execute immediate example 3 / USING Clause

6.     Execute immediate example 4 / INTO Clause

7.     Execute immediate example 5 / single row query

8.     What is REF Cursor?

9.     Dynamic SQL with multi row query

10.  Execute immediate/ more examples

11.  DBMS_SQL Package VS Execute immediate


Design Consideration for PL/SQL Code

1.     Standardizing Part 1

2.     Standardizing Part 2

3.     Definer’s Rights

4.     Invoker’s Rights

5.     Autonomous Transactions

6.     Features Autonomous Transactions

7.     The (IN) parameters always passed by reference

8.     The (out/in out) parameters can be passed by value or reference

9.     Passing by reference example (IN parameters)

10.  Passing by value VS passing by reference using nocopy

11.  When Does NOCOPY hint has no effects?

12.  NOCOPY Performance Exercise

13.  Using the PARALLEL_ENABLE hint

14.  Using the RESULT_CACHE hint

15.  Using the DETERMINISTIC hint

16.  Understanding Bulk Binding

17.  Bulk Binding Example

18.  Bulk binding with save exception part 1

19.  Bulk binding with save exception part 2

20.  Understanding Bulk collect

21.  Bulk collect another example

22.  Bulk collect and cursors

23.  Using returning in bulk collect



Creating Triggers

1.     Understanding Triggers

2.     Tigger Types/ Trigger Event Types

3.     Statement Level trigger VS Row Level (Part 1)

4.     Statement Level trigger VS Row Level (Part 2)

5.     Triggers common cases

6.     Statement Level trigger Example 1

7.     Statement Level trigger Example 2

8.     Understanding the NEW & OLD qualifiers

9.     Row level trigger example

10.  Row level trigger (create Audit table)

11.  Trigger Firing sequence

12.  Compile/enable/ disable/ drop trigger

13.  Using the triggers for populating a default value

14.  Instead of triggers (on Views)


Creating Compound, DDL, and Event Database Triggers

1.     Follows Statement in triggers

2.     Understanding Compound triggers

3.     Understanding Mutating Table

4.     Solving Mutating Table issue using compound trigger

5.     Using Compound trigger & array to solve Mutating

6.     Mutating table & ON DELETE CASCADE

7.     Database Triggers VS Stored procedures

8.     Creating DDL triggers (Schema or Database)

9.     Create system event trigger

10.  Final comments about the triggers


Using the PL/SQL Compiler

1.     Introduction about PL/SQL compiler

2.     Understanding (plsql_code_type) parameter

3.     Understanding (plsql_optimize_level) parameter

4.     Understanding Warnings in PL/SQL

5.     Understanding (plsql_warnings) parameter

6.     More examples for plsql_warnings

7.     Using the package dbms_warning


Managing PL/SQL Code

1.     Understanding conditional compilation

2.     Understanding (dbms_db_version ) package

3.     conditional compilation examples

4.     Understanding plsql_ccflags part 1

5.     Understanding plsql_ccflags part 2

6.     Understanding plsql_ccflags part 3

7.     plsql_ccflags Real example

8.     create_wrapped

9.     The PL/SQL wrapper utility


Managing Dependencies

Certification Back to Top

This Oracle PL/SQL training course will help the participant to master the Oracle Certified PL/SQL Developer Exam. As a part of this Oracle PL/SQL training, the participants will master the procedural language extension.

In the Oracle Pl/SQL Course module, the participants can understand about the procedural language elements. In the Oracle Pl/SQL course, the participants will learn about the fundamentals of Pl/SQL programming.

Oracle Pl/SQL Certification is an important level in becoming a Pl/SQL Developer. As a part of the Oracle PL/SQL course training, the Oracle Pl/SQL Developer get trained in data processing extensions in the procedural language. Oracle Pl/SQL tutorial helps the participants to fulfil the role of a Pl/SQL Developer.

The Oracle Pl/SQL certification exam validates that the participants possess the basic concepts and demonstrate their skills in creating database-centric web applications for oracle databases. The process of becoming Oracle certified Pl/SQL Developer expands the participants knowledge and skills by getting exposed to Oracle Database features.

Uplatz provides appropriate teaching and expertise training is provided to equip the participants with skills to successfully implement the learnt concepts in an enterprise.

Course Completion Certificate will be awarded by Uplatz upon the completion of the Oracle Pl/SQL course training.

Below given are the Certification details of Oracle Database 11g: Program with Pl/SQL

· Certification Level: Associate

· Exam Name: Oracle Database 11g: Program with Pl/SQL

· Exam Code: 1Z0-144

· Exam Mode: Online

· Total Number of Questions: 63

· Pass Score: 65%

· Time Duration: 90 Minutes

· Exam Price: $330

Career & Jobs Back to Top

An Oracle Pl/SQL Developer draws an average salary of $116,293 per year depending on the knowledge and hands-on experience. The Oracle Pl/SQL Developer job roles are in high demand and make a rewarding career.

The Oracle Pl/SQL Developers have major demand in global-based companies and MNC’s. The adoption of the oracle database solution in global companies can open up a job opportunity easily. The Global companies hire Oracle Pl/SQL Developer considering the skill of building high-performance web applications for oracle database. The Learners earn the Oracle Pl/SQL Developer course completion certification through our expert training and course curriculum.

The Oracle Pl/SQL Developer course is ideally designed for individuals who possess none or strong understanding in SQL or PL/SQL fundamentals.

After pursuing Oracle Pl/SQL course, the participants can pursue a wide range of career paths.

The following are the job titles in Oracle PL/SQL field:

· SQL Developer Associate

· PL/SQL Application Developer

· PL/SQL Developer Associate

· SQL Database Developer

· Oracle Pl/SQL Professional

· Oracle DBA (Database Administrator)

· Oracle Pl/SQL Tuning Analyst

· Cloud Database Engineers


With this training, the participants will become highly skilled in Oracle PL/SQL and will be able to develop complex SQL and manage complex Oracle databases including the DaaS (Database-as-a-Service) cloud databases.

Interview Questions Back to Top

1) What is PL/SQL?

PL/SQL is a procedural language which has interactive SQL, as well as procedural programming language constructs like conditional branching and iteration.


2) Differentiate between % ROWTYPE and TYPE RECORD.

% ROWTYPE is used when a query returns an entire row of a table or view.

TYPE RECORD, on the other hand, is used when a query returns column of different tables or views.

Eg. TYPE r_emp is RECORD (sno smp.smpno%type,sname smp sname %type)

e_rec smp %ROWTYPE

Cursor c1 is select smpno,dept from smp;

e_rec c1 %ROWTYPE


3) Explain uses of cursor.

Cursor is a named private area in SQL from which information can be accessed. They are required to process each row individually for queries which return multiple rows.


4) Show code of a cursor for loop.

Cursor declares %ROWTYPE as loop index implicitly. It then opens a cursor, gets rows of values from the active set in fields of the record and shuts when all records are processed.

Eg. FOR smp_rec IN C1 LOOP




5) Explain the uses of database trigger.

A PL/SQL program unit associated with a particular database table is called a database trigger. It is used for :

a) Audit data modifications.

b) Log events transparently.

c) Enforce complex business rules.

d) Maintain replica tables

e) Derive column values

f) Implement Complex security authorizations


6) What are the two types of exceptions.

Error handling part of PL/SQL block is called Exception. They have two types : user_defined and predefined.


7) Show some predefined exceptions.















8) 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.


9) Show how functions and procedures are called in a PL/SQL block.

Function is called as a part of an expression.


Procedure is called as a statement in PL/SQL.



10) Explain two virtual tables available at the time of database trigger execution.

Table columns are referred as THEN.column_name and NOW.column_name.

For INSERT related triggers, NOW.column_name values are available only.

For DELETE related triggers, THEN.column_name values are available only.

For UPDATE related triggers, both Table columns are available.


11) What are the rules to be applied to NULLs whilst doing comparisons? 

1) NULL is never TRUE or FALSE

2) NULL cannot be equal or unequal to other values

3) If a value in an expression is NULL, then the expression itself evaluates to NULL except for concatenation operator (||)


12) How is a process of PL SQL compiled?

Compilation process includes syntax check, bind and p-code generation processes.

Syntax checking checks the PL SQL codes for compilation errors. When all errors are corrected, a storage address is assigned to the variables that hold data. It is called Binding. P-code is a list of instructions for the PL SQL engine. P-code is stored in the database for named blocks and is used the next time it is executed.


13) Differentiate between Syntax and runtime errors.

A syntax error can be easily detected by a PL/SQL compiler. For eg, incorrect spelling.

A runtime error is handled with the help of exception-handling section in an PL/SQL block. For eg, SELECT INTO statement, which does not return any rows.


14) Explain Commit, Rollback and Savepoint.

For a COMMIT statement, the following is true:

  • Other users can see the data changes made by the transaction.
  • The locks acquired by the transaction are released.
  • The work done by the transaction becomes permanent.

A ROLLBACK statement gets issued when the transaction ends, and the following is true.

  • The work done in a transition is undone as if it was never issued.
  • All locks acquired by transaction are released.

It undoes all the work done by the user in a transaction. With SAVEPOINT, only part of transaction can be undone.


15) Define Implicit and Explicit Cursors.

A cursor is implicit by default. The user cannot control or process the information in this cursor.

If a query returns multiple rows of data, the program defines an explicit cursor. This allows the application to process each row sequentially as the cursor returns it.


16) Explain mutating table error.

It occurs when a trigger tries to update a row that it is currently using. It is fixed by using views or temporary tables, so database selects one and updates the other.


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) How many triggers can be applied to a table?

A maximum of 12 triggers can be applied to one table.


19) 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.


20) If a cursor is open, how can we find in a PL/SQL Block?

the %ISOPEN cursor status variable can be used.


21) Show the two PL/SQL cursor exceptions.




22) What operators deal with NULL?

NVL converts NULL to another specified value.


IS NULL and IS NOT NULL can be used to check specifically to see whether the value of a variable is NULL or not.


23) Does SQL*Plus also have a PL/SQL Engine?

No, SQL*Plus does not have a PL/SQL Engine embedded in it. Thus, all PL/SQL code is sent directly to database engine. It is much more efficient as each statement is not individually stripped off.


24) What packages are available to PL SQL developers?



25) Explain 3 basic parts of a trigger.

  • A triggering statement or event.
  • A restriction
  • An action


26) What are character functions?

INITCAP, UPPER, SUBSTR, LOWER and LENGTH are all character functions. Group functions give results based on groups of rows, as opposed to individual rows. They are MAX, MIN, AVG, COUNT and SUM.


27) Explain TTITLE and BTITLE.

TTITLE and BTITLE commands that control report headers and footers.


28) Show the cursor attributes of PL/SQL.

%ISOPEN : Checks if the cursor is open or not

%ROWCOUNT : The number of rows that are updated, deleted or fetched.

%FOUND : Checks if the cursor has fetched any row. It is true if rows are fetched

%NOT FOUND : Checks if the cursor has fetched any row. It is True if rows are not fetched.


29) What is an Intersect?

Intersect is the product of two tables and it lists only matching rows.


30) What are sequences?

Sequences are used to generate sequence numbers without an overhead of locking. Its drawback is that the sequence number is lost if the transaction is rolled back.


31) How would you reference column values BEFORE and AFTER you have inserted and deleted triggers?

Using the keyword "new.column name", the triggers can reference column values by new collection. By using the keyword "old.column name", they can reference column vaues by old collection.


32) What are the uses of SYSDATE and USER keywords?

SYSDATE refers to the current server system date. It is a pseudo column. USER is also a pseudo column but refers to current user logged onto the session. They are used to monitor changes happening in the table.


33) How does ROWID help in running a query faster?

ROWID is the logical address of a row, it is not a physical column. It composes of data block number, file number and row number in the data block. Thus, I/O time gets minimized retrieving the row, and results in a faster query.


34) What are database links used for?

Database links are created in order to form communication between various databases, or different environments like test, development and production. The database links are read-only to access other information as well.


35) What does fetching a cursor do?

Fetching a cursor reads Result Set row by row.


36) What does closing a cursor do?

Closing a cursor clears the private SQL area as well as de-allocates memory


37) Explain the uses of Control File.

It is a binary file. It records the structure of the database. It includes locations of several log files, names and timestamps. They can be stored in different locations to help in retrieval of information if one file gets corrupted.


38) Explain Consistency

Consistency shows that data will not be reflected to other users until the data is commit, so that consistency is maintained.


39) Differ between Anonymous blocks and sub-programs.

Anonymous blocks are unnamed blocks that are not stored anywhere whilst sub-programs are compiled and stored in database. They are compiled at runtime.


40) Differ between DECODE and CASE.

DECODE and CASE statements are very similar, but CASE is extended version of DECODE. DECODE does not allow Decision making statements in its place.

select decode(totalsal=12000,'high',10000,'medium') as decode_tesr from smp where smpno in (10,12,14,16);

This statement returns an error.

CASE is directly used in PL SQL, but DECODE is used in PL SQL through SQL only.


41) Explain autonomous transaction.

An autonomous transaction is an independent transaction of the main or parent transaction. It is not nested if it is started by another transaction.

There are several situations to use autonomous transactions like event logging and auditing.


42) Differentiate between SGA and PGA.

SGA stands for System Global Area whereas PGA stands for Program or Process Global Area. PGA is only allocated 10% RAM size, but SGA is given 40% RAM size.


43) What is the location of Pre_defined_functions.

They are stored in the standard package called "Functions, Procedures and Packages".


44) Explain polymorphism in PL SQL.

Polymorphism is a feature of OOP. It is the ability to create a variable, an object or function with multiple forms. PL/SQL supports Polymorphism in the form of program unit overloading inside a member function or package..Unambiguous logic must be avoided whilst overloading is being done.


45) What are the uses of MERGE?

MERGE is used to combine multiple DML statements into one.

Syntax : merge into tablename


on(join condition)

when not matched then

[insert/update/delete] command

when matched then

[insert/update/delete] command


46) Can 2 queries be executed simultaneously in a Distributed Database System?

Yes, they can be executed simultaneously. One query is always independent of the second query in a distributed database system based on the 2 phase commit.


47) Explain Raise_application_error.

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


48) What is out parameter used for eventhough return statement can also be used in pl/sql?

Out parameters allows more than one value in the calling program. Out parameter is not recommended in functions. Procedures can be used instead of functions if multiple values are required. Thus, these procedures are used to execute Out parameters.


49) How would you convert date into Julian date format?

We can use the J format string :

SQL > select to_char(to_date('29-Mar-2013','dd-mon-yyyy'),'J') as julian from dual;



50) Explain SPOOL

Spool command can print the output of sql statements in a file.


select smp_name, smp_id from smp where dept='accounts';

spool off;


51) Mention what PL/SQL package consists of?

A PL/SQL package consists of

  • PL/SQL table and record TYPE statements
  • Procedures and Functions
  • Cursors
  • Variables ( tables, scalars, records, etc.) and constants
  • Exception names and pragmas for relating an error number with an exception
  • Cursors


52) Mention what are the benefits of PL/SQL packages?

It provides several benefits like

  • Enforced Information Hiding: It offers the liberty to choose whether to keep data private or public
  • Top-down design: You can design the interface to the code hidden in the package before you actually implemented the modules themselves
  • Object persistence: Objects declared in a package specification behaves like a global data for all PL/SQL objects in the application. You can modify the package in one module and then reference those changes to another module
  • Object oriented design: The package gives developers strong hold over how the modules and data structures inside the package can be used
  • Guaranteeing transaction integrity: It provides a level of transaction integrity
  • Performance improvement: The RDBMS automatically tracks the validity of all program objects stored in the database and enhance the performance of packages.


53) Mention what are different methods to trace the PL/SQL code?

Tracing code is a crucial technique to measure the code performance during the runtime. Different methods for tracing includes

  • trcsess and tkproof utilities


54) Mention what does the hierarchical profiler does?

The hierarchical profiler could profile the calls made in PL/SQL, apart from filling the gap between the loopholes and the expectations of performance tracing. The efficiencies of the hierarchical profiler includes

  • Distinct reporting for SQL and PL/SQL time consumption
  • Reports count of distinct sub-programs calls made in the PL/SQL, and the time spent with each subprogram call
  • Multiple interactive analytics reports in HTML format by using the command line utility
  • More effective than conventional profiler and other tracing utilities


55) Mention what does PLV msg allows you to do?

The PLV msg enables you to

  • Assign individual text message to specified row in the PL/SQL table
  • It retrieves the message text by number
  • It substitutes automatically your own messages for standard Oracle error messages with restrict toggle
  • Batch load message numbers and text from a database table directly PLV msg PL/SQL table


56) Mention what is the PLV (PL/Vision) package offers?

  • Null substitution value
  • Set of assertion routines
  • Miscellaneous utilities
  • Set of constants used throughout PL vision
  • Pre-defined datatypes


57) Mention what is the use of PLVprs and PLVprsps?

  • PLVprs: It is an extension for string parsing for PL/SQL, and it is the lowest level of string parsing functionality
  • PLVprsps: It is the highest level package to parse PL/SQL source code into separate atomics. It relies on other parsing packages to get work done.


58) Explain how you can copy a file to file content and file to PL/SQL table in advance PL/SQL?

With a single program call - "fcopy procedure", you can copy the complete contents of one file into another file. While to copy the contents of a file directly into a PL/SQL table, you can use the program "file2pstab".


59) Explain how exception handling is done in advance PL/SQL?

For exception handling PL/SQl provides an effective plugin PLVexc. PLVexc supports four different exception handling actions.

  • Continue processing
  • Record and then continue
  • Halt processing
  • Record and then halt processing

For those exceptions that re-occurs you can use the RAISE statement.


60) Mention what problem one might face while writing log information to a data-base table in PL/SQL?

While writing log information to a database table, the problem you face is that the information is only available only once the new rows are committed to the database. This might be a problem as such PLVlog is usually deployed to track errors and in many such instances the current transaction would fail or otherwise needed a rollback.


61) Mention what is the function that is used to transfer a PL/SQL table log to a database table?

To transfer a PL/SQL table log a database log table function "PROCEDURE ps2db" is used.


62) When you have to use a default "rollback to" savepoint of PLVlog?

The default "rollback to" savepoint of PLVlog is used when the users has turned on the rollback activity and has not provided an alternative savepoint in the call to put_line. The default savepoint is initialized to the c none constant.


63) Why PLVtab is considered as the easiest way to access the PL/SQL table?

The PL/SQL table are the closest to arrays in PL/SQL, and in order to access this table you have to first declare a table type, and then you have to declare PL/SQL table itself. But by using PLVtab, you can avoid defining your own PL/SQL table type and make PL/SQL data-table access easy.


64) Mention what does PLVtab enables you to do when you showthe contents of PL/SQL tables?

PLVtab enables you to do following things when you show the contents of PL/SQL tables

  • Display or suppress a header for the table
  • Display or suppress the row numbers for the table values
  • Show a prefix before each row of the table


65) Explain how can you save or place your msg 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


66) Mention what is the use of function "module procedure" in PL/SQL?

The "module procedure" enables to convert all the lines of code in a definite program unit with one procedure call. There are three arguments for modules

  • module_in
  • cor_in
  • Last_module_in


67) Mention what PLVcmt and PLVrb does in PL/SQL?

PL/Vision offers two packages that help you manage transaction processing in PL/SQL application. It is PLVcmt and PLVrb.

  • PLVcmt: PLVcmt package wraps logic and complexity for dealing with commit processing
  • PLVrb: It provides a programmatic interface to roll-back activity in PL/SQL
Q1. What are the payment options?
A1. We have multiple payment options: 1) Book your course on our webiste by clicking on Buy this course button on top right of this course page 2) Pay via Invoice using any credit or debit card 3) Pay to our UK or India bank account 4) If your HR or employer is making the payment, then we can send them an invoice to pay.

Q2. Will I get certificate?
A2. Yes, you will receive course completion certificate from Uplatz confirming that you have completed this course with Uplatz. Once you complete your learning please submit this for to request for your certificate https://training.uplatz.com/certificate-request.php

Q3. How long is the course access?
A3. All our video courses comes with lifetime access. Once you purchase a video course with Uplatz you have lifetime access to the course i.e. forever. You can access your course any time via our website and/or mobile app and learn at your own convenience.

Q4. Are the videos downloadable?
A4. Video courses cannot be downloaded, but you have lifetime access to any video course you purchase on our website. You will be able to play the videos on our our website and mobile app.

Q5. Do you take exam? Do I need to pass exam? How to book exam?
A5. We do not take exam as part of the our training programs whether it is video course or live online class. These courses are professional courses and are offered to upskill and move on in the career ladder. However if there is an associated exam to the subject you are learning with us then you need to contact the relevant examination authority for booking your exam.

Q6. Can I get study material with the course?
A6. The study material might or might not be available for this course. Please note that though we strive to provide you the best materials but we cannot guarantee the exact study material that is mentioned anywhere within the lecture videos. Please submit study material request using the form https://training.uplatz.com/study-material-request.php

Q7. What is your refund policy?
A7. Please refer to our Refund policy mentioned on our website, here is the link to Uplatz refund policy https://training.uplatz.com/refund-and-cancellation-policy.php

Q8. Do you provide any discounts?
A8. We run promotions and discounts from time to time, we suggest you to register on our website so you can receive our emails related to promotions and offers.

Q9. What are overview courses?
A9. Overview courses are 1-2 hours short to help you decide if you want to go for the full course on that particular subject. Uplatz overview courses are either free or minimally charged such as GBP 1 / USD 2 / EUR 2 / INR 100

Q10. What are individual courses?
A10. Individual courses are simply our video courses available on Uplatz website and app across more than 300 technologies. Each course varies in duration from 5 hours uptop 150 hours. Check all our courses here https://training.uplatz.com/online-it-courses.php?search=individual

Q11. What are bundle courses?
A11. Bundle courses offered by Uplatz are combo of 2 or more video courses. We have Bundle up the similar technologies together in Bundles so offer you better value in pricing and give you an enhaced learning experience. Check all Bundle courses here https://training.uplatz.com/online-it-courses.php?search=bundle

Q12. What are Career Path programs?
A12. Career Path programs are our comprehensive learning package of video course. These are combined in a way by keeping in mind the career you would like to aim after doing career path program. Career path programs ranges from 100 hours to 600 hours and covers wide variety of courses for you to become an expert on those technologies. Check all Career Path Programs here https://training.uplatz.com/online-it-courses.php?career_path_courses=done

Q13. What are Learning Path programs?
A13. Learning Path programs are dedicated courses designed by SAP professionals to start and enhance their career in an SAP domain. It covers from basic to advance level of all courses across each business function. These programs are available across SAP finance, SAP Logistics, SAP HR, SAP succcessfactors, SAP Technical, SAP Sales, SAP S/4HANA and many more Check all Learning path here https://training.uplatz.com/online-it-courses.php?learning_path_courses=done

Q14. What are Premium Career tracks?
A14. Premium Career tracks are programs consisting of video courses that lead to skills required by C-suite executives such as CEO, CTO, CFO, and so on. These programs will help you gain knowledge and acumen to become a senior management executive.

Q15. How unlimited subscription works?
A15. Uplatz offers 2 types of unlimited subscription, Monthly and Yearly. Our monthly subscription give you unlimited access to our more than 300 video courses with 6000 hours of learning content. The plan renews each month. Minimum committment is for 1 year, you can cancel anytime after 1 year of enrolment. Our yearly subscription gives you unlimited access to our more than 300 video courses with 6000 hours of learning content. The plan renews every year. Minimum committment is for 1 year, you can cancel the plan anytime after 1 year. Check our monthly and yearly subscription here https://training.uplatz.com/online-it-courses.php?search=subscription

Q16. Do you provide software access with video course?
A16. Software access can be purchased seperately at an additional cost. The cost varies from course to course but is generally in between GBP 20 to GBP 40 per month.

Q17. Does your course guarantee a job?
A17. Our course is designed to provide you with a solid foundation in the subject and equip you with valuable skills. While the course is a significant step toward your career goals, its important to note that the job market can vary, and some positions might require additional certifications or experience. Remember that the job landscape is constantly evolving. We encourage you to continue learning and stay updated on industry trends even after completing the course. Many successful professionals combine formal education with ongoing self-improvement to excel in their careers. We are here to support you in your journey!

Q18. Do you provide placement services?
A18. While our course is designed to provide you with a comprehensive understanding of the subject, we currently do not offer placement services as part of the course package. Our main focus is on delivering high-quality education and equipping you with essential skills in this field. However, we understand that finding job opportunities is a crucial aspect of your career journey. We recommend exploring various avenues to enhance your job search:
a) Career Counseling: Seek guidance from career counselors who can provide personalized advice and help you tailor your job search strategy.
b) Networking: Attend industry events, workshops, and conferences to build connections with professionals in your field. Networking can often lead to job referrals and valuable insights.
c) Online Professional Network: Leverage platforms like LinkedIn, a reputable online professional network, to explore job opportunities that resonate with your skills and interests.
d) Online Job Platforms: Investigate prominent online job platforms in your region and submit applications for suitable positions considering both your prior experience and the newly acquired knowledge. e.g in UK the major job platforms are Reed, Indeed, CV library, Total Jobs, Linkedin.
While we may not offer placement services, we are here to support you in other ways. If you have any questions about the industry, job search strategies, or interview preparation, please dont hesitate to reach out. Remember that taking an active role in your job search process can lead to valuable experiences and opportunities.