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                                                                                                                                                                                    
---------- ------------------------------------------------------------------------------------------------------------------------------------                                                         

       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

---------- -------------------- --------------------------------------------------------------------------------

         1 Test 1               When in the course of human events
         2 Test 2               When in the course of canine events
         3 Test 3               When in the course of feline events
 
SQL>
SQL>
SQL> select *

  2  from longtest2;
 
     MY_ID MY_DESC              MY_TEXT
---------- -------------------- --------------------------------------------------------------------------------

         1 Test 1               When in the course of human events
         2 Test 2               When in the course of canine 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-l
Received on Thu Jun 27 2013 - 18:44:54 CEST

Original text of this message