ORACLE SQL Interview Practical Questions with Queries With Answers Part 3

31) In pl/sql functions what is use of out parameter even though we have return statement.

With out parameters you can get the more than one out values in the calling program. It is recommended not to use out parameters in functions. If you need more than one out values then use procedures instead of functions.

We can't use OUT paramter in function.We must have to use RETURN to pass values out of function otherwise use procs

32) What is difference between a Cursor declared in a procedure and Cursor declared in a package specification ?

A cursor declared in a package specification is global and can be accessed by other procedures or procedures in a package.

A cursor declared in a procedure is local to the procedure that can not be accessed by other procedures.

 One more differene is cursor declared in a package specification must have RETURN type 

A cursor declare in the package

specification that can be accessed

in the other procedure or procedures of the package.

A cursor declare in the procedure

that can't be accessed by other procedure.

33) What is Character Functions?

Character Functions are INITCAP, UPPER, LOWER, SUBSTR & LENGTH. Additional functions are GREATEST & LEAST. Group Functions returns results based upon groups of rows rather than one result per row, use group functions. They are AVG, COUNT, MAX, MIN & SUM.

34) What are the cursor attributes used in PL/SQL ?

%ISOPEN - to check whether cursor is open or not

% ROWCOUNT - number of rows fetched/updated/deleted.

% FOUND - to check whether cursor has fetched any row. True if rows are fetched.

% NOT FOUND - to check whether cursor has fetched any row. True if no rows are featched.

These attributes are proceeded with SQL for Implicit Cursors and with Cursor name for Explicit Cursors.

35) What are the datatypes a available in PL/SQL ?

1. Character Datatypes:

char(size), nchar(size),

varchar2(size), nvarchar2(size),

long, raw, longraw

2. Numeric Datatypes

number(p,s), numeric(p,s),

float, dec(p,s), decimal(p,s),

integer, int, smallint,real,

double precision

3. Date/Time Datatypes:

date, timestamp,

timestamp with local time zone,

timestamp with time zone,

interval year to month,

interval day to seconds

4. LOB data types:

bfile,blob,clob,nclob

5. Rowid Data types:

rowid,urowid

36) What are the components of a PL/SQL block ?

A set of related declarations and procedural statements is called block.

---

components of PL/SQL are:
declare:
(optional) variable declare 

Begin:
(Mandatory)Procedural statement

Exception:
(optional)
error to be trapped 

End: 
(Mandatory)

So BEGIN and END are required in PL/SQL block

37) How to disable multiple triggers of a table at at a time?

ALTER TABLE<TABLE NAME> DISABLE ALL TRIGGER

ALTER TABLE TT_DCB DISABLE ALL TRIGGERS

38) What are the components of a PL/SQL Block ?

Declarative part, Executable part and Exception part.

Datatypes PL/SQL

39) What is Consistency?

Consistency : Assures users that the data they are changing or viewing is not changed until the are thro' with it.

40) What are advantages fo Stored Procedures

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

Easy maintenance

? Improved data security and integrity

? Improved performance

Modularity, Reusability, one time compilation.

Faster in execution: As Procedure is a complied schema object and stored in database, hence it takes less time to execution.

41) What is PL/SQL table ?

Objects of type TABLE are called "PL/SQL tables", which are modeled as (but not the same as) database tables, PL/SQL tables use a primary PL/SQL tables can have one column and a primary key.

Cursors

TYPE tab IS TABLE OF VARCHAR2(30);

This way we can make declaration of PL/SQL tables. They are also reffed as Nested Table and are pat of PLSQL collections. They are used for bulk data processing.

42) In a Distributed Database System Can we execute two queries simultaneously ? Justify ?

As Distributed database system based on 2 phase commit,one query is independent of 2 nd query so of course we can run.

43) What is Indexes?

Indexes are optional structures associated with tables used to speed query execution and/or guarantee uniqueness. Create an index if there are frequent retrieval of fewer than 10-15% of the rows in a large table and columns are referenced frequently in the WHERE clause. Implied tradeoff is query speed vs. update speed. Oracle automatically update indexes. Concatenated index max. is 16 columns.

44) Give the structure of the function ?

FUNCTION name (argument list .....) Return datatype is

local variable declarations

Begin

executable statements

Exception

execution handlers

End;

create or replace function <name>(arg1,arg2,....)

return datatype

as

....

variable declaration

begin

...

program code

...

return <variable>

exception

exception statement

end;

45) What is a cursor ? Why Cursor is required ?

Cursor is a named private SQL area from where information can be accessed. Cursors are required to process rows individually for queries returning multiple rows.

The oracle server uses works areas

called private sql area.Here all the DML statement is executed and to processing statement.basically

it's a implicit cursor.

there are two types of cursor

1.Implicit cursor.

2.Explicit cursor.

Implicit cursor is open for all DML statement.after execute the statement cursor is atomatically closed.

Explicit cursor is created by programmer.

explicit cursor is needed when

query returns more than one rows.

In that case,programmer creates

explicit cursor.open the cursor.

then fetch the value from the active set.

after fetching all the value,

cursor is closed by programmer.

46) What is the use of CASCADE CONSTRAINTS?

When this clause is used with the DROP command, a parent table can be dropped even when a child table exists.

47) What is Date Functions?

48) What is TTITLE and BTITLE?

TTITLE & BTITLE are commands to control report headings & footers.

ttitle and btitle used in sql * in reports . to show the header and footer of the reports.

49) What is COLUMN?

COLUMN command define column headings & format data values.

50) What is COMPUTE?

command control computations on subsets created by the BREAK command.

                 *******Thank you for reading and All the best**************

Your comments will help me a lot...please give your comments which will encourage me to post more.....