Oracle PL/SQL/Data Type/Long

Change column type from long to clob

   <source lang="sql">
 

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>

 </source>
   
  

Conversion of Longs to LOBs

   <source lang="sql">
 

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.

 </source>
   
  

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

   <source lang="sql">
 

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.

 </source>
   
  

Insert char value to long type column

   <source lang="sql">
 

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>

 </source>
   
  

Long raw type column

   <source lang="sql">
 

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.

 </source>
   
  

Long type column

   <source lang="sql">
 

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.

 </source>
   
  

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

   <source lang="sql">
 

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>

 </source>