Answer Posted / tripti gour

In order to process a sql statements oracle will allocate an
area of memory known as the context area. The context area
contains information necessary to complete the processing,
including the number of rows processed by the select
statement, a pointer to the parsed representation of the
statement, and in the case of a query, the active set,
which is the set of rows returned by the query.
A cursor is a handle or pointer to the context area.
Sql cursor attributes are:
1. SQL%ROWCOUNT: number of rows affected by the most recent
sql statement (an integer value).
2. SQL%FOUND: Boolean attribute that evaluates to TRUE if
the most recent SQL statement affects one or more rows.
3. SQL%NOTFOUND: Boolean attribute that evaluates to true if
the most recent SQL statement does not affects any rows.
4. SQL%ISOPEN: always evaluates to false because pl/sql
closes implicit cursors immediately after they are executed.

Cursor Types:
1. Implicit cursor
2. Explicit Cursor
3. REF cursor
4. Parametrized cursor
5. FOR LOOP Cursor

Implicit cursor:

a.Are opened implicitly by oracle whenever a DML or select
statement is executed.
b.Opened, fetched, closes internally.
c.Un-named cursors
d.Attributes: sql%isopen, sql%found, sql%notfound, sql%rowcount.

Explicit cursor

•Are declared and opened explicitly by developers to
manipulate multiple rows returned by queries one by one.
•Manually we have to declare, open, fetch, and close it.
•Name given to a context area.
•Attributes: cur_name%isopen, cur_name%found,
ur_name%notfound, cur_name%rowcount.

REF CURSOR EXAMPLE

declare
type t1 is ref cursor;
v1 t1;
begin
open v1 for select * from inv;
open v1 for select * from inv2;
end;

Parameterized cursor:

We can pass parameters for cursor as like procedures and
functions.
Syntax: cursor cursor_name[parameter_name datatype] is
select statement;
the advantage of parameterized cursor is, a single cursor
can be opened and closed several times in a block, returning
different active set in each occasion.
Note: formal parameters should not be mentioned with data type

Thanks,
Tripti

What happens to the current transaction if a ddl statement is executed?

1057

---

How many types of table in Oracle?

1169

---

How to create additional tablespaces for an new database?

1072

---

WHAT IS THE DEFINITION OF DEFAULT CUSTOMER IN AR?

2245

---

What is the difference between $oracle_base and $oracle_home?

1136

---

What is SQL access advisor in Oracle?

1228

---

What is the difference between PFILE and SPFILE in Oracle?

1106

---

1) What is ONE_SIZE_FITS_ALL approach? 2) Explain the Common & Variable Header of DATA FILE? 3) What are the Drawbacks to using OMF DB? and the Advantages? 4) List out the Advantages of Undo T.spaces over the Undo SEGMENT? 5) Difference between the Temporary tablespace with TEMPFILE and the Tablespace with TEMPORARY Keyword? 6) What are the situation extents are freeing for reuse.

2140

---

Explain parameter file in oracle.

1079

---

What do you understand by a database object? Can you list a few of them?

1061

---

How do you increase the OS limitation for open files (LINUX and/or Solaris)?

1975

---

What is difference between cartesian join and cross join?

1151

---

Select all the employees who were hired in last 2 years and who works in dept where max managers are working.

3021

---

What is the difference between count (*), count (expression), count (distinct expression)?

1046

---

Who developed oracle & when?

1054

---