Tom (seems he's answered all of our questions) talks about dynamic sql and bulk binds here:
http://asktom.oracle.com/pls/ask/f?p=4950:8:235473530985571686::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:455220177497,

-----Original Message-----
From: [Email Address Removed] [mailto:[Email Address Removed] Friday, May 30, 2003 1:20 PM
To: LazyDBA.com Discussion
Subject: RE: RE: how does oracle manage unique constraints?


i just realized something that this is a dynamic procedure and you cannot base pl/sql tables on records and get them to work with bulk binds in 8i.

i also dont think there is a way to dynamiclly instantiate pl/sql table objects at run time either... like you can in other languages.

we have tables with up to 150 columns in them and there are about 75 tables that we load from. Id have to do an enormous series of case statements. or is there another way?
>
> From: "Quintin, Richard" <[Email Address Removed] Date: 2003/05/30 Fri PM 01:17:53 EDT
> To: "LazyDBA.com Discussion" <[Email Address Removed] Subject: RE: RE: how does oracle manage unique constraints?
>
> Yea, you'll want to do something like the following (which was ripped from Tom :-)
>
> open cursor;
> loop
> fetch c bulk collect into l_data LIMIT 1000; /* This limits each collection to 1000, adjust as necessary */
> loop
> begin
> forall i in l_start .. l_data.count
> insert into t values ( l_data(i) );
> EXIT;
> exception
> when others then
> dbms_output.put_line( 'Bad row index = ' || (l_start+sql%rowcount) ||
> ' ' || sqlerrm );
> l_start := l_start + sql%rowcount + 1;
> end;
> end loop;
> exit when c%notfound;
> end loop;
>
> close cursor;
>
> -----Original Message-----
> From: [Email Address Removed] [mailto:[Email Address Removed] Sent: Friday, May 30, 2003 1:08 PM
> To: Quintin, Richard; LazyDBA.com Discussion
> Subject: RE: RE: how does oracle manage unique constraints?
>
>
> we have master tables as large as 8GB and we only have 500MB in the buffer
> cache, plus other processes going on. I think the biggest slow down comes that
> while we are doing the large ingestions those blocks are getting pushed out of
> the buffer cache and being re-queried by the anti-join.
>
> I ran statspack, but Im just digging into it and am not sure what to check for.
>
> so I think Ill need less memory with these bulk collects actually and less
> problems with re-querying the same data.
>
> only issue is multiple bulk collects. Ill play with it.
>
>
> --------
> Oracle documentation is here: http://tahiti.oracle.com/pls/tahiti/tahiti.homepage
> To unsubscribe: send a blank email to oracledba-[Email Address Removed] To subscribe: send a blank email to oracledba-[Email Address Removed] Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl
> Tell yer mates about http://www.farAwayJobs.com
> By using this list you agree to these terms:http://www.lazydba.com/legal.html
>
>


--------
Oracle documentation is here: http://tahiti.oracle.com/pls/tahiti/tahiti.homepage
To unsubscribe: send a blank email to oracledba-[Email Address Removed] subscribe: send a blank email to oracledba-[Email Address Removed] the list archive: http://www.LAZYDBA.com/odbareadmail.pl
Tell yer mates about http://www.farAwayJobs.com
By using this list you agree to these terms:http://www.lazydba.com/legal.html

Oracle LazyDBA home page