Running Insert Within Loop?

From: Curtis VonAncken <cvonancken_at_orkand.com>
Date: 26 Mar 2002 12:51:50 -0800
Message-ID: <b712ca72.0203261251.270b0eef_at_posting.google.com>

Hello folks, I am a longtime SQL Server person and relatively new to Oracle and PL/SQL. Can someone please tell me what I am doing wrong here? I simply want to loop through this insert statement depending on the results that I have placed in the cursor/record. This is just a small portion of of a package i am building but the only seciton that seems to be hanging me up. Ideas?? BTW If I take out the entire insert statement and replace it with;
dbms_output.put_line (table1a_rec.st_cur); it works that way. I can also run the INSERT separately.

DECLARE

	CURSOR table1a_cur IS
			SELECT
			(State)AS st_cur
			FROM master
			WHERE operation_type in (1,3)
			GROUP BY State
			ORDER BY State;
	table1a_rec table1a_cur%ROWTYPE;

BEGIN
IF NOT table1a_cur%ISOPEN
THEN
        OPEN table1a_cur;
END IF;

	FETCH table1a_cur INTO table1a_rec;
	LOOP
			INSERT INTO tmptbl_table1a_TOTALS
			SELECT
			STATE1, COUNT(STATE1), SUM(PROD1), COUNT(STATE2), SUM(PROD2)
			FROM tmptbl_table1a_year1, tmptbl_table1a_year2
			WHERE STATE1 = STATE2
			AND STATE1 = table1a_rec.st_cur
			GROUP BY STATE1
			ORDER BY STATE1;
	FETCH table1a_cur INTO table1a_rec;

EXIT WHEN table1a_cur%NOTFOUND;
END LOOP;
IF table1a_cur%ISOPEN
THEN
        CLOSE table1a_cur;
END IF;
END;
/

DECLARE
*
ERROR at line 1:
ORA-06540: PL/SQL: compilation error
ORA-06553: PLS-906: Compilation is not possible Received on Tue Mar 26 2002 - 21:51:50 CET