I am new to oracle and I am learning cursors.My table City has two columns city_id,city_name.So,this is what I tried:
DECLARE
CURSOR city_list is
SELECT * from OT.City;
v_list SYS_REFCURSOR;
BEGIN
OPEN city_list FOR
v_list := city_list;
DBMS_OUTPUT.PUT_LINE(v_list.city_id);
EXIT WHEN city_list%NOTFOUND;
CLOSE city_list;
END;
/
i am trying to assign the data of cursor to the newly declared value v_list SYS_REFCURSOR;
.But the output is coming as error at v_list := city_list;
.How can I assign all the values of cursors to the another cursor directly or is there any other methods?
You can still use SYS_REFCURSOR
by locating between OPEN
and FOR
clauses, and followed by your cursor, and then
need to add LOOP FETCH INTO... END LOOP
template and Close the opened cursor after you current code :
DECLARE
v_list SYS_REFCURSOR;
v_city_id city.city_id%TYPE;
v_city_name city.city_name%TYPE;
BEGIN
OPEN v_list FOR
SELECT city_id, city_name FROM City;
LOOP
FETCH v_list
INTO v_city_id, v_city_name;
EXIT WHEN v_list%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_city_id || ' - ' || v_city_name);
END LOOP;
CLOSE v_list;
END;
/