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;
Posts
- Get link
- X
- Other Apps
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;
- Get link
- X
- Other Apps
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;
- Get link
- X
- Other Apps
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;
- Get link
- X
- Other Apps
------ 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;
- Get link
- X
- Other Apps
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
- Get link
- X
- Other Apps
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...