Oracle Interview Questions and Answers for freshers

1. To see current user nameSql> show user;
2. Change SQL prompt name
SQL> set sqlprompt “Manimara > “
Manimara >
Manimara >
3. Switch to DOS prompt
SQL> host
4. How do I eliminate the duplicate rows ?
SQL> delete from table_name where rowid not in (select max(rowid) from table group by
duplicate_values_field_name);
or
SQL> delete duplicate_values_field_name dv from table_name ta where rowid <(select min(rowid) from
table_name tb where ta.dv=tb.dv);
Example.
Table Emp
Empno Ename
101 Scott
102 Jiyo
103 Millor
104 Jiyo
105 Smith
delete ename from emp a where rowid < ( select min(rowid) from emp b where a.ename = b.ename);
or
delete from t1 a where rowid not in (select max(rowid) from ti b where a.no=b.n0)
The output like,
Empno Ename
101 Scott
102 Millor
103 Jiyo
104 Smith
5. How do I display row number with records?To achive this use rownum pseudocolumn with query, like SQL> SQL> select rownum, ename from emp;
Output:
1 Scott
2 Millor
3 Jiyo
4 Smith
6. Display the records between two range
select rownum, empno, ename from emp where rowid in
(select rowid from emp where rownum <=&upto
minus
select rowid from emp where rownum<&Start);
Enter value for upto: 10
Enter value for Start: 7
ROWNUM EMPNO ENAME
--------- --------- ----------
1 7782 CLARK
2 7788 SCOTT
3 7839 KING
4 7844 TURNER
7. I know the nvl function only allows the same data type(ie. number or char or date Nvl(comm, 0)), if commission is null then the text “Not Applicable” want to display, instead of blank space. How do I write the query?
SQL> select nvl(to_char(comm.),'NA') from emp;
Page 11 of 259
Output :
NVL(TO_CHAR(COMM),'NA')
-----------------------
NA
300
500
NA
1400
NA
NA
8. Oracle cursor : 
Implicit & Explicit cursors
Oracle uses work areas called private SQL areas to create SQL statements.
PL/SQL construct to identify each and every work are used, is called as Cursor.
For SQL queries returning a single row, PL/SQL declares all implicit cursors.
For queries that returning more than one row, the cursor needs to be explicitly declared.
9. Explicit Cursor attributes
There are four cursor attributes used in Oracle
cursor_name%Found, cursor_name%NOTFOUND, cursor_name%ROWCOUNT, cursor_name%ISOPEN
10. Implicit Cursor attributes
Same as explicit cursor but prefixed by the word SQL
SQL%Found, SQL%NOTFOUND, SQL%ROWCOUNT, SQL%ISOPEN
Tips : 1. Here SQL%ISOPEN is false, because oracle automatically closed the implicit cursor after executing
SQL statements.
: 2. All are Boolean attributes.
11. Find out nth highest salary from emp table
SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP
B WHERE a.sal<=b.sal);
or
select min(sal) from(select distinct sal from emp order by sal desc where rownum<=&n)
Enter value for n: 2
SAL
---------
3700
12. To view installed Oracle version information
SQL> select banner from v$version;
13. Display the number value in Words
SQL> select sal, (to_char(to_date(sal,'j'), 'jsp')) from emp;
the output like,
SAL (TO_CHAR(TO_DATE(SAL,'J'),'JSP'))
--------- -----------------------------------------------------
800 eight hundred
1600 one thousand six hundred
1250 one thousand two hundred fifty
If you want to add some text like,
Rs. Three Thousand only.
SQL> select sal "Salary ",(' Rs. ' (to_char(to_date(sal,'j'), 'Jsp')) ' only.'))"Sal in Words" from emp
/
Salary Sal in Words
------- ------------------------------------------------------
800 Rs. Eight Hundred only.
1600 Rs. One Thousand Six Hundred only.
1250 Rs. One Thousand Two Hundred Fifty only.
14. Display Odd/ Even number of recordsOdd number of records:
select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);
1
3
5
Even number of records:
select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp)
2
Page 12 of 259
4
6
15. Which date function returns number value?
months_between
16. Any three PL/SQL Exceptions?
Too_many_rows, No_Data_Found, Value_Error, Zero_Error, Others
17. What are PL/SQL Cursor Exceptions?
Cursor_Already_Open, Invalid_Cursor
18. Other way to replace query result null value with a text
SQL> Set NULL ‘N/A’
to reset SQL> Set NULL ‘’
19. What are the more common pseudo-columns?
SYSDATE, USER , UID, CURVAL, NEXTVAL, ROWID, ROWNUM
20. What is the output of SIGN function?1 for positive value,
0 for Zero,
-1 for Negative value.
21. What is the maximum number of triggers, can apply to a single table?
12 triggers.

oracle interview questions for freshers(1)

1.What is the difference between a hot backup and a cold backup and the benefits associated with each?

A hot backup is basically taking a backup of the database while it is still up and running and it must be in archive log mode. A cold backup is taking a backup of the database while it is shut down and does not require being in archive log mode.

Benefits:

The benefit of taking a hot backup is that the database is still available for use while the backup is occurring and you can recover the database to any ball in time. The benefit of taking a cold backup is that it is typically easier to administer the backup and recovery process. In addition, since you are taking cold backups the database does not require being in archive log mode and thus there will be a slight performance gain as the database is not cutting archive logs to disk.

2.I know the nvl function only allows the same data type(ie. number or char or date

Nvl(comm, 0)), if commission is null then the text “Not Applicable” want to display, instead ofblank space. How do I write the query?

SQL> select nvl(to_char(comm.),’NA’) from emp;

Output :NVL(TO_CHAR(COMM),’NA’)

———————–

NA

300

500

NA

1400

NA

NA

3. Oracle cursor : Implicit & Explicit cursors

Oracle uses work areas called private SQL areas to create SQL statements.

PL/SQL construct to identify each and every work are used, is called as Cursor.

For SQL queries returning a single row, PL/SQL declares all implicit cursors.

For queries that returning more than one row, the cursor needs to be explicitly declared.

4. Explicit Cursor attributes

There are four cursor attributes used in Oracle

cursor_name%Found, cursor_name%NOTFOUND, cursor_name%ROWCOUNT, cursor_name%ISOPEN

5. Implicit Cursor attributes

Same as explicit cursor but prefixed by the word SQL

SQL%Found, SQL%NOTFOUND, SQL%ROWCOUNT, SQL%ISOPEN

Tips : 1. Here SQL%ISOPEN is false, because oracle automatically closed the implicit cursor after

executing SQL statements.

: 2. All are Boolean attributes.

6. What is the maximum number of triggers, can apply to a single table?

              12 triggers.

7.what are actual and formal parameters

      Actual Parameters

The variables or expressions referenced in the parameter list of a subprogram call are actual parameters.
Following procedure call lists two actual parameters named empno and amt:

raise_sal(empno, amt);

      Formal Parameters

The variables declared in a subprogram specification and referenced in the subprogram body are formal parameters.
Following procedure declares two formal parameters named empid and amt:

PROCEDURE raise_sal(empid INTEGER, amt REAL) IS current_salary REAL;

8.Explain an exception and its types

Exception is the error handling part of PL/SQL block. 

Exception is of two types: Predefined and user defined.

Some of Predefined exceptions are

• ZERO_DIVIDE
• NO_DATA_FOUND
• TOO_MANY_ROWS
• LOGON_DENIED
• CURSOR_ALREADY_OPEN
• INVALID_NUMBER
• NOT_LOGGED_ON
• STORAGE_ERROR
• PROGRAM-ERROR
• TIMEOUT_ON_RESOURCE
• VALUE_ERROR
• DUP_VAL_ON_INDEX
• INVALID_CURSOR. 

oracle interview questions for freshers(2)

1. What is a transaction ?Answer: A transaction is a set of SQL statements between any two COMMIT and ROLLBACK statements.
   
2. What is implicit cursor and how is it used by Oracle ?Answer: An implicit cursor is a cursor which is internally created by Oracle.It is created by Oracle for each individual SQL.
   
3. Which of the following is not a schema object : Indexes, tables, public synonyms, triggers and packages ?Answer: Public synonyms
   
4. What is PL/SQL?Answer: PL/SQL is Oracle's Procedural Language extension to SQL.The language includes object oriented programming techniques such as encapsulation, function overloading, information hiding (all but inheritance), and so, brings state-of-the-art programming to the Oracle database server and a variety of Oracle tools.
   
5. Is there a PL/SQL Engine in SQL*Plus?Answer: No.Unlike Oracle Forms, SQL*Plus does not have a PL/SQL engine.Thus, all your PL/SQL are send directly to the database engine for execution.This makes it much more efficient as SQL statements are not stripped off and send to the database individually.
   
6. Is there a limit on the size of a PL/SQL block?Answer: Currently, the maximum parsed/compiled size of a PL/SQL block is 64K and the maximum code size is 100K.You can run the following select statement to query the size of an existing package or procedure. SQL> select * from dba_object_size where name = 'procedure_name'
   
7. Can one read/write files from PL/SQL?Answer: Included in Oracle 7.3 is a UTL_FILE package that can read and write files.The directory you intend writing to has to be in your INIT.ORA file (see UTL_FILE_DIR=...parameter).Before Oracle 7.3 the only means of writing a file was to use DBMS_OUTPUT with the SQL*Plus SPOOL command.
   DECLARE
   fileHandler UTL_FILE.FILE_TYPE;
   BEGIN
   fileHandler := UTL_FILE.FOPEN('/home/oracle/tmp', 'myoutput','W');
   UTL_FILE.PUTF(fileHandler, 'Value of func1 is %sn', func1(1));
   UTL_FILE.FCLOSE(fileHandler);
   END;
   
8. How can I protect my PL/SQL source code?Answer: PL/SQL V2.2, available with Oracle7.2, implements a binary wrapper for PL/SQL programs to protect the source code.This is done via a standalone utility that transforms the PL/SQL source code into portable binary object code (somewhat larger than the original).This way you can distribute software without having to worry about exposing your proprietary algorithms and methods.SQL*Plus and SQL*DBA will still understand and know how to execute such scripts.Just be careful, there is no "decode" command available. The syntax is: wrap iname=myscript.sql oname=xxxx.yyy
   
9. Can one use dynamic SQL within PL/SQL? OR Can you use a DDL in a procedure ? How ?Answer: From PL/SQL V2.1 one can use the DBMS_SQL package to execute dynamic SQL statements.
   Eg: CREATE OR REPLACE PROCEDURE DYNSQL AS
   cur integer;
   rc integer;
   BEGIN
   cur := DBMS_SQL.OPEN_CURSOR;
   DBMS_SQL.PARSE(cur,'CREATE TABLE X (Y DATE)', DBMS_SQL.NATIVE);
   rc := DBMS_SQL.EXECUTE(cur);
   DBMS_SQL.CLOSE_CURSOR(cur);
   END;