0

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

2
Contributors
2
Replies
3
Views
8 Years
Discussion Span
Last Post by palavi
0

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

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;
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.