Cursor Example on ATS database
declare
v_object_id dic_objects.object_id%TYPE ;
v_object_name dic_objects.object_name%TYPE := 'ACC_REP040_E' ;
v_title_a DIC_OBJ_titles.TITLE%TYPE ;
v_title_e DIC_OBJ_titles.TITLE%TYPE ;
v_tab_name dic_obj_tabs.TAB_NAME%TYPE ;
v_tab_name2 dic_obj_tabs.TAB_NAME%TYPE ;
XXX NUMBER;
type array_one is table of dic_obj_tabs.TAB_NAME%type;
tbl_num array_one;
cursor obj_tabs(v_obj_no number) is select TAB_NAME from dic_obj_tabs where object_id = v_obj_no;
cursor obj_tabs2(v_obj_no number) is select TAB_NAME from dic_obj_tabs where object_id = v_obj_no;
begin
begin
select object_id
into v_object_id
from dic_objects
where object_name = v_object_name;
dbms_output.put_line('Object id = '|| v_object_id);
exception when no_data_found then
dbms_output.put_line('Object id is not exisits');
end;
v_object_id := 130;
begin
select title
into v_title_a
from DIC_OBJ_titles
where OBJECT_ID = v_object_id
and LANG_ID = 1;
dbms_output.put_line('Arabic title = '|| v_title_a);
exception when no_data_found then
dbms_output.put_line('Arabic title is not exisit');
end;
begin
select title
into v_title_e
from DIC_OBJ_titles
where OBJECT_ID = v_object_id
and LANG_ID = 2;
dbms_output.put_line('English title = '|| v_title_e);
exception when no_data_found then
dbms_output.put_line('English title is not exisit');
end;
begin
--OPEN obj_tabs(v_object_id);
--FETCH obj_tabs INTO XXX;
FOR i IN obj_tabs(v_object_id) loop
select TAB_NAME
into v_tab_name
from DIC_OBJ_tabs --(select rownum,TAB_NAME from DIC_OBJ_tabs where OBJECT_ID = v_object_id)
where TAB_NAME = i.TAB_NAME
and object_id = v_object_id;
EXIT WHEN obj_tabs%NOTFOUND;
dbms_output.put_line('Tab name is ' || v_tab_name );
END LOOP;
--CLOSE obj_tabs;
exception when no_data_found then
dbms_output.put_line('There is no Tab');
end;
begin
-- select TAB_NAME
--into tbl_num
-- from dic_obj_tabs where object_id = v_object_id;
--tbl_num := array_one(select TAB_NAME into from dic_obj_tabs where object_id = v_obj_no);
null;
exception when no_data_found then
dbms_output.put_line('There is no Tab');
end;
begin
open obj_tabs2(v_object_id);
loop
fetch obj_tabs2 into v_tab_name2;
exit when obj_tabs2%notfound;
dbms_output.put_line('fINALLY ' || v_tab_name2);
end loop;
close obj_tabs2;
exception when no_data_found then
dbms_output.put_line('There is no Tab');
end;
end;