将Long转换为Varchar2 [英] Converting Long to Varchar2

问题描述

我正在尝试从长列中插入varchar2列.这是下面的示例，TEXT.TEXT_COL = VARCHAR2(4000) 和NOTE.TEXT_NOTE = LONG.

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; 

运行上述sql时出现错误

When i run the above sql i get error

SQL错误:ORA-00997:非法使用LONG数据类型

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

我也使用了TO_LOB()，但是仍然是相同的错误.

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

有没有可以简单地隐藏long并将其放入varchar2的功能.让我知道你的想法.

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

推荐答案

无法使用单个语句立即从long转换为varchar2，因为long具有某些限制.

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

您可以创建临时表，或使用 PL/SQL代码来解决您的问题:

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代码:

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;

这篇关于将Long转换为Varchar2的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！