Oracle Interview Questions – Part 19
Q.What is the difference between boiler plat images and image items?
Boiler plate Images are static images (Either vector or bit map) that you import from the file system or database to use a graphical elements in your form, such as company logos and maps. Image items are special types of interface controls that store and display either vector or bitmap images. Like other items that store values, image items can be either base table items(items that relate directly to database columns) or control items. The definition of an image item is stored as part of the form module FMB and FMX files, but no image file is actually associated with an image item until the item is populate at run time.
Q.What are the triggers available in the reports?
Before report, Before form, After form , Between page, After report.
Q.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.
Q.Describe Oracle database’s physical and logical structure?
Physical : Data files, Redo Log files, Control file.
Logical : Tables, Views, Tablespaces, etc.
Q.Can you increase the size of a tablespace ? How?
Yes, by adding datafiles to it.
Q.What are the various types of Exceptions ?
User defined and Predefined Exceptions.
Q.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=.
Q.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 (
INTO TABLE image_table
FIELDS TERMINATED BY ‘,’
image_data LOBFILE (file_name) TERMINATED BY EOF
Q.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.
Q.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.
Q.Can we define exceptions twice in same block ?
Q.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.
Q.Can you have two functions with the same name in a PL/SQL block ?
Q.Can you have two stored functions with the same name ?
Q.Can you call a stored function in the constraint of a table ?
Q.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.
Q.What is the use of Control files ?
Contains pointers to locations of various data files, redo log files, etc.
Q.What are the various types of parameter modes in a procedure ?
IN, OUT AND INOUT.
Q.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.
Q.Can functions be overloaded ?
Q.Can 2 functions have same name & input parameters but differ only by return datatype ?
Q.What are the constructs of a procedure, function or a package ?
The constructs of a procedure, function or a package are :
variables and constants
Q.Why Create or Replace and not Drop and recreate procedures ?
So that Grants are not dropped.
Q.Can you pass parameters in packages ? How ?
Yes. You can pass parameters to procedures or functions in a package.
Q.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
Q.Can null keys be entered in cluster index, normal index ?
Q.What is the use of Data Dictionary ? Used by Oracle to store information about various physical and logical Oracle structures e.g. Tables, Tablespaces, datafiles, etc
Q.What are the advantages of clusters ?
Access time reduced for joins.
Q.What are the disadvantages of clusters ? The time for Insert increases.
Q.Can Long/Long RAW be clustered ?
Q.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.
Q.What is the maximum no. of statements that can be specified in a trigger statement ?
Q.Can views be specified in a trigger statement ?
Q.What are the values of :new and :old in Insert/Delete/Update Triggers ?
INSERT : new = new value, old = NULL
DELETE : new = NULL, old = old value
UPDATE : new = new value, old = old value
Q.What are cascading triggers? What is the maximum no of cascading triggers at a time?
When a statement in a trigger body causes another trigger to be fired, the triggers are said to be cascading. Max = 32.
Q.What are mutating triggers ?
A trigger giving a SELECT on the table on which the trigger is written.
Q.What is the advantage of a stored procedure over a database trigger ?
We have control over the firing of a stored procedure but we have no control over the firing of a trigger.
Q.What are constraining triggers ?
A trigger giving an Insert/Update on a table having referential integrity constraint on the triggering table.
Q.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:
INFILE ‘mydata.dat’ BADFILE ‘mydata.bad’ DISCARDFILE ‘mydata.dis’
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
Q.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. –>
TRUNCATE INTO TABLE T1
FIELDS TERMINATED BY ‘,’