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.
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.
TO_CHAR (character) converts NCHAR , NVARCHAR2 , CLOB , or NCLOB data to the database character set. The value returned is always VARCHAR2 .
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.
The VARCHAR2 datatype represents variable-length character strings. On most platforms, the maximum length of a VARCHAR2 value is 65535 bytes.
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;
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With