Re: How to create a view containin a lblob column

"Anurag Varma" <avdbi_at_hotmail.com> wrote in message news:<Q8Klc.150548$M3.68258_at_twister.nyroc.rr.com>...
> "Prem K Mehrotra" <premmehrotra_at_hotmail.com> wrote in message news:43441e77.0405031136.77be7636_at_posting.google.com...
> > I have following table containing a blob column:
> >
> > SQL> desc ltr_body;
> > Name Null? Type
> > ----------------------------------------- -------- ----------------------------
> > LTR_ID NOT NULL VARCHAR2(20)
> > LTR_DESC_TXT VARCHAR2(300)
> > LTR_BODY_TXT BLOB
> > ADD_DT DATE
> > ADD_OPER_ID VARCHAR2(25)
> > UPDT_DT DATE
> > UPDT_OPER_ID VARCHAR2(25)
> >
> > I want to define a view:
> >
> > sqlplus login/password << !
> > create or replace view vltr_body as
> >
> > select LTR_ID, LTR_DESC_TXT, blob_uncompress(read(LTR_BODY_TXT) ltr_body_txt,
> > ADD_DT, ADD_OPER_ID, UPDT_DT, UPDT_OPER_ID
> > from ltr_body;
> >
> > !
> > I get following error:
> > SP2-0678: Column or attribute type can not be displayed by SQL*Plus
> >
> > Is there any way to define a view, so that I can apply my user written function
> > to a column which is blob type.
>
>
> do you have an extra (newline) between create or replace view ... and the select statement?
>
> Can you remove that. It seems to be executing your select statement ONLY!! not the create table as select.
>
>
> Anurag

Actually, I don't know whether there is a way to create views using sqlplus by itself. However, when I put create view statement inside a PL/SQL anonymus block,
view gets created:

sqlplus login/password << !

declare

        sql_stmt varchar2(512);
begin
sql_stmt := '
create or replace view vltr_body as
select LTR_ID, LTR_DESC_TXT, blob_uncompress(LTR_BODY_TXT) ltr_ body_txt,
ADD_DT, ADD_OPER_ID, UPDT_DT, UPDT_OPER_ID from ltr_body';

execute immediate (sql_stmt);
end;
/

! Received on Tue May 04 2004 - 08:21:01 CDT