Workaround for Ora-00997: Illegal Use of Long Datatype

Workaround for ORA-00997: illegal use of LONG datatype

ORA-00997: illegal use of LONG datatype

It is a restriction on usage of LONG data type. You cannot create an object type with a LONG attribute.

SQL> CREATE TABLE t AS SELECT data_default FROM user_tab_cols;
CREATE TABLE t AS SELECT data_default FROM user_tab_cols
                         *
ERROR at line 1:
ORA-00997: illegal use of LONG datatype

SQL>

Alternatively, you could use TO_LOB as a workaround. Which would convert it into CLOB data type.

For example,

SQL> CREATE TABLE t AS SELECT TO_LOB(data_default) data_default FROM user_tab_cols;

Table created.

SQL> desc t;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DATA_DEFAULT                                       CLOB

SQL>

See more examples of workarounds here.

ORA-00997: illegal use of LONG datatype

I found a solution and it is pretty simple actually. Just put it in a loop!

CREATE OR REPLACE PROCEDURE default_proc
IS
BEGIN
   EXECUTE IMMEDIATE 'TRUNCATE TABLE default_table';
   FOR n IN (SELECT data1, data2, data3, data4
    FROM default.table@dblink)
    LOOP
        INSERT INTO default_table (data1, data2, data3, data4)
        VALUES (n.data1, n.data2, n.data3, n.data4);
    END LOOP;
EXCEPTION
WHEN OTHERS THEN
   DBMS_OUTPUT.PUT_LINE('ERROR IS : ' || SQLCODE || ' : ' || SQLERRM);
END default_proc;

Illegal use of LONG datatype Oracle

Solution

1. given the many problems i had with long raw i decide to create a
   table like this:

   CREATE TABLE FOTS_EMPL ( cod_empr, cod_empl, foto)
AS
SELECT F.COD_EMPR, F.COD_EMPL, TO_LOB (FOT_EMPL)
FROM FOEMP f;

2. i took the function given by @tbone and i added and if condition
   like this:

   CREATE OR REPLACE FUNCTION base64enc (p_blob IN BLOB)
RETURN CLOB
AS
l_clob CLOB;
l_step PLS_INTEGER := 1998;
BEGIN
IF p_blob IS NOT NULL
THEN
FOR i IN 0 .. TRUNC ( (DBMS_LOB.getlength (p_blob) - 1) / l_step)
LOOP
l_clob :=
l_clob
|| UTL_RAW.cast_to_varchar2 (
UTL_ENCODE.base64_encode (
DBMS_LOB.SUBSTR (p_blob, l_step, i * l_step + 1)));
END LOOP;
RETURN l_clob;
ELSE
RETURN NULL;
END IF;
END base64enc;

3. this was my final select statement:

   SELECT e.NOM_EMPL First_name,
APE_EMPL Last_name,
e.NOM_EMPL || ' ' || e.APE_EMPL Full_name,
car.NOM_CARG position,
COS.NOM_CCOS Area,
base64enc(foto) Picture,
E.FEC_NACI Birth_date
FROM EMPLE e
INNER JOIN CONTR c
ON E.COD_EMPL = C.COD_EMPL
INNER JOIN cargo car
ON C.COD_CARG = CAR.COD_CARG
INNER JOIN CCOST cos
on COS.COD_CCOS = C.COD_CCOS
LEFT JOIN FOTS_EMPL F -- new table with blob instead of long raw
ON e.cod_empl = F.COD_EMPL AND e.cod_empr = f.cod_empr
WHERE C.IND_ACTI = 'A';

Thank you very much.

ORA-00997: illegal use of LONG datatype: while creating a table with select statement

There's always a way to move data over a database link but some data types require multiple steps.

One option is to do the LONG to LOB conversion on the remote database, copy the converted data over, and then cleanup the intermediate table:

begin
    dbms_utility.exec_ddl_statement@dblink_nm('create table temp_lob_results as select to_lob(a) a from tab1');
    execute immediate 'create table tab2 as select * from temp_lob_results@dblink_nm';
    dbms_utility.exec_ddl_statement@dblink_nm('drop table temp_lob_results');
end;
/

Another option is to use PL/SQL, which can implicitly convert LONG to LOBs. (Normally using PL/SQL like this is a horrible idea because it's so much slower and
more complicated than a single SQL statement.)

--create table tab2(...);

begin
    for tab1_rows in
    (
        select * from tab1@dblink_nm
    ) loop
        insert into tab2 values(tab1_rows.a);
    end loop;
end;
/

illegal use of LONG datatype 00997. 00000 - illegal use of LONG datatype

You're doing an implicit conversion from the Informix data type to the Oracle one. But you can't use to_lob() with a remote table, which is the explicit equivalent.

You should be able to achieve this with a PL/SQL cursor, separating the query and the insert:

begin
  for rec in (
    select "emp_num", "emp_pic", "thumb"
    from "empmaster1pics"@GMR
  )
  loop
    insert into empphoto (emp_num, emp_pic, thumb)
    values (rec."emp_num", rec."emp_pic", rec."thumb");
  end loop;
end;
/

I don't have an Informix database to verify, but it works with a link to an Oracle database and a table with a long raw column, which is the closest equivalent to your byte column and which gets the same ORA-00997 with your original code. (Except you can only have one long raw column in a table, so I've only been able to test with emp_pic or thumb, not both).

---

Related Topics