Oracle Interview Questions And Answers Part - 33

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