Regarding traversing through the FETCH statement

[Guest]

Hi all,

with the Fetch statement the next value in the Result set of a select query defined in a cursor can be retrieved, is it possible to traverse the result set of the select query with Fetch statement like Fetch first or Fetch last as in SQL server ?

[Tim...]

Hi.

You can only move from start to end like:

OPEN c_my_cursor;
LOOP
FETCH c_my_cursor
INTO l_my_variable;
EXIT WHEN c_my_cursor%NOTFOUND

-- Do something
END LOOP;
CLOSE c_my_cursor;

But it's bette practise to do:

FOR i IN c_my_cursor LOOP
-- Do something
END LOOP;

And even better to do:

FOR i IN (SELECT col_1, col_2
FROM my_table
WHERE ......)
LOOP
-- Do something
END LOOP;

Cheers

Tim...

[Guest]

Thanx Tim,

following is my understanding from ur answer, please correct me if i was wrong, thank you Tim,

So to traverse with fetch stmt is not possible in Oracle... I have to do it with loops and other logics and we are not provided with any inbuilt functions or properties ...

Thank you once again Tim,
Mike

[Tim...]

Hi.

I'm not sure what you mean by traverse. You don't have to use a loop if you don't want to, but if you want to process every row in a cursor this is the most sensible approach to take.

There is no way to jump straight to the end, or to a specific row of a cursor, unless you actually change the query driving the cursor, which is not what you want to do.

An approach that might help you is to use a BULK COLLECT into a PL/SQL table. This is a collection, like an array, which allows you to jump around to the first or last or any row you want. Like this:

SET SERVEROUTPUT ON
DECLARE
TYPE emp_tab IS TABLE OF emp%ROWTYPE;

t_tab emp_tab := emp_tab();
BEGIN
SELECT *
BULK COLLECT INTO t_tab
FROM emp;

DBMS_OUTPUT.put_line('Last ENAME : ' || t_tab(t_tab.last).ename);
DBMS_OUTPUT.put_line('First ENAME: ' || t_tab(t_tab.first).ename);
END;
/

You might want to read this also:

http://www.oracle-base.com/articles/9i/ ... sing9i.php

Be careful not to use this type of prcessing for everything. These collections are held in memory on the DB server so they are not suitable for massive queries unless you are happy to tie up lots of memory.

Cheers

Tim...

[Guest]

Thanks for your efforts Tim,

Let me rephrase my question,
is it possible to access the value of a specific row (instead of a fetch next)of a select query in a fetch statement or as u said cud i access the last value or a first value from a fetch statement

If this does not works out i could try for the bulk collections as u suggested


Thank you again Tim

Mike

[Tim...]

OK.

You definitely can't do that. Once a cursor is defined you can only start at the top and work down. To do it in revers you could obviously order the cursor differently, but you are still traversing it top to bottom.

Cheers

Tim...

[Guest]

Thanks Tim

I got to do a work around for the required...

Thanks again for your efforts and immiediate response Tim

Cheers
Mike

[Tim...]