Oracle SQL/PLSQL Questions & Answers2:

Oracle SQL/PLSQL Questions & Answers:

What are cursors and how many types of cursors?

Cursor is a pointer variable in a memory and use for DML operations. Cursor basically is a private SQL memory area it is also use to improve the performance of the database.

There are two types of cursors.

1- Implicit cursor

2- Explicit cursor

Implicit cursor use oracles to manipulate the DML opperations and programmer have no control on this type of cursor. We use sql%notfound and sql%rowcount in implicit cursor. Explicit cursors are created by the programmer and programmer can control it by using these keywords Fetch, Open and close.

 Define Oracle cursor attributes.

There are five types of cursors attributes

1- %isopen      (Verify whether this cursor is open or not)

2- %found       (If cursor fetch the data then %found return true)

3- %notfound (If cursor fetches not data then %notfound return true)

4- %rowcount (It return no. of rows that are in cursor and also give position of record)

5- %bulk_rowcount (%bulk_rowcount is same like %rowcount but it is used in bulk)

How can we pass variable in cursors?

We can pass the variable in cursor by using parameter cursor. Just we give the variable in it like cursor C1 (my_variable number).

 What is the CASE statement and where we can use it?

CASE statement is just like IT-THEN-ELSE condition it is normally use when we are using some type of if else condition. When condition is founded true the case statement return the result and no further evaluating of data.

What is INDEX, when and where we can use it?

Index is using for performance tuning and also give us faster data retrieval of record from tables. Index can create on any column in table by default oracle B-Tree index.

Which operator that are not coming in Indexing?

NOT IN operator is not coming in indexing.

 How we can get limited rows in SQL?

We can get limited rows in oracle database by using ROWNUM or HAVING clause.

 What is the difference between Sub query and Co-related query?

One query contains more than one sub-query. The inner most query run first then second outer then third outer and so on. If we will not use relation on inner query to outer query then this query is called sub-query if we use relation then this query called Correlated query.

Example of Sub Query:

SELECT EMP_NAME, DEPTNO FROM EMP

WHERE EMP_NAME IN (SELECT EMP_NAME FROM DEPT)

Example of Correlated Query:

SELECT EMP_NAME, DEPTNO FROM EMP

WHERE EMP_NAME IN (SELECT EMP_NAME FROM DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO)

 What is exception handling and how we can handle the exception in Oracle?

Exception is an error situation which arises during program execution. When an error occurs exception is raised normally execution is stopped and control transfers to exception handling part. Exception handlers are routines written to handle the exception. The exceptions can be internally defined User-defined exception. In oracle we can handle exception by using these statements.

EXCEPTION WHEN

DUP_VAL_ON_INDEX

NOT_LOGGED_ON

NO_DATA_FOUND

TOO_MANY_ROWS

VALUE_ERROR

 Can we define more than one exception in one block?

Yes we can define more than one exception in one block in oracle form builder like we will       use DUP_VAL_ON_INDEX and NO_DATA_FOUND simultaneously

 What is SQL sequence and what are their attributes?

Sequence is an Oracle object that is using for generating the sequence of number. IT=T is normally using for column that have primary key.

“CREATE SEQUENCE TRANS_ID_SEQ

START WITH 1

MAXVALUE 99999999

MINVALUE 1

NOCYCLE

NOCACHE

NOORDER”

 What is CYCLE AND NO CYCLE in sequence?

When we use “CYCLE” option in sequence and sequence reaches its MAXVALUE, it will start again from the MINVALUE. That is not good when we are using the sequence for primary key. In “NOCYCLE” option cursor did not start from minimum value again.

 

Back (Page 1)                   Next (Page 3)

Other Related Questions & Answers: