Re: Copying tree-like data

Pardon my JDBC slant to things, but my guess would be

1. SELECT the desired sub-tree with the START WITH/CONNECT BY into a result set or cursor (PL/SQL).
2. start a transaction
3. Determine the number of nodes SELECTED and request a block of new IDs from the sequence.
4. Turn batch updates on.
5. INSERT the new nodes using a PreparedStatement
6. commit

An alternative to step 5 would be to modify the resultset from step 1 and INSERT the modified result back. I haven't done this, but the 8.1.6 docs say that its possible.

Good luck, Gordon

"Stefan Larsson" <d95stela+news_at_dtek.chalmers.se> wrote in message news:slrn92563i.f5r.d95stela+news_at_licia.dtek.chalmers.se...
> Hi!
>
> What is the best way of copying tree-like data?
>
> I have a table looking like:
>
> CREATE TABLE t1 (
> ID_ INTEGER,
> Data ...,
> Parent_ID_ INTEGER
> );
>
> where parent_id_ identifies other rows in t1.
>
> I also have the sequence seq_IDs to generate unique IDs.
>
> I'm trying to create a PL/SQL procedure 'Copy' which will start
> at a specified ID and make a copy of the tree from the specified
> row and down, using fresh IDs from the sequence and connecting them
> in the proper way.
>
> The obvious solution (to me, anyway) is to use a recursive procedure,
> but I have a feeling that there should be a more efficient way, maybe
> using Oracle's START WITH/CONNECT BY queries.
>
> Any suggestions?
>
> /stefan
Received on Tue Nov 28 2000 - 08:26:42 CST