How to Search the Long Datatype Within an Oracle Database

Searching LONG datatype in Oracle

Yes, data type LONG in Oracle System-Views is a pain. When I have to use such values I use this one:

DECLARE
    high_value INTEGER;
BEGIN
    FOR aPart IN (SELECT * FROM USER_TAB_SUBPARTITIONS) LOOP
        EXECUTE IMMEDIATE 'BEGIN :ret := '||aPart.HIGH_VALUE||'; END;' USING OUT high_value;

        SELECT ...
        WHERE ... = high_value;

    end loop;

END;

Note, in this example HIGH_VALUE is an integer value. However, it can be anything else (e.g. a TIMESTAMP), consider this in your procedure. For example like this:

FUNCTION IntervalType(tableName IN VARCHAR2) RETURN VARCHAR2 IS

    EXPRESSION_IS_OF_WRONG_TYPE EXCEPTION;
    PRAGMA EXCEPTION_INIT(EXPRESSION_IS_OF_WRONG_TYPE, -6550);

    ds INTERVAL DAY TO SECOND;
    ym INTERVAL YEAR TO MONTH;
    str VARCHAR2(1000);

BEGIN

    SELECT INTERVAL
    INTO str 
    FROM USER_PART_TABLES 
    WHERE TABLE_NAME = tableName;

    EXECUTE IMMEDIATE 'BEGIN :ret := '||str||'; END;' USING OUT ym;
    RETURN 'YEAR TO MONTH Interval of '||ym;

EXCEPTION 
    WHEN EXPRESSION_IS_OF_WRONG_TYPE THEN
        EXECUTE IMMEDIATE 'BEGIN :ret := '||str||'; END;' USING OUT ds;
        RETURN 'DAY TO SECOND Interval of '||ds;

END IntervalType;

Problem with a ORACLE LONG datatype field when searching for it with JPA

You should be able to map Long as LOB like:

@Lob
@Column(name = "TXT_MENSAGEM")
private String txtMensagem;

How to select a LONG column in Case Select Query

As proposed here you may workaround using a global temporary table

First insert the relevant part (record) of your table in the temporary table. Than you can select from it as required, because both columns in teh temporary table have same type - CLOB.

You need not to delete from the temp. table, a COMMIT will do it.

Example

create table tab
(short_text varchar2(10),
 long_text LONG,
 datatype_id number);

insert into tab values ('xxx','xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx', 1);     
insert into tab values ('yyy','yyyxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx', 2); 

This fails:

select datatype_id,
case when datatype_id=1 then long_text
else   short_text    end as txt from tab;

ORA-00932: inconsistent datatypes: expected LONG got CHAR

But copying data in temporary table works

CREATE GLOBAL TEMPORARY TABLE t2
(short_text CLOB,
 long_text CLOB,
 datatype_id number)
ON COMMIT DELETE ROWS;

insert into t2 (short_text,   LONG_TEXT, datatype_id )
select short_text,  to_lob(LONG_TEXT), datatype_id from tab; 

select datatype_id,
case when datatype_id=1 then long_text
else   short_text    end as txt from t2;

DATATYPE_ID TXT                                                                             
----------- --------------------------------------------------------------------------------
          1 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx                                                
          2 yyy 

Find all database records where the char data length in a column is larger than X

I suggest you create a function that calculate length of notes,receiving as parameter your table id (I asumme tbl_name_id) like this:

CREATE OR REPLACE function get_length(val long) return number
is
res long;
begin
    select notes into res from tbl_name where val = tbl_name_id;
    return length(res);  
end;

And then you can do this:

select * from tbl_name where get_length(tbl_name_id) > 1

You can also see here http://www.techonthenet.com/oracle/questions/long_length.php

---

Related Topics