|
Oracle Interview Questions and Answers
What are the triggers available in the reports?
Before report, Before form, After form , Between page,
After report.
Why is a Where clause faster than a group filter or a
format trigger?
Because, in a where clause the condition is applied
during data retrievalthan after retrieving the data.
Can one selectively load only the records that one need?
(for DBA)
Look at this example, (01) is the first character,
(30:37) are characters 30 to 37:
LOAD DATA
INFILE 'mydata.dat' BADFILE 'mydata.bad' DISCARDFILE 'mydata.dis'
APPEND
INTO TABLE my_selective_table
WHEN (01) <> 'H' and (01) <> 'T' and (30:37) =
'19991217'
(
region CONSTANT '31',
service_key POSITION(01:11) INTEGER EXTERNAL,
call_b_no POSITION(12:29) CHAR
)
Can one skip certain columns while loading data? (for
DBA)
One cannot use POSTION(x:y) with delimited data.
Luckily, from Oracle 8i one can specify FILLER columns.
FILLER columns are used to skip columns/fields in the
load file, ignoring fields that one does not want. Look
at this example: -- One cannot use POSTION(x:y) as it is
stream data, there are no positional fields-the next
field begins after some delimiter, not in column X. -->
LOAD DATA
TRUNCATE INTO TABLE T1
FIELDS TERMINATED BY ','
( field1,
field2 FILLER,
field3
)
How does one load multi-line records? (for DBA)
One can create one logical record from multiple physical
records using one of the following two clauses:
. CONCATENATE: - use when SQL*Loader should combine the
same number of physical records together to form one
logical record.
. CONTINUEIF - use if a condition indicates that
multiple records should be treated as one. Eg. by having
a '#' character in column 1.
How can get SQL*Loader to COMMIT only at the end of the
load file? (for DBA)
One cannot, but by setting the ROWS= parameter to a
large value, committing can be reduced. Make sure you
have big rollback segments ready when you use a high
value for ROWS=.
Can one improve the performance of SQL*Loader? (for DBA)
A very simple but easily overlooked hint is not to have
any indexes and/or constraints (primary key) on your
load tables during the load process. This will
significantly slow down load times even with ROWS= set
to a high value.
Add the following option in the command line:
DIRECT=TRUE. This will effectively bypass most of the
RDBMS processing. However, there are cases when you
can't use direct load. Refer to chapter 8 on Oracle
server Utilities manual.
Turn off database logging by specifying the
UNRECOVERABLE option. This option can only be used with
direct data loads. Run multiple load jobs concurrently.
How does one use SQL*Loader to load images, sound clips
and documents? (for DBA)
SQL*Loader can load data from a "primary data file", SDF
(Secondary Data file - for loading nested tables and
VARRAYs) or LOGFILE. The LOBFILE method provides and
easy way to load documents, images and audio clips into
BLOB and CLOB columns. Look at this example:
Given the following table:
CREATE TABLE image_table (
image_id NUMBER(5),
file_name VARCHAR2(30),
image_data BLOB);
Control File:
LOAD DATA
INFILE *
INTO TABLE image_table
REPLACE
FIELDS TERMINATED BY ','
(
image_id INTEGER(5),
file_name CHAR(30),
image_data LOBFILE (file_name) TERMINATED BY EOF
)
BEGINDATA
001,image1.gif
002,image2.jpg
What is the difference between the conventional and
direct path loader? (for DBA)
The conventional path loader essentially loads the data
by using standard INSERT statements. The direct path
loader (DIRECT=TRUE) bypasses much of the logic involved
with that, and loads directly into the Oracle data
files. More information about the restrictions of direct
path loading can be obtained from the Utilities Users
Guide.
GENERAL INTERVIEW QUESTIONS
What are the various types of Exceptions ?
User defined and Predefined Exceptions.
Can we define exceptions twice in same block ?
No.
What is the difference between a procedure and a
function ?
Functions return a single variable by value whereas
procedures do not return any variable by value. Rather
they return multiple variables by passing variables by
reference through their OUT parameter.
Can you have two functions with the same name in a
PL/SQL block ?
Yes.
Can you have two stored functions with the same name ?
Yes.
Can you call a stored function in the constraint of a
table ?
No.
What are the various types of parameter modes in a
procedure ?
IN, OUT AND INOUT.
What is Over Loading and what are its restrictions ?
OverLoading means an object performing different
functions depending upon the no. of parameters or the
data type of the parameters passed to it.
Can functions be overloaded ?
Yes.
Can 2 functions have same name & input parameters but
differ only by return datatype ?
No.
What are the constructs of a procedure, function or a
package ?
The constructs of a procedure, function or a package are
:
variables and constants
cursors
exceptions
Why Create or Replace and not Drop and recreate
procedures ?
So that Grants are not dropped.
Can you pass parameters in packages ? How ?
Yes. You can pass parameters to procedures or functions
in a package.
What are the parts of a database trigger ?
The parts of a trigger are:
A triggering event or statement
A trigger restriction
A trigger action
What are the various types of database triggers ?
There are 12 types of triggers, they are combination of
:
Insert, Delete and Update Triggers.
Before and After Triggers.
Row and Statement Triggers.
(3*2*2=12)
Page Numbers :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34 35
Have a Question ?
post your questions here. It
will be answered as soon as possible.
Check
Job Interview Questions
for more Interview Questions with Answers
|
