Re: Converitng LONG to CHAR

In article <38C51A97.77D81081_at_cableone.net>,   "Robert W. Stanford" <stanford_at_cableone.net> wrote:
> what's the url to thomas's homepage?
>
> fumi wrote:
>
> > Franz Mueller <nospam#####franz.mueller_at_orbis.de> wrote in message
> > news:38c139bc.159449_at_news.salink.net...
> > > 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.
>
>

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

   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                              tkyte_at_us.oracle.com
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.