Re: SELECT on table containing BLOB column raises error

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

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> schrieb im Newsbeitrag news:ZTmV9.25035$jM5.66218_at_newsfeeds.bigpond.com...
> 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
> >
> >
> >
> >
> >
> >
> >
>
>
Received on Thu Jan 16 2003 - 02:44:06 CST