From: davis@aurs01.uucp (Alan Davis)
Newsgroups: comp.databases.oracle
Subject: Problem with pl/sql declare cursor ... for update of tablename
Date: 6 Jan 93 23:31:12 GMT
Organization: Alcatel Network Systems, Raleigh NC

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@rockdal.aud.alcatel.com  

Alan Davis                      aur : davis@aurfs1
Alcatel Network Systems         BIX : adavis
adavis@rockdal.aud.alcatel.com  CIS : 72317,3661