Oracle PL/SQL/Data Type/Long

Change column type from long to clob

  
SQL>
SQL>
SQL> create table myTable (c1 long);
Table created.
SQL>
SQL> insert into myTable values ("a");
1 row created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> alter table myTable modify (c1 clob);
Table altered.
SQL>
SQL>
SQL> drop table myTable;
Table dropped.
SQL>

Conversion of Longs to LOBs

  
SQL>
SQL>
SQL> CREATE TABLE myTable (
  2     id NUMBER,
  3     text LONG);
Table created.
SQL>
SQL> INSERT INTO myTable (id, text)VALUES (1, "Change the column from LONG to CLOB");
1 row created.
SQL>
SQL> ALTER TABLE myTable
  2  MODIFY text CLOB;
Table altered.
SQL>
SQL> DESC myTable
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 TEXT                                               CLOB
SQL>
SQL> drop table myTable;
Table dropped.

Demonstrate a method of converting to a LONG column to CLOB:

  
SQL>
SQL>
SQL> create table t
  2  (c1 number primary key,
  3  c2 long );
Table created.
SQL>
SQL> insert into t values (1, "b");
1 row created.
SQL>
SQL> -- This will fail because LONG doesn"t support character functions:
SQL> select  * from t where instr(c2, "b") > 0;
select  * from t where instr(c2, "b") > 0
                             *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG

SQL>
SQL> -- Change the LONG to CLOB:
SQL> alter table t modify c2 clob;
Table altered.
SQL>
SQL> select  * from t where instr(c2, "b") > 0;
         1 b
1 row selected.
SQL>
SQL> drop table t;
Table dropped.

Insert char value to long type column

  
SQL>
SQL> create table l
  2   (c1 long);
Table created.
SQL>
SQL> insert into l
  2   values ("a");
1 row created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> alter table l modify (c1 clob);
Table altered.
SQL>
SQL> drop table l;
Table dropped.
SQL>

Long raw type column

  
SQL> CREATE TABLE myTable (
  2    id              INTEGER PRIMARY KEY,
  3    long_raw_column LONG RAW NOT NULL
  4  );
Table created.
SQL>
SQL> drop table myTable;
Table dropped.

Long type column

  
SQL> CREATE TABLE myTable (
  2    id          INTEGER PRIMARY KEY,
  3    long_column LONG NOT NULL
  4  );
Table created.
SQL>
SQL>
SQL> drop table myTable;
Table dropped.

Set long: query a table or view that has a LONG column

  

SQL> -- you try to query a table or view that has a LONG column
SQL>
SQL> set pagesize 1000;
SQL> set long 100;
SQL>
SQL> select text from all_views where view_name = "USER_TABLES" and rownum < 20;
TEXT
--------------------------------------------------------------------------------
select o.name, decode(bitand(t.property, 2151678048), 0, ts.name, null),
       decode(bitand(t.prop

SQL>