Converting LONG to VARCHAR2 1

[golyg]

I have a field that is of type LONG and I would like to convert this field to a VARCHAR2 in a view so that I can display this field on a report. Crystal Report does not like the LONG datatype.

Is there anyway that i can do this in the creation of the view?
I don't mind losing some of the data in the view, as I think some of the data is quite large

thanks,

 

[sem]

In any case this field can not be larger than 4000 (SQL varchar2 limit). If this suits you, create some dummy function, accepting rowid as a parameter and returning varchar2 value where long field (or its part) was fetched.

create function conv(pRowid in rowid)
return varchar2
is
retVal varchar2(4000);
begin
select longField into retVal
from myTable
where rowid = pRowid;

return retVal;
exception
when others then return null;
end;

create view myView as select conv(rowid) longField from myTable;

Regards, Dima

 

[Mockenrue]

Wasn't 32767 the limit for varchar2 fields ?

 

[golyg]

I thought it was 4000....

 

[golyg]

Hey sem...

another question
the line of code:
when others then return null;

what does 'others' mean?

thanks so much, you have been such a great help...

 

[sem]

OTHERS stands for ANY unhandled exception.

Regards, Dima

 

[sem]

Mockenrue: 32K is limit of pl/sql (not sql) varchar2 type.

Regards, Dima

 

[Mockenrue]

A-ha, o.k.