Oracle Interview Questions

Oracle Interview Questions

Have you prepared for your interview, can you answer these basic Interview questions on Oracle.

Best Oracle Interview Questions and answers online

Download Oracle Interview Questions PDF

Oracle Interview Questions

Yes, Parameters can be passed to explicit cursors. A cursor parameter could appear wherever a constant appears in a query.

Example:

CURSOR c1 (median IN NUMBER) IS
SELECT job, ename FROM emp WHERE sal > median;

The different types of rollback segments are:

  • Private Available to particular instance and
  • Public Available to all instances

No. SQL*Plus doesn’t contain PL/SQL engine, unlike Oracle Forms. Because of which all PL/SQL is sent to the database engine to get executed which increases the efficiency. Each SQL statement is individually sent to the database and not stripped off.

As of now a compiled/ parsed block of PL/SQL has a maximum size limitation of 64K and max. code size being 100K. The statement for querying existing procedure or the package size is as follows:

SQL> select * from dba_object_size where name = 'procedure_name'

Oracle 7.3 has a UTL_FILE package included in it which is used to read/ write files. The directory where you want to write to has to be in the INIT.ORA file. Prior to Oracle 7.3, DBMS_OUTPUT with the SQL*Plus SPOOL command was the only way to write a file.

Source codes of PL/SQL V2.2 made available with Oracle 7.2 are protected by implementing the binary wrapper. A stand-alone function does this by transforming the source code of PL/SQL to a portable binary object code. Thus the software can be distributed without the proprietary methods and algorithms getting exposed. Such scripts can still be understood and executed by SQL*DBA and SQL*Plus. The only precaution to be taken is that “decode” command shouldn’t be available.

There are two exception types:

  • User-defined &
  • Pre defined.

There exist 12 different types of database triggers. They are made up of different combinations of:

  • Statement and row triggers,
  • Before and after triggers,
  • Update, delete and insert triggers.

Changing old and new values in an insert, delete and update triggers

 INSERT : new = new value, old = NULL

 DELETE : new = NULL, old = old value

 UPDATE : new = new value, old = old value

Clusters reduce the access time for joins and increases it for insert.

In a table, a check condition of a column can reference another column in the same table thus providing self referential integrity.

Yes a rollback is possible to any savepoint.

The & operator signifies that a user input is needed for the PL SQL block variable. The && operator signifies that this variable’s value must be the same as inputted earlier by the user for same variable.