Skip to Main Content
  • Questions

  • use dblinke ,select mysql "text" type

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, chen.

Asked: March 24, 2016 - 2:42 am UTC

Last updated: March 26, 2016 - 1:47 am UTC

Version: oracle 11

Viewed 1000+ times

You Asked

hi:

insert into oracletable(message) select "Message" from "mysqltable"@tomysql;

ORA-00997: illegal use of LONG datatype


oracle ==> dblink(use gateway) => mysql

oracle 11g:
table column type
oracletable----message----varchar2

mysql v5:
talbe column type
mysqltable-----message-----text


how can i insert into data to oracle from mysql ?

thanks!

and Connor said...

If the data is less than 32k, you can use some plsql to get around this. I've done something similar in the past when accessing SQL Server data across the HS gateway, eg

<code>
declare
type t_record is ( x int, y int, z long );

type t_row_list is table of t_record
index by pls_integer;
l_long_var varchar2(32767);

begin
for i in (
select
"SqlServerX" ss_x
,"SqlServerY" ss_y
,"SqlServerZ" ss_z
from my_table@sql_server_hs
)
loop
l_row_list(l_row_list.count+1).x := i.ss_x;
l_row_list(l_row_list.count).y := i.ss_y;
l_long_var := i.ss_z;
l_row_list(l_row_list.count).z := l_long_var;
end loop;

forall i in 1 .. l_row_list.count
insert into my_target values l_row_list(i);

end;
<code>

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Some trouble

A reader, March 25, 2016 - 6:06 am UTC

Hi,

thanks , but it cann't work. likt that...

DECLARE
   TYPE t_record IS RECORD( x int,y long);
   type l_row_list is table of t_record index by pls_integer;
   l_long_var varchar2(32767);
BEGIN
   FOR i IN (SELECT "ID" xxx, "Message" yyy
               FROM "test1"@tosyslog)
   LOOP
      l_row_list (l_row_list.COUNT + 1).x := i.xxx;
      l_long_var := i.yyy;
      l_row_list (l_row_list.COUNT).y := l_long_var;
   END LOOP;
 13
   FORALL ii IN 1 .. l_row_list.COUNT
      INSERT INTO syslog2
           VALUES l_row_list (ii);
END;
 18  /
      l_row_list (l_row_list.COUNT + 1).x := i.xxx;
                             *
ERROR at line 9:
ORA-06550: line 9, column 30:
PLS-00302: component 'COUNT' must be declared
ORA-06550: line 9, column 7:
PL/SQL: Statement ignored
ORA-06550: line 11, column 30:
PLS-00302: component 'COUNT' must be declared
ORA-06550: line 11, column 7:
PL/SQL: Statement ignored
ORA-06550: line 14, column 33:
PLS-00302: component 'COUNT' must be declared
ORA-06550: line 14, column 17:
PL/SQL: Statement ignored

Connor McDonald
March 26, 2016 - 1:47 am UTC

Sorry - I can't give you "tested to be working" code because I dont have a remote HS source to test against, but here's hopefully a better version

DECLARE
   TYPE t_record IS RECORD( x int,y long);
   type t_row_list is table of t_record index by pls_integer;
   l_row_list t_row_list;
   l_long_var varchar2(32767);
BEGIN
   FOR i IN (SELECT "ID" xxx, "Message" yyy
               FROM "test1"@tosyslog)
   LOOP
      l_row_list (l_row_list.COUNT + 1).x := i.xxx;
      l_long_var := i.yyy;
      l_row_list (l_row_list.COUNT).y := l_long_var;
   END LOOP;
 13
   FORALL ii IN 1 .. l_row_list.COUNT
      INSERT INTO syslog2
           VALUES l_row_list (ii);
END;


thanks

A reader, March 28, 2016 - 1:31 am UTC

ok , thanks for you help.

it work !!!




more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.