Actually, we just upgraded to 8.0
I guess this lowers my options, doesnt it?
I'm 30. Not quite senile. Sorry.
maa
- Ryan <[EMAIL PROTECTED]> wrote:
> if your in 8i you can only use one field for a bulk collect( i think
> ic ant
> remember). I think they fixed that in 9i.
>
> i meant whatever.
>
> i also 'think' to do a bulk collect in 8i you cant anchor the pl/sql
> table.
>
> has to be varchar2, number, etc... im pretty sure you can do it with
> a
> rowid. I dont remember either.
>
> Im 29. Im senile. Sorry.
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> Sent: Tuesday, July 08, 2003 8:04 PM
>
>
> > --- Ryan <[EMAIL PROTECTED]> wrote:
> > > uhhh... this is pretty easy.
> > >
> > > declare
> > > type mytable is table of Whatever
> >
> > should I really put whatever, or table%rowtype?
> >
> >
> > > l_table mytable;
> > > l_updatevalue mytable
> >
> > are both variables of the same type?
> >
> >
> > >
> > > begin
> > >
> > > select rowid, updateValue
> >
> > do you mean column-to-be-updated as opposed to "updatevalue"?
> >
> >
> >
> > > bulk collect into
> > > l_table;
> > > from table;
> > >
> > > now update off the rowid for your value.
> > >
> > > go through like 5000 records in the pl/sql table at a time.
> >
> > like how? Its not an array...
> >
> > >
> > > end;
> > >
> > > ----- Original Message -----
> > > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> > > Sent: Tuesday, July 08, 2003 7:29 PM
> > >
> > >
> > > > huh???
> > > >
> > > > --- Ryan <[EMAIL PROTECTED]> wrote:
> > > > > bulk collect the flag into a pl/sql table. forall with a
> limit
> > > clause
> > > > > and then commit after hitting each limit.
> > > > > this is on asktom.
> > > >
> > > > anything more down the earth for me please?
> > > >
> > > > thx
> > > > maa
> > > >
> > > >
> > > >
> > > > > ----- Original Message -----
> > > > > To: "Multiple recipients of list ORACLE-L"
> <[EMAIL PROTECTED]>
> > > > > Sent: Tuesday, July 08, 2003 7:09 PM
> > > > >
> > > > >
> > > > > > I have a table of about one million records.
> > > > > >
> > > > > > About 100,000 of them have a flag which I need it set to
> 0.
> > > > > >
> > > > > > Because of the size of the rollback segment, I cannot
> > > > > > update them all and then commit, I need to do it in
> > > > > > sets of 1000 records.
> > > > > >
> > > > > > Do I need a cursor for this?
> > > > > >
> > > > > > I had something like that:
> > > > > > DECLARE
> > > > > > I BINARY_INTEGER;
> > > > > >
> > > > > >
> > > > > > Cursor MyCursor IS
> > > > > > SELECT *
> > > > > > FROM (Table_Name)
> > > > > > WHERE (Condition)
> > > > > > FOR UPDATE;
> > > > > >
> > > > > > BEGIN
> > > > > > I := 0;
> > > > > > FOR MyRec IN MyCursor LOOP
> > > > > > UPDATE (Table_name)
> > > > > > SET delete_flag = 1
> > > > > > WHERE CURRENT OF MyCursor;
> > > > > >
> > > > > > I := I + 1;
> > > > > > IF MOD(I, 1000) = 0 THEN
> > > > > > COMMIT;
> > > > > > END IF;
> > > > > >
> > > > > > END LOOP;
> > > > > > END;
> > > > > > /
> > > > > >
> > > > > > But "FOR UPDATE" does not really work well, and at the
> 1000th
> > > > > record
> > > > > > when it reaches the commit, its dropping out of the loop.
> > > > > >
> > > > > > Can I use ROWNUM to update them in batches of 1000 per
> time?
> > > > > >
> > > > > > Thanks,
> > > > > > maa
> > > > > >
> > > > > > --
> > > > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > > > > > --
> > > > > > Author: Maryann Atkinson
> > > > > > INET: [EMAIL PROTECTED]
> > > > > >
> > > > > > Fat City Network Services -- 858-538-5051
> > > http://www.fatcity.com
> > > > > > San Diego, California -- Mailing list and web
> hosting
> > > > > services
> > > > > >
> > > > >
> > >
> ---------------------------------------------------------------------
> > > > > > To REMOVE yourself from this mailing list, send an E-Mail
> > > message
> > > > > > to: [EMAIL PROTECTED] (note EXACT spelling of
> 'ListGuru')
> > > and in
> > > > > > the message BODY, include a line containing: UNSUB ORACLE-L
> > > > > > (or the name of mailing list you want to be removed from).
> You
> > > may
> > > > > > also send the HELP command for other information (like
> > > > > subscribing).
> > > > > >
> > > > >
> > > > > --
> > > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > > > > --
> > > > > Author: Ryan
> > > > > INET: [EMAIL PROTECTED]
> > > > >
> > > > > Fat City Network Services -- 858-538-5051
> > > http://www.fatcity.com
> > > > > San Diego, California -- Mailing list and web hosting
> > > services
> > > > >
> > >
> ---------------------------------------------------------------------
> > > > > To REMOVE yourself from this mailing list, send an E-Mail
> message
> > > > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru')
> and
> > > in
> > > > > the message BODY, include a line containing: UNSUB ORACLE-L
> > > > > (or the name of mailing list you want to be removed from).
> You
> > > may
> > > > > also send the HELP command for other information (like
> > > subscribing).
> > > >
> > > >
> > > > __________________________________
> > > > Do you Yahoo!?
> > > > SBC Yahoo! DSL - Now only $29.95 per month!
> > > > http://sbc.yahoo.com
> > > > --
> > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > > > --
> > > > Author: MaryAnn Atkinson
> > > > INET: [EMAIL PROTECTED]
> > > >
> > > > Fat City Network Services -- 858-538-5051
> http://www.fatcity.com
> > > > San Diego, California -- Mailing list and web hosting
> > > services
> > > >
> > >
> ---------------------------------------------------------------------
> > > > To REMOVE yourself from this mailing list, send an E-Mail
> message
> > > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru')
> and in
> > > > the message BODY, include a line containing: UNSUB ORACLE-L
> > > > (or the name of mailing list you want to be removed from). You
> may
> > > > also send the HELP command for other information (like
> > > subscribing).
> > > >
> > >
> > > --
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > > --
> > > Author: Ryan
> > > INET: [EMAIL PROTECTED]
> > >
> > > Fat City Network Services -- 858-538-5051
> http://www.fatcity.com
> > > San Diego, California -- Mailing list and web hosting
> services
> > >
> ---------------------------------------------------------------------
> > > To REMOVE yourself from this mailing list, send an E-Mail message
> > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and
> in
> > > the message BODY, include a line containing: UNSUB ORACLE-L
> > > (or the name of mailing list you want to be removed from). You
> may
> > > also send the HELP command for other information (like
> subscribing).
> >
> >
> > __________________________________
> > Do you Yahoo!?
> > SBC Yahoo! DSL - Now only $29.95 per month!
> > http://sbc.yahoo.com
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: MaryAnn Atkinson
> > INET: [EMAIL PROTECTED]
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting
> services
> >
> ---------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like
> subscribing).
> >
> >
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Ryan
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
Do you Yahoo!?
SBC Yahoo! DSL - Now only $29.95 per month!
http://sbc.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: MaryAnn Atkinson
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Tue Jul 08 2003 - 18:40:58 CDT
