Trying to help myself

declare   cursor c1 is select level m, level*10 n from dual connect by level < 3; n  number; m number; begin  -- Open explicit  cursor fetch record one by one exit after exhausting close cursor open c1;    loop     fetch c1 into m,n;    dbms_output.put_line('m'||m||  '  n'||n);    exit when c1%notfound; end loop; close c1;   /* No need to explicitly open the cursor  fetch will not work as c2 is rowtype ,so here is an implicit            copy from  cursor initial record pointer to c2 ,,Cursor will be closed automatically once iteration            exhausts all the records , cursor record values can be used via c2 record attributes . */  for c2 in c1 loop   m:= c2.m;  n:= c2.n;    dbms_output.put_line('for m'||m||  '  n'||n); end loop; end;

 declare   n  number; begin     select 1 into n from dual; if sql%found then -- if rows returned dbms_output.put_line('implicit1'||sql%rowcount);  end if; if sql%notfound then --if rows not returned dbms_output.put_line('implicit2'||sql%rowcount);  else  dbms_output.put_line('dump sql%notfound ' );  end if; if sql%isopen then --if cursor is in open state dbms_output.put_line('implicit3'||sql%rowcount);  else  dbms_output.put_line('dump sql%isopen' );  end if; end;

 declare   n  number; begin  -- Implicit cursor is opened , counter i copied the datatype as %rowtype fetch can't be used. for i in  (select level from dual connect by level < 4 ) loop  n :=i.level;   --fetch i into n;-- ORA-06550: line 10, column 9: PLS-00456: item 'I' is not a cursor     dbms_output.put_line('n'||n);    end loop; end;

 declare  temp SYS_REFCURSOR; n  number; begin   -- open sys_refcursor for select statement open  temp for select level from dual connect by level < 4 ; loop -- cursor is in open state , cursor attributes are applicable   fetch temp  into n; -- fetch current record from temp assign to n      dbms_output.put_line('n'||n);    exit when temp%notfound;-- loop through the curesor until all the dataset exhausts end loop;   if  temp%found then      dbms_output.put_line('tempfound'||temp%rowcount);   end if;      if  temp%notfound then    dbms_output.put_line('tempnotfound'||temp%rowcount);   end if;     if  temp%isopen then    dbms_output.put_line('tempisopen'||temp%rowcount);   end if;      close temp;    end;

 ------ SYS RECURSOR set of data returning from other program  CREATE OR REPLACE FUNCTION TEST_REF    RETURN SYS_REFCURSOR   AS   TEMP SYS_REFCURSOR; BEGIN  OPEN TEMP FOR SELECT 1 ID, 'CC' NAME FROM DUAL;  RETURN TEMP; END; / DECLARE  X  SYS_REFCURSOR; ID NUMBER; NAME VARCHAR2(100); BEGIN  X:=TEST_REF; -- FETCH    --  cursor is in open state pointing to the first record loop through all the data set until records exhaust    LOOP     FETCH X INTO  ID,NAME;   EXIT WHEN X%NOTFOUND;   DBMS_OUTPUT.PUT_LINE(ID ||'*******'||NAME);   END LOOP; END; 

select  regexp_replace('ham,src,account,number','[^,]+') rep_exc_comma,-- replace al with null excluding , regexp_replace('aeioAu:123456','[^:A-Z]+' ) rep_exc_colonatoz-- replace al with null excluding  : and A TO Z from dual ;    REP_EXC_COMMA REP_EXC_COLONATOZ ,,, A: / select  level , regexp_substr('ham,src,account,number','[^,]+',1,level), regexp_replace('ham,src,account,number','[^,]+'), length(regexp_replace('ham,src,account,number','[^,]+')) from dual connect by level < length(regexp_replace('ham,src,account,number','[^,]+'))+2;   LEVEL REGEXP_SUBSTR('HAM,SRC,ACCOUNT,NUMBER','[^,]+',1,LEVEL) REGEXP_REPLACE('HAM,SRC,ACCOUNT,NUMBER','[^,]+') LENGTH(REGEXP_REPLACE('HAM,SRC,ACCOUNT,NUMBER','[^,]+')) 1 ham ,,, 3 2 src ,,, 3 3 account ,,, 3 4 number ,,, 3

  DECLARE   -- create record type  TYPE rec IS RECORD (a NUMBER, b NUMBER);   -- create associative array of record type  TYPE test_rec is TABLE OF rec index by binary_integer ;          -- declare collection variable , doesn't need to  get initialized   v_test_rec test_rec;          -- declare cursor to get set of records   CURSOR c1 IS SELECT LEVEL a , LEVEL*10 b FROM DUAL CONNECT BY LEVEL < 5;   i number;  counter number  ;  BEGIN   i := 1; -- initialize counter counter := 1; -- initialize counter -- Open cursor explicitly, fetch all data into collection variable in one go and explicitly close the cursor .  FOR c2 IN c1 LOOP      v_test_rec(i).a :=C2.a  ;      v_test_rec(i).b :=C2.b  ;      i := i+1;  END LOOP;    -- loop through nested table data for di...

/* Record type  based Associative array  using bulk collect */   DECLARE   -- create record type  TYPE rec IS RECORD (a NUMBER, b NUMBER);   -- create associative array of record type  TYPE test_rec is TABLE OF rec index by binary_integer ;          -- declare collection variable , as this will be used for bulk collect it will get initialized  and assigned during fetch operation  v_test_rec test_rec;          -- declare cursor to get set of records   CURSOR c1 IS SELECT LEVEL a , LEVEL*10 b FROM DUAL CONNECT BY LEVEL < 5;   BEGIN   -- Open cursor explicitly, fetch all data into collection variable in one go and explicitly close the cursor .   OPEN C1;     FETCH C1 BULK COLLECT INTO v_test_rec;   CLOSE C1;  -- loop through nested table data for display purpose   FOR counter IN v_test_rec.FIRST..v_test_rec....

/* Record type based Nested Table with bulk collect option*/   DECLARE   -- create record type  TYPE rec IS RECORD (a NUMBER, b NUMBER);   -- create nested table of record type  TYPE test_rec is TABLE OF rec ;          -- declare collection variable , as this will be used for bulk collect it will get initialized  and assigned during fetch operation  v_test_rec test_rec;          -- declare cursor to get set of records   CURSOR c1 IS SELECT LEVEL a , LEVEL*10 b FROM DUAL CONNECT BY LEVEL < 5;   BEGIN   -- Open cursor explicitly, fetch all data into collection variable in one go and explicitly close the cursor .   OPEN C1;     FETCH C1 BULK COLLECT INTO v_test_rec;   CLOSE C1;  -- loop through nested table data for display purpose  FOR counter IN v_test_rec.FIRST..v_test_rec.LAST loop        ...

/* Record type based Nested Table with simple for loop */  DECLARE   -- create record type  TYPE rec IS RECORD (a NUMBER, b NUMBER);   -- create nested table of record type  TYPE test_rec is TABLE OF rec ;         -- Initialize collection to assign values otherwise you will get error collection not initialized  v_test_rec test_rec:= test_rec();          -- declare cursor to get set of records   CURSOR c1 IS SELECT LEVEL a , LEVEL*10 b FROM DUAL CONNECT BY LEVEL < 5;   BEGIN   -- cursor opened , traversed and closed implicitly   FOR c2 IN c1 LOOP          -- assign memory space for value assignment purpose       v_test_rec.EXTEND;        -- assign cursor value to collection field by field      v_test_rec(v_test_rec.LAST).a := c2.a;         -- assign curs...