Problem with pl/sql declare cursor ... for update of tablename

From: Alan Davis <davis_at_aurs01.uucp>
Date: 6 Jan 93 23:31:12 GMT
Message-ID: <61843_at_aurs01.UUCP>

I am going bonkers over this :

Given the table definition :

---

CREATE TABLE PBAPINS
   (

   BOARD_ENTITY         INTEGER,
   REFDES               CHAR(20),
   PIN_NUMBER           CHAR(20),
   PIN_NAME             CHAR(20),
   PAD_STACK_NAME       CHAR(20),
   NET_NAME             CHAR(20),
   DRILL_HOLE_NAME      NUMBER,
   DRILL_HOLE_X         NUMBER,
   DRILL_HOLE_Y         NUMBER,
   PIN_X                NUMBER,
   PIN_Y                NUMBER,
   NODE_NUMBER          INTEGER

   )

---

and the Ultrix C function to add a sequential number to each row based on the net_name column :

    status = pbadb_number_nodes(99999); /* sample function call */

---

/* int pbadb_number_nodes(long bd_entity)
** pl/sql block
** Add a node number to each group of pins with the same netname

*/

int pbadb_number_nodes(long bd_entity)
{

    EXEC SQL BEGIN DECLARE SECTION;

        long entity;
    EXEC SQL END DECLARE SECTION;        

    entity = bd_entity; /* kludge because I can't bind C parameters to pl/sql */     oraca.orastxtf = 3; /* turn on line number checking in oraca */

  EXEC SQL EXECUTE <---- line 431 in source file     

    DECLARE

        CURSOR net_cur IS SELECT *
	FROM pbapins 
            WHERE board_entity = :entity 
            ORDER BY net_name
            FOR UPDATE OF pbapins;

        net_rec    net_cur%ROWTYPE;
        curnode INTEGER := 0;
        last_net VARCHAR2;
            
    BEGIN
        last_net := ' ';

        OPEN net_cur;
        LOOP
            FETCH net_cur INTO net_rec;
            EXIT WHEN net_cur%NOTFOUND;

            IF net_rec.net_name NOT LIKE last_net THEN
                curnode := curnode + 1;
                UPDATE pbapins SET pbapins.node_number = (curnode)
                    WHERE CURRENT OF net_cur;
            ELSE
                UPDATE pbapins SET pbapins.node_number = (curnode)
                    WHERE CURRENT OF net_cur;
            END IF;

            last_net := net_rec.net_name;

        END LOOP;
    CLOSE net_cur;        

    END;
  END-EXEC;   return(0);

error:

    handle_error("pbadb_number_nodes");
/* NOTREACHED */
return (1);     

}
/* end pbadb_number_nodes */

---

The runtime error message is :

---

An error has occurred in pbadb_number_nodes. ORA-06503: PL/SQL: error 0 - Unhandled exception ORA-00904: invalid column name Num of rows processed is 0.
Last SQL statement:
 DECLARE CURSOR NET_CUR IS SELECT BOARD_ENTITY,REFDES,PIN_NUMBER,PAD_ST Line number: 431

---

What is the problem 'ere?
I've played with this for days with no luck getting it to work unless I remove the update ... pbapins stuff.

I am running Oracle on Ultrix:

ORACLE RDBMS V6.0.34.2.1, transaction processing option - Production PL/SQL V1.0.34.0.1 - Production
ORACLE Precompiler: Version 1.3.18.1.3 on Wed Jan 6 18:11:43 1993  

---

Alan Davis                      
Alcatel Network Systems         

adavis_at_rockdal.aud.alcatel.com

Alan Davis                      aur : davis_at_aurfs1
Alcatel Network Systems         BIX : adavis

adavis_at_rockdal.aud.alcatel.com CIS : 72317,3661 Received on Thu Jan 07 1993 - 00:31:12 CET