SELECT on table containing BLOB column raises error

From: Sven Strauß <sstrauss_at_gmx.de>
Date: Wed, 15 Jan 2003 17:43:55 +0100
Message-ID: <b0434f$atb$07$1@news.t-online.com>

Hi,
I have a tables "BLOBS" in an Oracle 9iR2 instance on linux. The following statement doesn't work on the table. I've got no clue where the problem is (try to use the oerr tool to translate the german language).

###

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

                            *

FEHLER in Zeile 2: <-- Error in line 2 ORA-06550: Zeile 2, Spalte 29:
PL/SQL: ORA-06552: PL/SQL: Compilation unit analysis terminated ORA-06553: PLS-320: Die Typ-Deklaration dieses Ausdruckes ist unvollständig oder fehlerhaft
ORA-06550: Zeile 2, Spalte 7:
PL/SQL: SQL Statement ignored
####

Sure, the statement makes no sense, but an "select ... into bla" raises the same error.
I created an other table with a similar structure, and everything works fine. So probably the issue is in these table "BLOBS".

It works fine without PL/SQL:
###

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

BLOB_NAME

---

---

4126.blob

###

Structure of table:
###

SQL> describe blobs;

 Name                                      Null?    Typ
 ----------------------------------------- -------- ------------------------
----
 BLOB                                               BLOB
 BLOB_LENGTH                               NOT NULL NUMBER(12)
 BLOB_NAME                                 NOT NULL VARCHAR2(250)

More information on table
####

SQL> select * from user_tables where table_name='BLOBS';

TABLE_NAME TABLESPACE_NAME PCT_FREE PCT_USED INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE FREELISTS

FREELIST_GROUPS LOGGING   BAC   NUM_ROWS      CACHE  TABLE_LOCK   PARTITION
TEM SEC NESTED    BUFFER_POOL           ROW_MOVEMENT             GLOBAL_ST
USER_STAT  SKIP_CORRUPT             MONITORIN  DEPENDENCIES
------------ ---------------- ---------- ---------- ---------- ---------- --
------------ ----------- ----------- ----------- ------------ ---------- ---
------------ --------- --- ---------- ---- ------ ------------ --------- ---
 --- --------- --------------------- ------------------------ --------- ----
-----  ------------------------ ---------  ------------
BLOBS        CM52TS                   10         40          1        255
65536                       1  2147483645                       1
1 YES       N                       N  ENABLED      NO        N   N   NO
DEFAULT               DISABLED                 NO        NO         DISABLED
NO         DISABLED

Thanks for Help,
Sven Received on Wed Jan 15 2003 - 10:43:55 CST