Results 1 to 2 of 2

Thread: Casting a BLOB

  1. #1
    Join Date
    Feb 2004

    Unanswered: Casting a BLOB


    I need to run this query (this is a simplified part of cursor that is acting as a return value, that's why I need to keep the union):

    select id, name,content from npublic where nlevel = 0
    union all
    select id,name, NULL AS content from npublic where nlevel != 0;

    content is a BLOB field of the table npublic.
    I know there's an error there because the type of the NULL doesn't match with that of the content. The question is: is there any way to "cast" a null in a BLOB, or is it possible to run the query above doing something else (of course I need to preserve the union there).


    Last edited by fmilano; 09-03-04 at 15:01.

  2. #2
    Join Date
    Sep 2003
    Milan, Italy
    SQL> create table b (x blob);
    Table created.
    SQL> create or replace view vvv as
      2  select x from b
      3  union all
      4   select to_blob(null) b from dual;
    View created.
    SQL> desc vvv
     Name                                      Null?    Type
     ----------------------------------------- -------- ------
     X                                                  BLOB
    SQL> insert into b (x) values (to_blob ('aaaaaa'));
    1 row created.
    SQL> set serveroutput on size 10000
    SQL> declare
      2    l_blob blob;
      3  begin
      4    for c in (select x from vvv) loop
      5       l_blob := c.x;
      6       if (l_blob is null) then
      7         dbms_output.put_line ('null');
      8       else
      9         dbms_output.put_line ('not null');
     10       end if;
     11    end loop;
     12  end;
     13  /
    not null
    PL/SQL procedure successfully completed.
    So, to_blob(null) should work for you.

    Last edited by alberto.dellera; 09-05-04 at 14:12.

Posting Permissions

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