Oracle Interview Questions Page 5

1.What is apseudo column. Give some examples

It is a column that is not an actual column in the table.

eg USER, UID, SYSDATE, ROWNUM, ROWID, NULL, AND LEVEL.

Suppose customer table is there having different columns like customer no,payments.What will be the query to select top three max payments.

SELECT customer_no, payments from customer C1 WHERE 3<=(SELECT COUNT(*) fromcustomer C2 WHERE C1.payment <= C2.payment)

2.What is thepurpose of a cluster.

Oracle does not allow a user to specifically locate tables, since that is apart of the function of the RDBMS. However, for the purpose of increasingperformance, oracle allows a developer to create a CLUSTER. A CLUSTER providesa means for storing data from different tables together for faster retrievalthan if the table placement were left to the RDBMS.

3.What is acursor.

Oracle uses work area to execute SQL statements and store processinginformation PL/SQL construct called a cursor lets you name a work area andaccess its stored information A cursor is a mechanism used to fetch more thanone row in a Pl/SQl block.

4.Differencebetween an implicit & an explicit cursor.

PL/SQL declares a cursor implicitly for all SQL data manipulation statements,including quries that return only one row. However,queries that return morethan one row you must declare an explicit cursor or use a cursor FOR loop.

Explicit cursor is a cursor in which the cursor name is explicitly assigned toa SELECT statement via the CURSOR...IS statement. An implicit cursor is usedfor all SQL statements Declare, Open, Fetch, Close. An explicit cursors areused to process multirow SELECT statements An implicit cursor is used toprocess INSERT, UPDATE, DELETE and single row SELECT. .INTO statements.

5.What are cursorattributes

%ROWCOUNT
%NOTFOUND
%FOUND
%ISOPEN

6.What is acursor for loop

Cursor For Loop is a loop where oracle implicitly declares a loop variable, theloop index that of the same record type as the cursor's record

7.Differencebetween NO DATA FOUND and %NOTFOUND

NO DATA FOUND is an exception raised only for the SELECT....INTO statementswhen the where clause of the querydoes not match any rows. When the whereclause of the explicit cursor does not match any rows the %NOTFOUND attributeis set to TRUE instead.

8.What a SELECTFOR UPDATE cursor represent.

SELECT......FROM......FOR......UPDATE[OF column-reference][NOWAIT] Theprocessing done in a fetch loop modifies the rows that have been retrieved bythe cursor.
A convenient way of modifying the rows is done by a method with two parts: theFOR UPDATE clause in the cursor declaration, WHERE CURRENT OF CLAUSE in anUPDATE or declaration statement.

9.What 'WHERECURRENT OF ' clause does in a cursor.

LOOP

SELECTnum_creditsINTOv_numcreditsFROM classes

WHEREdept=123 and course=101;

UPDATEstudents

SETcurrent_credits=current_credits+v_numcredits

WHERECURRENT OFX;

ENDLOOP

COMMIT;

END;

10.What is use of acursor variable? How it is defined.

A cursor variable is associated with different statements at run time, whichcan hold different values at run time. Static cursors can only be associatedwith one run time query. A cursor variable is reference type(like a pointer inC).
Declaring a cursor variable: TYPE type_name IS REF CURSOR RETURN return_typetype_name is the name of the reference type,return_type is a record typeindicating the types of the select list that will eventually be returned by thecursor variable.

11.What should bethe return type for a cursor variable.Can we use a scalar data type as returntype.

The return type for a cursor must be a record type.It can be declaredexplicitly as a user-defined or %ROWTYPE can be used. eg TYPE t_studentsref ISREF CURSOR RETURN students%ROWTYPE

12.How you open andclose a cursor variable.Why it is required.

OPEN cursor variable FOR SELECT...Statement CLOSE cursor variable In order toassociate a cursor variable with a particular SELECT statement OPEN syntax isused.In order to free the resources used for the query CLOSE statement is used.

13.How you werepassing cursor variables in PL/SQL 2.2.

In PL/SQL 2.2 cursor variables cannot be declared in a package.This is becausethe storage for a cursor variable has to be allocated using Pro*C or OCI withversion 2.2,the only means of passing a cursor variable to a PL/SQL block isvia bind variable or a procedure parameter.

14.Can cursorvariables be stored in PL/SQL tables.If yes how.If not why.

No, a cursor variable points a row which cannot be stored in a two-dimensionalPL/SQL table.

15.Differencebetween procedure and function.

Functions are named PL/SQL blocks that return a value and can be called witharguments procedure a named block that can be called with parameter. Aprocedure all is a PL/SQL statement by itself, while a Function call is calledas part of an expression.

16.What aredifferent modes of parameters used in functions and procedures.

IN
OUT
INOUT

17.What isdifference between a formal and an actual parameter

The variables declared in the procedure and which are passed, as arguments arecalled actual, the parameters in the procedure declaration. Actual parameterscontain the values that are passed to a procedure and receive results. Formalparameters are the placeholders for the values of actual parameters

18.Can the defaultvalues be assigned to actual parameters.

Yes

19.Can a functiontake OUT parameters.If not why.

No.A function has to return a value,an OUT parameter cannot return a value.

20.What is syntaxfor dropping a procedure and a function .Are these operations possible.

Drop Procedure procedure_name

Drop Function function_name

21.What are ORACLEPRECOMPILERS.

Using ORACLE PRECOMPILERS ,SQL statements and PL/SQL blocks can be containedinside 3GL programs written in C,C++,COBOL,PASCAL, FORTRAN,PL/1 AND ADA.
The Precompilers are known as Pro*C,Pro*Cobol,... This form of PL/SQL is knownas embedded pl/sql,the language in which pl/sql is embedded is known as thehost language.
The prcompiler translates the embedded SQL and pl/sql ststements into calls tothe precompiler runtime library.The output must be compiled and linked withthis library to creater an executable.

22.What is OCI.What are its uses.

Oracle Call Interface is a method of accesing database from a 3GL program.Uses--No precompiler is required,PL/SQL blocks are executed like other DMLstatements.

The OCI library provides

-functions to parse SQLstatemets

-bind input variables

-bind output variables

-execute statements

-fetch the results

23.Differencebetween database triggers and form triggers.

a) Data base trigger(DBT) fires when a DML operation is performed on a database table.Form trigger(FT) Fires when user presses a key or navigates betweenfields on the screen
b) Can be row level or statement level No distinction between row level andstatement level.
c) Can manipulate data stored in Oracle tables via SQL Can manipulate data inOracle tables as well as variables in forms.
d) Can be fired from any session executing the triggering DML statements. Canbe fired only from the form that define the trigger.
e) Can cause other database triggers to fire.Can cause other database triggersto fire,but not other form triggers.

24.What is anUTL_FILE.What are different procedures and functions associated

with it. UTL_FILE is a package that adds the ability to read and write tooperating system files Procedures associated with it are FCLOSE, FCLOSE_ALL and5 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 areFOPEN, ISOPEN.

25.Can you use acommit statement within a database trigger.

No

26.What is themaximum buffer size that can be specified using the DBMS_OUTPUT.ENABLEfunction?

1,000,000

Posted in: Oracle