Technical Thoughts

Bulk collect is used to load a bulk of data from a table.BULK COLLECT is usually using alsong with INTO clause.We want to load whole data of a particular column according to a condition we can use BULK COLLECT . We have to declare array or a table type or a row type of variable to store datas . DECLARE    type akhil_tp is table of   employee.ename%type;   lv_akhil_tp   akhil_tp;  begin     select ename BULK COLLECT INTO lv_akhil_tp from employee; for i in 1..lv_akhil_tp.count() loop dbms_output.put_line(lv_akhil_tp(i)); end loop; end;    

into clause in oracle is used to put values or datas into a variable. eg:  Select empname into ename from employee where emp_id=10;  If we declare ename as varchar we can put the value of empname into ename. Usually into clause is used with select statement. INTO clause is used to , do the operations with use of that particular value. In the above case we can use ename for further operations. DECLARE   cnt number ; BEGIN   select count(1) into cnt from emp; dbms _ output . put _ line (cnt); end ;

                                        Plsql types are used to store different type of datas as a single unit. It means a plsql type contains elements that are of different type eg: Number,Varchar2,table type,row type etc..                     If we want to insert some datas into a table we can use a simple insert querry.Think about a situation like,we are inserting datas according to different conditions  of data that exist in another table.In this situations we can use TYPES to store data from any point of the code into types and at the end just insert into that type to target table. The creation part comes under DECLARE section. We have to create an object for the type at the same time we c...

In oracle 10 g we can do this by SELECT length ( 'akhil' )           - length ( REGEXP_REPLACE ( 'akhil' , '[a,e,i,o,u,A,E,I,O,U]' , '' )) FROM DUAL ; In oracle 11g we can simplify this code to SELECT REGEXP_COUNT ( 'akhil' , '[a,e,i,o,u,A,E,I,O,U]' ) from dual We can do this in any of the versions like 9i,10g,11g etc.. by Select length (' akhil' )- length ( translate ('akhil','zaeiouAEIOU',z) ) from dual The operation of the abouve statement is like below        It will translate the each existance of 'z ' with z,because of there is nothing to be specified to replace for a,e,i,o,u,A,E,I,O,U . It will find the count of that. Reduce the count of this from the total length of the string . That is length of string -length of non vowel elements.