Re: Running Insert Within Loop?

PL/SQL is notorious for giving misleading information during a compile/run.

I think the problem is in the actual insert statement:

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; You need to change it to:
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 ) You can also remove the "order by" clause. It's not needed.

One thing that I need to point out is that the "COUNT(STATE1)" will return the count where STATE1 is OTHER than NULL. If it's NULL, it won't get counted.

JP

cvonancken_at_orkand.com (Curtis VonAncken) wrote in message news:<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 Wed Mar 27 2002 - 14:28:02 CET