Results 1 to 8 of 8

Thread: Blob

  1. #1
    Join Date
    Jan 2004
    Location
    Brazil
    Posts
    4

    Unanswered: Blob

    Friends,

    I have this problem...
    How can I read a Blob using like command?

    For example, in a report of search of words.
    desc is a BLOB

    select
    *
    from
    chamados,
    eventochamados,
    liberados
    where
    CD_CODIGO = EC_CD_CODIGO and
    CD_CODIGO = LB_CD_CODIGO and
    desc like DESC
    order by
    CD_CODIGO,
    EC_INICIO

    error: Data Type inconsistent
    Filter: %word%

    Thanks a lot.
    Angélica.
    paternolli@hotmail.com
    paternolli@zipmail.com.br

  2. #2
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130
    It should be a CLOB (Character LOB) if it contains characters, not a BLOB (Binary LOB) ... convert it if you want to stay away from trouble (e.g. charset conversion)!

    If it were a CLOB:

    SQL> create table tt (c clob);

    Table created.

    SQL> insert into tt (c) values ('viva la vida loca');

    1 row created.


    SQL> select dbms_lob.instr (c, 'vida') from tt;

    DBMS_LOB.INSTR(C,'VIDA')
    ------------------------
    9

    SQL> select dbms_lob.instr (c, 'remarquez') from tt;

    DBMS_LOB.INSTR(C,'REMARQUEZ')
    -----------------------------
    0

    So searching for a word is as easy as

    SQL> select * from tt where dbms_lob.instr (c, 'vida') > 0;

    C
    -----------------------------------------------------------------
    viva la vida loca

    For complex searches or big tables or big clobs, this is very inefficient - you may want to build a special index (context) - search infos about Oracle Text (aka Intermedia Text), for light-speed searches (such as those in Search Engines of web sites such as Amazon).

    HTH
    Al

  3. #3
    Join Date
    Oct 2003
    Posts
    706

    Exclamation

    A "full text index" (by any other name) would be an absolute requirement for such a case. Nearly every DBMS has this capability because searches of blob-text would otherwise be impossible.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  4. #4
    Join Date
    Jan 2004
    Location
    Brazil
    Posts
    4

    BLOB AGAIN

    Thanks Alberto, but when I execute this command it's wrong as:

    select * from table where dbms_lob.instr(desc, 'word') > 0
    invalid fiel type LONG

    My report pass parameter %word%, for example.
    How can I pass this parameter to this command?

    select * from table where dbms_lob.instr(desc, 'word') > 0


    Field desc on table has:
    I need to find this word.

    Angélica.


    Originally posted by alberto.dellera
    It should be a CLOB (Character LOB) if it contains characters, not a BLOB (Binary LOB) ... convert it if you want to stay away from trouble (e.g. charset conversion)!

    If it were a CLOB:

    SQL> create table tt (c clob);

    Table created.

    SQL> insert into tt (c) values ('viva la vida loca');

    1 row created.


    SQL> select dbms_lob.instr (c, 'vida') from tt;

    DBMS_LOB.INSTR(C,'VIDA')
    ------------------------
    9

    SQL> select dbms_lob.instr (c, 'remarquez') from tt;

    DBMS_LOB.INSTR(C,'REMARQUEZ')
    -----------------------------
    0

    So searching for a word is as easy as

    SQL> select * from tt where dbms_lob.instr (c, 'vida') > 0;

    C
    -----------------------------------------------------------------
    viva la vida loca

    For complex searches or big tables or big clobs, this is very inefficient - you may want to build a special index (context) - search infos about Oracle Text (aka Intermedia Text), for light-speed searches (such as those in Search Engines of web sites such as Amazon).

    HTH
    Al

  5. #5
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130

    Re: BLOB AGAIN

    Originally posted by angelica
    select * from table where dbms_lob.instr(desc, 'word') > 0
    invalid fiel type LONG
    So the field is a LONG, not a BLOB or CLOB ?

    Try describing the table (i.e. issue "desc t" from sqlplus); does it show 'LONG' or 'BLOB' as the column type ?

    --

    When you issue, on a varchar2 columns, the command

    where col like '%word%'

    you are asking for all the rows containing 'word' as a substring (such as 'AAwordB', 'wordCC', 'rrrrword', etc).

    With CLOBs, you can equivalently use the dbms_lob.instr that returns the position of 'word' into the clob, or zero if the clob does not contain the string (as i showed you yesterday).

    If your report submits a pattern different from '%word%, afaik there's not a generic way to apply a generic pattern to the clob column (in 10g yes, they have regular expressions so it may be done). I can think about some workarounds - but are you sure that your report submits patterns different from '%<something>%' ?

    Al

  6. #6
    Join Date
    Jan 2004
    Location
    Venezuela
    Posts
    151

    Re: Blob

    DBMS_LOB
    The DBMS_LOB package provides subprograms to operate on BLOBs, CLOBs, NCLOBs, BFILEs, and temporary LOBs. You can use DBMS_LOB to access and manipulation specific parts of a LOB or complete LOBs.

    This package must be created under SYS (connect internal). Operations provided by this package are performed under the current calling user, not under the package owner SYS.

    DBMS_LOB can read and modify BLOBs, CLOBs, and NCLOBs; it provides read-only operations for BFILEs. The bulk of the LOB operations are provided by this package.

    http://download-west.oracle.com/docs...b.htm#ARPLS020

    For deep details about you can check out this document:
    Application Developer's Guide - Large Objects (LOBs) Contents / Search / Index / PDF
    http://download-east.oracle.com/docs...920/a96591.pdf
    Joel Pérez

  7. #7
    Join Date
    Jan 2004
    Location
    Brazil
    Posts
    4

    Re: BLOB AGAIN

    Alberto,

    you're right...
    it's a long field.

    So, how can I locate a string in a long field passing a parameter?
    I'm sorry worry you, but I don't know how to do this...

    For cd_assunto - VARCHAR2 it's ok.
    select
    *
    from
    chamados,
    eventochamados,
    liberados
    where
    CD_CODIGO = EC_CD_CODIGO and
    CD_CODIGO = LB_CD_CODIGO and
    cd_assunto like :des
    order by
    CD_CODIGO,
    EC_INICIO

    but for ec_descricao - LONG, it's not ok.

    select
    *
    from
    chamados,
    eventochamados,
    liberados
    where
    CD_CODIGO = EC_CD_CODIGO and
    CD_CODIGO = LB_CD_CODIGO and
    ec_descricao like :des
    order by
    CD_CODIGO,
    EC_INICIO

    Thanks.
    Angélica.

    Originally posted by alberto.dellera
    So the field is a LONG, not a BLOB or CLOB ?

    Try describing the table (i.e. issue "desc t" from sqlplus); does it show 'LONG' or 'BLOB' as the column type ?

    --

    When you issue, on a varchar2 columns, the command

    where col like '%word%'

    you are asking for all the rows containing 'word' as a substring (such as 'AAwordB', 'wordCC', 'rrrrword', etc).

    With CLOBs, you can equivalently use the dbms_lob.instr that returns the position of 'word' into the clob, or zero if the clob does not contain the string (as i showed you yesterday).

    If your report submits a pattern different from '%word%, afaik there's not a generic way to apply a generic pattern to the clob column (in 10g yes, they have regular expressions so it may be done). I can think about some workarounds - but are you sure that your report submits patterns different from '%<something>%' ?

    Al

  8. #8
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130
    I would suggest to convert the LONG column into a CLOB, can you ?

    1) does your report submit patterns different from '%<something>%' ?
    2) which is maximum length of the long ?
    May it be longer then 4k?
    May it be longer then 32K ?

Posting Permissions

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