如何在Oracle中将LONG更改为CLOB

huangapple go评论54阅读模式
英文:

How to Change LONG to CLOB in Oracle

问题

如何在Oracle中将LONG数据类型更改为CLOB并使用函数?

ALTER TABLE "TABLE_NAME"
ADD "CLOB_NAME" CLOB;

UPDATE "TABLE_NAME"
SET "CLOB_NAME" = TO_CLOB("LONG_NAME");

ALTER TABLE "TABLE_NAME"
DROP COLUMN "LONG_NAME";

ALTER TABLE "TABLE_NAME"
RENAME COLUMN "CLOB_NAME" TO "LONG_NAME";

我不想使用这段代码,因为我无法更改表元素并且没有任何权限。

英文:

How can I Change LONG datatype to CLOB in Oracle with function?

ALTER TABLE "TABLE_NAME"
ADD "CLOB_NAME" CLOB;

UPDATE "TABLE_NAME"
SET "CLOB_NAME" = TO_CLOB("LONG_NAME");

ALTER TABLE "TABLE_NAME"
DROP CLOUMN "LONG_NAME";

ALTER TABLE "TABLE_NAME"
RENAME CLOUMN "CLOB_NAME" TO "LONG_NAME";

I don't wanna use that code because I can't change the table element and doesn't have any permission.

答案1

得分: 0

以下是您要翻译的内容:

一种选择是仅更改表格。

这是一个示例:

具有“long”数据类型列的表格:

SQL> create table test (col long);

表格已创建。

让我们填充它:

SQL> begin
2 for cur_r in (select text from all_views
3 where text_length < 30000
4 and text is not null
5 )
6 loop
7 insert into test (col) values (cur_r.text);
8 end loop;
9 end;
10 /

PL/SQL过程已成功完成。

它包含多少行?

SQL> select count(*) from test;

COUNT(*)

    45

只是一个摘录:

SQL> select * from test where rownum = 1;

COL

SELECT q_name QUEUE, qt.msgid MSG_ID, corrid CORR_ID, priority MSG_PRIORITY,

好的,现在,修改表格,将“long”修改为“clob”:

SQL> alter table test modify col clob;

表格已更改。

结果:

SQL> select * from test where rownum = 1;

COL

SELECT q_name QUEUE, qt.msgid MSG_ID, corrid CORR_ID, priority MSG_PRIORITY,

SQL>;

英文:

One option is to just alter table.

Here's an example:

Table with long datatype column:

SQL&gt; create table test (col long);

Table created.

Let's populate it:

SQL&gt; begin
  2    for cur_r in (select text from all_views
  3                  where text_length &lt; 30000
  4                    and text is not null
  5                 )
  6    loop
  7      insert into test (col) values (cur_r.text);
  8    end loop;
  9  end;
 10  /

PL/SQL procedure successfully completed.

How many rows does it contain?

SQL&gt; select count(*) from test;

  COUNT(*)
----------
        45

Just an excerpt:

SQL&gt; select * from test where rownum = 1;

COL
--------------------------------------------------------------------------------
SELECT  q_name QUEUE, qt.msgid MSG_ID, corrid CORR_ID,  priority MSG_PRIORITY,

OK; now, alter table and modify long to clob:

SQL&gt; alter table test modify col clob;

Table altered.

Result:

SQL&gt; select * from test where rownum = 1;

COL
--------------------------------------------------------------------------------
SELECT  q_name QUEUE, qt.msgid MSG_ID, corrid CORR_ID,  priority MSG_PRIORITY,

SQL&gt;

huangapple
  • 本文由 发表于 2023年4月10日 21:02:34
  • 转载请务必保留本文链接:https://go.coder-hub.com/75977379.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定