CODEWITHSUNDEEP
mysqlsqldatabaseoracle-database
Gourav C
Gourav C

Reputation: 111

illegal use of LONG datatype while copying Mysql table to 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

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

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.

Upvotes: 9

Views: 1137

Answers (1)

Steven Ensslen
Steven Ensslen

Reputation: 1376

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.

Upvotes: 1

Related Questions