PL/SQL interview questions – top 10

  1. What is PL/SQL?   PL/SQL was created by Oracle to expand on some of SQL’s limitations. The aim is to create a more comprehensive solution for Oracle data warehousing.
  2. 2.      What is data warehousing?    This is the enterprise-size practice of analyzing data so that management can make faster and more effective decisions based on fact.
  3. 3.      What is a PL/SQL block?  This is a collection of SQL and PL/SQL statements which achieve a project. There are three sections to a block. The declaration section is optional; it declares any placeholders (temporary storage areas) for variables, constants, records and constants. The execution section is mandatory; this is where the actual tasks to perform are listed. The exception section is optional, and states how to handle errors. You can nest one PL/SQL block within another.
  4. 4.      What is a record?  This is a data type unique to PL/SQL. It is a temporary storage area which holds a complete record from a database.
  5. 5.      Which iterative statements does PL/SQL support?  An iterative statement is a loop (also called a recursion). PL/SQL supports simple loops, While loops, and For loops.
  6. 6.      What are PL/SQL cursors?  In PL/SQL a cursor is a temporary virtual work area where the execution section of a block can occur. In the cursor only one record can be active (worked on) at a time, but several records can reside there. Once one record is processed, the cursor moves to the next record.
  7. 7.      What are stored procedures?  A procedure (proc) is the same as a macro, a collection of processes to be enacted. Parameters can be passed in and out of the procedure. The structure is the same as a block. These procedures are stored and called in a main block.
  8. 8.      What is a PL/SQL function? A function is the same as a procedure. The difference is that a function must always return a value; in a procedure this is optional.
  9. 9.      How does PL/SQL handle errors?  An exception (error) handling structure is available in PL/SQL which tells the program what to do when an error occurs. This is for improper data types, methods of exiting loops and other runtime errors. Runtime errors are errors which occur in properly-structured code during execution of that code. Exceptions can be named – these are errors that occur when a program violates an RDBM rule, such as no data being found or division by zero. Unnamed exceptions are less frequent and return an error code number rather than a name. User-defined exceptions violate business rules.

10.  How do triggers work?  A trigger, as the name implies, is a condition which cause a procedure to be activated which updates the data in a database. There are about ten PL/SQL clauses which perform updates when triggered. Examples of triggers are when a product price is changed, audit trails when changes occur, and changes in related tables that need to be made when one table’s data is changed.

Leave a Reply

Your email address will not be published.