Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Converting Long to Varchar2

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.

like image 427
Kriti Avatar asked Jun 23 '15 13:06

Kriti


People also ask

How long is a VARCHAR2?

The maximum length for VARCHAR2 is 32672 BYTE or 8168 CHAR which is the same as the maximum length for VARCHAR of 32672 OCTETS or 8168 CODEUNITS32. Similarly, when the NVARCHAR2 data type is explicitly encountered in SQL statements, it is implicitly mapped following the same rules as the NVARCHAR data type.

Can we convert CLOB to VARCHAR2 in Oracle?

TO_CHAR (character) converts NCHAR , NVARCHAR2 , CLOB , or NCLOB data to the database character set. The value returned is always VARCHAR2 .

Can VARCHAR2 handle special characters?

As the name suggests, varchar means character data that is varying. Also known as Variable Character, it is an indeterminate length string data type. It can hold numbers, letters and special characters.

Does VARCHAR2 need length?

The VARCHAR2 datatype represents variable-length character strings. On most platforms, the maximum length of a VARCHAR2 value is 65535 bytes.


2 Answers

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;
    
like image 155
Martin Avatar answered Oct 02 '22 16:10

Martin


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

like image 21
garbuya Avatar answered Oct 02 '22 15:10

garbuya