Re: How to find a blob is empty?

"Prem K Mehrotra" <premmehrotra_at_hotmail.com> wrote in message news:43441e77.0405091130.4f846551_at_posting.google.com...
> In my program I need to find whether a blob (blob is empty. ie.,blob
> locator points to empty_blob.
>
> I wrote following code:
> declare
>
> blob_loc blob;
> buffer raw(255) := utl_raw.cast_to_raw(
> 'This is a new line of text4!\n\r');
> begin
> blob_loc := empty_blob();
> dbms_output.put_line('Size of lob but before compression: ' ||
> dbms_lob.getlength(blob_loc) || ' bytes'
> );
>
> end;
>
> I get following error:
>
> declare
> *
> ERROR at line 1:
> ORA-22275: invalid LOB locator specified
> ORA-06512: at "SYS.DBMS_LOB", line 535
> ORA-06512: at line 8
>
> I was expecting that length will be zero for emty_blob()but I get
> error
> instead.Why?
>
> I ma using Oracle9i.
>
> Prem

Prem,

Your code works fine in Oracle 10.1.0.2 on WinXP Pro but I can reproduce your error on Oracle 9.2.0.1 on WinXP Pro.

I have reviewed the documentation in "Oracle 9.2 Supplied PL/SQL Packages and Types" manuals and found no restriction on this function (GETLENGTH).

My guess is that there is a bug in 9.2.0.1 for this function which is fixed in a later release. You would need to consult MetaLink to confirm these and get the appropiate patches.

Douglas Hawthorne Received on Sun May 09 2004 - 21:45:51 CDT