Recording Update through trigger based on csv file upload [message #667532] |
Thu, 04 January 2018 06:26  |
 |
oracbeg
Messages: 16 Registered: July 2017
|
Junior Member |
|
|
Hello Everyone.
Request you to please suggest me some way on this.
I have a csv file which i am uploading to a table using UTL_FILE. This activity runs on daily basis where file name remains same but data in there changes (insertion or update).
I need to record that particular row with required columns which was changed in the csv file.
Created a procedure and used below as condition which works for upload part.
merge into test using dual on (Id = v_Id)
when not matched then insert (Id, name, address)
values (v_Id, v_name, v_address)
when matched then update set
name = v_name, address = v_address;
For recording that update i used a trigger on this table (test)
create or replace TRIGGER test_trg before update on test for EACH ROW
begin
If Updating Then
insert into test_log
(name, address
)
values
(:new.name,
:new.address
);
The trouble i am facing is that instead of recording that single record which is changed in table test using utl_file upload it records whole table values of required columns.
Trigger works fine if i manually edit or update the test table but in case of UTL_FILE upload procedure trigger records whole table data of required columns but i need only the row which i actually changed in csv file and in database table.
[Updated on: Thu, 04 January 2018 06:30] Report message to a moderator
|
|
|
|
|
Re: Recording Update through trigger based on csv file upload [message #667594 is a reply to message #667535] |
Sun, 07 January 2018 22:33  |
 |
oracbeg
Messages: 16 Registered: July 2017
|
Junior Member |
|
|
Thank you so much for your reply . It worked and figured it out. As i mentioned that i am using utl_file to upload a csv file. A unusual issue i am facing is that it is uploading csv data in double quotes. I think it is is due to a column having data with line breaks like RAFFINERIE HEIDE 6.375% 17-01/12/2022. I am using something like this v_Securityname := REGEXP_SUBSTR(v_NewLine, '("[^"]*"|[^,]+)', 2, 3); Any way i could get over these line breaks and including that i need to skip headers while uploading csv file.
[Updated on: Mon, 08 January 2018 00:44] Report message to a moderator
|
|
|