Oracle developers

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...

VARRAY: Varray sands for variable-size array. Varray has a fixed limit on its size,specified as part of the declaration. Varray index always starts with 1 not 0. A varray is automatically NULL when it is declared and must be  initialized before its elements can be referenced. SYNTAX:       TYPE < type_name > IS  VARRAY  (size_limit) OF  <data_type  > [NOT NULL];        < varray_name >    type_name;  ----declaring variable. examples: 1)  DECLARE         TYPE char_type IS VARRAY (5) OF VARCHAR2 (10);           --calls the Varray constructor         c_list  char_type := char_type ();  BEGIN   --allocates space in the varray       c_list.EXTEND;    c_list (1) := 'SCOTT';     c_list.EXTEND;    c_li...

Collection methods: A collection method is a built-in function or procedure that operates on collections and is called using dot notation. Following are the functions used with collections. EXISTS COUNT LIMIT FIRST AND LAST PRIOR AND NEXT EXTEND TRIM DELETE Syntax: collection_name.method_name [(parameters)] Collection methods cannot be called from SQL statements. Only the EXISTS method can be used on a NULL collection. All other methods applied on a null collection raise the COLLECTION_IS_NULL error. 1.COUNT: COUNT method returns the number of elements with the space allocated in Varrays and Nested tables. The COUNT method displays number of elements in associate arrays. COUNT can be smaller than LIMIT for Varrays. Example: DECLARE --Index by table type    TYPE etab IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;    emptab  etab; --Nested Table Type    TYPE tab_type IS TABLE OF VARCHAR2 (10);    t...

MUTATING_ERROR :                  Either insert or update or delete or select any two operations perform on same table at the same time in trigger,at that time mutating error will be happend. to avoid this mutating error we can use pragma autonomous_transaction. examples of mutating error:          example 1 :             here we are deleting the row and at the same time we are selecting(DRL) the record then it leads to mutating error CREATE OR REPLACE TRIGGER mut_trig AFTER DELETE ON test  FOR EACH ROW DECLARE  a VARCHAR2(10); BEGIN   SELECT name INTO a    FROM test    WHERE id=111;   INSERT INTO test (id,name) VALUES (222,'VOLVO');   DBMS_OUTPUT.PUT_LINE('NAME: '||a); END mut_trig; / Trigger created. sql>delete from test; ERROR at line 1: ORA-04091: table SYSTEM.TEST is mutating, trigger/function may not se...