Cursor : Infinite loop without an exit command

Question

I am working on nested cursor where i have a scenario like if the cursor output does not return any data i need to insert data to a table and if it exist then i need to insert to another table say a log table.

        DECLARE
            CURSOR emp_details IS
            SELECT
                empname,
                lastname
            FROM
                emp
            WHERE
                empname = 'xxx';
        
            v_is_active    VARCHAR2(50 BYTE);
            v_is_include   VARCHAR2(50 BYTE);
            lv_emp_name    emp.empname%TYPE;
            lv_lastname    emp.lastname%TYPE;
        BEGIN
            OPEN emp_details;
            
            LOOP
                FETCH emp_details INTO
                    lv_emp_name,
                    lv_lastname;
                **IF emp_details%notfound** THEN
                    IF v_is_active = 'A' THEN

Do some activity like insert into a table T1

                    END IF;
        
                    IF v_is_include = 'I' THEN

Do some activity like insert into a table T2

                    END IF;
        
                ELSE
                
                    INSERT INTO emp (
                        empname,
                        isnew
                    ) VALUES (
                        lv_emp_name,
                        0
                    );
        
                
                
                END IF;
            -----exit : if i add here this will terminate the loop and will execute only once ie the loop will not go for next iteration. If i didnt use exit it is going on infinite loop.
        
        -- IF IF emp_details%FOUND - IF CURSOR HAS A RCORD 
            END LOOP;
        
            CLOSE emp_details;
        END;

Using Notfound method i am checking if value existing and inserting the data. here my problem is i am not abl to exit the method. If i exist the method using EXIT command it just run for only one row and will not execute the cursors second row.How to exit from cursor without going on infinite loop

Don't you just want the exit inside the if, before the else? Your logic seems strange though, normally you would just exit when c%notfound, not do any work. Are you maybe confused about what found/notfound means? Those refer to the last fetch, not whether the cursor found any rows at all. You can track that with a variable if that is what you want, but it's not clear. — Alex Poole
– Alex Poole, Commented May 2, 2023 at 18:46
i dont want to exit when not found, i want to make an entry when it is not found ... can you tell me this part "not whether the cursor found any rows at all. You can track that with a variable if that is what you want" — coder11 b
– coder11 b, Commented May 2, 2023 at 18:56
If your cursor query returns 10 rows then for the first 10 fetches %found is true. Then after the 11th fetch %notfound is true. Are you trying to do something when the cursor query returns zero rows? And do you need a cursor - you don't seem to use the data, so maybe you could just do a count instead? — Alex Poole
– Alex Poole, Commented May 2, 2023 at 19:04
Are you trying to do something when the cursor query returns zero rows? - yes i am trying to do a insert here.... i use nested cursor.To get the values to insert if notfound ,i am fetching from the outer/previous cursor and making an insertion — coder11 b
– coder11 b, Commented May 2, 2023 at 19:12
Why must you use a cursor? Better just insert select, after which check if sql%rowcount is zero, in which case you do what has to be done in case no rows are found through your select. I'd rather avoid context switching by fetching and inserting row by row. — Bogdan Dincescu
– Bogdan Dincescu, Commented May 3, 2023 at 13:38

Karthik Srinivasan · Accepted Answer · 2023-05-03 13:44:57Z

0

Hope below one helps where cursor not required

BEGIN
  INSERT INTO emp(empname,isnew) 
  SELECT 'xxx',0 FROM DUAL
  WHERE NOT EXISTS (
     SELECT 1 FROM emp WHERE empName = 'xxx'
  );
  IF sql%Rowcount > 0 THEN
     INSERT INTO log_table values(...);
  END IF;
  COMMIT;
END;

answered May 3, 2023 at 13:44

Karthik Srinivasan

413 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Cursor : Infinite loop without an exit command

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related