CODEWITHSUNDEEP
sqloracle-databaseplsql
NathanC
NathanC

Reputation: 51

Is there a reason this procedure is not updating my Table?

The below code is supposed to do the following: 1: Read a txt file 2: Print it to a CSV file; 3: update my "CATEGORY2" Table in my database

This is not updating the Table how i want it to. Is this the correct way to do this? Is an Update Statement the correct thing to use in this procedure dose it need to be an INSERT INTO.

DROP Sequence Seed_ID;
Create Sequence Seed_ID INCREMENT BY 1 Start with 1;

DECLARE 
  fw UTL_FILE.FILE_TYPE; 
  fr UTL_FILE.FILE_TYPE;
  textOut Varchar2(2000); ----Store All txt file
  Seed_word Varchar2(2000); -----Seed words store
  i number; -----Incramenter for Category ID
  match_var VARCHAR(255); ---Use to mathc the category with seed words
  mvi number; -- match var incramenter
BEGIN
  fr := UTL_FILE.FOPEN('FILE_READ_WRITE', 'seed.txt', 'r');
  fw := UTL_FILE.FOPEN('FILE_READ_WRITE', 'seed2.csv', 'w');
  LOOP
    BEGIN
      i :=1;
      mvi := 1;
      UTL_FILE.GET_LINE(fr,textOut);
      match_var := REGEXP_SUBSTR (textOut, '[^,]+' ,1,1); --- gets first word in each line   
      UPDATE Category2  ---Should this insert the values int the Category tablE??????????
      SET Category_ID= mvi, Category_name= textout
      WHERE mvi = CATEGORY_ID;
      mvi :=  mvi+1;  
      WHILE TRUE LOOP
        BEGIN
          Seed_word := REGEXP_SUBSTR (textOut, '[^,]+' ,1,i+1);
          i := i+1;
          EXIT WHEN seed_word IS NULL;
          UTL_FILE.PUT_LINE(fw,Seed_ID.nextval ||','||Seed_word||','||mvi);
        END;
      END LOOP;
    END;
  END LOOP;    
EXCEPTION  
  WHEN OTHERS THEN
    dbms_output.put_line(SQLERRM);
    UTL_FILE.FCLOSE(fr);
    UTL_FILE.FCLOSE(fw);
END;

I am currently not getting any errors but it is not updating my table at all. This reading the TXT file and writing it to the CSV file, its just not updating the tablle want it to. with the information.

Upvotes: 0

Views: 78

Answers (2)

Belayer
Belayer

Reputation: 14886

Perhaps your answer is built into the script already:

UPDATE Category2  ---Should this insert the values int the Category tablE??????????

This implies you are expecting rows that do not exist to be inserted but they are not. Well the answer to that question is NO. As indicated an update that effects 0 rows is not considered an error, but neither is it an indication that a row should be created. If that is indeed the functionally you are looking for then you need the Merge statement instead of update. 

Merge into Category2 c2
  using (select mvi, textout from dual) m
     on (m.mvi = c2.category_id)
  when matched then
          set c2.category_name = m.testout
  when not matched then 
       insert (category_id, category_name)
       values (m.mvi, m.textout);

With that you will get your Insert if the requested category_id does not exits. However,I agree with @JeffreyKemp about the assumed logic, it does seem a little based on questionable assumptions. also for a complete answer you need to provide test data, as formatted text, and the expected output for that data.

Upvotes: 0

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60272

An UPDATE statement that finds zero records matching the criteria will not be treated as a failure, so PL/SQL will continue without error even if no records were updated.

  UPDATE Category2  ---Should this insert the values int the Category tablE??????????
  SET Category_ID= mvi, Category_name= textout
  WHERE mvi = CATEGORY_ID;

Your code appears to initialise mvi to 1 and then increment on each loop execution. This logic seems to assume that category_id starts from 1 for the first line in the file up to the maximum ID corresponding to the last line in the file. If that's indeed the case then that's fine; but I would check to see what records are in the category2 table and check whether the category_id values are indeed what you expect.

As Rustam pointed out, your UPDATE also needlessly updates category_id=mvi which doesn't do anything useful (since the where clause already determined that these are already identical).

Upvotes: 3

Related Questions