Home » SQL & PL/SQL » SQL & PL/SQL » Cursor ...
icon1.gif  Cursor ... [message #286630] Sun, 09 December 2007 02:53 Go to next message
chaitan18
Messages: 4
Registered: December 2007
Junior Member
Hi,

I just started learning about oracle database. Can someone please explain what a cursor is ? When I log into sqlplus, and issue a DML statement say "select * from emp", is a cursor created?

orelse is cursor only associated with PL-SQL?

Thank you so much,
Chaitan
Re: Cursor ... [message #286632 is a reply to message #286630] Sun, 09 December 2007 03:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:

When I log into sqlplus, and issue a DML statement say "select * from emp", is a cursor created?

Yes, every SQL statement needs a cursor to be executed.

PL/SQL User's Guide and Reference
Chapter 1 Overview of PL/SQL
Section Understanding the Main Features of PL/SQL
Subsection Understanding PL/SQL Data Abstraction
Paragraph Cursors

Chapter 6 Performing SQL Operations from PL/SQL
Section Managing Cursors in PL/SQL

Regards
Michel
Re: Cursor ... [message #286667 is a reply to message #286630] Sun, 09 December 2007 11:30 Go to previous messageGo to next message
chaitan18
Messages: 4
Registered: December 2007
Junior Member
Thank you for your time Michel. I am reading a topic on PGA and am trying to understand the concept. Can you please confirm one more thing -

In a Dedicated Server, When I issue a statement such as:

select * from emp order by emp_id;

Before this SQL statement is parsed, Oracle creates implicit Cursor and automatically manages opening and closing of cursors.

After Fetching the records into memory, Oracle sorts data in PGA based on SORT_AREA_SIZE (if set) and the results of the sort are then stored in the cursor and then passed to SQLPLUS in the form of array. After all the results are displayed the cursor is closed.

Is my understanding correct?

Chaitan
Re: Cursor ... [message #286670 is a reply to message #286667] Sun, 09 December 2007 11:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
The cursor steps are:
- open (creation)
- parse
- execute (determine or build the result set)
- fetch(es)
- close

Once you close the cursor you can no more fetch it.

Database Concepts
Chapter 24 SQL, PL/SQL, and Java
Section Overview of SQL
Subsection SQL Processing

Regards
Michel
Re: Cursor ... [message #286684 is a reply to message #286670] Sun, 09 December 2007 18:36 Go to previous messageGo to next message
chaitan18
Messages: 4
Registered: December 2007
Junior Member
Thankyou Michel.
Re: Cursor ... [message #286762 is a reply to message #286630] Mon, 10 December 2007 01:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
A new thread on AskTom with the same question: Cursor in SQL

Regards
Michel

[Updated on: Mon, 10 December 2007 01:39]

Report message to a moderator

Re: Cursor ... [message #286976 is a reply to message #286762] Mon, 10 December 2007 14:49 Go to previous message
chaitan18
Messages: 4
Registered: December 2007
Junior Member
Thank you Michel. I have seen the link. Thank you once again for your help.

Chaitan
Previous Topic: DBMS_SCHEDULER
Next Topic: multiple self join
Goto Forum:
  


Current Time: Sat Feb 08 07:51:53 CST 2025