Re: SELECT on table containing BLOB column raises error

From: Holger Baer <holger.baer_at_science-computing.de>
Date: Thu, 16 Jan 2003 13:31:30 +0100
Message-ID: <3E26A622.30207@science-computing.de>

Sven, there are (at least) three errors in what you try to achieve:

• you declare a variable of type number, but select a varchar2 field, which if you're lucky will result in a implicit conversion, but very probably will fail (how to convert 'blah' into a number?)
• you have an anonymous pl/sql block in your example, you *MUST* include an select ... into clause within pl/sql (from your remarks you seem to be aware of this, nevertheless in your last post you didn't include it)
• and most important (which is why you're getting the error) your table definition is not the best choice: you 've named the blob column 'blob' which is a reserved word.

Although Oracle allows you to use reserved words as column names, it's absolutely not recommended to do so and this is why you fail.

Besides, as was suggested from another poster, learn about the package dbms_lob, I don't know what you're trying to achieve, but the length of a blob can be determined with dbms_lob, so having a extra column for that is normally not necessary - because of the special treatment a blob gets it will most probably lead to errors:

To write to a blob field, you select an lob_locator and write to that afterwards with dbms_lob. (This is a security issue sometimes, because even if you only have 'select' granted for that table, you can write to the blob field using the lob_locator). Only then, when you exactly know how much you've written into the lob field, you can correctly set the length of the blob. Pretty much work to do if you ask me....

So with this table definition you should be able to achieve what you probably wanted in the first place:

create table blobs (

	blob_name	varchar2(250),
	blob_field 	blob

);

Note: Personally, I tend to put LOB fields at the end of a table definition, but that'ts probably overenginering - the physical storage of a blob is in its own segment which shouldn't influence the space usage of the table segment (I could be wrong with the latter).

Corrections and additions welcome

Holger

Sven Strauß wrote:
> Maybe the error description was unclear, but as Anurag already said, I try
> to fetch a varchar2() row from a table that ist named "BLOBS" and that also
> includes a blob() datatype row. (But I don't want to see the movies for
> now. )
>
> That works:
>

>>select blob_name from blobs where blob_name='4126.blob';

>
>
> This statement doesn't:
>

>>  1  declare bla number(12);
>>  2  begin select blob_name from blobs where blob_name='4126.blob';
>>  3  end;
>>  4  /

>
>
> And yes, I'm not too deeply involved in Oracle Stuff. Therefore I ask for
> help in the Newsgroup.
>
> Regards,
> Sven
>
Received on Thu Jan 16 2003 - 06:31:30 CST