12

I am trying to insert into varchar2 column from a long column. here is the below example, TEXT.TEXT_COL = VARCHAR2(4000) and NOTE.TEXT_NOTE = LONG.

INSERT INTO TEXT(ROW_ID, TEXT_COL)
SELECT 1, TEXT_NOTE FROM NOTE;

When i run the above sql i get error

SQL Error: ORA-00997: illegal use of LONG datatype

I used TO_LOB() too, but still the same error.

Is there any function which simply coverts long and put it in varchar2. Let me know your thoughts.

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Kriti
  • 197
  • 1
  • 4
  • 11
  • 1
    you cannot do it in a single statement using only built-in procedures, see [this thread](https://community.oracle.com/thread/2429393) – smnbbrv Jun 23 '15 at 14:03
  • You'd need to use PL/SQL or an intermediate table with a CLOB column. You may want a CLOB anyway if any of the values in the LONG column are more than 4000 bytes, of course; or are you happy truncating those? – Alex Poole Jun 23 '15 at 14:29
  • I have to use this sql in informatica session level. I tried at the mapping level of informatica built in datatypes but its not working either. – Kriti Jun 23 '15 at 14:42
  • is there any other way i can handle this ? – Kriti Jun 23 '15 at 14:51
  • Did CAST(TEXT_NOTE AS VARCHAR) throw an error at you? – Thinkeye Apr 03 '19 at 14:45

3 Answers3

17

Converting from long to varchar2 right away using a single statement is not possible, as long has certain restrictions.

You can either Create a temporary table or use PL/SQL code to solve your problem:

  • Temporary Table:

    CREATE TABLE TABLE2 AS SELECT TO_LOB(COLUMN1) COLUMN FROM TABLE1;

  • PL/SQL Code:

    DECLARE
  VAR1 LONG;
  VAR2 VARCHAR2(4000);
BEGIN
  SELECT TEXT INTO VAR1 FROM USER_VIEWS WHERE ROWNUM = 1;  
  VAR2 := SUBSTR(VAR1, 1, 4000);
  DBMS_OUTPUT.PUT_LINE(VAR2);
END;
Martin
  • 209
  • 1
  • 12
1

It looks like Oracle internally converts LONG to something else (probably CLOB) when you select LONG in FOR loop. I did not find any explanations in Oracle documentation, but this works

BEGIN
  FOR V IN (SELECT ROWID,TEXT_NOTE FROM NOTE)
  LOOP
    INSERT INTO TEXT VALUES(V.ROWID, SUBSTR(V.TEXT_NOTE, 1, 4000) );   
  END LOOP;
  COMMIT;
END;

This is an example how to copy all views from another schema to your schema

BEGIN
  FOR V IN (SELECT VIEW_NAME, TEXT_LENGTH, TEXT FROM ALL_VIEWS WHERE OWNER = 'PROD')
  LOOP
    EXECUTE IMMEDIATE 'CREATE OR REPLACE FORCE VIEW '||V.VIEW_NAME||' AS '||SUBSTR(V.TEXT, 1, V.TEXT_LENGTH);
    DBMS_OUTPUT.PUT_LINE('View '||V.VIEW_NAME||' created');
  END LOOP;
END;

For some reason it only works for FOR loop and does not work if you use WITH or select from another query

INSERT INTO TEXT
WITH V AS(SELECT ROWID ROW_ID,TEXT_NOTE FROM NOTE)
SELECT V.ROW_ID, SUBSTR(V.TEXT_NOTE, 1, 4000) FROM V; 

INSERT INTO TEXT
SELECT ROW_ID, SUBSTR(TEXT_NOTE, 1, 4000) 
FROM (SELECT ROWID ROW_ID,TEXT_NOTE FROM NOTE);

Both inserts raise the same error

ORA-00932: inconsistent datatypes: expected CHAR got LONG

garbuya
  • 59
  • 6
  • When TEXT_LENGTH is greater than 32760 the select ... from ALL_VIEWS will throw the error "ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind" (I tested on "Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit" with different views to get that limit number (I don't know why not the same as max of varchar2 (32767)). – Manuel Romeiro Oct 02 '19 at 16:12
0

you can use standard function to convert long to varchar2:

sys.DBMS_METADATA_UTIL.long2varchar

or to clob:

sys.DBMS_METADATA_UTIL.long2clob
ildar
  • 101
  • 7