How you open and close a cursor variable.Why it is required?

OPEN cursor variable FOR SELECT...Statement

CLOSE cursor variable In order to associate a cursor variable with a particular SELECT statement OPEN syntax is used. In order to free the resources used for the query CLOSE statement is used.

Interview Candidate
Aug 29th, 2004
5
2790

SQL

Answer

Showing Answers 1 - 5 of 5 Answers

Mainak Aich

Mar 2nd, 2006

If a cursor is not opened it can't be used by the pl/sql block and eventually no rows will be fetched from the cursor.So we've to open the cursor before we use it.A cursor need to be closed because it allocates a memory when we open the cursor.So if we don't close it ,it will unnecessaryly hold a chuck of valuable main memory.So we must close it after the cursor has been used.

S.Senthil Kumar

Oct 31st, 2006

Without Open Cursor statement the data which is in the table cannot be load into cursor variable.The Close Cursor statement deallocates the memory occupied by the cursor

g_sidhu
Profile

Feb 6th, 2008

• The OPEN-FOR statement associates a cursor variable with a multirow query, executes the query, identifies the result set, and positions the cursor to point to the first row of the result set.
• The FETCH statement returns a row from the result set of a multirow query, assigns the values of select-list items to corresponding variables or fields in the INTO clause, increments the count kept by %ROWCOUNT, and advances the cursor to the next row.
• The CLOSE statement disables a cursor variable.