Oracle 10g Interview Tips and Questions

Oracle is strong in database management and data-warehousing for more than decades. It is leading the RDBMS world on top rank. Being a Oracle techie, one has lots of opportunities.

We posted our first resource on oracle-interview questions. We have posted Volume-I of oracle interview questions. You can expect couple of more volumes here.

Download oracle interview questions here.

We love your feedback on our articles which will help us to give more articles with more quality in near future. Please feel free to leave your comments. Thanks for staying with us.

If you have any problem downloading please visit my google site Free-career-guide.

Apart from these questions, if you know about Oracle Analytic functions, it will be added strength during a Oracle interview. Please see my page on Oracle Analytic Functions if you want to know about.

(1) How do you generate file output from SQL?

Using SPOOL command.

Ex:

SPOOL C:\ScriptOutput.log;

SELECT * FROM SCOTT.EMP WHERE DEPT='SALES';

SPOOL OFF;

(2) What statement is used in Oracle SQL* Plus to enable/disable feedback/information after SQL Output?

Use the options FEEDBACK and VERIFY to OFF.

SET FEEDBACK OFF

SET VERIFY OFF

(3) How do you execute a host operating system command from within SQL?

Use HOST (HO) command or exclamation mark "!" followed by OS command.

Ex: In windows

HOST dir, HO DIR, !DIR

Ex: In Unix

HO ls, HO cp sample.sql sample.bak

(4) How do you prevent output of a SQL from coming to the screen?

Set the TERMOUT option to off in SQL Plus.

SET TERMOUT OFF or SET TERM OFF

(5) How do you set the number of lines per page of output?

SET PAGESIZE n

This command sets the size of the page to n.

(6) How do you set the size of a line in a page?

SET LINESIZE 130

Sets the number of characters per line to 130.

(7) What SQLPlus command is used to format a column output of SELECT statements?

COLUMN command.

Ex: SET COLUMN EMP_NAME HEADING EMPLOYEE_NAME, SET COLUMN EMP_NAME FORMAT A40

(8) When will you use declare statements?

DECLARE statement must be the first line of anonymous PL/SQL blocks. However, if you want to declare a private section inside a procedure/function/package/anonymous block, you can use separate DECLARE..BEGIN..END;

(9) What is the default sorting order of ORDER BY clause in SELECT statement?

Ascending order

(10) What is a Cartesian product?

If join is undefined between two or more tables in a select statement, result will be a cartesian product, i.e., each row in a table will be associated with all rows in joined tables.

if table a,b & c were joined without any join condition, restant number of rows will product of rows in a, b & c. Say tables-a,b&c has 5 records each, resultant number of records will be 5*5*5 = 125.

(11) What is the difference between a procedure, function and anonymous block?

A function must return a value and a procedure doesn't have to. Anonymous blocks are not stored in database where procedure and function are stored.

(12) What would happens if constraint name is left out of a constraint clause?

Oracle will use default name of SYS_Cxxxx where xxxx is a system generated number.

(13) What is mutating table error and how can you get around it?

This will occurs when a trigger try to update a row it is currently using. The usual fix involves use of either a temporary table or a view, so that data is selected from one object while updating the other object.

(14) What is the use of %ROWTYPE and %TYPE in PL/SQL?

%ROWTYPE defines a row with all columns in a table. The %TYPE defines a variable with data type of a column.

Eg:

declare

emp_row emp%rowtype;

emp_id emp.emp_id%type;

....

(15) How will you find whether a cursor is open or closed?

Use the %ISOPEN cursor status variable.

(16) How will you find if a database schema is invalid?

Status column of the DBA_, ALL_ or USER_OBJECTS views, depending upon whether you own or only have permission on the view or are using a DBA account.

(1) Mention few ORACLE supplied packages you have used in your code?

DBMS_SQL, DBMS_PIPE, DBMS_TRANSACTION, DBMS_LOCK, DBMS_ALERT, DBMS_OUTPUT, DBMS_JOB, DBMS_UTILITY, DBMS_DDL, UTL_FILE.

(2) What are PL/SQL tables?

PL/SQL tables are arrays which can be accessed using a index (integer). A PL/SQL table can be defined using %ROWTYPE or using TYPE, TABLE, RECORD statements in declaration.

(3) What happens to the table space definition when you disable a PK?

Table space definitions will be lost and we have to specify when we enable.

(4) Tell what do you know about SQLCODE and SQLERRM in PL/SQL?

SQLCODE - is a system variable which returns the value of the error number for last error encountered in a PL/SQL Block.

SQLERRM - is a system varaible which returns the value of the error message for last error encountered in a PL/SQL Block.

(5) What is exception in ORACLE?

Exceptions are error handlers in ORACLE PL/SQL.When an error occurred in a PL/SQL, oracle looks for a EXCEPTION block and a matching exception in WHEN clauses.

Eg:

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('Data Error:'||SQLERRM);

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('Execution Error:'||SQLERRM);

END;

(6) Below query returned 3 rows. Justify is this correct?

SELECT TRUNC(SYSDATE)-1 yesterday, TRUNC(SYSDATE) today, TRUNC(SYSDATE)+1 tomorrow FROM DUAL;

In fact dual is a dummy table and expected to have single row. But here it looks like some user have inserted rows in to this table by mistake. It will be a problem when dual is used by any of stored procedures/other objects.

(7) Have you ever faced - Snapshot too old - issue? What is the cause and how can we prevent this?

This is caused by large transactions especially involving huge number of records (and running for a while) and is happening when their rollback space is overlapped by/to other transactions space (or even its own space).

Find more pages on Oracle:

• Oracle Interview Tips and Questions
• Good resume Format
• Writing a better SQL
• Oracle 10g New Features in SQL
• Over come the 255 Character Limit of DBMS_OUTPUT
• Oracle Exceptions
• Mastering Oracle Date Arithmetic