Oracle and PL/SQL

1.      What is shared SQL

Oracle automatically notices when applications send identical SQL statements to the database. The SQL area used to process the first occurrence of the statement is shared - that is, used for processing subsequent occurrences of that same statement. Therefore, only one shared SQL area exists for a unique statement. Since shared SQL areas are shared memory areas, any Oracle process can use a shared SQL area. The sharing of SQL areas reduces memory usage on the database server, thereby increasing system throughput.

In evaluating whether statements are identical, Oracle considers SQL statements issued directly by users and applications as well as recursive SQL statements issued internally by a DDL statement.

2. What is the difference between the following 2 SQL statements as far as Oracle processing is concerned

INSERT INTO emp VALUES (1234, 'DAVIS', 'SALESMAN', 7698, '14-FEB-1988', 1600, 500, 30);

INSERT INTO EMP VALUES (1234, 'DAVIS', 'SALESMAN', 7698, '14-FEB-1988', 1600, 500, 30);

Both the above SQL statements perform the same function, but the SQL processing happens separately for both the SQLs. The answer of the above question will help you understand that

3. What is the difference between a DDL statement and DML statement?

A DDL is a Data Definition Language statement like Create, Alter, Drop table and a DML is a Data manipulation like select, update, delete, insert etc

4. What is the difference between implicit and explicit cursors?

Every SQL statement issued opens up an implicit cursor. While explicit cursors are ones opened by the PL/SQL programs for processing records

5. What is %ROWTYPE

The %ROWTYPE attribute provides a record type that represents a row in a table (or view). The record can store an entire row of data selected from the table or fetched from a cursor or strongly typed cursor variable. In the example below, you declare two records. The first record stores a row selected from the emp table. The second record stores a row fetched from cursor c1.

DECLARE

   emp_rec emp%ROWTYPE;

   CURSOR c1 IS SELECT deptno, dname, loc FROM dept;

   dept_rec c1%ROWTYPE;

Columns in a row and corresponding fields in a record have the same names and datatypes. In the following example, you select column values into a record named emp_rec:

DECLARE

   emp_rec emp%ROWTYPE;

   ...

BEGIN

   SELECT * INTO emp_rec FROM emp WHERE ...

The column values returned by the SELECT statement are stored in fields. To reference a field, you use dot notation. For example, you might reference the deptno field as follows:

IF emp_rec.deptno = 20 THEN ...

6.      What is %TYPE

The %TYPE attribute provides the datatype of a variable or database column. In the following example, %TYPE provides the datatype of a variable:

credit REAL(7,2);

debit  credit%TYPE;

Variables declared using %TYPE are treated like those declared using a datatype specifier. For example, given the previous declarations, PL/SQL treats debit like a REAL(7,2) variable. The next example shows that a %TYPE declaration can include an initialization clause:

balance         NUMBER(7,2);

minimum_balance balance%TYPE := 10.00;

The %TYPE attribute is particularly useful when declaring variables that refer to database columns. You can reference a table and column, or you can reference an owner, table, and column, as in

my_dname scott.dept.dname%TYPE;

Using %TYPE to declare my_dname has two advantages. First, you need not know the exact datatype of dname. Second, if the database definition of dname changes, the datatype of my_dname changes accordingly at run time.

Note, however, that a NOT NULL column constraint does not apply to variables declared using %TYPE. In the next example, even though the database column empno is defined as NOT NULL, you can assign a null to the variable my_empno:

DECLARE

   my_empno emp.empno%TYPE;

   ...

BEGIN

   my_empno := NULL;  -- this works

7.      What are Built-in functions?

PL/SQL provides many powerful functions to help you manipulate data. These built-in functions fall into the following categories:

• error-reporting
• number
• character
• conversion
• date
• miscellaneous

8.      If the candidate is aware of Built-in functions, ask for atleast 2 date functions and what they do?

Following are the date function available

ADD_MONTHS

LAST_DAY

MONTHS_BETWEEN

NEW_TIME

NEXT_DAY

ROUND

SYSDATE

9.      Ask the candidate what is the DECODE function in SQL and ask for the syntax?

The function DECODE compares its first argument to one or more search expressions, which are paired with result expressions. Any search or result expression can be null. If a search is successful, the corresponding result is returned. In the following example, if the column rating is null, DECODE returns the value 1000:

SELECT DECODE(rating, NULL, 1000, 'C', 2000, 'B', 4000, 'A', 5000)

   INTO credit_limit FROM accts WHERE acctno = my_acctno;

10.  What are the different Parameters that can be used in calls to Oracle PL/SQL stored procedures?

IN – used to pass by value, parameters to the stored procedure

OUT – used to return by value, parameters from the stored procedure

IN/OUT – used to pass and receive by value, parameters to and from the stored procedure

11.  What is the advantage of having separate specification and body portions for PL/SQL packages?

First of all Specification contains the signature of each of the procedures/functions included in a particular package. Body contains the actual code for the same procedures/functions in the package. These two are separately compiled. Change in one does not require the other also to be compiled. This is an advantage during design phase where the procedures/functions inside a package can be deteremined and the specification made and compiled. The code or the body can be created at a later stage during development.

12.  What is an outer join? What is the syntax?

The outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and those rows from one table for which no rows from the other satisfy the join condition. Such rows are not returned by a simple join. To write a query that performs an outer join of tables A and B and returns all rows from A, apply the outer join operator (+) to all columns of B in the join condition. For all rows in A that have no matching rows in B, Oracle returns NULL for any select list expressions containing columns of B

SELECT ename, job, dept.deptno, dname

FROM emp, dept

WHERE emp.deptno (+) = dept.deptno;

13.  If  tableA has 100 rows and tableB has 20000 rows, then how will the WHERE clause look like for an equijoin between the 2 tables, the optimization point of view. Also ask why?

WHERE tableB.column1 = tableA.column1

This is because, oracle translates an equijoin into a nested for loop. Which justifies, because in the above where clause complete scan will be carried out for tableA, which is small in size, for each row of tableB.

14.  What is a CURSOR in a PL/SQL block? Give an example of a CURSOR

Oracle uses work areas to execute SQL statements and store processing information. A PL/SQL construct called a cursor lets you name a work area and access its stored information

DECLARE

   CURSOR c1 IS

      SELECT empno, ename, job FROM emp WHERE deptno = 20;

15.  What is the difference between implicit and explicit cursors

There are two kinds of cursors: implicit and explicit. PL/SQL implicitly declares a cursor for all SQL data manipulation statements, including queries that return only one row

16.  What is a Cursor FOR loops?

In most situations that require an explicit cursor, you can simplify coding by using a cursor FOR loop instead of the OPEN, FETCH, and CLOSE statements. A cursor FOR loop implicitly declares its loop index as a record that represents a row in a database table, opens a cursor, repeatedly fetches rows of values from the result set into fields in the record, then closes the cursor when all rows have been processed. In the following example, the cursor FOR loop implicitly declares emp_rec as a record:

DECLARE

   CURSOR c1 IS

      SELECT ename, sal, hiredate, deptno FROM emp;

   ...

BEGIN

   FOR emp_rec IN c1 LOOP

      ...

      salary_total :=  salary_total + emp_rec.sal;

   END LOOP;

To reference individual fields in the record, you use dot notation, in which a dot (.) serves as the component (field) selector.

17.  Can a transaction be assigned to a particular rollback segment, during runtime? If yes, why is it needed?

Yes, a transaction can be assigned to a particular rollback segment. A rollback segment in Oracle is used to write data between 2 commits (i.e a transaction).

The above facility is useful for online transactions (which require small rollback segments, as data volume is less) versus offline transactions (which requires a large rollback segment as data volume is high). This ia just a better way of planning an Oracle database.

18.  What is a database trigger?

Complex business rules that cannot be enforced using declarative integrity constraints can be enforced using triggers. Triggers, which are similar to PL/SQL anonymous blocks, are automatically executed when a triggering statement is issued, regardless of the user or application

19.  List the types of database triggers

Before/After – Insert, Update, Delete

20.  How is Error Handling done in PL/SQL blocks

In PL/SQL, a warning or error condition is called an exception. Exceptions can be internally defined (by the runtime system) or user defined. Examples of internally defined exceptions include division by zero and out of memory. Some common internal exceptions have predefined names, such as ZERO_DIVIDE and STORAGE_ERROR. The other internal exceptions can be given names.

You can define exceptions of your own in the declarative part of any PL/SQL block, subprogram, or package. For example, you might define an exception named insufficient_funds to flag overdrawn bank accounts. Unlike internal exceptions, user-defined exceptions must be given names.

When an error occurs, an exception is raised. That is, normal execution stops and control transfers to the exception-handling part of your PL/SQL block or subprogram. Internal exceptions are raised implicitly (automatically) by the runtime system. User-defined exceptions must be raised explicitly by RAISE statements, which can also raise predefined exceptions.

To handle raised exceptions, you write separate routines called exception handlers. After an exception handler runs, the current block stops executing and the enclosing block resumes with the next statement. If there is no enclosing block, control returns to the host environment.

21.  What is a user defined Exception and how is it handled

PL/SQL lets you define exceptions of your own. Unlike predefined exceptions, user-defined exceptions must be declared and must be raised explicitly by RAISE statements.

Exceptions can be declared only in the declarative part of a PL/SQL block, subprogram, or package. You declare an exception by introducing its name, followed by the keyword EXCEPTION. In the following example, you declare an exception named past_due:

DECLARE

   past_due EXCEPTION;

Exception and variable declarations are similar. But remember, an exception is an error condition, not a data item. Unlike variables, exceptions cannot appear in assignment statements or SQL statements. However, the same scope rules apply to variables and exceptions.

Raising an Exception example

PL/SQL blocks and subprograms should raise an exception only when an error makes it undesirable or impossible to finish processing. You can place RAISE statements for a given exception anywhere within the scope of that exception. In the following example, you alert your PL/SQL block to a user-defined exception named out_of_stock:

DECLARE

   out_of_stock   EXCEPTION;

   number_on_hand NUMBER(4);

BEGIN

   ...

   IF number_on_hand < 1 THEN

      RAISE out_of_stock;

   END IF;

   ...

EXCEPTION

   WHEN out_of_stock THEN

      -- handle the error

END;