Re: Transfering Records To Another Table

From: <michael_bialik_at_my-deja.com>
Date: Wed, 21 Jul 1999 20:02:23 GMT
Message-ID: <7n5905$6hm$1_at_nnrp1.deja.com>


Hi.

[Quoted]  Following an outline of server-side procedure to execute  an update :

 CREATE OR REPLACE PROCEDURE copy_data ( parm1 IN VARCHAR2,   parm2 IN NUMBER, ... , parmN IN VARCHAR2 ) IS   CURSOR tab1_crs ( p1 VARCHAR2, p2 NUMBER ) IS    SELECT f1,f2,f3,f4,f5 FROM table1
   WHERE fx = p1 AND fy = p2;
  BEGIN
    FOR tab1_rec IN tab1_crs( parm1, parm2 ) LOOP

      INSERT INTO tab2 (f11,f12,f13,f14,f15,f16 )
       VALUES (tab1_rec.f1,tab1_rec.f2,tab1_rec.f3,
               tab1_rec.f4,tab1_rec.f5,parm3);
    END LOOP;
    COMMIT;
  END;   I am not sure about COMMIT ( you have to decide where you want it).   If you are updating tab2 - change INSERT to UPDATE.   You didn't say what is Oracle version you are using.   For Oracle 8i - check BULK BINDs example in PL/SQL manual -   it will improve the performance.

  Good luck. Michael.

In article <pujl3.205$Bp2.21849_at_news.oh.voyager.net>,   <lili96ilil_at_aol.com> wrote:
> Yeah, my data goes from server to client to server. Table
definitions...
> There are about five fields being copied from table to table. All
either
> char, varchar2, date, or number and are less than 15 characters
long. For
> each query, there are a few hundred records that are transferred. Is
this
> what you wanted to know? I've only been working with Oracle for 2
weeks.
> Actually, one of the values that gets plugged into table2 is a
variable that
> the user specifies. So would I have to do it this way? If so, I'd
use the
> insert to another table command?
>
> Or could I also do it the other way and just have the variable sent
to the
> procedure on the server? This way, would I use the Procedure
Builder? If
> so, could you give me a basic outline of performing this task?
>
> michael_bialik_at_my-deja.com wrote in message
<7n2muf$7ru$1_at_nnrp1.deja.com>...
> >Hi.
> > Do you copy your data from server to client and then sending it
> > back to server ( insert to another table ).
> > If that's the case - it is NOT the best way to do things.
> > You have to create a stored procedure on server and call it
> > from Developer by passing parameters.
> > Is it possible for you to post a table definitions and the
> > description of parameters ( what you have to copy )?
> >
> > Michael.
>
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Wed Jul 21 1999 - 22:02:23 CEST

Original text of this message