# Database Discussions > Oracle >  Insert into / add

## mrossez

Hi there! I am working on an Oracle 8 DB. We use a CRM Tool at our helpdesk and a special module which includes fields such as name, first name, address,...
The module can look up the DB for existing calls/requests.
My problem is: when a request is found in the DB, the new content is not added, but is replacing the former text. 
Can you help? I am a novice in SQL. The following procedure has been generated by the module, which provides a GUI.
Thanks a lot in advance!
Matthieu
____________________
if (NB_AFFAIRES=1) then
      update AFFAIRES set AFFAIRES.AF_REMARQUE=P_Nachricht,AFFAIRES.T9_CODE=  P_Kontaktart,AFFAIRES.AF_INFO_COMP30=P_Vorgangsnum  mer,AFFAIRES.T20_CODE=P_Medium_Antwort,AFFAIRES.AF  _INFO_COMP25=P_VIN,AFFAIRES.AF_INFO_COMP11=P_EZD,A  FFAIRES.AF_INFO_COMP6=P_Km_Stand,AFFAIRES.AF_INFO_  COMP16=P_NoVorgang,AFFAIRES.T7_CODE=65 where AF_CODE=V_AF_CODE;
else
      select SEQ_AFFAIRES.nextval into V_AF_CODE from dual;
      insert into AFFAIRES (AFFAIRES.AF_REMARQUE,AFFAIRES.T9_CODE,AFFAIRES.AF  _INFO_COMP30,AFFAIRES.T20_CODE,AFFAIRES.AF_INFO_CO  MP25,AFFAIRES.AF_INFO_COMP11,AFFAIRES.AF_INFO_COMP  6,AFFAIRES.AF_INFO_COMP16,AFFAIRES.T7_CODE,AFFAIRE  S.AF_DATE_CREA,AFFAIRES.AF_DATE_CONT,AFFAIRES.REFE  RENCE,AFFAIRES.AF_CODE) values (P_Nachricht,P_Kontaktart,P_Vorgangsnummer,P_Mediu  m_Antwort,P_VIN,P_EZD,P_Km_Stand,P_NoVorgang,65,sy  sdate,sysdate,V_REFERENCE,V_AF_CODE);
end if;

----------


## skhanal

I am not clear about the problem, do you want to insert a new record even if it is found in DB?

----------


## mrossez

Thanks for your answer.

I try to explain it better:

We have a webformular with fields like name, first name, aso... 
The customer can inform us whether he is contacting us for a new problem or an existing problem. All existing problems have an ID-Number.

In case of a new call, all fields will be filled in in our DB.

In case of an existing call, only the new information /free text) given by the customer should be inserted.

At the moment, when a call number is found, the new information written by the customer is filled in the field "remarque". But the problem is: the whole field "remarque" is replaced. All information which were present before this field is updated are deleted.

I hope I made my case clearer. Sorry for my english!  :Smilie: 

Matthieu

----------


## skhanal

I am assuming that P_Nachricht is the new string. If you want to append the next string with the existing one you can use || operator.

update AFFAIRES set AFFAIRES.AF_REMARQUE=AF_REMARQUE || decode(P_Nachricht,null,'',P_Nachricht),
AFFAIRES.T9_CODE=P_Kontaktart,AFFAIRES.AF_INFO_COM  P30=P_Vorgangsnummer,AFFAIRES.T20_CODE=P_Medium_An  twort,AFFAIRES.AF_INFO_COMP25=P_VIN,AFFAIRES.AF_IN  FO_COMP11=P_EZD,AFFAIRES.AF_INFO_COMP6=P_Km_Stand,  AFFAIRES.AF_INFO_COMP16=P_NoVorgang,AFFAIRES.T7_CO  DE=65 where AF_CODE=V_AF_CODE;

----------


## mrossez

Hi again,

I just read your reply, and just wanted to say thank you.

I am going to change the procedure right now.

Hope it'll work !  ;o)

Matth

----------


## mrossez

Hi again skhanal,

I wanted to thank you and inform you that the code is "valid". Everything works fine. Knowledge sharing is great! I have been reading SQL online curricula before finding your community and your forum, but I did not find such code. I assume that '' in P_Nachricht,null,'',P_Nachricht indicates a space between the former text and the latest content... I will try to write ' -- NEW MAIL -- ' and see what happens (I want to "highlight" the newest content). Or do you know how to add a blank line between the 2 contents?!...

Anyway, thanks again!

Matthieu

----------


## skhanal

You can concatenate chr(10) in between two values

oldvalue || chr(10) || newvalue

----------


## mrossez

Hallo Sir!

I had to make changes in our procedure: The field AFFAIRES.AF_REMARQUE could get a too small amount of characters.

First, I have been giving these values:

CREATE OR REPLACE PROCEDURE INSERT_CLIENTS2
(P_Name varchar2,P_Vorname varchar2,P_PLZ varchar2,P_Vorgangsnummer varchar2,P_Nachricht varchar2)
is 
V_REFERENCE CLIENTS.REFERENCE%type;
V_AF_CODE AFFAIRES.AF_CODE%type;
V_HI_CODE HISTORIQUE.HI_CODE%type;
V_AG_CODE AGENDA.AG_CODE%type;
NB_AFFAIRES INTEGER;
NB_CLIENTS INTEGER;
NB_HISTORIQUE INTEGER;
NB_ITEM INTEGER;
Tp_val Date;
rem1 VARCHAR2(2000);
rem2 VARCHAR2(2000);
rem3 VARCHAR2(2000);
rem4 VARCHAR2(2000);
rem5 VARCHAR2(2000);

Begin 
rem1 := SUBSTR(P_Nachricht,1,2000);
rem2 := SUBSTR(P_Nachricht,2001,2000);
rem3 := SUBSTR(P_Nachricht,4001,2000);
rem4 := SUBSTR(P_Nachricht,6001,2000);
rem5 := SUBSTR(P_Nachricht,8001,2000);
________________________

Second, I changed the command /Insert into AFFAIRES (AFFAIRES.AF_REMARQUE,AFFAIRES.T9_Code,...)/ in / (AFFAIRES.AF_REMARQUE,AFFAIRES.AF_REM_SUPP,AFFAIRE  S.AF_REM_SUPP2,AFFAIRES.AF_REM_SUPP3,AFFAIRES.AF_R  EM_SUPP4,AFFAIRES.T9_Code,...)

an replaced the value P_Nachricht by rem1,rem2,rem3,rem4,rem5

________________________

Then I changed the commands 
/if (NB_AFFAIRES=1) then
update AFFAIRES set AFFAIRES.AF_REMARQUE=P_Nachricht,AFFAIRES.T9_CODE=  P_Kontaktart,.../ 

in

/if (NB_AFFAIRES=1) then
      update AFFAIRES set AFFAIRES.AF_REMARQUE=rem1,AFFAIRES.AF_REM_SUPP=rem  2,AFFAIRE.AF_REM_SUPP2=rem3,AFFAIRES.AF_REM_SUPP3=  rem4,AFFAIRES.AF_RE_SUPP4=rem5,AFFAIRES.T9_CODE=P_  Kontaktart,...)/
______________________________

As such, I wrote the following command for the append function:

/if (NB_AFFAIRES=1) then
      update AFFAIRES set AFFAIRES.AF_REMARQUE= rem1,AFFAIRES.AF_REM_SUPP=rem2,AFFAIRES.AF_REM_SUP  P2=rem3,AFFAIRES.AF_REM_SUPP3=rem4,AFFAIRES.AF_REM  _SUPP4=rem5 || decode(rem1,rem2,rem3,rem4,null,'',rem1,rem2,rem3,  rem4),AFFAIRES.T9_CODE=P_Kontaktart,...)/

________________________

The program indicates the procedure is not valid.

Do you have a clue?

Thank you for the previous help, and many thanks if U have time to have a look at my new request...

Matth :Confused:

----------

