Re: PL/SQL question

I just enter it and it does not function.....it still ask me to declare the variables and the # of columns and the columns I want and not matching up......

There are columns in the destination table that do no correspond to the first table.....i need those columns to be filled in as well... this script wil be part of a trigger that will fire on a per record basis...

Please advise...

"andrewst" <member14183_at_dbforums.com> wrote in message news:2729358.1049465317_at_dbforums.com...
>
> Originally posted by Andrew Hardy
> > Clint,
> >
> > > BEGIN
> > > OPEN c_MILEAGE_CAL;
> > > LOOP
> > > EXIT WHEN c_MILEAGE_CAL%NOTFOUND;
> > > FETCH c_MILEAGE_CAL INTO
> > > V_EMPLOID, V_NUM_MILES, V_CENTSPERMILE, V_DATE, V_DESCR;
> > > V_REFUND := V_NUM_MILES * V_CENTSPERMILE;
> > > INSERT INTO EXPENSE_LOG
> > > VALUES(EXPLOG_COUNT_SEQ.NEXTVAL,V_EMPLOID,11004,V_REFUND,V_DA-
> > TE,
> > V_DESCR,
> > > V_NUM_MILES );
> > > END LOOP;
> > > CLOSE c_MILEAGE_CAL;
> > > COMMIT;
> > > END;
> >
> > Try putting the FETCH before the EXIT WHEN. The way you have it
> > the FETCH
> > fails, a row is inserted and then you exit the loop.
> >
> > Andy
>
> Or better still, use the excellent PL/SQL FOR loop, which saves a lot of
> code AND prevents you from making silly mistakes like that:
>
>
> DECLARE
> CURSOR c_MILEAGE_CAL IS
> SELECT EMPLO_ID, NUM_MILES, MIL_ALLOWANCE, ENTRY_DATE, DESCR,
> V_NUM_MILES * V_CENTSPERMILE REFUND
> FROM MILE_LOG;
> BEGIN
> FOR r IN c_MILEAGE_CAL LOOP
> INSERT INTO EXPENSE_LOG -- Please list the EXPENSE_LOG columns here
!!!!
> VALUES(EXPLOG_COUNT_SEQ.NEXTVAL,R.EMPLOID,11004,R.REFUND,R.ENTRY_DATE,
R.DESCR,
> R.NUM_MILES );
> END LOOP;
> COMMIT;
> END;
>
> Note the complete absence of variable declarations!
>
> Or, even better (simpler AND faster):
>
>
> BEGIN
> INSERT INTO EXPENSE_LOG -- Please list the EXPENSE_LOG columns here!!!!
> SELECT EMPLO_ID, NUM_MILES, MIL_ALLOWANCE, ENTRY_DATE, DESCR,
> V_NUM_MILES * V_CENTSPERMILE REFUND
> FROM MILE_LOG;
> COMMIT;
> END;
>
> (Feeling of deja vu here... haven't I made these same suggestions
> before?)
>
> --
> Posted via http://dbforums.com
Received on Sat Apr 05 2003 - 11:30:31 CST