Re: Accessing Table's rows

In article <1993Feb9.062202.20208_at_cssc-melb.tansu.com.au>, ttabet_at_currie.tansu.com.au (Tabet Tabet) writes:
|> Hi all,
|>
|> I am using Oracle RDBMS (SQL - PLUS) version 6.031, and I want to access each row
|> in a table one by one, using sql script file. Curently, I am having a problem
|>
|> Any idea how to achieve this.
|>
|> ( I have tried FETCH CURSOR .... but did not work).
|>
|>
|> Thanks in advance.

T,

I tried to email this to you, but it bounced, so here it is for the net to see. This seems to be a fairly good method for doing what it appears you wish to do.

DECLARE CURSOR cursor_name IS SELECT *

                      FROM table_name
                      WHERE condition;

cursor_name2 cursor_name%rowtype;

BEGIN OPEN cursor_name;
  LOOP
  FETCH cursor_name INTO cursor_name2;
  EXIT WHEN cursor_name%NOTFOUND;

  perform whatever you would like on the columns at this point, including   INSERTS, UPDATES, DELETES, ...   END LOOP;
  COMMIT; <- only if INSERT, UPDATE or DELETES performed in loop.

CLOSE cusror_name;

END;
/
EXIT;

>From the top; The cursor as you know defines which records to include (you may leave out the WHERE clause if all records are desired. The line:

cursor_name2 cursor_name%rowtype;

Is used to define a record, which will later be used by the INTO clause of the FETCH command.

You then OPEN your cursor. LOOP until the cursor_name%NOTFOUND, and perform your tasks on the rows selected. One thing to remember, the rows are FETCHed into the record cursor_name2 not cursor_name. Therefore, when you want to manipulate the column refer to it as cursor_name2.column_name not cursor_name.column_name.

You then CLOSE the cursor, END the routine and EXIT. This may be invoked in sqlplus by using the _at_routine_name command.

Good Luck,

Kelvin R. Hubbard                           email: khubbard_at_ems.cdc.com
Active Software, Inc.           
C/O Empros Systems International            Phone: (612) 553-4492
2300 Berkshire Lane North                     FAX: (612) 553-4018
Plymouth, Minnesota  55441-3694 USA      Mailstop: PLY008

                        In search of the Big Iron.
==========================================================================