This discussion is archived
6 Replies Latest reply: Nov 21, 2013 4:47 AM by Karthick_Arp RSS

Reg: LONG Update

infochandu Newbie
Currently Being Moderated

All,

 

I am trying to update a LONG column as like below, But i am unable to do it. Can any one please give me the solution. Image is attached.

 

How to perform an update against the LONG data type column.

SQL> SHOW USER
USER is "SCOTT"
SQL> CREATE TABLE LONG_TEST ( ID NUMBER, LONG_COL LONG);

Table created.

SQL> INSERT INTO LONG_TEST VALUES( 1,' THIS IS LONG TEST');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> UPDATE LONG_TEST SET LONG_COL='APPENDING'||LONG_COL;

UPDATE LONG_TEST SET LONG_COL='APPENDING'||LONG_COL
                                           *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG


SQL> DESC LONG_TEST;
Name                                                  Null?    Type
----------------------------------------------------- -------- ------------------------------------
ID                                                             NUMBER
LONG_COL                                                       LONG

SQL> UPDATE LONG_TEST SET LONG_COL='APPENDING';

1 row updated.

SQL>

Thanks

  • 1. Re: Reg: LONG Update
    Suri Pro
    Currently Being Moderated

    see below example

     

    SQL> create table t ( ename varchar2(40), address long);

    Table created

    SQL> insert into t values ( null, 'PQRXYZ');

    1 row inserted

    SQL> update t
      2  set t.ename = address;

    update t
    set t.ename = address

    ORA-00932: inconsistent datatypes: expected NUMBER got LONG

     

    You can use CLOB datatype right. Why you are using LONG ?

  • 2. Re: Reg: LONG Update
    Suri Pro
    Currently Being Moderated

    From the link http://www.oracle-developer.net/display.php?id=430

     

    LONG columns cannot appear in

    • SQL built-in functions, expressions, or conditions

     

     

    In addition, LONG columns cannot appear in these parts of SQL statements:

    • GROUP BY clauses, ORDER BY clauses, or CONNECT BY clauses or with the DISTINCT operator in SELECT statements
    • The UNIQUE operator of a SELECT statement
    • The column list of a CREATE CLUSTER statement
    • The CLUSTER clause of a CREATE MATERIALIZED VIEW statement
    • SQL built-in functions, expressions, or conditions
    • SELECT lists of queries containing GROUP BY clauses
    • SELECT lists of subqueries or queries combined by the UNION, INTERSECT, or MINUS set operators
    • SELECT lists of CREATE TABLE ... AS SELECT statements
    • ALTER TABLE ... MOVE statements
    • SELECT lists in subqueries in INSERT statements
  • 3. Re: Reg: LONG Update
    Karthick_Arp Guru
    Currently Being Moderated

    LONG data type is deprecated. Oracle recommends to use CLOB or BLOB data type accordingly. In your case CLOB would be the appropriate data type to use.

     

    Here is an example using CLOB

     

    SQL> create table a (no integer, val clob);

     

    Table created.

     

    SQL> insert into a (no, val) values (1, 'karthick');

     

    1 row created.

     

    SQL> select * from a;

     

            NO VAL
    ---------- -------------------------------------------------------------------
             1 karthick

     

    SQL> update a set val = val || ' oracle' where no = 1;

     

    1 row updated.

     

    SQL> select * from a;

     

            NO VAL
    ---------- -------------------------------------------------------------------
             1 karthick oracle

     

    SQL>


    See how simple its to use CLOB.

  • 4. Re: Reg: LONG Update
    infochandu Newbie
    Currently Being Moderated

    Thanks Karthick for your reply.

     

    My existing table is with LONG type, and If i change it to CLOB, would there be any impact on data or anything

     

    Thanks

  • 5. Re: Reg: LONG Update
    Suri Pro
    Currently Being Moderated

    I thought we cant modify the datatype if the table is having any records. But it seems to be working without any issues.

     

     

    SQL> create table t1 ( ename varchar2(40), address long );

    Table created

    SQL> insert into t1 values ('suri','India');

    1 row inserted

    SQL> alter table t1 modify address clob;

    Table altered

    SQL> select * from t1;

    ENAME                                    ADDRESS
    --------                                        ----------------
    suri                                     India

     

     

    infochandu wrote:

     

    Thanks Karthick for your reply.

     

    My existing table is with LONG type, and If i change it to CLOB, would there be any impact on data or anything

     

    Thanks

    You may need to see dependant objects for this table. If any procedure/fucntion/package using the same column with out mentioning %TYPE, you may need to change it to CLOB.

     

    Cheers,

    Suri

  • 6. Re: Reg: LONG Update
    Karthick_Arp Guru
    Currently Being Moderated

    infochandu wrote:

     

    Thanks Karthick for your reply.

     

    My existing table is with LONG type, and If i change it to CLOB, would there be any impact on data or anything

     

    Thanks

     

    This is documented

     

    Migrating Columns from LONGs to LOBs

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points