How to assign value to oracle cursor while iterating?
1 answer
by
dalton_moen ,
2 years ago
@lew
You cannot assign a value directly to an Oracle cursor while iterating through it. Cursors in Oracle are read-only and cannot be updated or modified while iterating through them.
If you need to modify the data being retrieved by a cursor, you will need to fetch the data into a variable or array and then modify the values in the variable or array.
For example, you can use a loop to fetch each row from the cursor into a variable and then update the values in the variable:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
DECLARE
CURSOR c_emp IS
SELECT employee_id, first_name, last_name
FROM employees;
v_employee_id employees.employee_id%TYPE;
v_first_name employees.first_name%TYPE;
v_last_name employees.last_name%TYPE;
BEGIN
OPEN c_emp;
LOOP
FETCH c_emp INTO v_employee_id, v_first_name, v_last_name;
EXIT WHEN c_emp%NOTFOUND;
-- Modify the values in the variables here
-- For example, update the last_name to 'Doe'
v_last_name := 'Doe';
-- Display the modified data
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_employee_id || ', First Name: ' || v_first_name || ', Last Name: ' || v_last_name);
END LOOP;
CLOSE c_emp;
END;
/
|
In the above example, we fetch each row from the c_emp cursor into the variables v_employee_id, v_first_name, and v_last_name, and then update the v_last_name variable to 'Doe'. This modified data is then displayed using DBMS_OUTPUT.
Related Threads:
How to get value of model when iterating over collection in knockout.js?
How to assign undefined value in a knockout.js variable?
How to properly assign null value from bash script to mysql?
How to call cursor from a stored procedure in oracle?
How to run a procedure having cursor output in oracle?
How to assign groups based on sum of values in oracle?