illegal use of LONG datatype while copying Mysql table to Oracle

Question

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

Example

create table table_to_copy
as
select * from table_to_copy@DBLINK;

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

---

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.

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

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

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.