abdeen-ahmad
12/10/2015 - 10:20 AM

Cursor Example on ATS database

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;