Re: Updating Long Field

Terry Heung-Man Lam wrote:
>
> Hi:
>
> I need to know how I can compute the results of adding two fields in
> oracle e.g. given below.
>
> table : Employee
> emp_address VARCHAR2(2000)
> emp_resume VARCHAR2(2000)
>
> table : temp_emp
> emp_add_resume LONG;
>
> I need to have a trigger on table Employee which concatenates the
> emp_address and emp_resume fields and inserts a record into the
> temp_emp.emp_add_resume field.
>
> Since oracle VARCHAR fields has a limit of 2000 the result of the
> concatenatation will be greater than the limit.
> As far as I know PL/SQL variables cannot exceed 2000 characters,
> I would like to know how this can be done...
>
> Thank you in advance...
>
> -- Terry
>
>
>
>

  I created a table called 'temp_emp' with a single LONG column.   Then created the procedure below :-

        1 procedure test2 IS
        2   v1 varchar2(2000) := 'food';
        3   v2 varchar2(2000) := 'good';
        4   l1 long;
        5 begin
        6   dbms_output.enable(1000000);
        7   for i in 1..499 loop
        8     v1 := v1 || 'food';
        9     v2 := v2 || 'good';
       10   end loop;
       11   dbms_output.put_line('length of v1 is
'||TO_CHAR(length(v1)));
       12   dbms_output.put_line('length of v2 is
'||TO_CHAR(length(v2)));
       13   l1 := v1 || v2;
       14   insert into temp_emp values(l1);
       15   commit;
       16 end;

Finally, performed a select on the table - it seemed to have worked. Received on Tue Jun 24 1997 - 00:00:00 CDT