This content has been marked as final. Show 8 replies
SQL> create table search_long(id number,text long);
SQL> insert into search_long values(1,'oracle is good');
1 row created.
SQL> insert into search_long values(1,'the latest version of oracle is 10g');
1 row created.
SQL> select * from search_long where text like '%oracle%';
select * from search_long where text like '%oracle%'
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG
Wrote file afiedt.buf
1 create or replace function search_text(x rowid) return varchar2 is
2 temp_text varchar2(4000);
4 select text into temp_text from search_long where rowid=x;
5 return temp_text;
SQL> select text from search_long where search_text(rowid) like '%oracle';
oracle is good
the latest version of oracle is 10g
You have to use some alternative way while searching for LONG column
I tried the solution suggested by Waseem.
Seems fine to me except for the problem that it does a case sensitive search.
How could I do a case insensitve search?
Also due to reasons beyond my control it is not possible to use an alternative datatype other than LONG at this stage.
Thanks in Advance
In trying Mohammed's solution to querying LONG columns, it does not work against a VIEW (eg. USER_VIEWS) in that a ROWID does not exist. It does work however against actual tables (realizing only the first 32K of text is searched, our longest view is 18K).
What are the actual data dictionary tables which contain the LONG column which holds the TEXT of the view?
I need to find all views where there exists a string ''%TBL%' within the TEXT (which is a portion of an actual TABLE NAME). Or, is there a data dictionary view which shows what objects are used within a view. I know PL/SQL developer can do this when you click the 'references' folder on any given view, but I cannot see how they are doing it merely by seeing partial cursors.
What are the actual data dictionary tables whichselect text from dba_views where view_name='DBA_VIEWS';
contain the LONG column which holds the TEXT of the
will give you the answer (sys.view$)
so you could create a TEXT index on that view and use CONTAIN, but I am not sure it is the right method.
As suggested, you can create a table with clob.
create table t as select u.name owner, o.name view_name, to_lob(v.text) text from sys.obj$ o, sys.view$ v, sys.user$ u where o.obj# = v.obj# and o.owner# = u.user#; Table created. SYS@LSC01> select view_name from t where text like '%xxx%'; no rows selected