How to Change LONG to CLOB in Oracle

Question

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.

Answer 1

One option is to just alter table.

Here's an example:

Table with long datatype column:

SQL> create table test (col long);

Table created.

Let's populate it:

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 procedure successfully completed.

How many rows does it contain?

SQL> select count(*) from test;

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

Just an excerpt:

SQL> 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> alter table test modify col clob;

Table altered.

Result:

SQL> select * from test where rownum = 1;

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

SQL>