Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2005
    Posts
    116

    Unanswered: Query on Long Datatype using LIKE Operator

    Hi Guys

    I have a column in a table called test1 and its datatype is long..Im trying to query it using a like operator but it failed..Is there any suggestions or sample query on how can i query a long datatype column

    this is the query is used but it displays an error

    select * from test where dbms_lob.instr(test1,'#123%')>0;

    ERROR at line 1:
    ORA-00997: illegal use of LONG datatype

    select * from test where test1 like '#123%';

    ERROR at line 1:
    ORA-00932: inconsistent datatypes: expected NUMBER got LONG


    Thankx for any help in advance

  2. #2
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    That's the kind of reason they invented CLOBs. Is there any way you can migrate the data to use a CLOB?

    If not, an approach is possible that makes use of the fact that there can only be one LONG column per table, so given the ROWID a PL/SQL function can load the value into a CLOB variable (or the first 32K into a VARCHAR2). It won't be straightforward or efficient though.
    Last edited by WilliamR; 02-09-05 at 17:21.

  3. #3
    Join Date
    Feb 2005
    Posts
    116
    What is the differents between CLOB and LONG DataType

    Lets say i convert my existing table structure for a column from LONG to CLOB, will it effect the existing data?
    Last edited by a1jit; 02-10-05 at 06:22.

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    LONG is no longer supported essentially.
    This is evident by your current situation and many others out there
    (like the LONG column in the partition table that you cannot use! ugh!! argh!!).

    anyways ...
    converting to CLOB will not lose data. However, with CLOB now you can manipulate it much easier than you could with a LONG column.

    look up: dbms_clob and to_clob
    (i think those are right but someone correct me if not)
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Your right there Duck ... convert those columns and make life easier ...

    SYS.DBMS_LOB package

    HTH
    Gregg

  6. #6
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Try ALTER TABLE tablename MODIFY columnname CLOB;
    (requires 9i, though the note mentions an 8i technique)

    Code:
    SQL> CREATE TABLE testtab (id INT, longcol LONG);
    
    Table created.
    
    SQL> DECLARE
      2     v_long VARCHAR2(32737) := RPAD('x',32737,'x');
      3  BEGIN
      4     INSERT INTO testtab VALUES(1, v_long);
      5* END;
    SQL> /
    
    PL/SQL procedure successfully completed.
    
    SQL> SELECT ID, LENGTH(longcol) FROM testtab;
    SELECT ID, LENGTH(longcol) FROM testtab
                      *
    ERROR at line 1:
    ORA-00932: inconsistent datatypes: expected NUMBER got LONG
    
    
    SQL> ALTER TABLE testtab MODIFY longcol CLOB;
    
    Table altered.
    
    SQL> SELECT ID, LENGTH(longcol) FROM testtab;
    
            ID LENGTH(LONGCOL)
    ---------- ---------------
             1           32737
    
    1 row selected.
    The CLOB column will automatically create a system-generated LOB object in your schema. You can supply your own name for this with the extended syntax:
    Code:
    SQL> ALTER TABLE testtab MODIFY longcol CLOB
      2  LOB (longcol) STORE AS bananas;
    
    Table altered.
    
    SQL> desc bananas
    ERROR:
    ORA-04043: object bananas does not exist
    
    
    SQL> @which bananas
    
    Objects matching 'bananas':
    Name                         Type             OWNER                    Granted?
    ---------------------------- ---------------- ------------------------ --------
    BANANAS                      LOB              WILLIAM                  Yes
    As for the difference between LONG and CLOB: LONG is an ancient type going back to the earliest Oracle versions (I remember Oracle 5 had them) and they have always had some severe limitations, as you have found. The two LOB ('Large Object') types, BLOB (binary) and CLOB (character) were new in 8i, and had some new features added in 9i such as compatibility with most of the regular SQL character functions.
    Last edited by WilliamR; 02-11-05 at 15:15.

  7. #7
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    and had some new features added in 9i such as compatibility with most of the regular SQL character functions.
    Just to precise your thought WilliamR, it's only true for CLOBs and NCLOBs (CLOBs using the National Charset) if I'm right, not LOBs. You can also use Oracle Text's DOMAIN Indexes on CLOBs (and NCLOBs) in order to do Full-Text Searches (not on LOBs, not to say on LONGs ).

    HTH & Regards,

    RBARAER
    Last edited by RBARAER; 02-11-05 at 15:38.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •