> > > Hi,
> > >
> > > I eould like to convert a LONG to a char field. In the newsgroup I
> > > found the following sp:
> > >
> > >
snip
> > > This compiles without any problem but if I try to access it:
> > >
> > > SQL> select long2char(long_field) from my_table;
> > >
> > > I get: ORA-00997: illegal use of LONG datatype
> > >
> > > What can I do?
> >
> > This function can only be used in PL/SQL, not in SQL.
> > That's why the complier didn't return any error messages.
> > LONG datatype can not be used in the parameters of functions in SQL.
> > That's why ORA-997 occured.
> >
> > But, this function is useless, since VARCHAR2 and LONG datatype are
> > almost the same in PL/SQL.
> >
> > You can find the answer in Thomas Kyte's home page.
>
>

Answer:

I don't have that specific thing on my page (see below) however, if you
are trying to select a substr of a long in a SQL query (upto 2000 bytes
in 7.x, 4000 bytes in 8.x) you would code:

create or replace function lsubstr( p_rowid in rowid,
                                    p_from in number,
                                    p_for in number  ) return varchar2
as
   l_tmp  long;
begin
   select LONG_COL into l_tmp from T where rowid = p_rowid;
   return substr( l_tmp, p_from, p_for );
end;
/

Then you can:

select a, b, c, lsubstr( ROWID ) from T where .....;
 

This works AS LONG AS the long column is 32k or less.  values larger
then that do not fit into plsql variables.

--
Thomas Kyte                              [email protected]
Oracle Service Industries
http://osi.oracle.com/~tkyte/index.html
--
Opinions are mine and do not necessarily reflect those of Oracle Corp
 

Sent via Deja.com http://www.deja.com/
Before you buy.