Home / Interview Questions / Database Interview Questions / PL/Sql Interview Questions Part 4

PL/Sql Interview Questions Part 4

1. What will you get by the cursor attribute SQL%FOUND?

 

It returns the Boolean value TRUE if at least one row was processed.

 

 

2. What will you get by the cursor attribute SQL%NOTFOUND?

 

It returns the Boolean value TRUE if no rows were processed.

 

 

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

 

  • A PL/SQL package can be specified as a file that groups functions, cursors, stored procedures, and variables in one place.

 

 

 

4. What are the two different parts of the PL/SQL packages?

 

PL/SQL packages have the following two parts:

  • Specification part: It specifies the part where the interface to the application is defined.
  • Body part: This part specifies where the implementation of the specification is defined.

 

 

5. Which command is used to delete a package?

 

  • The DROP PACKAGE command is used to delete a package.

 

 

6. How to execute a stored procedure?

 

There are two way to execute a stored procedure.

From the SQL prompt, write EXECUTE or EXEC followed by procedure_name.

  •  EXECUTE or [EXEC] procedure_name;

Simply use the procedure name

  • procedure_name;

 

 

7. What are the advantages of stored procedure?

  • Modularity, extensibility, reusability, Maintainability and one time compilation.

 

 

 

8. What are the cursor attributes used in PL/SQL?

 

  • %ISOPEN: it checks whether the cursor is open or not.
  • %ROWCOUNT: returns the number of rows affected by DML operations:                                                                                           INSERT,DELETE,UPDATE,SELECT.
  • %FOUND: it checks whether cursor has fetched any row. If yes TRUE.
  • %NOTFOUND: it checks whether cursor has fetched any row. If no TRUE.

 

 

 

9. What is the difference between syntax error and runtime error?

 

  • A syntax error can be easily detected by a PL/SQL compiler.
  • For example: incorrect spelling etc.
  • A runtime error is handled with the help of exception-handling section in a PL/SQL block.
  • For example: SELECT INTO statement, which does not return any rows.

 

 

 

10. Explain the Commit statement?

 

Following conditions are true for the Commit statement:

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

 

 

11. Explain the Rollback statement?

The Rollback statement is issued when the transaction ends. Following conditions are true for a Rollback statement:

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

 

 

12. Explain the SAVEPOINT statement?

 

With SAVEPOINT, only part of transaction can be undone.

 

 

 

13. What is mutating table error?

 

  • Mutating table error is occurred when a trigger tries to update a row that it is currently using.
  • It is fixed by using views or temporary tables.

 

 

14. What is consistency?

 

Consistency simply means that each user sees the consistent view of the data.

 

 

 

15. What is cursor and why it is required?

 

  • A cursor is a temporary work area created in a system memory when an 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 and manipulate this data.
  • A cursor can hold more than one row, but can process only one row at a time.
  • Cursor are required to process rows individually for queries.

 

 

16. How many types of cursors are available in PL/SQL?

 

There are two types of cursors in PL/SQL.

  1. Implicit cursor, and
  2. explicit cursor

 

 

About Syed Saad Ali

As a certified and experienced Oracle Database Administrator with Almost 10 Years of Experience I am able to perform all levels of database maintenance and am fully qualified to do Oracle updates as well. Capitalizing on my interest in Business & IT both, I chose to navigate my career towards Enterprise Solutions, which has enabled me to excel in the combination of both. I have developed exceptional analytical skills that allow me to diagnose and take care of issues immediately. Extremely skilled in planning and execution of special projects in time- critical environments. Extensive knowledge in directing, coordinating and exercising concern authority for planning, organization, control and successful completions. Very skilled at solving customer relations issues by seeking first to understand and then to be understood. Effectively able to communicate with customers, vendors and management.

Check Also

Apps Technical Interview Questions Part 5

Leave a Reply