Oracle Interview Questions

Oracle Programming Interview Questions and Answers

Oracle Programming Interview Questions

Q. Difference between procedure and function?

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.

Q. What is the difference between REPLACE and TRANSLATE

A.

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

TRANSLATE(STRING,IF,THEN)
Translate looks at each character in 'STRING' and then check 'IF' to see if that character is there, if it is there then it notes the position in 'IF' where it found the character and then looks the same position in 'THEN' for example :
SELECT TRANSLATE(7671234,234567890,'BCDEFHIJ') from dual;
Result of query above is: GFG1BCD

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

REPLACE('ADAH','A','BLAH')
This evaluate the string 'ADAH'.
Everywhere an 'A' is found, it will be replaced with a string 'BLAH' so the result will be - BLAHDBLAHH

Q. Difference between truncate and delete

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.

Q. Write a query to find the highest earning employee in each department ?

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
       );

Q. Write a query to list the department names which have at least one employee?

A.

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

Q. What is the difference between IN and EXISTS?

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)

NoteExists is much faster than IN(when subquery result is very large)
IN is faster than EXISTS(when subquery result is very small)

Q.
CREATE TABLE my_data (
   v VARCHAR2(100),
   n NUMBER(4),
   d DATE
)
/
Then I construct following block
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;
/
How many rows will be in the table if I execute this block ?

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.

Q. Write a SQL query to print output in below format
*
**
***
****
*****

A.

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

Q. What is Exception? What will happen if write WHEN OTHERS before other exceptions?

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.

Q. What are SQLCODE and SQLERRM ?

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.

Q. What is PRAGMA EXCEPTION_INIT ?

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);

Q. What is the difference between UNION and UNION ALL ?

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.

Q. What are the types of triggers?

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.
Q. What is a Trigger ?

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.



Python if , elif and else

Python Conditions and If statements

  • 0
Python for beginners

Learning Python Part 1

  • 3
Struct Alignment and Padding

Struct Alignment and Padding in C++ And C

  • 0
Friend function

Friend function C++

  • 0
The diamond problem Solution C++

Solving the Diamond Problem with Virtual Inheritance

  • 0
Pointers

C++ Pointers

  • 0
Structures

C++ Structures

  • 0
Types of Inheritance in C++

Inheritance and access specifiers C++

  • 0
Java date pattern

Java Date Pattern Syntax

  • 0
Java Date and Calendar

Java Date formats

  • 0
JAVA Data Type

Data types in Java

  • 0
Java unreachable code

Unreachable Code Error in Java

  • 0
INTERVIEW EXPERIENCES

Articles

09

FEB

×

Forgot Password

Please enter your email address below and we will send you information to change your password.