| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Interesting Issue
A copy of this was sent to Alex Vilner <avilner_at_usa.net>
(if that email address didn't require changing)
On Mon, 15 Jun 1998 16:43:32 -0400, you wrote:
>Hello,
>
>I am trying to write a generic procedure for moving data from one table
>to another, that does COMMIT's every X number of rows and does some
>intelligent error processing.
>
>I would like to pass the table names to the stored procedure, e.g.
>source_name and destination_name. Since in Oracle I cannot do something
>like:
> FOR rec IN (SELECT * from source) LOOP
> INSERT INTO dest VALUES ( rec.XXX, rec.YYY ) ...
>
>my resort is to use DBMS_SQL package, where I can say something like:
> c := DBMS_SQL.OPEN_CURSOR;
> DBMS_SQL.PARSE( c, 'SELECT * FROM ' || source, DBMS_SQL.V7 );
>
>Is there a way to have dynamic binding, where I can declare a variable
>of %ROWTYPE of the source and destination tables and map the columns?
>Or, even better, is there a simpler way to accomplish what I am trying
>to do?
>
>The key requirement is to be able to commit INSERTs into destination
>after a specified # of records.
>Any help / ideas will be greatly appreciated.
>
If thats the key requirment, you might consider looking into the sqlplus copy command, for example:
You need a sql*net connect string that loops back to your local database (not a dblink, a sqlplus connect string, you need to be able to "sqlplus scott/tiger_at_yourdatabase"...
For example, I just:
create table foo
( The_Whole_View varchar2(65),
TextLength number, TheText Long )
which is a table, sort of like all_views (which has a long)... Then I:
SQL> copy from tkyte/tkyte_at_aria insert foo (the_whole_view, textlength, thetext ) using select owner||'.'||view_name, text_length, text from all_views;
So the sqlplus command transformed the table for me (the columns are not the same). Also, I could have used a where clause to pick off just some rows.
You'll want to set
set arraysize N -- amount of rows the copy command will copy with each fetch set long N -- size of your longest long set copycommit M -- number of fetches to do before commit (N*M rows!!)
in plus before doing this. see the manual for all the options....
>Sincerely,
>
>Alex Vilner
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Tue Jun 16 1998 - 05:21:36 CDT
 |
 |