Re: Help.... insert into ....

From: Ken Denny <ken_at_kendenny.com>
Date: Tue, 30 Apr 2002 23:05:44 GMT
Message-ID: <Xns9200C2B1681F2kendenny_at_65.82.44.7>

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in news:ucndfobeiprb88_at_corp.supernews.com:

> 
> "Mridul" <mridul_p_at_hotmail.com> wrote in message
> news:652361f.0204272354.4785c34b_at_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...

> 
> How about this (IMO you should always try to learn SQL before resorting
> to PL/SQL, quite often there is no need for it)
> 
> delete from safety_data
> where text_field='create_date_time';
> 
> insert into safety_data
> select a.document_id
>        , 'create_date_time'
>        ,  to_date(a.text_value||' '||b.text_value,'dd-mm-yyyy hh24:mi')
>        -- 
> your error
> from safety_data a
>       , safety_data b
> where a.document_id = b.document_id;
> 
> delete from safety_data
> where text_field='create_date' or text_field='create_time';
> 
> commit;
> 
> --Ready

Not quite. You need to add to your where clause:

AND a.text_field = 'create_date' AND b.text_field = 'create_time'

But since we suspect there is a problem with the text_values having spaces on the end which was causing the original problem, and we're inserting the date and time into a varchar2 field, how about:

INSERT INTO safety_data
(SELECT a.document_id,

        'create_date_time',
        rtrim(a.text_value)||' '||rtrim(b.text_value)

    FROM safety_data a, safety_data b
    WHERE a.document_id = b.document_id
    AND a.text_field = 'create_date'
    AND b.text_field = 'create_time');

-- 
Ken Denny (PL/SQL guru in search of employment)
http://www.kendenny.com/

If it ain't broke, let me have a crack at it. 

Received on Wed May 01 2002 - 01:05:44 CEST