Help.... insert into ....

From: Mridul <mridul_p_at_hotmail.com>
Date: 28 Apr 2002 00:54:41 -0700
Message-ID: <652361f.0204272354.4785c34b@posting.google.com>

I have a table in the foll format..

DOC_NUMBER NUMBER
TEXT_FIELD VARCHAR2(255)
TEXT_VALUE VARCHAR2(255) The text_field & text_value contain information about different attributes for the particular doc_number.

Two of the attributes for text_field are create_date, and 'create_time'.

1, 'create_date', '21/04/2002'
1, 'create_time', '10:14'

I want to concatentate them as a new row in the same table...

1, 'create_date_time', '21/04/2002 10:14'

I have written a procedure for this.. but the procedure gives an error on execution...

create or replace procedure foo as

   type rc is ref cursor;

    chk_cnt        number;
    doc_num        number;
    rowdate        varchar2(255);
    rowtime        varchar2(255);

cursor date_cur is 
      select distinct doc_number from safety_data;
     
begin
	chk_cnt:=1;
         delete from safety_data where text_field='create_date_time';

         for date_val in date_cur
	 loop
         doc_num:=date_val.doc_number;
 	 SELECT COUNT(*)  INTO chk_cnt FROM SAFETY_DATA  WHERE
DOC_NUMBER=DOC_NUM AND TEXT_FIELD='create_date';
         IF(chk_cnt>0) THEN	        
         SELECT TEXT_VALUE INTO rowdate FROM SAFETY_DATA
                     WHERE DOC_NUMBER=DOC_NUM AND

TEXT_FIELD='create_date';

         SELECT TEXT_VALUE INTO rowtime FROM SAFETY_DATA
                    WHERE DOC_NUMBER=DOC_NUM AND
TEXT_FIELD='create_time';
	 INSERT INTO SAFETY_DATA VALUES(DOC_NUM,'create_date_time',

rowdate||' '||rowtime);     

         end if;
         end loop;	
         commit work;

end;
/

The error is that, on the last INSERT, I get a message that the insert size is more than the capacity of the column...

ERROR at line 1:
ORA-01401: inserted value too large for column

Can anyone pls pls pls. help me out with this?

Regards,
Mridul.

PS: Sorry for cross-posting... I have no idea which is the relevant NG for this... Received on Sun Apr 28 2002 - 02:54:41 CDT