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?
Advertisement
Answer
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; /