Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: SELECT on table containing BLOB column raises error

Re: SELECT on table containing BLOB column raises error

From: Sven Strauß <sstrauss_at_gmx.de>
Date: Fri, 17 Jan 2003 11:08:34 +0100
Message-ID: <b08kn9$qo$01$1@news.t-online.com>


Hello Holger,

your were perfectly right, especially with advice #3. I already found an hint to that issue in a newsgroup archive before, so I read the sql and pl/sql oracle reference, section "reserved words", but I couldn't find the string "BLOB" as a reserved word. Now I renamed the column to BLOBCOL, and everything works fine.

Additionally, I was slightly aware the "select ... into" clause mistake, I ommited that because the error message was the same. It was pure laziness ;-( . Similar with the incorrect type definition of variable "var". I tried so much that I finally end up completely confused...

The reason for not using the dbms_lob package is that I try to manipulate data in a given application, which has exactly this structure. Actually, I'd like to modify data with a tcl script, connecting to oracle with the "oratcl" package, which is the one who raised the error for the first time. But now, it should work without complains, at least concerning this issue.

Holger, once again, thanks a lot for your valuable and in-depth help and information!

Regards,
Sven

Holger Baer" <holger.baer_at_science-computing.de> schrieb im Newsbeitrag news:3E26A622.30207_at_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 Fri Jan 17 2003 - 04:08:34 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US