DELETE existing data before insert

Question

I'd like to delete the existing data before insert based on ids. So if there are ids in incoming file then existing ids should be deleted, and then data should be entered. Something like this:

DATABASE TABLE BEFORE:

--------------------------------
| ID  |  DATA                  |
--------------------------------
| 1   |  This is data for ID 1 |
| 2   |  This is data for ID 2 |
| 3   |  This is data for ID 3 |
--------------------------------

INCOMING FILE:

-----------------------------------------
| ID  |  DATA                           |
-----------------------------------------
| 1   |  This is new data for ID 1      |
| 2   |  This is new data for ID 2      |
| 4   |  This is new data for ID 4      |
| 1   |  This is also new data for ID 1 |
-----------------------------------------

DATABASE TABLE AFTER:

-----------------------------------------
| ID  |  DATA                           |
-----------------------------------------
| 1   |  This is new data for ID 1      |
| 2   |  This is new data for ID 2      |
| 4   |  This is new data for ID 4      |
| 1   |  This is also new data for ID 1 |
| 3   |  This is data for ID 3          |
-----------------------------------------

This is my procedure.

CREATE OR REPLACE PROCEDURE absence_records_in AS
   l_v_file      UTL_FILE.file_type;
   l_filename    VARCHAR2(128);
   l_buffer      VARCHAR2(4096);
   l_start       BINARY_INTEGER := 1;
   l_pos         BINARY_INTEGER;
   TYPE typ_cols IS TABLE OF VARCHAR2(4096) INDEX BY BINARY_INTEGER;
   tbl_cols      typ_cols;
   l_col_no      BINARY_INTEGER;
   l_count       BINARY_INTEGER := 0;
BEGIN
   l_filename := 'records.csv';
   l_v_file := UTL_FILE.fopen('DIR_FENIX_IN', l_filename, 'R');
   UTL_FILE.get_line(l_v_file, l_buffer);
   LOOP
       BEGIN
           UTL_FILE.get_line(l_v_file, l_buffer);
           IF l_buffer IS NULL THEN
               EXIT;
           END IF;
           l_col_no := 1;
           l_start  := 1;
           WHILE INSTR(l_buffer, ';', 1, l_col_no) !=0 
           LOOP
               l_pos := INSTR(l_buffer, ';', 1, l_col_no);
               tbl_cols(l_col_no) := SUBSTR( l_buffer, l_start, l_pos - l_start);
               l_start  := l_pos + 1;
               l_col_no := l_col_no + 1;
           END LOOP;
           l_start := l_pos + 1;
           tbl_cols(l_col_no) := SUBSTR( l_buffer, l_start);

           DELETE FROM absence_records WHERE id = tbl_cols(1)
           LOG ERRORS INTO in_errors('File: '||l_filename||' => delete operation')  REJECT LIMIT UNLIMITED;

           l_count := l_count + 1;
               EXCEPTION WHEN NO_DATA_FOUND THEN
           EXIT;
       END;
       BEGIN
           UTL_FILE.get_line(l_v_file, l_buffer);
           IF l_buffer IS NULL THEN
               EXIT;
           END IF;
           l_col_no := 1;
           l_start  := 1;
           WHILE INSTR(l_buffer, ';', 1, l_col_no) !=0 
           LOOP
               l_pos := INSTR(l_buffer, ';', 1, l_col_no);
               tbl_cols(l_col_no) := SUBSTR( l_buffer, l_start, l_pos - l_start);
               l_start  := l_pos + 1;
               l_col_no := l_col_no + 1;
           END LOOP;
           l_start := l_pos + 1;
           tbl_cols(l_col_no) := SUBSTR( l_buffer, l_start);

           INSERT INTO absence_records (id, data)
           VALUES (tbl_cols(1), tbl_cols(2))
           LOG ERRORS INTO in_errors('File: '||l_filename|| ' => insert operation')  REJECT LIMIT UNLIMITED;

           l_count := l_count + 1;
               EXCEPTION WHEN NO_DATA_FOUND THEN
           EXIT;
       END;
   END LOOP;
   UTL_FILE.fclose(l_v_file);        
END absence_records_in;

So in short, I need to start one loop that will delete only data which is in comparison, and then another loop that will insert the data. Any ideas? Thanks in advance :-)

score 0 · Answer 1 · edited May 23 '17 at 12:18

0

A slightly different twist on the above would be to merge/upsert the data. Have a look here at how to merge data in Oracle: Oracle: how to UPSERT (update or insert into a table?)

edited May 23 '17 at 12:18

Community

1
1

answered Oct 08 '12 at 10:59

ifx

561
2
13

score 0 · Answer 2 · answered Oct 08 '12 at 11:07

0

I would do this in the followng way:

read the data from the file into a new table TEMP
delete all records with an ID that is in TEMP, something like DELETE FROM TABLE WHERE TABLE.ID IN (SELECT ID FROM TEMP)
add all records from TEMP to TABLE

answered Oct 08 '12 at 11:07

Zane

926
8
21

Your stored procedure is mainly concerned with parsing the CSV file. Can't you use something else to read the csv file into a table? Look e.g. here http://stackoverflow.com/questions/6198863/oracle-import-csv-file-using-sqlplus – Zane Oct 08 '12 at 12:54

score 0 · Answer 3 · answered Oct 08 '12 at 11:15

Try the following:

Close the first loop immediately after the END for the first BEGIN that has an exception handler.
Close the file.
Open the file again.
Start another loop immediately before the second BEGIN that has an exception handler.

Note that instead of (2) and (3) you could just do UTL_FILE.fseek(file, 0).

Share and enjoy.

DELETE existing data before insert

3 Answers3