Re: PL/SQL question

clint wrote:

> 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

Type the following a the SQL*Plus command prompt:

SQL> desc <table_name>

look at the column where it says "NOT NULL".

You must provide values for each and every one of these.

Daniel Morgan Received on Sat Apr 05 2003 - 12:49:06 CST