Hello Sven,
I accidently replied to you in private (I saw your mail first, your post
second). One last advice though: in all your posts you're missing to
give us your oracle version - not that it mattered in this case, but
in future be sure to include your oracle version and OS - else some
proposals might just won't work (and even Oracle is known to have
bugs - which are of course version dependent).
Best regards,
Holger
Sven Strauß wrote:
> 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
Received on Fri Jan 17 2003 - 06:51:19 CST
