Is there a way to use Like in a Long field?

[lopes_andre]

Viva,

I need to search strings within a long field that is, make a select using the like.

Is it possible to do this in a LONG field?


Thanks

[fsitja]

It is not possible, firing an error or-00932.

Inclusive it is recommended that no more long types in the new oracle versions because this will be discontinued. Replace by BLOB if you are feasible and the varchar2 does not serve you.
http://www.techonthenet.com/oracle/errors/ora00932.php

[dr_gori]

Just to complement:
BLOB - for binaries
clob - for characters

: -O

[fsitja]

Oh yeah, pretend it was TYPO! : roll:

[dr_gori]

Remember that in dbms_lob has some functions that can help, such as dbms_lob.instr, dbms_lob.substr ...

These can "simulate" a like ...

[lopes_andre]

Live again.

I spent the Long Clob field to be able to use the like, but now needed to change again for Long and I can not.

The question, is there a way to pass a cloze field for Long?


Thanks.


Greetings.

[NightSpy]

Andre, could not turn to Varchar2 ?? I think it would be more feasible.

[Trevisolli]

Brother,

Doing alter returning I believe it is not possible. I made some tests here and I could not.

Create it as Long (I am using version 9i) is possible yes, however, after changed, you can not return to the original datatype.

may be any Oracle Feature , which no longer allows you to use this type of data in some bank versions (would need to take a more calm search).

But to be sure, I did the tests below:

Select all

 
CREATE TABLE teste (cod NUMBER(10), descricao LONG); 
 
SELECT * 
FROM teste; 
 
ALTER TABLE teste MODIFY descricao CLOB; 
 
SELECT * 
FROM teste; 
 
 
ALTER TABLE teste MODIFY descricao LONG; 
 
-- ORA-22859: invalid modification of columns 

a tip: I do not know the Your structure and complexity of your table, but if you are obligatoryly to need this type of data again (as informed by Dr_Gori, if other datatypes do not satisfy your need), give a drop on your table (saving information before) and recreate This datatype you will probably get.

If you can not give a Drop in the table, create another column in this table, type long , give a update of the values ??of the CloB Pro Long field and then drop this cloble column.

There is the Brother tip.

Anything, send us to us.

[GuGatto]

You can create a function to convert the field.

Select all

CREATE OR REPLACE FUNCTION get_search_condition 
( 
  p_owner           all_constraints.owner%TYPE, 
  p_constraint_name all_constraints.constraint_name%TYPE 
) RETURN VARCHAR2 IS 
  v_long LONG; 
BEGIN 
  SELECT search_condition 
  INTO   v_long 
  FROM   all_constraints 
  WHERE  constraint_name = p_constraint_name 
  AND    owner = p_owner 
  AND    constraint_type = 'C'; 
 
  RETURN substr(v_long, 1, 32760); 
END;

then use it at Query:

Select all

SELECT constraint_name, 
       get_search_condition(owner, constraint_name) AS search_condition 
FROM   all_constraints 
WHERE  owner = 'MYSCHEMA' 
AND    table_name = 'MYTABLENAME' 
AND    constraint_type = 'C' 
AND    get_search_condition(owner, constraint_name) = 'TEXT_SEARCH';