Re: SELECT on table containing BLOB column raises error

On my test workbench at home, I have a table called 'FILMS', where one of the columns (F_CONTENT) stores, as a BLOB, the actual movie. Strangely enough, when I do a select F_CONTENT from films where film_name='TITANIC' sqlplus doesn't suddenly decide to display on my monitor Kate Winslett and Leonardo Dicaprio doing the hokey-kokey on the largest iceberg in sight. Shame, that.

Can you think why?

Er, could it by any chance be that sqlplus is a character-based tool and wouldn't have the faintest clue about how to go about displaying binary data?

Or is it just that sqlplus has better taste than me in movies?

BLOBS can't be selected in sqlplus, period. You need to investigate DBMS_LOB (if you're that way inclined) or use some other tool to query the database (for example, I embed a media player control in an Access form, and connect to the Oracle database via ODBC. It's slow, but then so was the movie.

Regards
HJR "Sven Strauß" <sstrauss_at_gmx.de> wrote in message news:b0434f$atb$07$1_at_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

>

>

>

>

>

>

>