Illegal use of a LONG data type when copying a Mysql table in Oracle

Question

I have MySQL and an Oracle server. I have to periodically copy some tables from MySQL to the Oracle server, and this is done using the planned PLSQL procedure, and for this I created DBLINK between MySQL and Oracle. Everything works fine until I had to copy one table that started to throw an error

Example

create table table_to_copy as select * from table_to_copy@DBLINK ; 

"oracle sql error ora-00997 illegal use of long data type

---

I read a couple of comments, and this is mainly because of the implicit conversion, and most of the suggestions were to do an explicit to_lob conversion. But to do something manually is not possible.

note that

• The organization of work is such that I do not have access to the MySQL server, the only thing I get is the name of the table is DBLINK. Therefore, I can only retrieve data using the select statement
• The solution for the above question should be considered in some kind of automatic correction. This is due to the fact that a request to copy a table can be hundreds of tables like any given point, and I can not go through all the tables for correction / verification manually.

Please help, your expert comments are very valuable to me.

Note. Here are a few other questions that might look something like this: Illegal use of LONG datatype Oracle but they do not have a solution to what I'm looking for.

Answer 1

I understand that this is quite late, and that I do not have such a fine-tuning. However, what I did with Oracle (11gR2) on SQL Server (2008R2 and earlier) was to read INFORMATION_SCHEMA.COLUMNS through the database link, and then dynamically generate the row for 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 save a series of templates in an array of PL / SQL indexes with an index that is a data type, and the value is an expression of the type 'to_date(''$$'',''YYYYMMDD'')' , from which the $$ characters are replaced with the column_name name. If you need to completely remove the data type, which I often do, I just put an empty string in the data type array.