Oracle Programming Interview Questions and Answers

A.

1. Function must return a value(scalar, inline table or multi statement table) whereas stored proc may or may not return a value.
2. Function can return a table whereas stored procs can create a table but can`t return table.
3. Stored procs can be called independently using exec keyword whereas functions are called using select statements.
4. Stored procs can be used to change sever configuration(in terms of security-i.e. setting granular permission of user rights) whereas function can`t be used for this.
5. XML and output parameters can`t be passed to functions whereas it can be with sp`s.
6. Transaction related statement can be handled in sp whereas it can`t be in function.

A.

TRANSLATE:
Translate function does character by character substitution in a string
format:

TRANSLATE(STRING,IF,THEN)

SELECT TRANSLATE(7671234,234567890,'BCDEFHIJ') from dual;

REPLACE:
REPLACE function replaces a character or characters in a string with zero or more characters.

REPLACE('ADAH','A','BLAH')

A.

• TRUNCATE is a DDL command whereas DELETE is a DML command.

• TRUNCATE is much faster than DELETE.

  (Reason: When you type DELETE. All the data get copied into the ROLLBACK Tablespace first then delete operation get performed. That`s why when you type ROLLBACK after deleting a table, you can get back the data(The system get it for you from the Rollback Tablespace). All this process take time. But when you type TRUNCATE, it removes data directly without copying it into Rollback Tablespace.That's why truncate is faster.Once you truncate you cannot get back the data.)

• You can`t rollback in TRUNCATE but in DELETE you can rollback. TRUNCATE removes record permanently.
• In case of TRUNCATE, Trigger does`nt get fired. But in DML commands the DELETE trigger gets fired.
• You can`t use conditions(WHERE clause) in TRUNCATE. But in DELETE you can write conditions using WHERE clause.

A.

SELECT DEPARTMENT_ID, EMPLOYEE_ID, SALARY
      FROM EMPLOYEE E_0
      WHERE 1=
       (
          SELECT COUNT(DISTINCT SALARY) FROM EMPLOYEE E_I
                 WHERE E_0.DEPARTMENT_ID = E_I.DEPARTMENT_ID
                 AND E_0.SALARY<=E_I.SALARY
       );

A.

SELECT  DEPARTMENT_ID, DEPARTMENT_NAME
      FROM DEPARTMENTS D
      WHERE EXISTS 
       (
          SELECT  1 FROM EMPLOYEES E
                 WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID);

A.

Using the IN clause, you`re telling the rule-based optimizer that you want the inner query to drive the outer query(IN= inside to outside)

When you write EXISTS in a where clause,you`re telling the optimizer that you want the outer query to be run first, using each value to fetch a value from inner query(EXISTS= outside to inside)

Note − Exists is much faster than IN(when subquery result is very large)
IN is faster than EXISTS(when subquery result is very small)

CREATE TABLE my_data (
   v VARCHAR2(100),
   n NUMBER(4),
   d DATE
)
/

DECLARE
   c_maximum CONSTANT INTEGER := 100;
   TYPE strings_t IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
   l_strings strings_t;
   l_counter PLS_INTEGER := l_strings.FIRST;
BEGIN   
   LOOP
      INSERT INTO my_data VALUES (
         TO_CHAR (l_counter), l_counter, SYSDATE + l_counter);
      l_counter := l_counter + 1;
      EXIT WHEN l_counter > c_maximum;   
   END LOOP;
   COMMIT;
END;
/

A.

0

Note − Whenever you are using a variable as an iterator inside a loop, make sure that it is initialized to a non-NULL value (usually 0). Otherwise, the iterator will almost certainly stay NULL throughout the entire loop's execution.So the script will stuck in loop.

*
**
***
****
*****

A.

SELECT LPAD('*',LEVEL,'*') FROM dual CONNECT BY LEVEL<=5;

A.

An exception is a run time error or warning condition, which can be predefined or user defined. Predefined exceptions are raised implicitly (automatically) by runtime system. User defined exceptions must be raised explicitly by RAISE statements.

The WHEN OTHERS clause must be the last exception handler in the exception section. If you place any other WHEN clause after WHEN OTHER, you will receive following compilation error:

PLS-00370: OTHERS handlers must be last among the exception handlers of a block.

A.

SQLCODE : it returns the error number for the last encountered error.
SQLERRM : it returns the actual error message of the last encountered error.

• When a SQL statement raises an exception, oracle captures the error codes by using the SQLCODE and SQLERRM globally-defined variables.
• SQLCODE and SQLERRM can track exceptions that are handled by the OTHERS clause of the exception handler.
• SQLCODE returns the current error code from the error stack and the error message from the current error.

A.

PRAGMA EXCEPTION_INIT statement associate the declared exception with standard Oracle server error number. PRAGMA EXCEPTION_INIT tells the 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.

PRAGMA (also called pseudoinstructions) is the keyword that signifies that the statement is a compiler directive, which is not processed when PL/SQL block is executed. Rather, it directs the PL/SQL compiler to interpret all occurrences of the exception name within the block as the associated Oracle server error number.

Syntax

PRAGMA EXCEPTION_INIT(exception, error_number);

A.

UNION performs a DISTINCT on the result set, eliminating any duplicate rows.

UNION ALL does not remove duplicates and it therefore faster than UNION.

A.

There are two types of triggers based on the which level it is triggered.

1. Row level trigger - An event is triggered for each row updated, inserted or deleted.
2. Statement level trigger - An event is triggered for each sql statement executed.

A.

A trigger is a pl/sql block structure which is fired when a DML statement like Insert, Delete, Update is executed on a database table. A trigger is triggered automatically when an associated DML is executed.