Re: LONG to VARCHAR: How can it be done?

Try something like this...

create a function as follows:

	create or replace function lng2var (vARTIKEL_ID in number)
	  return varchar2 is
	    longvar varchar2(32767);
        begin
	  select BEZEICHNUNGD into longvar
            from view_name
           where artikel_id = vARTIKEL_ID;
          --
          return (substr(longvar,1,255));
        end;

then from your select statement...

	select ARTIKEL_ID, lng2var(ARTIKEL_ID)
          from view_name
         where ....
         order by ....
        /

I know I'm hitting the table twice, but a nice feature here is that one can manipulate the data from within the function prior to returning it back to the select statement. The function can be treated as temporary and dropped by the user after the required data is retrieved.

Hope I've helped,
Greg...

Thomas Greuter wrote:
>
> Hi2everybody,
>
> I have a view to a table on a SQL-Server using the oracle gateway. The view
> desc looks as follows:
> Name Null? Type
> ------------------------------- -------- ----
> ARTIKEL_ID NOT NULL NUMBER(10)
> BEZEICHNUNGD NOT NULL LONG
>
> BEZEICHNUNGD contains the text I have to put in another table where it is
> defined as VARCHAR2(255).
>
> Since simple INSERT/UPDATE doesn't work with LONG datatypes, I need some
> kind of conversion. There is no such thing in PL/SQL as I know.
> Q: How can this be done? I'm really in trouble, because this is part of a
> data sync process between two databases.
>
> Every hint is very, very welcome
>
> Thomas
> [ENV: ORACLE 7.3, NT4.0, Intel]
>
> [This msg is also posted in newsgroup oracle.sever]
Received on Mon Jul 21 1997 - 00:00:00 CDT