Oracle developers

CURSOR WITH  PARAMETERS: A cursor can be declare with parameters. Note: cursor parameters can be assigned default values. the scope of the cursor  parameters is local to the cursor the mode of the  parameters can only be IN. Example: Declare   Cursor c1 (p_deptno  emp.deptno%type) is   select ename,sal,deptno   from emp   where deptno=p_deptno; begin     for i in c1 (10) loop     dbms_output.put_line(i.ename||' '||i.sal||'  '||i.deptno);     end loop;         for i in c1(20) loop         dbms_output.put_line(i.ename||' '||i.sal||'  '||i.deptno);         end loop;     dbms_output.put_line('hai'); end; /

PRAGMA SERIALLY_REUSABLE : We want to maintain state of the global variable we are using pragma serially_reusable in packages. ------------with out serially reusable pragma in packages SQL> create or replace package pkg3            is                   g number(3):=27;            end pkg3;           / Package created. SQL> begin                pkg3.g :=30;           end;          / PL/SQL procedure successfully completed. SQL> begin                  dbms_output.put_line(pkg3.g);           end;          / 30 PL/SQL procedure successfully completed. -------------------------------------------- serially reusable in packages SQL> create...

WHAT IS THE DEFERENCE BETWEEN DELETE AND TRUNCATE ?

PACKAGE:                         A package is named collection of variables,cursors,types,procedures and functions. package does not accepts parameters. can not be invoked. can not be nested. package having two parts                           package specification                           package body package specification: we are defining global data and also declare subprograms package body: here we are implementing subprograms syntax: package specification create or replace package ( package_name ) is global_variable declaration; cursor  declaration; procedure  declaration; function  declaration; types  declaration; end (package_name); / package body: create or replace package body (package_name) is procedure implementation; function implemen...

BULK COLLECT WITH LIMIT Clause: in example we take emp table,in emp table have total 14 records declare   cursor c1 is select * from emp;   type tt is table of c1%rowtype index by binary_integer;   t tt;   cnt number :=0; begin      open c1;      loop           fetch c1 bulk collect into t limit 5;           exit when c1%notfound;              for i in 1.. t.count               loop                  cnt :=cnt+1;                  dbms_output.put_line(cnt ||'    '||t(i).ename);              end loop;       end loop;  close c1; end; / 1    SMITH 2    ALLEN 3    WARD 4    JONES 5    MARTIN ...

TRIGGERS:                        A Set of commands that are triggered to execute when an event occurs in the data base.  triggers stored in USER_TRIGGERS system table. they are used to impose business rules or user defined restrictions on table columns. syntax:             CREATE [ OR REPLACE ] TRIGGER  Trigger_name             { BEFORE / AFTER /  INSTEAD OF  }               {  INSERT [ OR ] UPDATE [ OR ] DELETE }              [ OF <column_name> ] ON <table_name>              [REFERENCING :OLD AS o :NEW AS n ]              [ FOR EACH ROW ]              [ FOLLOWS another_trigger ]              [ ENABLE / DISABLE...