Re: Inserting long data

From: Darren Mallette <khs_riderx_at_geocities.com>
Date: 1997/11/04
Message-ID: <345f49e0.353628780@207.93.1.8>#1/1

>> David P. Baker wrote:
>>
>> > Hi. We're running into a problem that must have been solved already: we're
>> > trying to insert data into a LONG column, and we're getting "ORA-01704: string
>> > literal too long". According to the book, a string literal can have no more
>> > than 2,000 characters.
>> >

I'm not sure if this is what you're trying to do, but let me describe what I've done for a similar problem. We have a table named long_description, with a description field of varchar2(70). We needed to take multiple records, and concatenate them into a long field. Here's how I did it using PL/SQL. Email me directly if you have any questions about this code.

DECLARE

    	CURSOR c1 is
		SELECT code, rtrim(description, ' ')
		FROM &&old_cassuser..long_description
		WHERE name_of_form = 'ACTIV_CD'
		order by code, sequence;

	num_recs        number;
	v_code	        varchar2(6);
	v_description   LONG;
        v_description_2 LONG;
	v_length	number;
	v_num_code	number;

BEGIN
        select count(code)
	into num_recs
	from &&old_cassuser..long_description
	where name_of_form = 'ACTIV_CD';

   OPEN c1;
    FOR i in 1..num_recs LOOP         

	FETCH c1 INTO v_code, v_description;
	EXIT WHEN c1%NOTFOUND;


        SELECT extended_task_description
        INTO v_description_2
        FROM plt_activity_codes
        WHERE activity_code = v_code;

                IF v_description_2 is NULL THEN
                v_description_2 := v_description;
                else
                v_description_2 := v_description_2 || chr(10) ||

v_description;

                END if;

	UPDATE plt_activity_codes
	SET extended_task_description = v_description_2
	WHERE activity_code = v_code;

        -- There will be (num_recs) update statements issued. Num_recs
represents the number of
        -- records in the long_description table that apply to

activity_codes.

        COMMIT;     END LOOP;
   CLOSE c1;

END;
/

--
Remove the "x" to reply.

Received on Tue Nov 04 1997 - 00:00:00 CST