Re: Inserting long columns
From: David Fitzjarrell <oratune_at_yahoo.com>
Date: Thu, 27 Jun 2013 09:44:54 -0700 (PDT)
Message-ID: <1372351494.59627.YahooMailNeo_at_web121605.mail.ne1.yahoo.com>
+It really depends on how long the LONG data is; if it's less than 32K you can write a PL/SQL block to populate a varchar2 variable and merge the data that way:
SQL>
SQL> create table yasmine(
2 ploo_id number,
3 ploo_dat long
4 );
Table created.
SQL>
SQL> create table yapinski(
2 ploo_too_id number,
3 ploo_too_dat long
4 );
Table created.
SQL>
SQL> begin
2 for i in 1..1000 loop
3 insert into yasmine
4 values(i, 'This is record number '||i);
5
6 if mod(i,17) = 0 then
7 insert into yapinski
8 values(i, 'This is record number '||i);
9 end if;
10 end loop;
11
12 commit;
13
14 end;
15 /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from yasmine
2 where ploo_id not in (select ploo_too_id from yapinski);
PLOO_ID PLOO_DAT
PLOO_ID PLOO_DAT
942 rows selected.
SQL>
SQL> insert into yapinski
2 select * From yasmine
3 where ploo_id not in (select ploo_too_id from yasmine);
where ploo_id not in (select ploo_too_id from yasmine)
*
ERROR at line 3:
ORA-00904: "PLOO_TOO_ID": invalid identifier SQL>
SQL> declare
2 v_long_conv varchar2(32767);
3 v_id number;
4 begin
5 for lrec in ( select ploo_id, ploo_dat from yasmine where ploo_id not in (select ploo_too_id from yapinski)) loop
6
7 v_id := lrec.ploo_id;
8 v_long_conv := lrec.ploo_dat;
9
10 insert into yapinski
11 values(v_id, v_long_conv);
12
13 end loop;
14
15 commit;
16
17 end;
18 /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from yasmine
2 where ploo_id not in (select ploo_too_id from yapinski);
no rows selected
SQL>
If it's longer than 32K then you'll need to convert the data to a clob and process accordingly:
SQL> create table longtest(my_id number, my_desc varchar2(20), my_text long);
Table created.
SQL>
SQL> insert into longtest values (1, 'Test 1', 'When in the course of human events');
1 row created.
SQL> insert into longtest values (2, 'Test 2', 'When in the course of canine events');
1 row created.
SQL> insert into longtest values (3, 'Test 3', 'When in the course of feline events');
1 row created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> select *
2 from longtest
3 where my_text like '%human%';
where my_text like '%human%'
*
ERROR at line 3:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG
3 Test 3 When in the course of feline events
2 from longtest2;
MY_ID MY_DESC MY_TEXT
3 Test 3 When in the course of feline events
SQL> David Fitzjarrell
From: Denise Gwinn <denise_at_mail.wvnet.edu> To: oracle-l <oracle-l_at_freelists.org> Sent: Thursday, June 27, 2013 10:09 AM
Subject: Inserting long columns
Date: Thu, 27 Jun 2013 09:44:54 -0700 (PDT)
Message-ID: <1372351494.59627.YahooMailNeo_at_web121605.mail.ne1.yahoo.com>
+It really depends on how long the LONG data is; if it's less than 32K you can write a PL/SQL block to populate a varchar2 variable and merge the data that way:
SQL>
SQL> create table yasmine(
2 ploo_id number,
3 ploo_dat long
4 );
Table created.
SQL>
SQL> create table yapinski(
2 ploo_too_id number,
3 ploo_too_dat long
4 );
Table created.
SQL>
SQL> begin
2 for i in 1..1000 loop
3 insert into yasmine
4 values(i, 'This is record number '||i);
5
6 if mod(i,17) = 0 then
7 insert into yapinski
8 values(i, 'This is record number '||i);
9 end if;
10 end loop;
11
12 commit;
13
14 end;
15 /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from yasmine
2 where ploo_id not in (select ploo_too_id from yapinski);
PLOO_ID PLOO_DAT
---------- ------------------------------------------------------------------------------------------------------------------------------------...
628 This is record number 628
630 This is record number 630
631 This is record number 631
632 This is record number 632
633 This is record number 633
634 This is record number 634
635 This is record number 635
636 This is record number 636
637 This is record number 637
638 This is record number 638
639 This is record number 639
PLOO_ID PLOO_DAT
---------- ------------------------------------------------------------------------------------------------------------------------------------
621 This is record number 621
622 This is record number 622
623 This is record number 623
624 This is record number 624
625 This is record number 625
626 This is record number 626
627 This is record number 627
942 rows selected.
SQL>
SQL> insert into yapinski
2 select * From yasmine
3 where ploo_id not in (select ploo_too_id from yasmine);
where ploo_id not in (select ploo_too_id from yasmine)
*
ERROR at line 3:
ORA-00904: "PLOO_TOO_ID": invalid identifier SQL>
SQL> declare
2 v_long_conv varchar2(32767);
3 v_id number;
4 begin
5 for lrec in ( select ploo_id, ploo_dat from yasmine where ploo_id not in (select ploo_too_id from yapinski)) loop
6
7 v_id := lrec.ploo_id;
8 v_long_conv := lrec.ploo_dat;
9
10 insert into yapinski
11 values(v_id, v_long_conv);
12
13 end loop;
14
15 commit;
16
17 end;
18 /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from yasmine
2 where ploo_id not in (select ploo_too_id from yapinski);
no rows selected
SQL>
If it's longer than 32K then you'll need to convert the data to a clob and process accordingly:
SQL> create table longtest(my_id number, my_desc varchar2(20), my_text long);
Table created.
SQL>
SQL> insert into longtest values (1, 'Test 1', 'When in the course of human events');
1 row created.
SQL> insert into longtest values (2, 'Test 2', 'When in the course of canine events');
1 row created.
SQL> insert into longtest values (3, 'Test 3', 'When in the course of feline events');
1 row created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> select *
2 from longtest
3 where my_text like '%human%';
where my_text like '%human%'
*
ERROR at line 3:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG
SQL>
SQL> create table longtemp (my_id number, my_desc varchar2(20), my_text clob);
Table created.
SQL>
SQL> insert into longtemp select my_id, my_desc, to_lob(my_text) from longtest;
3 rows created.
SQL>
SQL> create table longtest2(my_id number, my_desc varchar2(20), my_text long);
Table created.
SQL>
SQL> insert into longtest2 select * from longtemp;
3 rows created.
SQL>
SQL> select *
2 from longtemp;
MY_ID MY_DESC MY_TEXT
---------- -------------------- --------------------------------------------------------------------------------2 Test 2 When in the course of canine events
1 Test 1 When in the course of human events
3 Test 3 When in the course of feline events
SQL> SQL> SQL> select *
2 from longtest2;
MY_ID MY_DESC MY_TEXT
---------- -------------------- --------------------------------------------------------------------------------2 Test 2 When in the course of canine events
1 Test 1 When in the course of human events
3 Test 3 When in the course of feline events
SQL> David Fitzjarrell
From: Denise Gwinn <denise_at_mail.wvnet.edu> To: oracle-l <oracle-l_at_freelists.org> Sent: Thursday, June 27, 2013 10:09 AM
Subject: Inserting long columns
I have to merge some tables that contain a long datatype. A simple insert into tableA (select * from tableB) will not work. I've tried Googling, but not having much success. Could someone enlighten me please?
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Thu Jun 27 2013 - 18:44:54 CEST