Oracle 8i Features

" Returning into " clause:
 Used to return the values thru " DML" stmts.
 Used with update and delete stmts. Ex:
 >var a varchar2(20)
 >var b number
 > update emp set sal = sal + 3000
  where empno = 7900 returning ename,sal into   :a,:b;
 >print a b

 >delete from emp where empno = 7902
  returning ename,sal into :a,:b;
 >print a b
---------------------------------------------------------------
 

 * Bulk Collect: Used to return bulk data into pl/sql variables.
 Variables must be of pl/sql Table type only.
 Improves performance while retrieving data.
 Used with select, update, delete, Fetch stmts.

 select ename,sal into a,b from emp
 where empno = &ecode;
 enter value for ecode : 101

 declare
 type names is table of emp.ename%type index by    binary_integer;
 type pays is table of emp.sal%type index by    binary_integer;
 n names;  p pays;
 begin
 -- retrieving all employees in 1 transaction
 select ename,sal bulk collect into n,p from emp;
 -- printing table contents
 dbms_output.put_line('EMPLOY DETAILS ARE :');
 for i in 1 .. n.count loop
 dbms_output.put_line(n(i)||'   '||p(i));
 end loop;
 end;

 * update emp set sal = sal + 3000
    where deptno = 30
    returning ename,sal bulk collect into n,p;

 * delete from emp where job = 'CLERK'
    returning ename,sal bulk collect into n,p;
---------------------------------------------------------------
 

 Using in Fetch stmt : declare
 type names is table of emp.ename%type index by    binary_integer;
 type pays is table of emp.sal%type index by    binary_integer;
 n names;  p pays;
 cursor c1 is select ename,sal from emp;
 begin
 open c1;
 fetch c1 bulk collect into n,p;
 -- printing table contents
 for i in 1 .. n.count loop
 dbms_output.put_line(n(i)||'  '||p(i));
 end loop;
 end;
---------------------------------------------------------------
 

 Dynamic SQL: Supports to execute " DDL" stmts in Pl/sql block.
 syntax:  execute immediate(' DDL stmt ');

 >begin
  execute immediate(' create table employ1
  (ecode number(4), ename varchar2(20), sal           number(10)) ');
   end;

 Note: Table cannot be manipulated in same pl/sql            block

  begin
  execute immediate(' drop table employ1 ');
  end;
---------------------------------------------------------------
 

 Using Long and Raw Data types: ( 7.x )
 Long : Used to represent numbers or character values.
 Max limit is 2 Gb.
 But only once it can be used in Table.

 Raw(n): used to represent Images
 Max limit is 256 Bytes(7.x) / 2000 Bytes(8.0)

 Long Raw :   used to represent Images upto 2 Gb.
 * Raw and Long Raw can be manipulated by Gui         tools only

 >create table emp
   (ecode number(4), ename varchar2(20),
    sal number(12,2), description long,
   deptno number(2));

 > insert into emp values(1001,'RAM',22000,
  '+++++++++++++++++++++++++++',10);

 >create table ephoto
  ( ecode number(4), photo long raw );

 > create table customer
    (cust_id number(4), cname varchar2(20),
    caddr varchar2(50), photo long raw);
---------------------------------------------------------------
 

 Autonomous Transactions: (8.0) Defines the Trigger as an Independent Transaction.
 ( Generally Triggers are Dependent Transactions -   If  DML stmt is success Trigger is success   otherwise Trigger is a failure ).
  Allows " TCL " commands in Triggers.

 create trigger add_incr before insert on incr
 for each row
 declare                                                       
 Pragma Autonomous_Transaction;                          

begin                        
 update emp set sal = sal + :new.amt               
 where empno = :new.empno;                             
 commit;
 end;

 select empno,sal from emp; --- 7900 8000
 insert into incr values(7900,4000);
 select empno,sal from emp; --- 7900 12000
 select empno,amt from incr; --- 7900 4000
 rollback;
 select empno,amt from incr; --- no rows
 select empno,sal from emp; --- 7900 12000

Ex 2:
create or replace trigger del_ent
before delete on emp
for each row
declare
pragma autonomous_transaction;
begin
insert into del_entry values        del_entry
(:old.empno,user,sysdate);        ------------
commit;                   ecode
end;                   uname
                   dor
delete from emp where empno = 7009;
select * from del_entry;
rollback;
---------------------------------------------------------------