8 Replies Latest reply: Sep 18, 2006 12:15 PM by 45878 RSS

    Search on a LONG field

    471547
      I have a table MANMESS having a LONG field (MESSAGE)

      I want to search for a word e.g. "supplier" in this LONG field

      When I enter the statement
      select * from MANMESS where MESSAGE like '%supplier%';

      it gives an error message.

      I guess it is because MESSAGE is a LONG field and needs to be given special treatment

      Is there an alternative to so the same task?

      Thanks in Advance
      Paresh
        • 1. Re: Search on a LONG field
          BluShadow
          Well, if possible don't use a LONG column in your table, use CLOB instead.
          • 2. Re: Search on a LONG field
            444756
            *********************
            SQL> create table search_long(id number,text long);

            Table created.

            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> commit;

            Commit complete.

            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


            SQL> ed
            Wrote file afiedt.buf

            1 create or replace function search_text(x rowid) return varchar2 is
            2 temp_text varchar2(4000);
            3 begin
            4 select text into temp_text from search_long where rowid=x;
            5 return temp_text;
            6* end;
            SQL> /

            Function created.

            SQL> select text from search_long where search_text(rowid) like '%oracle';

            TEXT
            -----------------------------------------------

            oracle is good
            the latest version of oracle is 10g

            ********************

            You have to use some alternative way while searching for LONG column

            -aijaz
            • 3. Re: Search on a LONG field
              471547
              Hi,

              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
              Paresh
              • 4. Re: Search on a LONG field
                94799
                How could I do a case insensitve search?
                Well you would typically UPPER () or LOWER () both sides as usual.

                Be advised that this approach (PL/SQL function will only search first 32K of LONG).
                • 5. Re: Search on a LONG field
                  45878
                  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.

                  Thanks, Kate.
                  • 6. Re: Search on a LONG field
                    Laurent Schneider
                    What are the actual data dictionary tables which
                    contain the LONG column which holds the TEXT of the
                    view?
                    select text from dba_views where view_name='DBA_VIEWS';
                    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
                    • 7. Re: Search on a LONG field
                      JensPetersen
                      Tom Kyte provides a more generic procedure here
                      http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:839298816582#44365156010493
                      • 8. Re: Search on a LONG field
                        45878
                        Perfect, I used the to_lob to convert the LONG to CLOB into a new table grabbing those views which I am interested in, then was able to query the TEXT of the view no problem against the new table. Thank you, Kate.