Interview Questions

Scale yourself in rate of 1 to 10.
1 as the lower / 10 as the expert level
1. Difference between NO DATA FOUND and %NOTFOUND
NO DATA FOUND is an exception raised only for the SELECT....INTO statements when
the where clause of the query does not match any rows. When the where clause of the
explicit cursor does not match any rows the %NOTFOUND attribute is set to TRUE
instead.
2. Difference between procedure and function. What is the purpose of package.
Functions are named PL/SQL blocks that return a value and can be called with arguments
procedure a named block that can be called with parameter. A procedure all is a PL/SQL
statement by itself, while a Function call is called as part of an expression.
3. Compare and contrast TRUNCATE and DELETE for a table.
Both the truncate and delete command have the desired outcome of getting rid of all the
rows in a table. The difference between the two is that the truncate command is a DDL
operation and just moves the high water mark and produces a now rollback. The delete
command, on the other hand, is a DML operation, which will produce a rollback and thus
take longer to complete.
4. What is a CO-RELATED SUBQUERY

The subquery in a correlated subquery is revaluated for every row of the table or view
named in the outer query.
5. What is an UTL_FILE.What are different procedures and functions

associated
with it. UTL_FILE is a package that adds the ability to read and write to operating system
files Procedures associated with it are FCLOSE, FCLOSE_ALL and 5 procedures to
output data to a file PUT, PUT_LINE, NEW_LINE, PUTF, FFLUSH.PUT,
FFLUSH.PUT_LINE,FFLUSH.NEW_LINE. Functions associated with it are FOPEN,
ISOPEN.
6. What are different collections available in oracle.

Index-by tables / Nestes Tables / Varrays
Some of the collection method names?
Exists / count / first & last / next & prior / extend / trim / delete / limit
Describe the use of PL/SQL tables

Expected answer: PL/SQL tables are scalar arrays that can be referenced by a binary
integer. They can be used to hold values for use in later queries or calculations. In Oracle
8 they will be able to be of the %ROWTYPE designation, or RECORD.
7. What causes the "snapshot too old" error? How can this be prevented or
mitigated
This is caused by large or long running transactions that have either wrapped onto
their own rollback space or have had another transaction write on part of their
rollback space. This can be prevented or mitigated by breaking the transaction
into a set of smaller transactions or increasing the size of the rollback segments
and their extents.
8. How do you do Exception handling in oracle?

What is PRAGMA EXCEPTION_INIT( EXCEPTION_NAME,
ERROR_NO)
What purpose is it used?
RAISE_APPLICATION_ERROR(-20000 TO –20999 , ERROR_MESSAGE)
Exception Propagation.
9. What is Materialized views.
A database object that stores the results of a query

.
Refresh Options – COMPLETE / FAST / FORCE
10. What are Hints? How do you use them to tune?
11. How you will avoid your query from using indexes
SELECT * FROM emp Where emp_no+' '=12345;
i.e you have to concatenate the column name with space within codes in the where
condition.
SELECT /*+ FULL(a) */ ename, emp_no from emp where emp_no=1234;

i.e using HINTS
12. What are the different optimizers? What is the best? What are the different
ways of tuning SQL statements?
CBO / RBO
The EXPLAIN PLAN command is a tool to tune SQL statements. To use it you must
have an explain_table generated in the user you are running the explain plan for. This is
created using the utlxplan.sql script. Once the explain plan table exists you run the
explain plan command giving as its argument the SQL statement to be explained. The
explain_plan table is then queried to see the execution plan of the statement. Explain
plans can also be run using tkprof.
Access Path / Operators in a Execution Plan
TABLE ACCESS FULL

TABLE ACCESS BY INDEX ROWID
INDEX UNIQUE SCAN
INDEX RANGE SCAN
NESTED LOOPS
HASH JOIN
MERGE JOIN
REMOTE
SORT ORDER BY
SORT GROUP BY
SORT JOIN
13. Scenario having txt file having data. How will you move this txt file into
database?
Sql loader / External table / utl_file
14. Dynamic sql in plsql (how will you call ddl from a oracle)
DBMS_SQL / EXECUTE IMMEDIATE
15. Bulk Binds?
Forall / Bulk collect
UNIX
1. What is the purpose of the grep command?

Expected answer: grep is a string search command that parses
the specified string from the specified file or files
2. The system has a program that always includes the word

nocomp in its name, how can you determine the number of
processes that are using this program?
Expected answer: ps -ef|grep *nocomp*|wc –l
3. cron
crontab –l
Min Hour Day Month Day_of_week
minute (0-59),
hour (0-23),
day of the month (1-31),
month of the year (1-12),
day of the week (0-6 with 0=Sunday).
5. I have a text file. Inside the text file you have a word ‘database’ . I want to replace the word to ‘oracle’.
How will you do that?
4. AWK / SED
5. Tell me the unix commands you normally work on.
REPORTS
1. What is a data model in a report?
A Data model is composed of some (or all ) of the following data definition objects:
Queries / Groups / Columns / Parameters / Links
2. what is Boilerplate
in report?
Boilerplate consists of text and graphics that appear in a report each time it is run. Graphics drawn in the
layout as well as text added to the layout are called boilerplates
3. what are the different Report types?
Tabular / Form-like / Mailing Label / Form Letter / Group Left / Group Above / Matrix/ / Matrix with
group

Interview Questions

Uploaded by

Copyright:

Available Formats

Interview Questions

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Interview Questions

Uploaded by

Copyright:

Available Formats

Scale yourself in rate of 1 to 10.

1 as the lower / 10 as the expert level

1. Difference between NO DATA FOUND and %NOTFOUND

2. Difference between procedure and function. What is the purpose of package.

3. Compare and contrast TRUNCATE and DELETE for a table.

4. What is a CO-RELATED SUBQUERY

5. What is an UTL_FILE.What are different procedures and functions

6. What are different collections available in oracle.

Describe the use of PL/SQL tables

8. How do you do Exception handling in oracle?

9. What is Materialized views.

A database object that stores the results of a query

10. What are Hints? How do you use them to tune?

SELECT * FROM emp Where emp_no+' '=12345;

SELECT /*+ FULL(a) */ ename, emp_no from emp where emp_no=1234;

Access Path / Operators in a Execution Plan

TABLE ACCESS FULL

Sql loader / External table / utl_file

DBMS_SQL / EXECUTE IMMEDIATE

15. Bulk Binds?

Forall / Bulk collect

1. What is the purpose of the grep command?

2. The system has a program that always includes the word

Expected answer: ps -ef|grep *nocomp*|wc –l

5. Tell me the unix commands you normally work on.

1. What is a data model in a report?

Queries / Groups / Columns / Parameters / Links

3. what are the different Report types?

You might also like

SELECT /+ FULL(a) / ename, emp_no from emp where emp_no=1234;

Expected answer: ps -ef|grep nocomp|wc –l