Re: Converitng LONG to CHAR

From: Hakan Eren <heren_at_home.com>
Date: 2000/03/05
Message-ID: <38C1CCE0.700D0F8B@home.com>#1/1

Hi,

you need to use select statement in your procedure. You can go up to 32K for long types.

example:

SQLWKS> create table test
     2>   (f1 long)
     3> /

Statement processed.
SQLWKS> insert into test

     2>
values('fd;dsfdfjds;lfjsdafjepwirpweorlsdjfljfe[waurewjrlwker490504334j090r405i3503495093483050934urj;ldfjg;cfdhdfshflshflahflsdahfsldfhdsfhsdlfkhdsflkhdsflkhsdaflkhsdfaldhsflskahflskhflsdafhsldfhdlskhfsdlkhsfdlkahfsdalkhfdsalhriweqryweryweqoyweqoryweqroiryeoiywqeroiyrqwerykdhshlfhlxkzhlkxzhlkzxHCLKHCklzXHlkzxchlkXZHLKHfiasydfiosdayfoidysdfaioyfdiosda')

     3> /
1 row processed.
SQLWKS> commit
Statement processed.

SQLWKS> /
SQLWKS> CREATE OR REPLACE Function Long2Char RETURN VARCHAR2
     2> IS lngResult
     3> VARCHAR2(32000);
     4> Begin
     5>   SELECT f1 INTO lngResult from test;
     6>   Return (SUBSTR(lngResult, 1, 2000));
     7> End;
     8> /

Statement processed.
SQLWKS> select Long2Char from dual

     2> /
LONG2CHAR                                                                       
--------------------------------------------------------------------------------

fd;dsfdfjds;lfjsdafjepwirpweorlsdjfljfe[waurewjrlwker490504334j090r405i350349509 1 row selected.
SQLWKS> you can send "where" condition to the function and get the data.

Good luck

Hakan

Franz Mueller wrote:
>
> Hi,
>
> I eould like to convert a LONG to a char field. In the newsgroup I
> found the following sp:
>
> CREATE OR REPLACE Function Long2Char(lngVal IN long) RETURN VARCHAR2
> IS lngResult
> VARCHAR2(2000);
> Begin
> lngResult := lngVal;
> Return (lngResult);
> End;
> /
>
> 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?
Received on Sun Mar 05 2000 - 00:00:00 CST