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

From: Thomas Greuter <deletethis.grth1_at_zh.swissptt.ch>
Date: 1997/07/23
Message-ID: <01bc974a$c25f4990$62d5bb8a@tzhi95>#1/1

Hi Greg,

I did this:

FUNCTION F_POS_UPD_ARTTXTS RETURN NUMBER IS

-- 17.7.97 TG.
-- PRE condition:
-- Table local_artikeltexts erstellt
-- Synonym prida_artikeltexts auf artikeltext_at_prida definiert
-- SQL> CREATE PUBLIC SYNONYM artikeltexts FOR artikeltext_at_prida 
--
-- POST condition:
-- Table local_artikeltexts BezeichnungD abgefuellt
--
lvc_TextD	varchar2(255);
CURSOR c_artikel_ids IS
	SELECT artikel_id FROM local_artikeltexts;
BEGIN
	FOR art_ids IN c_artikel_ids LOOP
		lvc_TextD := '';
		SELECT bezeichnungD 
		INTO lvc_TextD 
		FROM artikeltexts 
		WHERE art_ids.artikel_id =  artikeltexts.artikel_id;
		UPDATE local_artikeltexts
		SET bezeichnungD = lvc_TextD
		WHERE art_ids.artikel_id = local_artikeltexts.artikel_id;
		COMMIT;
	END LOOP; -- c_artikel_ids
	RETURN 0;

END; But this doesn't work properly: The lvc_TextD gets nothing meaningful, i.e. it is just binary data. The code itself works properly.

I will try to get a view on the SQL-Server what does the convert from text (<- LONG for Oracle) to varchar.

Thanks for your idea,

Thomas

Greg Scholey <Greg.Scholey_at_pipe.nova.ca.nospam> schrieb im Beitrag <33D3BA4E.22B3_at_pipe.nova.ca.nospam>...
> 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...
>
Received on Wed Jul 23 1997 - 00:00:00 CDT