Re: SELECT on table containing BLOB column raises error

First, the OP should look at the pl/sql documentation to learn some basic pl/sql If you see more closely he is not really selecting a BLOB .. he's selecting a varchar2 column into a number datatype.

I guess page 1 in documentation is a good starting point?

Anurag

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message 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 Wed Jan 15 2003 - 22:24:39 CST