将Mysql表复制到Oracle时非法使用长数据类型

I have a MySQL and a Oracle server. I have to periodically copy some tables from MySQL to Oracle server and this is done via a scheduled PLSQL procedure and for this I have created a DBLINK between MySQL and Oracle. Everything is working fine until I had to copy one table which started giving error

我有一个MySQL和一个Oracle服务器。我必须定期地从MySQL复制一些表到Oracle服务器，这是通过一个预定的PLSQL过程完成的，为此我在MySQL和Oracle之间创建了一个DBLINK。一切正常，直到我不得不复制一个开始出错的表

Example

例子

create table table_to_copy
as
select * from table_to_copy@DBLINK;

"oracle sql error ora-00997 illegal use of long datatype"

“oracle sql error ora-00997非法使用长数据类型”

---

I have read couple of comments and this is mostly because of implicit conversion and most of the suggestions were to perform explicit to_lob conversion. But doing anything manual is not a feasible option.

我读过一些评论，这主要是因为隐式转换，大多数建议是执行显式to_lob转换。但是手工操作并不是一个可行的选择。

Please note that

请注意,

• work arrangement is such I do not have any access to MySQL server the only thing I get is table name and have DBLINK. So I can only pull data using select statement
• 工作安排是这样的，我没有任何访问MySQL服务器的唯一我得到的东西是表名和有DBLINK。所以我只能使用select语句来提取数据
• Solution for above mentioned issue has to be dealt in some kind of automated fix. This is because the request for table copy can be hundreds of table as any given point and I cannot go through all tables to fix/check manually.
• 上述问题的解决方案必须通过某种自动修复来解决。这是因为对表复制的请求可以像任何给定的点一样有数百个表，我不能遍历所有表来手动修复/检查。

Please help, your expert comments are highly valuable for me.

请帮忙，你的专家意见对我很有价值。

Note : there are some other questions here which might look similar like Illegal use of LONG datatype Oracle but they don't have the solution to what I am looking for.

注意:这里还有一些其他问题，可能类似于非法使用长数据类型Oracle，但是它们没有解决我所寻找的问题。

1 个解决方案

#1

I appreciate that this is rather late, and that I don't have this exact setup. However, what I have done from Oracle (11gR2) to SQL Server (2008R2 and earlier) is to read INFORMATION_SCHEMA.COLUMNS through the database link and then dynamically generate a string to EXECUTE IMMEDIATE in PL/SQL.

我很感激这已经很晚了，而且我没有这样的设置。然而，我从Oracle (11gR2)到SQL Server (2008R2及更早的版本)所做的就是读取INFORMATION_SCHEMA。通过数据库链接的列，然后动态地生成一个字符串，以在PL/SQL中立即执行。

DECLARE
  TYPE associative_array IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(50);

  data_type_tranforms associative_array;
  dynamicSQL varchar2(32767);
  column_list varchar2(32767) := '';
  expressions varchar2(32767) := '';

  FUNCTION apply_transform(column_name VARCHAR2, data_type VARCHAR2) RETURN VARCHAR2 AS
    transformed VARCHAR2(1000);
  BEGIN
    IF data_type_transforms.exists(data_type) THEN
      transformed :=  replace(data_type_transforms(data_type),'$$',column_name);
    ELSE
      transformed := column_name;
    END IF;
    RETURN transformed;
  END apply_transform;

  FUNCTION strip_last_character(input VARCHAR2) RETURN VARCHAR2 AS
    /* Remove the delimiter trailing after the last entry */
  BEGIN
    RETURN SUBSTR(input, 1, LENGTH(input) - 1);
  END strip_last_character;

BEGIN
  data_type_transforms('LONG') := 'to_lob($$)';

  FOR col IN (
    SELECT column_name
      ,data_type
    FROM information_schema.columns@DBLINK
    WHERE table_name = 'TABLE_TO_COPY'
    ORDER BY ordinal_position
  ) LOOP
    column_list := column_list || col.column_name ||',';
    expressions := expressions || apply_transform(col.column_name, col.data_type) ||','; 
  END LOOP;
  dynamicSQL := 'INSERT INTO table_to_copy ('||
    strip_last_character(column_list)||
    ') SELECT '||
    strip_last_character(expressions)||
    ' FROM table_to_copy@DBLINK';

  EXECUTE IMMEDIATE dynamicSQL;
END;

I keep a series of templates in a PL/SQL index-by array, with the index being a data type and the value being an expression like 'to_date(''$$'',''YYYYMMDD'')' from which the characters $$ get replaced with the column_name. If you need to drop a data type entirely, which I often do, I just put an empty string in the data-type array.

我在PL/SQL index-by数组中保存了一系列模板，索引是一种数据类型，值是一个表达式，比如'to_date(" $ "、" yyyyymmdd ")'。如果您需要完全删除数据类型(我经常这样做)，我只需在数据类型数组中放入一个空字符串。