Convert Long to Varchar2

Question

I am trying to insert in a varchar2 column from a long column. below is an 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 an error

SQL Error: ORA-00997: Illegal Use of LONG Data Type

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

Is there any function that just hides long and puts it in varchar2 . Let me know what you think.

Answer 1

Converting long to varchar2 right away with a single statement is not possible, since long has certain limitations.

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; 

Answer 2

It looks like Oracle internally converts LONG to probably CLOB when you select LONG in a FOR loop. I did not find any explanation in the Oracle documentation, but it 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 of 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, this only works for the 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 cause the same error

SQL Error: ORA-00932: Incompatible Data Types: CHAR Expected to Receive LONG