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

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Sun, 28 Apr 2002 10:41:40 +0200
Message-ID: <ucndfobeiprb88@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

The correct newsgroup for this is comp.databases.oracle.server

Hth

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address

Received on Sun Apr 28 2002 - 03:41:40 CDT