Re: Fetch to copy tables by blocks

Hum,
this solution I suggest is maybe slow (full table scan at each loop).

I will test the sqlplus copy statement and if it does not work, I will do a program in C with the OCI fuctions provided by Oracle.

What I don't know is WHY the OCI function ofetch provide the fetch utility so I can do "insert into table select " statement by blocks of N rows (without transferring data to the program) and that function is not available in sqldba or sqlplus (as it is in Sybase with "set rowcount 1000" statement).

It exists in the Oracle kernel but we cannot use it directly ???

In article <7rt5m3$jr$1_at_nnrp1.deja.com>,   laurent_pele_at_my-deja.com wrote:
> It seems to work on Oracle 8.05 if I create a view with the source
table
> and using the pseudo-column rownum.
>
> Example
> Create view Rownum_first_table as select F.*, rownum mylineNumber from
> First_table F
>
> then I can do
>
> Select * from Rownum_first_table where mylineNumber<1000;
>
> (no problem, I can do it directly)
>
> then
> Select * from Rownum_first_table where mylineNumber>=1000 and
> mylineNumber<1000;
>
> Works also [but not Select * from first_table where rownum>=1000 and
> rownum<2000; ]!
>
> I can also do insert into Second_table Select * from
Rownum_first_table
> where mylineNumber>=1000 and mylineNumber<2000;
>
> So I will try to include it in a loop in PL/SQL with a commit
statement
> and test if there is no data fetched by the query to exit the loop.
>
> I tried the example with Plus 80
> set arraysize 1000
> insert into second_table select * from first_table
>
> but all the table is copied, so it doesn't work.
>
> I haven't tried the create table nologging option because the
> target table is not created by me and may be Oracle need to create an
> internal temporary table (that can exceed temporary tablespace) as I
do
> not just copy one table to another but I have joint queries on several
> tables with own functions calls. Maybe it can be useful sometimes
>
> --
> Laurent PELE
> 13 rue Lantiez 75017 Paris
> mailto:laurent_at_pele.org
> Currency converter on http://195583.com
>
> In article <37E0D466.AE3_at_yahoo.com>,
> connor_mcdonald_at_yahoo.com wrote:
> > laurent_pele_at_my-deja.com wrote:
> > >
> > > Hello everybody
> > >
> > > I want to copy a table into another one by blocks of 1000 rows to
> avoid
> > > exceeding the rollback segments size.
> > >
> > > I know how I can do that with Sybase : I write a stored procedure
> > > with a loop that fetch a group of 1000 rows but I haven't found
the
> > > equivalent with Oracle.
> > >
> > > Can anybody may be of any help ?
> > > Thank you
> > >
> > > --
> > > Laurent PELE
> > > 13 rue Lantiez 75017 Paris
> > > mailto:laurent_at_pele.org
> > > Currency converter on http://195583.com
> > >
> > > Sent via Deja.com http://www.deja.com/
> > > Share what you know. Learn what you don't.
> >
> > See Thomas's post, and also some other options:
> >
> > create second_table nologging
> > as select * from first_table;
> >
> > OR
> >
> > create second_table nologging
> > as select * from first_table
> > where rownum < 1;
> >
> > insert /*+ APPEND */ into second_table
> > select * from first_table;
> >
> > Either of these will not have a rollback segment problem...
> >
> > HTH
> > --
> > ===========================================
> > Connor McDonald
> > "These views mine, no-one elses etc etc"
> > connor_mcdonald_at_yahoo.com
> >
> > "Some days you're the pigeon, and some days you're the statue."
> >
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Fri Sep 17 1999 - 06:06:23 CDT