SQL INTERVIEW QUESTIONS AND ANSWERS

WHAT IS THE DEFERENCE BETWEEN DELETE AND TRUNCATE ?

Comments

Post a Comment

Popular posts from this blog

PRAGMA SERIALLY_REUSABLE

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

COLLECTION METHODS

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

CURSOR PARAMETERS

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; /
Read more