Oracle

tranfer of data within tables

Please support our Oracle advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

Thread Solved

•

Join Date: Aug 2008

Posts: 53

Reputation: palavi is an unknown quantity at this point

Solved Threads: 0

palavi

Offline

Junior Poster in Training

tranfer of data within tables

Feb 16th, 2009

i ve 1 table named 'A',it contains many attributes in 1 attribute the values are stored like this "Q108:5380;Q208:5380" this data present in table 'A' should be entered in table named 'B' in two fields named B1,B2 as
B1 B2
Q108 5380
Q208 5380

like this in two columns this data should be entered in table named 'B' from table 'A'. here : means separates two columns and ; means separates two rows.
please help me or tell me how to write this in code. i am using oracle database. thank you

•

Join Date: Oct 2006

Posts: 48

Reputation: yilmazhuseyin is an unknown quantity at this point

Solved Threads: 5

yilmazhuseyin yilmazhuseyin is offline

Offline

Light Poster

Re: tranfer of data within tables

Feb 16th, 2009

here is some pl/sql code that gives you an idea I hope that would help

 Help with Code Tags 
 Oracle Syntax (Toggle Plain Text) 
DECLARE
a1 VARCHAR2(255);
b1 VARCHAR2(255);
a2 VARCHAR2(255);
b2 VARCHAR2(255);
qry VARCHAR2(4000);
CURSOR cr_a IS (SELECT * FROM a);
BEGIN
  --loop goes to every row in a
  FOR rc_a IN cr_a
  LOOP
    --seperates column of A table to for variable
    --you have to write it it is simple string operations
    seperatecolumn(rc_a.COLUMN , a1,a2,b1,b2);
    qry := 'insert into b (B1,B2) values (' || a1 || ',' || a2 || ')';
    --run query
    EXECUTE IMMEDIATE qry;
    qry := 'insert into b (B1,B2) values (' || b1 || ',' || b2 || ')';
    EXECUTE IMMEDIATE qry;
  END LOOP;
END;
declare
a1 varchar2(255);
b1 varchar2(255);
a2 varchar2(255);
b2 varchar2(255);
qry varchar2(4000);
cursor cr_a is (select * from a);
begin
  --loop goes to every row in a
  for rc_a in cr_a
  loop
    --seperates column of A table to for variable
    --you have to write it it is simple string operations
    seperatecolumn(rc_a.column , a1,a2,b1,b2);
    qry := 'insert into b (B1,B2) values (' || a1 || ',' || a2 || ')';
    --run query
    execute immediate qry;
    qry := 'insert into b (B1,B2) values (' || b1 || ',' || b2 || ')';
    execute immediate qry;
  end loop;
end;