Set - 6

Question 6 :

Differentiate between TRUNCATE and DELETE?

Answer :

TRUNCATE deletes much faster than DELETE
TRUNCATE
DELETE
It is a DDL statement
It is a DML statement
It is a one way trip, cannot ROLLBACK
One can Rollback
Doesn't have selective features (where clause)
Has
Doesn't fire database triggers
Does
It requires disabling of referential constraints.


Question 7 :

What is difference between a formal and an actual parameter?

Answer :

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


Question 8 :

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

Answer :

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


Question 9 :

What are different Oracle database objects?

Answer :

-TABLES
-VIEWS
-INDEXES
-SYNONYMS
-SEQUENCES
-TABLESPACES etc


Question 10 :

What is difference between SUBSTR and INSTR?

Answer :

SUBSTR returns a specified portion of a string eg SUBSTR('BCDEF',4) output BCDE INSTR provides character position in which a pattern is found in a string. eg INSTR('ABC-DC-F','-',2) output 7 (2nd occurence of '-')