All about SQL PL/SQL and LINUX: 2020

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

totalsal=totalsal+smp_recsal;

ENDLOOP;

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:

1) Audit data modifications.

2) Log events transparently.

3) Enforce complex business rules.

4) Maintain replica tables

5) Derive column values

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

DUP_VAL_ON_INDEX

ZERO_DIVIDE

NO_DATA_FOUND

TOO_MANY_ROWS

CURSOR_ALREADY_OPEN

INVALID_NUMBER

INVALID_CURSOR

PROGRAM_ERROR

TIMEOUT _ON_RESOURCE

STORAGE_ERROR

LOGON_DENIED

VALUE_ERROR

etc.

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.

total:=calculate_sal('b644')

Procedure is called as a statement in PL/SQL.

calculate_bonus('b644');

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.

Cursor_Already_Open

Invaid_cursor

22) What operators deal with NULL?

NVL converts NULL to another specified value.

var:=NVL(var2,'Hi');

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?

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.

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

using(query)

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;

JULIAN

50) Explain SPOOL

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

spool/tmp/sql_outtxt

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

DBMS_APPLICATION_INFO
DBMS_TRACE
DBMS_SESSION and DBMS_MONITOR
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

What is PL/SQL?

PL/SQL (Procedural Language/SQL) is basically a procedural extension of Oracle – SQL. PL/SQL helps the user to develop complex database applications using control structures, procedures, function, modules, etc.

This article will discuss the top-most PL/SQL interview question and answers.

PL/SQL Interview Questions And Answers

1) Differentiate PL/SQL and SQL?

Difference between SQL and PL/SQL can be categorized as follows

SQL	PL/SQL
SQL is a natural language which is very useful for interactive processing.	PL/SQL is a procedural extension of Oracle - SQL.
No procedural capabilities like condition testing, looping is offered by SQL.	PL/SQL supports procedural capabilities as well as high language features such as conditional statements, looping statements, etc.
All SQL statements are executed by the database server one at a time, thus it is a time-consuming process.	PL/SQL statements send the entire block of statements to the database server at the same time, thus network traffic is reduced considerably.
No error handling procedures are there in SQL.	PL/SQL supports customized error handling.

2) Enlist the characteristics of PL/SQL?

Characteristics of PL/SQL are as follows

PL/SQL allows access and sharing of the same sub programs by multiple applications.
PL/SQL is known for portability of code as code can be executed on any operating system provided Oracle is loaded on it.
With PL/SQL user can write their own customized error handling routines.
Improved transaction performance with integration to Oracle data dictionary.

3) What are the data types available in PL/SQL?

Data types define the ways to identify the type of data and their associated operations. There are 4 types of predefined data types explained as follows

Scalar Data Types: A scalar data type is an atomic data type that does not have any internal components.
For example

· CHAR (fixed length character value between 1 and 32,767 characters)

· VARCHAR2 (variable length character value between 1 and 32,767 characters)

· NUMBER ( fixed-decimal, floating-decimal or integer values)

· BOOLEAN ( logical data type for TRUE FALSE or NULL values)

· DATE (stores date and time information)

· LONG (character data of variable length)

Composite Data Types: A composite data type is made up of other data types and internal components that can be easily used and manipulated. For example RECORD, TABLE, and VARRAY.
Reference Data Types: A reference data types holds values, called pointers that designate to other program items or data items. For example REF CURSOR.
Large Object Data Types: A Large Object datatype holds values, called locators, that defines the location of large objects( such as video clips, graphic image, etc) stored out of line.
For example

· BFILE (Binary file)

· BLOB (Binary large object)

· CLOB ( Character large object)

· NCLOB( NCHAR type large object)

4) Explain the purpose of %TYPE and %ROWTYPE data types with the example?

PL/SQL uses %TYPE declaration attribute for anchoring. This attribute provides the datatype of a variable, constant or column. %TYPE attribute is useful while declaring a variable that has the same datatype as a table column.

For example, the variable m_empno has the same data type and size as the column empno in table emp.

m_empno emp.empno%TYPE;

%ROWTYPE attribute is used to declare a variable to be a record having the same structure as a row in a table. The row is defined as a record and its fields have the same names and data types as the columns in the table or view.

For example: dept_rec dept%ROWTYPE;

This declares a record that can store an entire row for DEPT table.

5) What do you understand by PL/SQL packages?

PL/SQL packages are schema objects that groups functions, stored procedures, cursors and variables at one place. Packages have 2 mandatory parts

Package Specifications
Package body

6) What do you understand by PL/SQL cursors?

PL/SQL requires a special capability to retrieve and process more than one row and that resource is known as Cursors. A cursor is a pointer to the context area, which is an area of memory containing SQL statements and information for processing the statements.

PL/SQL Cursor is basically a mechanism under which multiple rows of the data from the database are selected and then each row is individually processed inside a PL/SQL program.

7) Explain cursor types?

There are two types of cursors. They are explained as follows

1) Explicit Cursors: For queries that return more than one row, an explicit cursor is declared and named by a programmer. In order to use explicit cursor in PL/SQL, 4 steps are followed

Declare the cursor

Syntax: CURSOR <cursor_name> is
SELECT statement;

Where <cursor_name> is the name assigned to the cursor and SELECT statement is the query that returns rows to the cursor active set.

Open the cursor

Syntax: OPEN <cursor_nam>;

Where, <cursor_name> is the name of the previously defined cursor.

Fetch rows from the cursor

Syntax: FETCH <cursor_name> INTO <record_list>;

Where <cursor_name> refers to the name of the previously defined cursor from which rows are being fetched.

<record_list> represents the list of variables that will receive the data being fetched.

Closing the cursor

Syntax: CLOSE <cursor_name>;

Where <cursor_name> is the name of the cursor being closed.

2) Implicit cursors: When any SQL statement is executed, PL/SQL automatically creates a cursor without defining such cursors are known as implicit cursors.

For following statements, PL/SQL employs implicit cursors

INSERT
UPDATE
DELETE
SELECT ( queries that return exactly one row)

8) When do we use triggers?

The word ‘Trigger’ means to activate. In PL/SQL, the trigger is a stored procedure that defines an action taken by the database when database related event is performed. Triggers are mainly required for the following purposes

To maintain complex integrity constraints
Auditing table information by recording the changes
Signaling other program actions when changes are made to table
Enforcing complex business rules
Preventing invalid transactions

9) Explain the difference in execution of triggers and stored procedures?

A stored procedure is executed explicitly by issuing procedure call statement from another block via a procedure call with arguments.

The trigger is executed implicitly whenever any triggering event like the occurrence of DML statements happens.

10) Explain the difference between Triggers and Constraints?

Triggers are different from constraints in the following ways

Triggers	Constraints
Only affect those rows added after the trigger is enabled.	Affect all rows of the table including that already exist when the constraint is enabled.
Triggers are used to implement complex business rules which cannot be implemented using integrity constraints.	Constraints maintain the integrity of the database.

11) What is a PL/SQL block?

In PL/SQL, statements are grouped into units called Blocks. PL/SQL blocks can include constants, variables, SQL statements, loops, conditional statements, exception handling. Blocks can also build a procedure, a function or a package.

Broadly, PL/SQL blocks are two types

1) Anonymous blocks: PL/SQL blocks without header are known as anonymous blocks. These blocks do not form the body of a procedure, function or triggers.

Example:

	DECLARE
	num NUMBER(2);

	sq NUMBER(3);
	BEGIN

	num:= &Number1;
	sq := num*num;

	DBMS_OUTPUT.PUT_LINE(‘Square:’ \|\|sq);
	END;

2) Named blocks: PL/SQL blocks having header or labels are known as Named blocks. Named blocks can either be subprograms (procedures, functions, packages) or Triggers.

Example:

	FUNCTION sqr (num IN NUMBER)
	RETURN NUMBER is sq NUMBER(2);

	BEGIN
	sq:= num*num;

	RETURN sq;
	END;

12) Differentiate between syntax and runtime errors?

Syntax errors are the one which can be easily identified by a PL/SQL compiler. These errors can be the spelling mistake, etc.

Runtime errors are those errors in PL/SQL block for which exception handling section is to be included for handling the errors. These errors can be SELECT INTO statement which does not return any rows.

13) What are COMMIT, ROLLBACK, and SAVEPOINT?

COMMIT, SAVEPOINT, and ROLLBACK are three transaction specifications available in PL/SQL.

COMMIT statement: When DML operation is performed, it only manipulates data in database buffer and the database remains unaffected by these changes. To save/store these transaction changes to the database, we need to COMMIT the transaction. COMMIT transaction saves all outstanding changes since the last COMMIT and the following process happens

Affected rows locks are released
Transaction marked as complete
Transaction detail is stored in the data dictionary.

Syntax: COMMIT;

ROLLBACK statement: When we want to undo or erase all the changes that have occurred in the current transaction so far, we require rolling back of the transaction. In other words, ROLLBACK erases all outstanding changes since the last COMMIT or ROLLBACK.

Syntax to rollback a transaction fully

ROLLBACK;

SAVEPOINT statement: The SAVEPOINT statement gives a name and marks a point in the processing of the current transaction. The changes and locks that have occurred before the SAVEPOINT in the transaction are preserved while those that occur after the SAVEPOINT are released.

Syntax:

SAVEPOINT <savepoint_name>;

14) What is the mutating table and constraining table?

A table which is currently being modified by a DML statement like defining triggers in a table is known as a Mutating table.

A table that might need to be read from for a referential integrity constraint is known as constraining table.

15) What are actual parameters and formal parameters?

The variables or an expression referred to as parameters that appear in the procedure call statement is known as Actual parameters.

For example: raise_sal(emp_num, merit+ amount);

Here in the above example, emp_num and amount are the two actual parameters.

The variables that are declared in the procedure header and are referenced in the procedure body are called as Formal parameters.

For example:

PROCEDURE raise_sal( emp_id INTEGER) IS
curr_sal REAL:
………..
BEGIN
SELECT sal INTO cur_sal FROM emp WHERE empno = emp_id;
…….
END raise_sal;

Here in the above example, emp_id acts as a formal parameter.

16) What is the difference between ROLLBACK and ROLLBACK TO statements?

The transaction is completely ended after ROLLBACK statement i.e. ROLLBACK command completely undoes a transaction and release all locks.

On the other hand, a transaction is still active and running after ROLLBACK TO command as it undoes a part of the transaction up till the given SAVEPOINT.

17) Write a PL/SQL script to display the following series of numbers: 99,96,93……9,6,3?

	SET SERVER OUTPUT ON
	DECLARE

	BEGIN
	FOR i IN REVERSE 1..99

	LOOP
	IF Mod(i,3) = 0 THEN

	DBMS_OUTPUT.PUT_LINE(i);
	END IF;

	END LOOP;
		END;

18) What are the 3 modes of parameter?

3 modes of the parameter are IN, OUT, IN OUT. These can be explained as follows

IN parameters: IN parameters allow you to pass values to the procedure being called and can be initialized to default values. IN parameters acts like a constant and cannot be assigned any value.

OUT parameters: OUT parameters return value to the caller and they must be specified. OUT parameters act like an uninitialized variable and cannot be used in an expression.

IN OUT parameters: IN OUT parameters passes initial values to a procedure and returns updated values to the caller. IN OUT parameters act like an initialized variable and should be assigned a value.

19) Why is %ISOPEN always false for an implicit cursor?

An implicit cursor, SQL%ISOPEN attribute is always false because the implicit cursor is opened for a DML statement and is closed immediately after the execution of DML statement.

20) When a DML statement is executed, in which cursor attributes, the outcome of the statement is saved?

The outcome of the statement is saved in 4 cursor attributes. These are

SQL%FOUND
SQL%NOTFOUND
SQL%ROWCOUNT
SQL%ISOPEN

21) What are the ways on commenting in a PL/SQL code?

Comments are the text which is included with the code to enhance readability and for the understanding of the reader. These codes are never executed. There are two ways to comment in PL/SQL

1) Single line comment: This comment starts with double –.

Example:
DECLARE
num NUMBER(2); — it is a local variable.
BEGIN

2) Multi-line comment: This comment starts with /* and ends with */.

Example:
BEGIN
num := &p_num; /* This is a host variable used in program body */
……….
END

22) What do you understand by Exception handling in PL/SQL?

When an error occurs in PL/SQL, the exception is raised. In other words, to handle undesired situations where PL/SQL scripts terminated unexpectedly, an error handling code is included in the program. In PL/SQL, all exception handling code is placed in EXCEPTION section.

There are 3 types of EXCEPTION:

Predefined Exceptions: Common errors with predefined names.
Undefined Exceptions: Less common errors with no predefined names.
User-defined Exceptions: Do not cause runtime error but violate business rules.

23) Enlist some predefined exceptions?

Some of the predefined exceptions are

NO_DATA_FOUND: Single row SELECT statement where no data is returned.
TOO_MANY_ROWS: Single row SELECT statement where more than one rows are returned.
INVALID_CURSOR: Illegal cursor operation occurred.
ZERO_DIVIDE: Attempted to divide by zero.

24) What are PL/SQL cursor exceptions?

The exceptions related to PL/SQL cursors are

CURSOR_ALREADY_OPEN
INVALID_CURSOR

25) Explain the difference between cursor declared in procedures and cursors declared in the package specification?

The cursor declared in the procedure is treated as local and thus cannot be accessed by other procedures.

The cursor declared in the package specification is treated as global and thus can be accessed by other procedures.

26) What are INSTEAD of triggers?

The INSTEAD OF triggers are the triggers written especially for modifying views, which cannot be directly modified through SQL DML statements.

27) What are expressions?

Expressions are represented by a sequence of literals and variables that are separated by operators. In PL/SQL, operations are used to manipulate, compare and calculate some data. An expression is a composition of ‘Operators’ and ‘Operands’.

Operands: These are an argument to the operators. Operands can be a variable, function call or constant.
Operators: These specify the actions to be performed on operators. E.g. ‘+’, ‘*’, etc.

28) List different type of expressions with the example.

Expressions can be as mentioned below

Numeric or Arithmetic expressions : e.g. 20* 10+ 15
Boolean expressions: e.g. ‘spot’ LIKE ‘sp%t’
String expressions: e.g. LENGTH (‘NEW YORK’|| ‘NY’)
Date expressions: e.g. SYSDATE>TO_DATE(’15-NOV-16’, “dd-mm-yy”)

29) Write a program that shows the usage of WHILE loop to calculate the average of user entered numbers and entry of more numbers are stopped by entering number 0?

	DECLARE
	n NUMBER;

	avg NUMBER :=0 ;
	sum NUMBER :=0 ;

	count NUMBER :=0 ;
	BEGIN

	n := &enter_a_number;
	WHILE(n<>0)

	LOOP
		count := count+1;

	sum := sum+n;
	n := &enter_a_number;

	END LOOP;
	avg := sum/count;

	DBMS_OUTPUT.PUT_LINE(‘the average is’\|\|avg);
	END;

30) What do you understand by PL/SQL Records?

A PL/SQL records can be referred as a collection of values or say, a group of multiple pieces of information, each of which is of simpler types and can be related to one another as fields.

There are three types of records supported in PL/SQL

Table based records
Programmer based records
Cursor based records

What is PL/SQL?

A procedural language where code can be executed in blocks. It is an extension of SQL.

What are the differences between 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.

What is the basic structure of PL/SQL?

Answer:

[DECLARE]

--declaration statements (optional)

BEGIN

--execution statements

[EXCEPTION]

--exception handling statements

END;

Define cursor and its use.

A cursor is a pointer to a memory area assigned by Oracle to process SQL statements. The cursor is used to hold records returned by the SQL query. There are 2 types of cursors – implicit and explicit.

Why do we use database triggers? Give the syntax of a trigger.

The trigger is a stored procedure that is automatically invoked when an event happens. The event could be: insert, update, delete, etc… Syntax –

create trigger [trigger_name]

[before | after]

on [table_name]

[for each row]

[trigger_body]

How do you compile PL/SQL code?

Firstly, the syntax check is performed. When the developer corrects any syntax errors, Oracle binds all the variables holding data with a storage address. Finally, the p-code generation process takes place.

Explain exception handling in PL/SQL.

PL/SQL offers customized exception handling. When an error occurs, an error handling code is included in the program itself. There are 3 types of exceptions –

Pre-defined exceptions – common errors that are already defined. Example – NO_DATA_FOUND
Undefined exceptions – the errors that do not have predefined names.
User-defined exceptions – handled by the code written by the user.

Tell about a few data types in PL/SQL.

There are many data types –

Scalar types – primitive data types like CHAR, DATE, LONG, VARCHAR2 etc…
Composite – these are made up of other data types and can be easily updated. Example, RECORD, TABLE etc…
Reference data types like CURSOR
Large object types – BLOB, CLOB etc…

What is the difference between %TYPE and %ROWTYPE? Give an example.

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;

What constitutes a PL/SQL package?

Packages are schema objects that place functions, procedures, variables, etc… in one place. Packages should have –

Package specifications
Package body

List some schema objects that are created using PL/SQL.

Database links, triggers, stored procedures, functions and packages, views, synonyms, external procedure libraries, sequences, etc…

Explain the difference between procedure 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

Explain the difference between procedure and trigger.

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

What are the different types of cursors in PL/SQL?

There are two types of cursors –

Implicit cursor – PL/SQL applies implicit cursors for INSERT, UPDATE, DELETE and SELECT statements returning a single row.
Explicit cursor – created by a programmer for queries returning more than one row. Syntax–

CURSOR  is

SELECT statement;

OPEN ;

FETCH  INTO ;

CLOSE ;

What are the different types of constraints?

Answer:

Not NULL
Unique
Primary key
Foreign key
Check

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

Explain different types of PL/SQL blocks.

Block is any group of PL/SQL code like SQL statements, loops, variables, constants etc… There are 2 types of blocks –

Anonymous blocks – these do not have a header or name.
Named blocks – these blocks have header or label. They can be stored procedures, functions, triggers or packages.

Explain PL/SQL Records.

Records contain a set of data of various data types that can be related to each other as fields. Three types of records that are supported in PL/SQL are table-based records, programmer-based records, and cursor-based records.

Explain the difference between commit and savepoint.

Answer:

COMMIT – is used to make the database changes permanent. All the save points are erased and the transaction ends. Once committed, a transaction cannot be rolled back.

SAVEPOINT – is used to set points during a transaction to which a programmer can roll-back later. it is helpful when there is a series of transactions that can be divided into groups having savepoint.

What is the difference between actual and formal parameters?

The parameters that are used to call a procedure are called as actual parameters. Example –

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

The variables declared in a procedure header used in the body are called formal parameters. Example –

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

How is a DECLARE statement used?

DECLARE is used as the first statement for stand-alone files that consist of anonymous block of code which are not stored procedures, functions or triggers. Example –

DECLARE

num1 NUMBER(2);

num2 NUMBER(3);

BEGIN

-- logic goes here

END;

Explain about SQLCODE and SQLERRM.

SQLCODE and SQLERRM are used to trace exceptions that are not explicitly handled in the program. These are globally defined variables. SQLCODE returns the error code while SQLERRM returns the corresponding error message.

What is rollback? How is it different from rollback to statement?

Rollback erases all the database changes including all the savepoints. It ends a transaction.

‘Rollback to’ rollbacks the changes up to the savepoint mentioned in the code. The transaction will still be active.

What is IN OUT parameter?

IN OUT parameter mode passes a value to a subprogram and returns an updated value.

Is it possible to accept user inputs during runtime? How?

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

ACCEPT age number prompt ‘Enter your age:’

Give a simple way to run a query faster.

By using ROWID. It is not a physical column but the logical address of a row. It contains the block number, file number and row number thereby reducing I/O time hence making query execution faster.

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

ZERO_DIVIDE, NO_DATA_FOUND, TOO_MANY_ROWS, INVALID_CURSOR, DUP_VAL_ON_INDEX etc…

How do you trace the PL/SQL code?

You can trace through DBMS_* methods like

DBMS_APPLICATION_INFO
DBMS_TRACE
DBMS_SESSION and DBMS_MONITOR

How to restrict string length in PL/SQL?

Use CHAR (NUMBER) to get fixed length for a variable. Example – CHAR (10). If the length of the string is less than the specified number, it will be padded with white spaces.

What is the purpose of the UTL_FILE package in PL/SQL?

By using this package, developers can get the code read and write files to and from the computer. For doing this, the developer will need access grant from DBA user.

What are DBMS_OUTPUT and DBMS_DEBUG?

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

List some cursor attributes in PL/SQL.

Answer:

%ISOPEN: Check if the cursor is open
%ROWCOUNT: Get the number of rows that are 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.

What is the purpose of NVL?

NVL lets the programmer substitute a value for a NULL value. Example –

NVL (occupation, ‘default’)

On a table, how many triggers can be applied?

12 is the maximum number.

How can we achieve consistency using PL/SQL?

We can achieve consistency by setting the appropriate isolation level. For example, to give read consistency, the isolation level can be set to READ COMMITTED.

Write a simple procedure to select some records from the database using some parameters.

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;

Explain the error ORA-03113.

The error end-of-file on communication channel ORA-03113 means that there is a broken connection between the client and server channels. It could be a timeout because of which the connection was lost. You can troubleshoot by pinging the server and checking the connectivity.

Can you use IF statement inside a SELECT statement? How?

Yes, we can do so using the DECODE keyword in versions 9 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;

What is SYS.ALL_DEPENDENCIES?

SYS.ALL_DEPENDENCIES describes all the dependencies between packages, procedures, functions, triggers accessible to the current user. It shows columns like name, type, dependency_type, referenced_owner, etc…

Q1. What is the difference between PL SQL and SQL?

Ans.

PL SQL vs SQL
Comparison	SQL	PL/SQL
Execution	Single command at a time	Block of code
Application	Source of data to be displayed	Application created by data acquired by SQL
Structures include	DDL and DML based queries and commands	Includes procedures, functions, etc
Recommended while	Performing CRUD operations on data	Creating applications to display data obtained using SQL
Compatibility with each other	SQL can be embedded into PL/SQL	PL/SQL cant be embedded in SQL

Q2. What is SQL and also describe types of SQL statements?

Ans: SQL stands for Structured Query Language. SQL is a language used to communicate with the server to access, manipulate and control data.

There are 5 different types of SQL statements.

1. Data Retrieval: SELECT

2. Data Manipulation Language (DML): INSERT, UPDATE, DELETE, MERGE

3. Data Definition Language (DDL): CREATE, ALTER, DROP, RENAME, TRUNCATE.

4. Transaction Control Statements: COMMIT, ROLLBACK, SAVEPOINT

5. Data Control Language (DCL): GRANT, REVOKE

Q3. What is an alias in SQL statements?

Ans: Alias is a user-defined alternative name given to the column or table. By default column alias headings appear in upper case. Enclose the alias in a double quotation marks (“ “) to make it case sensitive. “AS” Keyword before the alias name makes the SELECT clause easier to read.

For ex: Select emp_name AS name from employee; (Here AS is a keyword and “name” is an alias).

Q4. What is a Literal? Give an example of where it can be used?

Ans: A Literal is a string that can contain a character, a number, or a date that is included in the SELECT list and that is not a column name or a column alias. Date and character literals must be enclosed within single quotation marks (‘ ‘), number literals need not.

For ex: Select last_name||’is a’||job_id As “emp details” from the employee; (Here “is a” is a literal).

Q5. What is the difference between SQL and iSQL*Plus?

*SQL Vs iSQLPlus**
SQL	*iSQLPlus**
Is a Language	Is an Environment
Character and date columns heading are left-justified and number column headings are right-justified.	Default heading justification is in Centre.
Cannot be Abbreviated (short forms)	Can be Abbreviated
Does not have a continuation character	Has a dash (-) as a continuation character if the command is longer than one line
Use Functions to perform some formatting	Use commands to format data

Q6. Define the order of Precedence used in executing SQL statements.

Order of Precedence used in executing SQL statements
Order Evaluated	Operator
1	Arithmetic operators (*, /, +, -)
2	Concatenation operators (\|\|)
3	Comparison conditions
4	Is[NOT] NULL, LIKE, [NOT] IN
5	[NOT] BETWEEN
6	NOT Logical condition
7	AND logical condition
8	OR logical condition

Q7. What are SQL functions? Describe in brief different types of SQL functions?

Ans: SQL Functions are a very powerful feature of SQL. SQL functions can take arguments but always return some value.
There are two distinct types of SQL functions:

1) Single-Row functions: These functions operate on a single row to give one result per row.

Types of Single-Row functions:

1. Character

2. Number

3. Date

4. Conversion

5. General

2) Multiple-Row functions: These functions operate on groups of rows to give one result per group of rows.

Types of Multiple-Row functions:

1. AVG

2. COUNT

3. MAX

4. MIN

5. SUM

6. STDDEV

7. VARIANCE

Q8. Explain character, number and date function in detail?

Ans: Character functions: accept character input and return both character and number values. Types of character function are:

a) Case-Manipulation Functions: LOWER, UPPER, INITCAP

b) Character-Manipulation Functions: CONCAT, SUBSTR, LENGTH, INSTR, LPAD/RPAD, TRIM, REPLACE

Number Functions: accept Numeric input and return numeric values. Number Functions are: ROUND, TRUNC and MOD

Date Functions: operates on values of the Date data type. (All date functions return a value of DATE data type except the MONTHS_BETWEEN Function, which returns a number. Date Functions are MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY, ROUND, TRUNC.

Q9. What is a Dual Table?

Ans: Dual table is owned by the user SYS and can be accessed by all users. It contains one columnDummy and one row with the value X. The Dual Table is useful when you want to return a value only once. The value can be a constant, pseudocolumn or expression that is not derived from a table with user data.

Q10. Explain Conversion function in detail?

Ans: Conversion Functions converts a value from one data type to another. Conversion functions are of two types:

Implicit Data type conversion:

1. VARCHAR2 or CHAR To NUMBER, DATE

2. NUMBER To VARCHAR2

3. DATE To VARCHAR2

Explicit data type conversion:

1. TO_NUMBER

2. TO_CHAR

3. TO_DATE

TO_NUMBER function is used to convert Character string to Number format. TO_NUMBER function use fx modifier. Format: TO_NUMBER ( char[, ‘ format_model’] ). fx modifier specifies the exact matching for the character argument and number format model of TO_NUMBER function.

TO_CHAR function is used to convert NUMBER or DATE data type to CHARACTER format. TO_CHAR Function use fm element to remove padded blanks or suppress leading zeros. TO_CHAR Function formats:TO_CHAR (date, ‘format_model’).Format model must be enclosed in single quotation marks and is case sensitive.

For ex: Select TO_CHAR (hiredate, ‘MM/YY’) from employee.

TO_DATE function is used to convert Character string to date format. TO_DATE function use fx modifier which specifies the exact matching for the character argument and date format model of TO_DATE function. TO_DATE function format: TO_DATE ( char[, ‘ format_model’] ).

For ex: Select TO_DATE (‘may 24 2007’,’mon dd rr’) from dual;

Oracle SQL Interview Questions And Answers

Q11. Describe different types of General Function used in SQL?

Ans: General functions are of following types:

1. NVL: Converts a null value to an actual value. NVL (exp1, exp2) .If exp1 is null then NVL function return value of exp2.

2. NVL2: If exp1 is not null, nvl2 returns exp2, if exp1 is null, nvl2 returns exp3. The argument exp1 can have any data type. NVL2 (exp1, exp2, exp3)

3. NULLIF: Compares two expressions and returns null if they are equal or the first expression if they are not equal. NULLIF (exp1, exp2)

4. COALESCE: Returns the first non-null expression in the expression list. COALESCE (exp1, exp2… expn). The advantage of the COALESCE function over NVL function is that the COALESCE function can take multiple alternative values.

5. Conditional Expressions: Provide the use of IF-THEN-ELSE logic within a SQL statement. Example: CASE Expression and DECODE Function.

Q12. What is the difference between COUNT (*), COUNT (expression), COUNT (distinct expression)? (Where expression is any column name of Table)

Ans: COUNT (*): Returns a number of rows in a table including duplicates rows and rows containing null values in any of the columns.

COUNT (EXP): Returns the number of non-null values in the column identified by expression.

COUNT (DISTINCT EXP): Returns the number of unique, non-null values in the column identified by expression.

Q13. What is a Sub Query? Describe its Types?

Ans: A subquery is a SELECT statement that is embedded in a clause of another SELECT statement. Sub query can be placed in WHERE, HAVING and FROM clause.

Guidelines for using subqueries:

1. Enclose sub queries within parenthesis

2. Place subqueries on the right side of the comparison condition.

3. Use Single-row operators with single-row subqueries and Multiple-row operators with multiple-row subqueries.

Types of subqueries:

1. Single-Row Subquery: Queries that return only one row from the inner select statement. Single-row comparison operators are: =, >, >=, <, <=, <>

2. Multiple-Row Subquery: Queries that return more than one row from the inner Select statement. There are also multiple-column subqueries that return more than one column from the inner select statement. Operators includes: IN, ANY, ALL.

Q14. What is the difference between ANY and ALL operators?

Ans: ANY Operator compares value to each value returned by the subquery. ANY operator has a synonym SOME operator.

> ANY means more than the minimum.

< ANY means less than the maximum

= ANY is equivalent to IN operator.

Subscribe to our youtube channel to get new updates..!

ALL Operator compares value to every value returned by the subquery.

> ALL means more than the maximum

< ALL means less than the minimum

<> ALL is equivalent to NOT IN condition.

Q15. What is a MERGE statement?

Ans: The MERGE statement inserts or updates rows in one table, using data from another table. It is useful in data warehousing applications.

PL/SQL Interview Questions for Experienced Professionals - (1/2/3 Years)

Q16. What is the difference between the “VERIFY” and the “FEEDBACK” command?

Ans: VERIFY Command: Use VERIFY Command to confirm the changes in the SQL statement (Old and New values). Defined with SET VERIFY ON/OFF.

Feedback Command: Displays the number of records returned by a query.

Q17. What is the use of Double Ampersand (&&) in SQL Queries? Give an example?

Ans: Use “&&” if you want to reuse the variable value without prompting the user each time.

For ex: Select empno, ename, &&column_name from employee order by &column_name;

Q18. What are Joins and how many types of Joins are there?

Ans: Joins are used to retrieve data from more than one table.

There are 5 different types of joins.

types of Joins
Oracle 8i and Prior	SQL: 1999 (9i)
Equi Join	Natural/Inner Join
Outer Join	Left Outer/ Right Outer/ Full Outer Join
Self Join	Join ON
Non-Equi Join	Join USING
Cartesian Product	Cross Join

Q19. Explain all Joins used in Oracle 8i?

Ans: Cartesian Join: When a Join condition is invalid or omitted completely, the result is a Cartesian product, in which all combinations of rows are displayed. To avoid a Cartesian product, always include a valid join condition in a “where” clause. To Join ‘N’ tables together, you need a minimum of N-1 Join conditions.

For ex: to join four tables, a minimum of three joins is required. This rule may not apply if the table has a concatenated primary key, in which case more than one column is required to uniquely identify each row.

Equi Join: This type of Join involves primary and foreign key relation. Equi Join is also called Simple or Inner Joins.

Non-Equi Joins: A Non-Equi Join condition containing something other than an equality operator. The relationship is obtained using an operator other than equal operator (=).The conditions such as <= and >= can be used, but BETWEEN is the simplest to represent Non-Equi Joins.

Outer Joins: Outer Join is used to fetch rows that do not meet the join condition. The outer join operator is the plus sign (+), and it is placed on the side of the join that is deficient in information. The Outer Join operator can appear on only one side of the expression, the side that has information missing. It returns those rows from one table that has no direct match in the other table. A condition involving an Outer Join cannot use IN and OR operator.

Self Join: Joining a table to itself.

Advanced PL/SQL Interview Questions For Experienced

Q20. Explain all Joins used in Oracle 9i and later release?

Ans: Cross Join: Cross Join clause produces the cross-product of two tables. This is same as a Cartesian product between the two tables.

Natural Joins: Is used to join two tables automatically based on the columns which have matching data types and names, using the keyword NATURAL JOIN. It is equal to the Equi-Join. If the columns have the same names but different data types, than the Natural Join syntax causes an error.

Join with the USING clause: If several columns have the same names but the data types do not match, than the NATURAL JOIN clause can be modified with the USING clause to specify the columns that should be used for an equi Join. Use the USING clause to match only one column when more than one column matches. Do not use a table name or alias in the referenced columns. The NATURAL JOIN clause and USING clause are mutually exclusive.

For ex: Select a.city, b.dept_name from loc a Join dept b USING (loc_id) where loc_id=10;

Joins with the ON clause: Use the ON clause to specify a join condition. The ON clause makes code easy to understand. ON clause is equals to Self Joins. The ON clause can also be used to join columns that have different names.

Left/ Right/ Full Outer Joins: Left Outer Join displays all rows from the table that is Left to the LEFT OUTER JOIN clause, right outer join displays all rows from the table that is right to the RIGHT OUTER JOIN clause, and full outer join displays all rows from both the tables either left or right to the FULL OUTER JOIN clause.

Q21. What is a difference between Entity, Attribute and Tuple?

Ans: Entity: A significant thing about which some information is required. For ex: EMPLOYEE (table). Attribute: Something that describes the entity. For ex: empno, empname, empaddress (columns). Tuple: A row in a relation is called Tuple.

Q22. What is a Transaction? Describe common errors can occur while executing any Transaction?

Ans: Transaction consists of a collection of DML statements that forms a logical unit of work.

The common errors that can occur while executing any transaction are:

The violation of constraints.

1. Data type mismatch.

2. Value too wide to fit in column.

3. The system crashes or Server gets down.

4. The session Killed.

5. Locking take place. Etc.

Q23. What is locking in SQL? Describe its types?

Ans: Locking prevents destructive interaction between concurrent transactions. Locks held until Commit or Rollback. Types of locking are:

Implicit Locking: Occurs for all SQL statements except SELECT.

Explicit Locking: Can be done by the user manually.

Further, there are two locking methods:

1. Exclusive: Locks out other users

2. Share: Allows other users to access

Q24. What is a difference between Commit, Rollback and Savepoint?

· COMMIT: Ends the current transaction by making all pending data changes permanent.

· ROLLBACK: Ends the current transaction by discarding all pending data changes.

· SAVEPOINT: Divides a transaction into smaller parts. You can rollback the transaction till a particular named savepoint.

PL SQL Interview Questions for 4 Years Experience

Q25. What are the advantages of COMMIT and ROLLBACK statements?

Ans: Advantages of COMMIT and ROLLBACK statements are:

· Ensure data consistency

· Can preview data changes before making changes permanent.

· Group logically related operations.

Q26. Describe naming rules for creating a Table?

Ans: Naming rules to be consider for creating a table are:

1. Table name must begin with a letter,

2. Table name can be 1-30 characters long,

3. Table name can contain only A-Z, a-z, 0-9,_, $, #.

4. Table name cannot duplicate the name of another object owned by the same user.

5. Table name cannot be an oracle server reserved word.

Q27. What is a DEFAULT option in a table?

Ans: A column can be given a default value by using the DEFAULT option. This option prevents null values from entering the column if a row is inserted without a value for that column. The DEFAULT value can be a literal, an expression, or a SQL function such as SYSDATE and USER but the value cannot be the name of another column or a pseudo column such as NEXTVAL or CURRVAL.

Q28. What is the difference between USER TABLES and DATA DICTIONARY?

Ans: USER TABLES: Is a collection of tables created and maintained by the user. Contain USER information. DATA DICTIONARY: Is a collection of tables created and maintained by the Oracle Server. It contains database information. All data dictionary tables are owned by the SYS user.

Q29. Describe a few Data Types used in SQL?

Ans: Data Types is a specific storage format used to store column values. Few data types used in SQL are:

1. VARCHAR2(size): Minimum size is ‘1’ and Maximum size is ‘4000’

2. CHAR(size): Minimum size is ‘1’and Maximum size is ‘2000’

3. NUMBER(P,S): " Precision" can range from 1 to 38 and the “Scale” can range from -84 to 127.

4. DATE

5. LONG: 2GB

6. CLOB: 4GB

7. RAW (size): Maximum size is 2000

8. LONG RAW: 2GB

9. BLOB: 4GB

10. BFILE: 4GB

11. ROWID: A 64 base number system representing the unique address of a row in the table.

Q30. In what scenario you can modify a column in a table?

Ans: During modifying a column:

1. You can increase the width or precision of a numeric column.

2. You can increase the width of numeric or character columns.

3. You can decrease the width of a column only if the column contains null values or if the table has no rows.

4. You can change the data type only if the column contains null values.

5. You can convert a CHAR column to the VARCHAR2 data type or convert a VARCHAR2 column to the CHAR data type only if the column contains null values or if you do not change the size.

Q31. Describe a few restrictions on using the “LONG” data type?

Ans: A LONG column is not copied when a table is created using a subquery. A LONG column cannot be included in a GROUP BY or an ORDER BY clause. Only one LONG column can be used per table. No constraint can be defined on a LONG column.

Q32. What is a SET UNUSED option?

Ans: SET UNUSED option marks one or more columns as unused so that they can be dropped when the demand on system resources is lower. Unused columns are treated as if they were dropped, even though their column data remains in the table’s rows. After a column has been marked as unused, you have no access to that column. A select * query will not retrieve data from unused columns. In addition, the names and types of columns marked unused will not be displayed during a DESCRIBE, and you can add to the table a new column with the same name as an unused column. The SET UNUSED information is stored in the USER_UNUSED_COL_TABS dictionary view.

PLSQL Interview Questions for 5 Years Experience

Q33. What is the difference between Truncate and Delete?

Ans: The main difference between Truncate and Delete is as below:

SQL Truncate Vs SQL Delete
TRUNCATE	DELETE
Removes all rows from a table and releases storage space used by that table.	Removes all rows from a table but does not release storage space used by that table.
TRUNCATE Command is faster.	DELETE command is slower.
Is a DDL statement and cannot be Rollback.	Is a DDL statement and can be Rollback.
Database Triggers do not fire on TRUNCATE.	Database Triggers fire on DELETE.

Q34. What is the main difference between CHAR and VARCHAR2?

Ans: CHAR pads blank spaces to a maximum length, whereas VARCHAR2 does not pad blank spaces.

Q35. What are Constraints? How many types of constraints are there?

Ans: Constraints are used to prevent invalid data entry or deletion if there are dependencies. Constraints enforce rules at the table level. Constraints can be created either at the same time as the table is created or after the table has been created. Constraints can be defined at the column or table level. Constraint defined for a specific table can be viewed by looking at the USER-CONSTRAINTS data dictionary table. You can define any constraint at the table level except NOT NULL which is defined only at the column level. There are 5 types of constraints:

1. Not Null Constraint

2. Unique Key Constraint

3. Primary Key Constraint

4. Foreign Key Constraint

5. Check Key Constraint.

Oracle PL SQL Technical interview questions

Q36. Describe types of Constraints in brief?

Ans: NOT NULL: NOT NULL Constraint ensures that the column contains no null values.

UNIQUE KEY: UNIQUE Key Constraint ensures that every value in a column or set of columns must be unique, that is, no two rows of a table can have duplicate values in a specified column or set of columns. If the UNIQUE constraint comprises more than one column, that group of columns is called a Composite Unique Key. There can be more than one Unique key on a table. Unique Key Constraint allows the input of Null values. Unique Key automatically creates index on the column it is created.

PRIMARY KEY: Uniquely identifies each row in the Table. Only one PRIMARY KEY can be created for each table but can have several UNIQUE constraints. PRIMARY KEY ensures that no column can contain a NULL value. A Unique Index is automatically created for a PRIMARY KEY column. PRIMARY KEY is called a Parent key.

FOREIGN KEY: Is also called Referential Integrity Constraint. FOREIGN KEY is one in which a column or set of columns take references of the Primary/Unique key of same or another table. FOREIGN KEY is called a child key. A FOREIGN KEY value must match an existing value in the parent table or be null.

CHECK KEY: Defines a condition that each row must satisfy. A single column can have multiple CHECK Constraints. During CHECK constraint following expressions is not allowed:

1) References to CURRVAL, NEXTVAL, LEVEL and ROWNUM Pseudo columns.

2) Calls to SYSDATE, UID, USER and USERENV Functions

Q37. What is the main difference between Unique Key and Primary Key?

Ans: The main difference between Unique Key and Primary Key are:

Unique Vs Primary Key
Unique Key	Primary Key
A table can have more than one Unique Key.	A table can have only one Primary Key.
Unique key column can store NULL values.	Primary key column cannot store NULL values.
Uniquely identify each value in a column.	Uniquely identify each row in a table.

Q38. What is a difference between ON DELETE CASCADE and ON DELETE SET NULL?

Ans: ON DELETE CASCADE Indicates that when the row in the parent table is deleted, the dependent rows in the child table will also be deleted. ON DELETE SET NULL Coverts foreign key values to null when the parent value is removed. Without the ON DELETE CASCADE or the ON DELETE SET NULL options, the row in the parent table cannot be deleted if it is referenced in the child table.

Q39. What is a Candidate Key?

Ans: The columns in a table that can act as a Primary Key are called Candidate Key.

Q40. What are Views and why they are used?

Ans: A View logically represents subsets of data from one or more table. A View is a logical table based on a table or another view. A View contains no data of its own but is like a window through which data from tables can be viewed or changed. The tables on which a view is based are called Base Tables. The View is stored as a SELECT statement in the data dictionary. View definitions can be retrieved from the data dictionary table: USER_VIEWS.

Views are used:

· To restrict data access

· To make complex queries easy

· To provide data Independence

· Views provide groups of user to access data according to their requirement.

Oracle PL/SQL Interview Questions And Answers For 7 Years Experience

Q41. What is the difference between Simple and Complex Views?

Ans: The main differences between two views are:

Simple Views Vs Complex Views
Simple View	Complex View
Derives data from only one table.	Derives data from many tables.
Contains no functions or group of data	Contain functions or groups of data.
Can perform DML operations through the view.	Does not always allow DML operations through the view.

Q42. What are the restrictions of DML operations on Views?

Ans: Few restrictions of DML operations on Views are:

You cannot DELETE a row if the View contains the following:

1. Group Functions

2. A Group By clause

3. The Distinct Keyword

4. The Pseudo column ROWNUM Keyword.

You cannot MODIFY data in a View if it contains the following:

1. Group Functions

2. A Group By clause

3. The Distinct Keyword

4. The Pseudo column ROWNUM Keyword.

5. Columns defined by expressions (Ex; Salary * 12)

You cannot INSERT data through a view if it contains the following:

Q43. What is PL/SQL, Why do we need PL/SQL instead of SQL, Describe your experience working with PLSQL and What are the difficulties faced while working with PL SQL and How did you overcome?

1. PL/SQL is a procedural language extension with SQL Language.

2. Oracle introduced PL/SQL

3. It is a combination of SQL and Procedural Statements and used for creating applications.

4. Basically PL/SQL is a block structured programming language whenever we are submitting PL/SQL

5. Blocks then all SQL statements are executing separately by using sql engine and also all procedure statements are executed separately.

6. Explain your current and previous projects along with your roles and responsibilities, mention some of the challenging difficulties you've faced in your project while working with PL/SQL.

Q44. What are the different functionalities of a Trigger ?

Ans: Trigger is also same as stored procedure & also it will automatically invoked whenever DML operation performed against table or view.

There are two types of triggers supported by PL/SQL

1. Statement Level Trigger.

2. Row Level Trigger

Statement Level Trigger: In statement-level trigger, the trigger body is executed only once for DML statement.

Row Level Trigger: In row level trigger, the trigger body is executed for each row DML statement. It is the reason, we are employing each row clause and internally stored DML transaction in trigger specification, these qualifiers :old, :new, are also called as records type variables.
These qualifiers are used in trigger specification & trigger body.

Syntax:
:old.column_name
Syntax:

:new column_name

When we are use this qualifiers in trigger specification then we are not allowed to use “:” in forms of the names of the qualifiers.

Q45. Write a PL/SQL Program which raise a user defined exception on thursday?

Ans:

declare

a
  exception

begin

If
  to_char(sysdate, ‘DY)=’THU’

then

raise
  a;

end
  if;

exception

when
  a then

dbms_output.put_line(‘my
  exception raised on thursday’);

end

;

Output: my exception raised on thursday

Q46.Write a PL/SQL program to retrieve emp table and then display the salary?

Ans:

declare

v_sal
  number(10);

begin
  select max(sal)intr v_sal;

from
  emp;

dbms_output.put_line(v.sal);

end;

/

(or)

declare

A
  number(10);

B
  number(10);

C
  number(10);

begin

a:=70;

b:=30;

c:=greatest+(a,b);

dbms_output.put_line(c);

end;

/

Output:70

PLSQL Interview Questions for 8 Years Experience

Q47. Write a PL/SQL cursor program which is used to calculate total salary from emp table without using sum() function?

Ans:

Declare

cursor
  c1 is select sal from emp;

v_sal
  number(10);

n.number(10):=0;

begin

open
  c1;

loop

fetch
  c1 into v_sal;

exit
  when c1%not found;

n:=n+v_sal;

end
  loop;

dbms_output.put_line(‘tool
  salary is’||’ ‘ ||n);

close
  c1;

end;

/

Output:
  total salary is: 36975

Q48. Write a PL/SQL cursor program to display all employee names and their salary from emp table by using % not found attributes?

Ans:

Declare

Cursor
  c1 is select ename, sal from emp;

v_ename
  varchar2(10);

v_sal
  number(10);

begin

open
  c1;

loop

fetch
  c1 into v_ename, v_sal;

exist
  when c1 % notfound;

dbms_output.put_line(v_name
  ||’ ‘||v_sal);

end
  loop;

close
  c1;

end;

/

Q49. What is Mutating Trigger?

Ans:

· Into a row-level trigger based on a table, the trigger body cannot read data from the same table and also we cannot perform DML operation on the same table.

· If we are trying this oracle server returns mutating error oracle-4091: table is mutating.

· This error is called a mutating error, and this trigger is called a mutating trigger, and the table is called a mutating table.

· Mutating errors are not occurred in statement-level trigger because through this statement-level trigger when we are performing DML operations automatically data committed into the database, whereas in the row-level trigger when we are performing transaction data is not committed and also again we are reading this data from the same table then only mutating errors is occurred.

Oracle PL/SQL Interview Questions and Answers for 10 Years Experience

Q50. What is Triggering Events (or) Trigger Predicate Clauses?

Ans: If we want to perform multiple operations in different tables then we must use triggering events within trigger body. These are inserting, updating, deleting clauses. These clauses are used in statement, row-level trigger. These triggers are also called as trigger predicate clauses.

Syntax:

If
  inserting then stmts;

else if

updating then
  stmts;

else if

deleting then
  stmts;

end
  if;

Q51. What is the Discard File?

Ans:

· This file extension is .dsc

· Discard file we must specify within the control file by using the discard file clause.

· Discard file also stores reflected record based on when clause condition within the control file. This condition must be satisfied into the table tablname clause.

Q52. What is REF CURSOR (or) CURSOR VARIABLE (or) DYNAMIC CURSOR ?

Ans:

Oracle 7.2 introduced ref cursor, This is an user-defined type which is used to process multiple records and also this is a record by record process.

In static cursor database servers executes only one select statement at a time for a single active set area where in ref cursor database servers executes number of select statement dynamically for a single active set area that's why those cursor are also called as dynamically cursor.

Generally we are not allowed to pass static cursor as parameters to use subprograms where as we can also pass ref cursor as parameter to the subprograms because basically refcursor is an user defined type in oracle we can also pass all user defined type as parameter to the subprograms.

Generally static cursor does not return multiple record into client application where as ref cursor are allowed to return multiple records into client application (Java, .Net, php, VB, C++).

This is an user defined type so we are creating it in 2 steps process i.e first we are creating type then only we are creating variable from that type that’s why this is also called as cursor variable.

Q53. What are The Types of Ref Cursors?

Ans: In all databases having 2 ref cursors.

1. Strong ref cursor

2. Weak ref cursor

Strong ref cursor is a ref cursor which have return type, whereas weak ref cursor has no return type.

Syntax:

1 2	`Type typename is ref cursor return` `record type data type;` `Variable Name typename`

Syntax

1 2	`Type typename is ref cursor` `Variable Name typename;`

In Weak ref cursor we must specify select statement by using open for clause this clause is used in executable section of the PL/SQL block.

Syntax:

1	`Open ref cursor varname for` `SELECT * FROM table_name condition;`

Q54. What is Difference Between trim, delete collection method?

Ans:

SQL>
  declare

type
  t1 is table of
  number(10);

v_t
  t1;=t1(10,20,30,40,50,60);

beign

v_t.trim(2);

dbms_output.put_line(‘after
  deleting last two
  elements’);

for i in

v_t.first..
  V_t.last

 loop

 dbms_output.put_line(v_t(i));

End
  loop;

vt.delete(2);

 dbms_output.put_line(‘after
  deleting second
  element;);

for i in

v_t.first..v_t.last

loop

If
  v_t.exists(i)
  then

dbms_output.put_line(v_t(i));

end
  if;

end
  loop;

end;

/

Q55. What is Overloading Procedures?

Ans: Overload refers to same name can be used for different purpose, in oracle we can also implement overloading procedure through package. Overloading procedure having same name with different type or different number of parameters.

Q56. What is Global Variables?

Ans: In oracle we are declaring global variables in Package Specification only.

Q57. What is Forward Declaration?

Ans: In oracle declaring procedures within the package body is called forward declaring generally before we are calling private procedures into public procedure first we must implements private into public procedure first we must implement private procedure within body otherwise use a forward declaration within the package body.

Q58. What is Invalid_number, Value_Error?

Ans: In oracle when we try to convert “string type to number type” or” data string into data type” then the oracle server returns two types of errors.
1. Invalid.number
2. Value_error (or) numeric_error

a) Invalid_number:
When PL/SQL block has a SQL statement and also those SQL statements try to convert string type to number type or data string into data type then oracle server returns an error: ora-1722-Invalid Number
For handling this error oracle provides number exception Invalid_number exception name.

Example:

begin

Insert

 intoemp(empno,
  ename, sal)

values(1,’gokul’,
  ‘abc’)

exception
  when invalid_number then dbms_output.put_line(‘insert proper data only’);

end;/

b)value_error
Whenever PL/SQL block having procedural statements and also those statements find to convert string type to number type then oracle servers returns an error: ora-6502:numeric or value error: character to a number conversion error
For handling, this error oracle provided exception value_error exception name

Example:

begin

declare
  z number(10);

begin

z:=
  ‘&x’ + ‘&y’;

dbms_output.put_line(z);

exception
  when value_error then dbms_output.put_line(‘enter numeric data value for

x & y
  only’);

end;/

Output:

Enter
  value for

x:3

Enter
  value for

y:2

z:=5

Enter
  value for

x:a

Enter
  value for

y:b

Error:enter
  numeric data value for

x & y only.

Q59. What is Flashback Query?

Ans:

· Flashback query are handle by Database Administrator only flashback queries along allows the content of the table to be retrieved with reference to the specific point of time by using as of clause that is flashback queries retrieves clause that is flashback queries retrieves accidental data after committing the transaction also.

· Flashback queries generally uses undo file that is flashback queries retrieve old data before committing the transaction oracle to provide two methods for flashback queries

Method1: using timestamp
Method2: using scn number

Newly Updated Oracle PL/SQL Interview Questions 2020

1) Explain what PL/SQL package consist of?

Ans: PL/SQL consists of two major parts, they are: package specification and package body.

Package specification: it acts as a public interface for your application which includes procedures, types, etc.

Package Body: It contains the code which required to implement the Package Specification

2) Explain what the benefits of PL/SQL Packages are?

Ans: These are the benefits of PL/SQL Packages

· We can store functions and procedures in a single unit called package.

· Packages provides security to grant privileges.

· Functions and procedures, within the package, shares a common variable among them.

· Packages support even if the functions are overloaded.

· Packages enhance the performance even when the multiple objects loaded into memory.

3) explain different methods to trace the PL/SQL code?

Ans: Tracing code is a necessary technique to test the performance of the code during runtime. We have different methods in PL/SQL to trace the code, which are,

· DBMS_ TRACE

· DBMS_ APPLICATION_INFO

· Tkproof utilities and trcsess

· DBMS_SESSION and DBMS_MONITOR

4) What does it mean by PL/SQL Cursors?

Ans: In PL/SQL to retrieve and process more it requires a special resource, and that resource is known as Cursor. A cursor is defined as a pointer to the context area. Context area is an area of memory which contains information and SQL statements for processing the statements.

5) what is the difference between Implicit and Explicit Cursors?

Ans: Implicit cursor used in PL/SQL to declare, all SQL data manipulation statements. An implicit cursor is used to declare SQL statements such as open, close, fetch etc.

An explicit cursor is a cursor and which is explicitly designed to select the statement with the help of a cursor. This explicit cursor is used to execute the multirow select function. An explicit function is used PL/SQL to execute tasks such as update, insert, delete, etc.

6) what is a trigger?

Ans: It is a program in PL/SQL, stored in the database and executed instantly before or after the UPDATE, INSERT and DELETE commands.

7) what are the uses of database triggers?

Ans: Triggers are programs which are automatically fired or executed when some events happen and are used for:

· To implement complex security authorizations.

· To drive column values.

· To maintain duplicate tables.

· To implement complex business rules.

· To bring transparency in log events.

8) Name the two exceptions in PL/SQL?

Ans: Error handling part of PL/SQL is called an exception. We have two types of exceptions, and they are User-defined and predefined.

9) which command is used to delete the package?

Ans: To delete the ‘Package’ in PL/SQL we use the DROP PACKAGE command.

10) what is the process for PL/SQL compilation?

Ans: The compilation process consists of syntax check, bind and p-code generation. It checks the errors in PL/SQL code while compiling. Once all errors are corrected, a storage address allocated to a variable which stores this data. This process is called as binding. P-Code consists of a list of rules for the PL/SQL engine. It is stored in the database and triggered when next time it is used.

Compare between SQL and PL/SQL.

Criteria	SQL	PL/SQL
What is it?	A single query or command execution	A full programming language
What does it comprise?	The data source for reports, web pages, etc.	An application language to build, format, and display reports, web pages, etc.
Characteristic	Declarative in nature	Procedural in nature
Used for	Manipulating data	Creating applications

2. What is PL/SQL?

Oracle PL/SQL is a procedural language that has both interactive SQL and procedural programming language constructs such as iteration and conditional branching.

3. What is the basic structure of PL/SQL?

PL/SQL uses a block structure as its basic structure. Anonymous blocks or nested blocks can be used in PL/SQL.

4. Explain the uses of a database trigger.

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

· Audit data modifications

· Log events transparently

· Enforce complex business rules

· Maintain replica tables

· Derive column values

· Implement Complex security authorizations

Any constant, variable, or parameter has a data type depending on which the storage constraints, format, and the range of values and operations are determined.

5. How is a process of PL/SQL compiled?

The 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.

6. What does a PL/SQL package consist 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

7. What are the benefits of PL/SQL packages?

PL/SQL packages provide several benefits as follows:

· Enforced information hiding: It offers the liberty to choose whether to keep data private or public.

· Top-down design: We can design the interface to the code hidden in the package before we actually implemented the modules.

· Object persistence: Objects declared in a package specification behave like global data for all PL/SQL objects in the application. We can modify the package in one module and then reference those changes to another module.

· Object-oriented design: The package gives developers stronghold 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.

8. What are different methods to trace the PL/SQL code?

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

· DBMS_APPLICATION_INFO

· DBMS_TRACE

· DBMS_SESSION and DBMS_MONITOR

· trcsess and tkprof utilities

9. What is the difference between functions, procedures, and packages in PL/SQL?

· Function: The main purpose of a PL/SQL function is to compute and return a single value. A function has a return type in its specification and must return a value specified in that type.

· Procedure: A procedure does not have a return type and should not return any value, but it can have a return statement that simply stops its execution and returns to the caller. A procedure is used to return multiple values; otherwise, it is generally similar to a function.

· Package: A package is a schema object which groups logically related PL/SQL types, items, and subprograms. You can also say that it is a group of functions, procedures, variables, and record TYPE statement. It provides modularity, due to which it aids application development. It is used to hide information from unauthorized users.

10. What is a stored procedure?

A stored procedure is a sequence of statements or a named PL/SQL block that performs one or more specific functions. It is similar to a procedure in other programming languages. It is stored in the database and can be repeatedly executed. It is stored as a schema object. It can be nested, invoked, and parameterized.

11. What is a cursor? Why is it required?

A cursor is a temporary work area created in system memory when a SQL statement is executed. A cursor contains information on a select statement and the row of data accessed by it. This temporary work area stores the data, retrieved from the database, to manipulate it. A cursor can hold more than one row but can process only one row at a time. A cursor is required to process rows individually for queries.

12. Explain the Day-to-day activities in PL/SQL.

1. Create database objects—tables, synonyms, sequences, etc.

2. To implement business rules, create procedures, functions, etc.

3. To impose business rules, create constraints, triggers, etc.

4. For data manipulation, create cursors

13. How to display records having the maximum salary from an employee table?

Select * from emp where sal= (select max(sal) from emp)

14. How to display the highest salary from an employee table?

Use the following code for displaying the highest salary from an employee table:

Select max(sal) from emp;

15. How to display the second highest salary from an employee table?

Select max(sal) from emp where sal not in ( select max(sal) from emp

16. What is a Join?

Join is a keyword used to query data from multiple tables based on the relationship between the fields of tables. Keys play a major role in Joins.

17. what is a View?

· A View is a virtual table consisting of data contained in a table.

· Views do not need any memory space.

· Views can be created on multiple tables.

18. What is a subquery? What are its types?

A subquery is a query within another query. The outer query is known as the main query and the inner query is called the subquery. A subquery is executed first, and the result of the subquery is passed to the main query.

There are two types of subqueries: correlated and non-correlated

19. what is a trigger?

A trigger is a database object that automatically executes in response to some events on the tables or views. It is used to apply the integrity constraint to the database objects.

Monday, 27 January 2020

PL SQL Interview questions

What is PL/SQL?

What are the differences between PL/SQL and SQL?

What is the basic structure of PL/SQL?

Define cursor and its use.

Why do we use database triggers? Give the syntax of a trigger.

How do you compile PL/SQL code?

Explain exception handling in PL/SQL.

Tell about a few data types in PL/SQL.

What is the difference between %TYPE and %ROWTYPE? Give an example.

What constitutes a PL/SQL package?

List some schema objects that are created using PL/SQL.

Explain the difference between procedure and function.

Explain the difference between procedure and trigger.

What are the different types of cursors in PL/SQL?

What are the different types of constraints?

What are the differences between triggers and constraints?

Explain different types of PL/SQL blocks.

Explain PL/SQL Records.

Explain the difference between commit and savepoint.

What is the difference between actual and formal parameters?

How is a DECLARE statement used?

Explain about SQLCODE and SQLERRM.

What is rollback? How is it different from rollback to statement?

What is IN OUT parameter?

Is it possible to accept user inputs during runtime? How?

Give a simple way to run a query faster.

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

How do you trace the PL/SQL code?

How to restrict string length in PL/SQL?

What is the purpose of the UTL_FILE package in PL/SQL?

What are DBMS_OUTPUT and DBMS_DEBUG?

List some cursor attributes in PL/SQL.

What is the purpose of NVL?

On a table, how many triggers can be applied?

How can we achieve consistency using PL/SQL?

Write a simple procedure to select some records from the database using some parameters.

Explain the error ORA-03113.

Can you use IF statement inside a SELECT statement? How?

What is SYS.ALL_DEPENDENCIES?

Q1. What is the difference between PL SQL and SQL?

PL SQL vs SQL

Q2. What is SQL and also describe types of SQL statements?

Q3. What is an alias in SQL statements?

Q4. What is a Literal? Give an example of where it can be used?

Q5. What is the difference between SQL and iSQL*Plus?

Q6. Define the order of Precedence used in executing SQL statements.

Q7. What are SQL functions? Describe in brief different types of SQL functions?

Q8. Explain character, number and date function in detail?

Q9. What is a Dual Table?

Q10. Explain Conversion function in detail?

Oracle SQL Interview Questions And Answers

Q11. Describe different types of General Function used in SQL?

Q12. What is the difference between COUNT (*), COUNT (expression), COUNT (distinct expression)? (Where expression is any column name of Table)

Q13. What is a Sub Query? Describe its Types?

Q14. What is the difference between ANY and ALL operators?

Subscribe to our youtube channel to get new updates..!

Q15. What is a MERGE statement?

PL/SQL Interview Questions for Experienced Professionals - (1/2/3 Years)

Q16. What is the difference between the “VERIFY” and the “FEEDBACK” command?

Q17. What is the use of Double Ampersand (&&) in SQL Queries? Give an example?

Q18. What are Joins and how many types of Joins are there?

Q19. Explain all Joins used in Oracle 8i?

Advanced PL/SQL Interview Questions For Experienced

Q20. Explain all Joins used in Oracle 9i and later release?

Q21. What is a difference between Entity, Attribute and Tuple?

Q22. What is a Transaction? Describe common errors can occur while executing any Transaction?

Q23. What is locking in SQL? Describe its types?

Q24. What is a difference between Commit, Rollback and Savepoint?

PL SQL Interview Questions for 4 Years Experience

Q25. What are the advantages of COMMIT and ROLLBACK statements?

Q26. Describe naming rules for creating a Table?

Q27. What is a DEFAULT option in a table?

Q28. What is the difference between USER TABLES and DATA DICTIONARY?

Q29. Describe a few Data Types used in SQL?

Q30. In what scenario you can modify a column in a table?

Q31. Describe a few restrictions on using the “LONG” data type?

Q32. What is a SET UNUSED option?

PLSQL Interview Questions for 5 Years Experience