Table name as a variable in PL/SQL procedure

From: Ronen <ronench_at_hotmail.com>
Date: 22 Jan 2002 00:18:05 -0800
Message-ID: <d66fd176.0201220018.306c4e21@posting.google.com>

Hello,

I would like to know how to create a procedure out of the following anonymous PL/SQL block, and how should I rewrite it in order to accept table name as variable(table name equal ‘fixposition').

Thanks in advance,

Ronen

--

CREATE TABLE fixposition(parentid number not null,

                        id number not null,
                        position number not null)

• update position starting from 0, whenever the parentid changes. DECLARE counter NUMBER; parentid_val NUMBER; id_val NUMBER; CURSOR parentid_cur IS SELECT DISTINCT parentid FROM fixposition; CURSOR id_cur IS SELECT id FROM fixposition WHERE parentid=parentid_val FOR UPDATE OF position;

BEGIN
 OPEN parentid_cur;
  FETCH parentid_cur INTO parentid_val;
  while parentid_cur%FOUND
   LOOP
    counter:=0;

     OPEN id_cur;
      FETCH id_cur INTO id_val;
      WHILE id_cur%FOUND
       LOOP
        UPDATE fixposition SET position=counter WHERE CURRENT OF
id_cur;
	counter:=counter+1;
        FETCH id_cur INTO id_val;   
       END LOOP;
     CLOSE id_cur; 
      FETCH parentid_cur INTO parentid_val;

   END LOOP;
 CLOSE parentid_cur;
  COMMIT;
End; Received on Tue Jan 22 2002 - 02:18:05 CST