Re: Can I copy data changing the key.

From: Blv n Drms <blvndrms_at_aol.com>
Date: 1995/06/23
Message-ID: <3sfoev$9vh_at_newsbf02.news.aol.com>#1/1

There is a way to do this (actually probably lots): I would use a stored procedure if I read your need correctly. I wrote a quick example for you...you would probably want to setup return values and some error handling..but it should give you the basic idea

create or replace procedure my_row_change (da_table varchar2, key_col varchar2, old_key number, new_key number) AS

cursor_id number;

old_key_value number;
our_table varchar2(30);
our_col    varchar2(30);
our_value number;
tmp_col    varchar2(30);
tmp_stmt  varchar2(2000);
tmp_sel    varchar2(2000);

cursor table_struct (our_table in varchar2) is
     select  column_name
     from    cols
     where table_name = our_table;

BEGIN

old_key_value := old_key;
our_col := key_col;
our_value := new_key;
our_table := da_table;
tmp_stmt := 'INSERT INTO '||our_table||' ('||our_col ;
tmp_sel  := 'SELECT '||''''||our_value||'''';

• get the column names into the insert statement

OPEN table_struct (our_table)
LOOP

         FETCH table_struct INTO tmp_col;
         EXIT WHEN table_struct%NOTFOUND;
   
         tmp_stmt := tmp_stmt||', '||tmp_col;
         tmp_sel   := tmp_sel||','||tmp_col;

END LOOP; tmp_stmt := tmp_stmt||') '||tmp_sel||' from '||our_table||'where '||our_col||' = '||old_key_value||';';

cursor_id := dbms_sql.open_cursor;
dbms_sql.parse (cursor_id, tmp_stmt, v7); status_cd :=dbms_sql.execute (cursor_id);

if status_cd = 1 then

     commit;
else

    rollback;
end if;

dbms_sql.close_cursor (cursor_id);
end;
/

Jack Richter

Jack Richter
Senior Consultant-Oracle
Eagles International, Inc Received on Fri Jun 23 1995 - 00:00:00 CEST