Oracle Interview Questions – Part 24

Q.Can one import/export between different versions of Oracle? (for DBA)
Different versions of the import utility is upwards compatible. This means that one can take an export file created from an old export version, and import it using a later version of the import utility. This is quite an effective way of upgrading a database from one release of Oracle to the next.
Oracle also ships some previous catexpX.sql scripts that can be executed as user SYS enabling older imp/exp versions to work (for backwards compatibility). For example, one can run $ORACLE_HOME/rdbms/admin/catexp7.sql on an Oracle 8 database to allow the Oracle 7.3 exp/imp utilities to run against an Oracle 8 database.

Q.What are different types of images?
Boiler plate imagesImage Items

Q.What is the use of hidden column? What are the various sub events a mouse double click event involves?
A hidden column is used to when a column has to embed into boilerplate text.

Q.What are the various sub events a mouse double click event involves? What are the various sub events a mouse double click event involves?

Double clicking the mouse consists of the mouse down, mouse up, mouse click, mouse down & mouse up events.

Q.There is a string 120000 12 0 .125 , how you will find the position of the decimal place?

INSTR(‘120000 12 0 .125′,1,’.’)
output 13

Q.What are different modes of parameters used in functions and procedures?

Q.How you were passing cursor variables in PL/SQL 2.2?
In PL/SQL 2.2 cursor variables cannot be declared in a package.This is because the storage for a cursor variable has to be allocated using Pro*C or OCI with version 2.2, the only means of passing a cursor variable to a PL/SQL block is via bind variable or a procedure parameter.

Q.When do you use WHERE clause and when do you use HAVING clause?
HAVING clause is used when you want to specify a condition for a group function and it is written after GROUP BY clause. The WHERE clause is used when you want to specify a condition for columns, single row functions except group functions and it is written before GROUP BY clause if it is used.

Q.Difference between procedure and function.?
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.

Q.Which is more faster – IN or EXISTS?
EXISTS is more faster than IN because EXISTS returns a Boolean value whereas IN returns a value.

Q.What a SELECT FOR UPDATE cursor represent.?
SELECT……FROM……FOR……UPDATE[OF column-reference][NOWAIT]
The processing done in a fetch loop modifies the rows that have been retrieved by the cursor. A convenient way of modifying the rows is done by a method with two parts: the FOR UPDATE clause in the cursor declaration, WHERE CURRENT OF CLAUSE in an UPDATE or declaration statement.

Q.What are various privileges that a user can grant to another user?

Q.What is difference between a formal and an actual parameter?
The variables declared in the procedure and which are passed, as arguments are called actual, the parameters in the procedure declaration. Actual parameters contain the values that are passed to a procedure and receive results. Formal parameters are the placeholders for the values of actual parameters

Q.What should be the return type for a cursor variable. Can we use a scalar data type as return type?

The return type for a cursor must be a record type.It can be declared explicitly as a user-defined or ROWTYPE can be used. eg TYPE t_studentsref IS REF CURSOR RETURN students ROWTYPE

Q.Can a primary key contain more than one columns?

Q.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.

Q.What are the uses of Database Trigger ?

Database triggers can be used to automatic data generation, audit data modifications, enforce complex Integrity constraints, and customize complex security authorizations.

Q.What is a Procedure ?

A Procedure consist of a set of SQL and PL/SQL statements that are grouped together as a unit to solve a specific problem or perform a set of related tasks.

Q.What are TYPE and ROWTYPE ? What are the advantages of using these over datatypes?

TYPE provides the data type of a variable or a database column to that variable.
ROWTYPE provides the record type that represents a entire row of a table or view or columns selected in the cursor.
The advantages are :
I. Need not know about variable’s data type
ii. If the database definition of a column in a table changes, the data type of a variable changes accordingly.

Q.What is difference between ROWTYPE and TYPE RECORD ?

ROWTYPE is to be used whenever query returns a entire row of a table or view.
TYPE rec RECORD is to be used whenever query returns columns of different table or views and variables.
E.g. TYPE r_emp is RECORD (eno emp.empno type,ename emp ename type );
e_rec emp ROWTYPE
cursor c1 is select empno,deptno from emp;
e_rec c1 ROWTYPE.

Q.What will happen after commit statement ?

Cursor C1 is
Select empno,
ename from emp;
open C1; loop
Fetch C1 into
Exit When
C1 notfound;—–
end loop;
The cursor having query as SELECT …. FOR UPDATE gets closed after COMMIT/ROLLBACK.
The cursor having query as SELECT…. does not get closed even after COMMIT/ROLLBACK.

Q.How packaged procedures and functions are called from the following?

a. Stored procedure or anonymous block
b. an application program such a PRC *C, PRO* COBOL

variable := PACKAGE NAME.FUNCTION NAME (arguments);
variable := PACKAGE NAME.FUNCTION NAME (arguments);
c. EXECUTE PACKAGE NAME.PROCEDURE if the procedures does not have any out/in-out parameters. A function can not be called.

Q.What are advantages fo Stored Procedures?

Extensibility,Modularity, Reusability, Maintainability and one time compilation.

Q.What is an Exception ? What are types of Exception ?

Exception is the error handling part of PL/SQL block. The types are Predefined and user defined. Some of Predefined exceptions are.

Q.What is Raise_application_error ?

Raise_application_error is a procedure of package DBMS_STANDARD which allows to issue an user_defined error messages from stored sub-program or database trigger.

Q.What is Oracle Label Security? (for DBA)

Oracle Label Security (formerly called Trusted Oracle MLS RDBMS) uses the VPD (Virtual Private Database) feature of Oracle8i to implement row level security. Access to rows are restricted according to a user’s security sensitivity tag or label. Oracle Label Security is configured, controlled and managed from the Policy Manager, an Enterprise Manager-based GUI utility.

Q.Explain the two type of Cursors ?

There are two types of cursors, Implicit Cursor and Explicit Cursor. PL/SQL uses Implicit Cursors for queries. User defined cursors are called Explicit Cursors. They can be declared and used.

Q.What are two parts of package ?

The two parts of package are PACKAGE SPECIFICATION & PACKAGE BODY. Package Specification contains declarations that are global to the packages and local to the schema. Package Body contains actual procedures and local declaration of the procedures and cursor declarations.

Q.What is an Lov?

A list of values is a single or multi column selection list displayed in a pop-up window

Q.What is a record Group?
A record group is an internal oracle forms data structure that has a similar column/row frame work to a database table.