===========================
Expand|Select|Wrap|Line Numbers
- declare
- cursor c1 is select * from dept;
- type drec is record (a dept.deptno%type,
- b dept.dname%type,
- c dept.loc%type);
- type ttype is table of drec index by binary_integer;
- dr drec;
- tt ttype;
- i binary_integer:=1;
- cnt number;
- begin
- open c1;
- fetch c1 into tt(i);
- while (c1%found=TRUE) loop
- dbms_output.put_line(tt(i).a||' '||tt(i).b||' '||tt(i).c);
- i := i+1;
- fetch c1 into tt(i);
- end loop;
- cnt := c1%rowcount;
- close c1;
- end;
----------------------
Expand|Select|Wrap|Line Numbers
- declare
- type tabtype is table of emp%rowtype index by binary_integer;
- rec tabtype;
- cursor c1 is select * from emp;
- counter number:=1;
- begin
- open c1;
- loop
- fetch c1 into rec(counter);
- exit when c1%notfound;
- counter:=counter+1;
- end loop;
- close c1;
- for i in rec.first..rec.last
- loop
- dbms_output.put_line(rec(i).empno||' '||rec(i).ename||' '||rec(i).sal);
- end loop;
- dbms_output.put_line('total record fetched = ' ||rec.count);
- end;
---------------------------
Expand|Select|Wrap|Line Numbers
- declare
- type my_table_type is table of emp.ename%type
- index by binary_integer;
- my_table my_table_type;
- begin
- for i in 1..5 loop
- select ename
- into my_table(i)
- from emp
- where empno=&empno;
- end loop;
- for i in 1..5 loop
- dbms_output.put_line(my_table(i));
- end loop;
- end ;
--------------------------
Expand|Select|Wrap|Line Numbers
- declare
- type tabtype is table of emp%rowtype index by binary_integer;
- rec tabtype;
- type tabtype1 is table of dept%rowtype index by binary_integer;
- rec1 tabtype1;
- cursor c1(p number) is select * from emp where deptno=p;
- cursor c2 is select * from dept;
- counter number:=1;
- counter1 number:=1;
- begin
- open c2;
- loop
- fetch c2 into rec1(counter1);
- exit when c2%notfound;
- dbms_output.put_line(rec1(counter1).deptno||' '||rec1(counter1).dname||' '||rec1(counter1).loc);
- open c1(rec1(counter1).deptno);
- loop
- fetch c1 into rec(counter);
- exit when c1%notfound;
- dbms_output.put_line(rec(counter).empno||' '||rec(counter).ename||' '||rec(counter).sal);
- counter:=counter+1;
- end loop;
- dbms_output.put_line('total record fetched = ' ||rec.count);
- counter1:=counter1+1;
- close c1;
- end loop;
- close c2;
- end;
---------------------------
Expand|Select|Wrap|Line Numbers
- DECLARE
- TYPE t_type IS TABLE
- OF emp.empno%TYPE INDEX BY BINARY_INTEGER;
- len BINARY_INTEGER := 0;
- tab t_type;
- CURSOR c1 IS SELECT empno FROM emp ORDER BY empno;
- BEGIN
- OPEN c1;
- LOOP
- FETCH c1 INTO tab(len+1);
- EXIT WHEN c1%NOTFOUND;
- len := len + 1;
- EXIT WHEN (c1%ROWCOUNT = 10);
- END LOOP;
- CLOSE c1;
- END;
==========================================
Expand|Select|Wrap|Line Numbers
- DECLARE
- ob_type VARCHAR2(6);
- ob_schema VARCHAR2(32);
- ob_name VARCHAR2(32);
- ob_stat VARCHAR2(32);
- cursor chk_it (ob_name VARCHAR2) IS SELECT status FROM dba_objects WHERE object_name=ob_name;
- BEGIN
- ob_type:='PACKAGE';
- ob_schema:='ACCT_DBA';
- ob_name:= 'CHARGE_FEES';
- IF chk_it%ISOPEN THEN CLOSE chk_it;
- OPEN chk_it(ob_name);
- ELSE
- OPEN chk_it(ob_name);
- END IF;
- FETCH chk_it INTO ob_status;
- IF ob_status = 'INVALID' THEN
- DBMS_DDL.ALTER_COMPILE(ob_type,ob_schema,ob_name);
- END IF;
- END;
- /