What are the complex scenario

1 .What is a Dual Table?

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.

2. What is the difference between TRUNCATE & DELETE?

• Truncate is a DDL command
• We can remove bulk amount of records at a time
• We can’t rollback the records
• Release the space in database
• Truncate reset the high water mark
• Truncate explicitly commit
• Delete is a DML command
• We can delete record by record
• We can rollback the records
• Can’t release the memory in database
• Delete can’t reset the water mark
• Delete implicitly commit

Differences:

• TRUNCATE commits after deleting the entire table i.e., cannot be rolled back.
• Database triggers do not fire on TRUNCATE DELETE allows the filtered deletion.
• Deleted records can be rolled back or committed.Database triggers fire on DELETE.

3. Difference between view and materialized view

• Difference
• View is a logical table
• View can hold the query
• We can’t create indexes on view
• View will create security purpose
• Mv is a physical table
• Mv can hold the query with refresh data
• We can create indexes on mv
• Mv will create performance issues

4. Difference between procedure and function?

• Procedure allow the DML statements without any restrictions
• We can’t call procedure in sql language
• We can store s in stored procedure
• Function not allow the DML statements (If you need to use we can use pragma)
• We can call Function in sql language
• Function can’t store s

5. What is a cursor?

Cursor is private sql area which is used to execute sql statements and store processing information

6. What is an explicit and implicit cursor and examples?

• The implicit cursor is automatically declared by oracle every time an sql statement is executed whenever you issue a sql statement, the oracle server opens an area of memory in which the command is parsed and executed. Every implicit cursor attribute starts with sql%.
• An explicit cursor is created and managed by the user. And used for multi row select statements.

7.What do u understand by database and what is objects in oracle

A database is defined as a collection of meaningful data. Objects in oracle means Table, Views, Procedures, Triggers, Synonym etc

8.What is a table, view, snapshot?

• Table: A table is the basic unit of data storage in an Oracle database. The tables of a database hold all of the user accessible data. Table data is stored in rows and columns.a
• Views: A view is a virtual table. Every view has a query attached to it. (The query is a SELECT statement that identifies the columns and rows of the table(s) the view uses.)
• Snapshot: A Snapshot is a recent copy of a table from a database or in some cases ,a subset of rows/columns of a table. It is also known as Materialized view.

9.Do a view contain data?

• Views do not contain or store data

What are the advantages of views?

Provide an additional level of table security, by restricting access to a predetermined set of rows and columns of a table.

• Hide data complexity.
• Simplify commands for the user.
• Present the data in a different perspective from that of the base table.
• Store complex queries.

10.What is an Oracle sequence?

A Sequence generates a serial list of unique numbers for numerical columns of a database’s tables.

11.What is a synonym?

A synonym is an alias for a table, view, sequence or program unit.

12.What are the types of synonyms?

There are two types of synonyms private and public.

13.What is a private synonym?

Only its owner can access a private synonym.

14.What is a public synonym?

Any database user can access a public synonym

15.What is an Oracle index?

An index is an optional structure associated with a table to have direct access to rows, which can be created to increase the performance of data retrieval. Indexes can be created on one or more columns of a table. Index may also be considered as an ordered list of content of a column.

16.What is a schema?

The set of objects owned by the user account is called the schema.

17.What is a join? Explain the different types of joins?

Join is a query, which retrieves related columns or rows from multiple tables.

• Self Join – Joining the table with itself.
• Equi Join – Joining two tables by equating two common columns.
• Non-Equi Join – Joining two tables by not equating two common columns.
• Outer Join – Joining two tables in such a way that query can also retrieve rows that do not have corresponding join value in the other table.

18.Difference between SUBSTR and INSTR?

• INSTR (String1, String2 (n, (m)), INSTR returns the position of the m-th occurrence of the string 2 in string1. The search begins from the nth position of string1.
• SUBSTR (String1 n, m) SUBSTR returns a character string of size m in string1, starting from the n-th position of string1.

19.What is the difference between CHAR and VARCHAR2? What is the maximum SIZE allowed for each type?

CHAR pads blank spaces to the maximum length. VARCHAR2 does not pad blank spaces. For CHAR the maximum length is 255 and 2000 for VARCHAR2

20.How to access the current value and next value from a sequence?

Current Value : Sequence name.CURRVAL

Next Value sequence name.NEXTVAL.

21.What are the components of physical database structure of Oracle database?

Oracle database consists of three types of files. One or more datafiles, two are more redo log files, and one or more control files.

22.Query to delete duplicate row from a table

Delete from emp where rowid not in (Select min(rowid) from emp Group By emp_dept)

23.What is a cursor, its attributes and types?

The Oracle Engine uses a work area for its internal processing in order to execute an SQL statement. This work area is private to SQL operation and is called Cursor.

• Implicit Cursor: If the Oracle engine opened a cursor for its internal processing then it is know as implicit cursor. It is invoked implicitly.
• Explicit Cursor: A cursor which is opened for processing data through a PL/SQL block is known as Explicit Cursor.

%ISOPEN —returns TRUE if cursor is open else FALSE.

Syntax is SQL%ISOPEN

%ROWCOUNT— returns number of records processed from cursor syntax is SQL %ROWCOUNT %FOUND—- returns TRUE if record is fetched successfully else FALSE, syntax is SQL%FOUND %NOTFOUND– returns TRUE if record is not fetched successfully else FALSE syntax is SQL%NOTFOUND Attributes Of a Explicit Cursor %ISOPEN—returns TRUE if cursor is open else FALSE. Syntax is cursor name%IS OPEN %ROWCOUNT— returns number of records processed from cursor syntax is cursor_name %ROWCOUNT %FOUND—- returns TRUE if record is fetched successfully else FALSE, syntax is cursorname %FOUND %NOTFOUND– returns TRUE if record is not fetched successfully else FALSE syntax is cursor name %NOTFOUND

24.What are inline views?

Inline view is Sub-query(queries written in a where clause of SQL statements.). It is a query whose return values are used in filtering conditions of the main query.

25.How can we refresh a snapshot?

• Refreshing Snapshots: A snapshot can be refreshed automatically or manually. If a snapshot has to be automatically refreshed then refresh clause must be specified in the CREATE SNAPSHOT. The FAST, COMPLETE or FORCE specifies the type of REFRESH used for automatic refresh. For automatic refresh we can specify the START WITH and NEXT parameter to decide the time interval for the next update.
• COMPLETE refresh: In complete refresh the snapshot query is executed and places the result in the snapshot.
• FAST refresh : In this only the changes made to the master table will be updated to the snapshot. The corresponding log file is used to update. Fast refresh will be done only if * The snapshot is a simple snapshot. * The snapshot’s master table has a snapshot log * The snapshot log was created before the snapshot was last refreshed or created.
• FORCE refresh : In this ORACLE decides how to refresh the snapshot at the scheduled refresh time. If a fast refresh is possible it performs a fast refresh else it does a complete refresh.

26.What is a tablespace?

A database is divided into a Logical Storage Unit called tablespaces. A tablespace is used to group related logical structures together.

27.Is sequence cyclic?

28.Select nth highest value from a list of values ?

SELECT a.emp_name,a.sal FROM emp a WHERE &n – 1= (SELECT COUNT(DISTINCT sql) FROM emp b WHERE b.sal > a.sal )

29.What are triggers and its types?

A trigger is a piece of code attached to a table that is executed after specified DML statements executed on that table. There are 12 types of triggers in PL/SQL that consist of combinations of the BEFORE, AFTER, ROW, STATEMENT, TABLE, INSERT, UPDATE, DELETE and ALL key words: For eg: BEFORE ALL ROW INSERT AFTER ALL ROW INSERT BEFORE INSERT AFTER INSERT

30.What is the maximum number of triggers, can apply to a single table?

12 triggers(Oracle).

Enroll in PL/SQL Certification Course to UPGRADE Your Skills

31.Difference between rowid and rownum?

ROWID is a pseudo column in every table. The physical address of the rows is use to for the ROWID.IN HEXADECIMAL representation, ROWID is shown as 18 character string of the following format BBBBBBBBB.RRRR.FFFF (block, row, file) FFFF is the fileid of the datafile that contains the row. BBBBBBBBB is the address of the datablock within the data file that contains the row. RRRR is the ROW NUMBER with the data block that contains the row. They are unique identifiers for any row in a table. They are internally used in the construction of indexes. Rownum is the sequential number of rows in the result set object.

32.What is the difference between a LEFT JOIN and an INNER JOIN statement?

A LEFT JOIN will take ALL values from the first declared table and matching values from the second declared table based on the column the join has been declared on. An INNER JOIN will take only matching values from both tables

33.How can I avoid a divide by zero error?

Use the DECODE function. This function is absolutely brilliant and functions like a CASE statement, and can be used to return different columns based on the values of others.

34.Is view updatable?

Only if the view is a simple horizontal slice through a single table.

35.What is Dual ?

The DUAL table is a table with a single row and a single column used where a table is syntactically required.

36.What is the difference between CHAR and VARCHAR ?

CHAR is fixed length character type at storage level, and that VARCHAR will be variable length.

37.How will I fetch the last inserted record in any table ?

select column 1, column 2…. From where rowid = (select max(rowid) from table);

38.What are constraints and its types?

Integrity Constraint : An integrity constraint is a declarative way to define a business rule for a column of a table. An integrity constraint is a statement about a table’s data that is always true.

Types of integrity constraints : The following integrity constraints are supported by ORACLE:

1. NOT NULL : disallows nulls (empty entries) in a table’s column

2. UNIQUE : disallows duplicate values in a column or set of columns

3. PRIMARY KEY : disallows duplicate values and nulls in a column or set of columns

4. FOREIGN KEY : requires each value in a column or set of columns match a value in a related table’s UNIQUE or PRIMARY KEY.

5. CHECK : disallows values that do not satisfy the logical expression of the constraint

39.What is Referential Integrity and Referential integrity constraint ?

• Referential Integrity : Referential integrity defines the relationships among different columns and tables in a relational database. It’s called referential integrity because the values in one column or set of columns refer to or must match the values in a related column or set of columns.
• A referential integrity constraint requires that for each row of a table, the value in the foreign key matches a value in a parent key.

40.What is groups by and having clauses? Explain with example

• Group by clause tells oracle to group rows based on distinct values that exist for specified columns. The group by clause creates a data set , containing several sets of records grouped together based on condition.
• Having Clause: Having clause can be used with GROUP BY clause. Having imposed a condition on the group by clause which further filters the group created by the GROUP BY clause. Select ename,empno From Empl Group by empno having empno > 10;

41.What are LOCKS? What are types of different types of Lock?

Locks are mechanisms intended to prevent destructive interaction between users accessing ORACLE data. ORACLE uses locks to control concurrent access to data. Locks are used to achieve two important database goals : Consistency : Ensures that the data a user is viewing or changing is not changed (by other users) until the user is finished with the data. Integrity : Ensures that the database’s data and structures reflect all changes made to them in the correct sequence.

Types of Locks :

1. Data Locks (DML)

2. Dictionary Locks (DDL)

3. Internal Locks and Latches

4. Distributed Locks

5. Parallel Cache Management Locks

Data Locks : Row Level and Table Level Row Level : Exclusive Locks Table Level

1. Row Share Table Locks (RS)

2. Row Exclusive Table Locks (RX)

3. Share Table Locks (S)

4. Share Row Exclusive Table Locks (SRX)

5. Exclusive Table Locks (X)

Dictionary Locks :

1. Exclusive DDL Locks

2. Share DDL Locks

3. Breakable Parse Locks Restrictiveness of Locks :

In general, two levels of locking can be used in a multi-user database:

• Exclusive Locks : An exclusive lock prohibits the sharing of the associated resource. The first transaction to exclusively lock a resource is the only transaction that can alter the resource until the exclusive lock is released.
• Share Locks : A share lock allows the associated resource to be shared, depending on the operations involved (e.g., several users can read the same data at the same time). Several transactions can acquire share locks on the same resource. Share locks allow a higher degree of data concurrency than exclusive locks.

42.Difference between unique key,primary key and foreign key ?

Foreign key: A foreign key is one or more columns whose values are based on the primary or candidate key values from another table. Unique key can be null; Primary key cannot be null.

43.What are Advantages of TRUNCATE Command over DELETE/DROP TABLE Command ?

The TRUNCATE command provides a fast, efficient method for deleting all rows from a table or cluster.

1. A TRUNCATE statement does not generate any rollback information and it commits immediately; it is a DDL statement and cannot be rolled back.

2. A TRUNCATE statement does not affect any structures associated with the table being truncated (constraints and triggers) or authorizations (grants).

3. A TRUNCATE statement also specifies whether space currently allocated for the table is returned to the containing tablespace after truncation.

4. As a TRUNCATE statement deletes rows from a table (or clustered table), triggers associated with the table are not fired.

5. Also, a TRUNCATE statement does not generate any audit information corresponding to DELETE statements if auditing is enabled. Instead, a single audit record is generated for the TRUNCATE statement being issued.

44.What are steps involved in Execution of SQL statements?

STEPS IN EXECUTION OF SQL STATEMENTS :

1. Create a cursor

2. Parse the statement

3. Describe Results

4. Defining outputs

5. Bind any variables

6. Execute the statement

7. Fetch rows of a query result

45.What do you mean by Parsing?

• Parsing is the process of:

1. Translating a SQL statement, verifying it to be a valid statement

2. Performing data dictionary lookups to check table and column definitions

3. Acquiring parse locks on required objects so that their definitions do not change during the statement’s parsing

4. Checking privileges to access referenced schema objects

5. Determining the execution plan to be used when executing the statement

6. Loading it into a shared SQL area

7. For distributed statements, routing all or part of the statement to remote nodes that contain referenced data

46.What is a HINT and what are types of HINT?

Hints are suggestions that you give the optimizer for optimizing a SQL statement. Hints allow you to make decisions usually made by the optimizer.

• ALL_ROWS : The ALL_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best throughput.
• FIRST_ROWS : The FIRST_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best response time.
• FULL : The FULL hint explicitly chooses a full table scan for the specified table.
• ROWID : The ROWID hint explicitly chooses a table scan by ROWID for the specified table.
• CLUSTER : The CLUSTER hint explicitly chooses a cluster scan to access the specified table.
• HASH : The HASH hint explicitly chooses a hash scan to access the specified table.
• INDEX : The INDEX hint explicitly chooses an index scan for the specified table.
• AND_EQUAL: The AND_EQUAL hint explicitly chooses an execution plan that uses an access path that merges the scans on several single-column indexes. (You can specify multiple indexes through this hint) INDEX_ASC: The INDEX_ASC hint explicitly chooses an index scan for the specified table. If the statement uses an index range scan, ORACLE scans the index entries in ascending order of their indexed values.
• INDEX_DESC: The INDEX_DESC hint explicitly chooses an index scan for the specified table. If the statement uses an index range scan, ORACLE scans the index entries in descending order of their indexed values.
• ORDERED : The ORDERED hint causes ORACLE to join tables in the order in which they appear in the FROM clause.
• USE_NL : The USE_NL hint causes ORACLE to join each specified table to another row source with a nested loop join using the specified table as the inner table.
• USE_MERGE : The USE_MERGE hint causes ORACLE to join each specified table with another row source with a sort-merge join.

47.What do u mean by EXCEPTION_INIT Pragma ?

EXCEPTION_INIT Pragma : To handle unnamed internal exceptions, you must use the OTHERS handler or the pragma EXCEPTION_INIT. A “pragma” is a compiler directive, which can be thought of as a parenthetical remark to the compiler. Pragmas (also called “pseudo instructions”) are processed at compile time, not at run time. They do not affect the meaning of a program; they simply convey information to the compiler. The predefined pragma EXCEPTION_INIT tells the PL/SQL compiler to associate an exception name with an Oracle error number. That allows you to refer to any internal exception by name and to write a specific handler for it. You code the pragma EXCEPTION_INIT in the declarative part of a PL/SQL block, subprogram, or package using the syntax PRAGMA EXCEPTION_INIT(exception_name, Oracle_error_number); where “exception_name” is the name of a previously declared exception. For internal exceptions, SQLCODE returns the number of the associated Oracle error. The number that SQLCODE returns is negative unless the Oracle error is “no data found,” in which case SQLCODE returns +100. SQLERRM returns the message associated with the Oracle error that occurred. The message begins with the Oracle error code. For user-defined exceptions, SQLCODE returns +1 and SQLERRM returns the message “User-Defined Exception” unless you used the pragma EXCEPTION_INIT to associate the exception name with an Oracle error number, in which case SQLCODE returns that error number and SQLERRM returns the corresponding error message. The maximum length of an Oracle error message is 512 characters including the error code, nested messages, and message inserts such as table and column names.

48.Describe Oracle database’s physical and logical structure ?

Physical: Data files, Redo Log files, Control file. Logical : Tables, Views, Tablespaces, etc.

PL/SQL Interview Questions and Answers

1.  Differentiate PL/SQL and SQL?

Answer: 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 subprograms by multiple applications.
• It is known for the portability of code as code can be executed on any operating system provided that Oracle is loaded on it.
• With PL/SQL users 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 range between 1 and 32,767 characters)
  • VARCHAR2 (variable length character value range 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 type 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 , etc) stored out of line.
• For example
  • BFILE (Binary file)
  • BLOB (Binary large object)
  • CLOB ( Character large object)
  • NCLOB( NCHAR type large object)

Recommended Reading =>> PL SQL Data Types

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

Answer: PL/SQL uses the %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.

%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:

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

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

Answer: PL/SQL packages are schema objects that group 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?

Answer: 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 program.

7. Explain cursor types.

Answer: There are two types of cursors.

They are explained as follows:

a) 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 is SELECT statement;

Here, 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 ;

Where, is the name of the previously defined cursor.

• Fetch rows from the cursor

Syntax: FETCH INTO ;

Here, refers to the name of the previously defined cursor from which rows are being fetched.

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

• Closing the cursor

Syntax: CLOSE ;

Here, is the name of the cursor being closed.

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

For the following statements, PL/SQL employs implicit cursors

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

8. When do we use triggers?

Answer: The word ‘Trigger’ means to activate. In PL/SQL, the trigger is a stored procedure that defines an action taken by the database when the 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 the table
• Enforcing complex business rules
• Preventing invalid transactions

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

Answer: A stored procedure is executed explicitly by issuing a 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?

Answer: 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?

Answer: 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:

(i) 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:

(ii) 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.

Recommended Reading =>> PL SQL Procedures And Functions

Example:

12. Differentiate between syntax and runtime errors?

Answer:

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

Runtime errors are those errors in PL/SQL block for which an 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?

Answer: 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 to be rolled back of the transaction. In other words, ROLLBACK erases all outstanding changes since the last COMMIT or ROLLBACK.

Syntax to rollback a transaction completely:

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 ;

14. What is the mutating table and constraining table?

Answer: A table that 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?

Answer: 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?

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

On the other hand, a transaction is still active and running after ROLLBACK TO command as it undo only 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?

Answer

18. What are the 3 modes of parameter?

Answer: 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 return 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?

Answer: 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 the DML statement.

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

Answer: 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 of commenting in a PL/SQL code?

Answer: 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?

Answer: 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 an 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?

Answer:

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?

Answer:

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?

Answer: 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?

Answer: 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?

Answer: 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. Example: ‘+’, ‘*’, etc.

28. List different type of expressions with the example.

Answer: Expressions can be as mentioned below:

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

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

Answer

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

Answer: 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

PL/SQL is very vast when it comes to learning and application. Hope these interview question and answers will help you go through.

To learn more about PL SQL read our comprehensive PL/SQL Tutorial Series.

Happy Learning!!

Scenario 1:

Select numeric & character values in separate columns using data present in single column.

Input Data – One column having both numeric & character values in it. See below sample data.

Output – Show numeric & character values in two separate columns using select query only. See below output.