ORACLE ADD_MONTHS DATETIME FUNCTION

ORACLE ADD_MONTHS DATETIME FUNCTION

  Oracle Add_months adds number of months to a Date type and returns Date Type.

Syntax:

                   Add_months(Date,[number of months])  returns [new Date object]

Get Next Month:

select add_months(sysdate,1) "next month" from dual;

Get Previous Month:

select add_months(sysdate,-1) "previous month" from dual;

Get Next  Year Same Day:

SQL>select add_months(sysdate,12) "Next Yr Same Day" from dual;

Add 5 yrs to Sysdate.

SQL> select add_months(sysdate,5*12) "After 5 years date is" from dual;

Add  3 quarters to a Date.

SQL> select add_months(sysdate,3*4) "After  3 quarters date is" from dual;

TAGS: get previous month in Oracle, Get Next Month in Oracle, get Next year same day, get n years after. add_months oracle date time.

Oracle Connection String in .NET Framework Oracle Connection String in Web.config

Oracle Connection String in Web.config

Oracle Connection String for Ms  Oracle OLEDB

<add name="ConnectionString" connectionString="Provider=MSDAORA;Data Source=localhost;Password=tiger;User ID=scott" providerName="System.Data.OleDb" />


Oracle Connection String for Instance Specific

<add name="ConnectionString2" connectionString="Data Source=XE;Password=tiger;User ID=scott" providerName="oracle.DataAccess.Client" />

Note:Here XE is the Instance Name.

Pls check your Instance name in ,if oracle is installed at C:\oraclexe\app\oracle\admin



Oracle Connection String for only one instance installed in machine

<add name="ConnectionString1" connectionString="Data Source=localhost;User ID=hr;password=hr"
      providerName="System.Data.OracleClient" />


Tags: Oracle connection String in web.config/app.config,Oracle connection String in asp.net,Oracle connection String in ado.net,Oracle connection String in C#,
Oracle connection String in WPF,Oracle connection String in WCF,System.Data.OracleClient Provider,System.Data.OleDb for oracle.

oracle SUBSCRIPT_OUTSIDE_LIMIT exception handling

ORACLE SUBSCRIPT_OUTSIDE_LIMIT exception handling

  PL/SQL  Subscript_outside_limit  exception occurs when you access an VARRAY with lower index is 0(default is 1) and Upper Index is more than number of elements.



declare
 type varray_type is VARRAY(5) OF Number;
 depts varray_type := varray_type(10,20,30);
begin
dbms_output.put_line(depts(0)); --throws subscript_outside_limit exception
end;


SQL> /
declare
*
ERROR at line 1:
ORA-06532: Subscript outside of limit
ORA-06512: at line 5


Handling  Subscript_outside_error exception

declare
 type varray_type is VARRAY(5) OF Number;
 depts varray_type := varray_type(10,20,30);
begin
dbms_output.put_line(depts(0));
exception
    when subscript_outside_limit then
       dbms_output.put_line(' Index range must start with 1 and should end with max number of elements in an array');
end;



Tags:oracle SUBSCRIPT_OUTSIDE_LIMIT exception handling,Handling  Subscript_outside_error exception,VARRAY in PL/SQL,Creating VARRAY in PL/SQL block,Accessing VARRAY in PL/SQL block,VARRAY STARING INDEX,VARRAY MAX INDEX,
ORA-06532: Subscript outside of limit

ORACLE CURSOR_ALREADY_OPEN EXCEPTION HANDLING

ORACLE CURSOR_ALREADY_OPEN EXCEPTION HANDLING

Cursors are used to handle multiple rows returned by select statement.

Cursors are 2types

                     1.Implicit Cursors

                      2.Explicit Cursors.

Implicit Cursors are automatically opened & closed.  For ex: in For loop Range Variables.

Explicit Cursors Should be opened with Open method and must be closed with close method before re-opening it.

for ex:  First block of code displays employee name and no,

             Second block of code Updates Employee Salary.

declare
cursor c1 is select * from emp;
r emp%rowtype;
begin
open c1;
dbms_output.put_line('display employee''s name and number');
loop
fetch c1 into r;
exit when c1%notfound;
dbms_output.put_line(r.empno||' '||r.ename);
end loop;
dbms_output.put_line('update employee salary');
open c1;
end;

OUTPUT:

update employee salary
declare
*
ERROR at line 1:
ORA-06511: PL/SQL: cursor already open 

Handling CURSOR_ALREADY_OPEN Exception

               close c1;

               open c1;

It won't generate exception.

It can be handled in exception block

declare
cursor c1 is select * from emp;
r emp%rowtype;
begin
open c1;
dbms_output.put_line('display employee''s name and number');
loop
fetch c1 into r;
exit when c1%notfound;
dbms_output.put_line(r.empno||' '||r.ename);
end loop;
dbms_output.put_line('update employee salary');
open c1;

exception

when cursor_already_open then

         dbms_output.put_line('cursor must be closed before reopening it');

end;

Tags:ORACLE CURSOR_ALREADY_OPEN EXCEPTION HANDLING,Handling CURSOR_ALREADY_OPEN Exception in PL/SQL, ORA-06511: PL/SQL: cursor already open,

ORACLE ZERO_DIVIDE EXCEPTION HANDLING

ORACLE ZERO_DIVIDE EXCEPTION HANDLING

           

Zero Divide exception occurs when number divided by zero.

It can be handled with ZERO_DIVIDE or OTHERS in Exception block

declare
a number;
b number;
c number;
begin
a:=10;
b:=0;
c:= a/b;
dbms_output.put_line(a||' '||b||' '||c);
end;

OUTPUT:

SQL> /
declare
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at line 8

Handling ZERO_DIVIDE Exception

Method 1:

declare
a number;
b number;
c number;
begin
a:=10;
b:=0;
c:= a/0;
dbms_output.put_line(a||' '||b||' '||c);
exception
when ZERO_DIVIDE then
dbms_output.put_line('ZERO DIVIDE NOT ALLOWED');

end;
/

Method 2:

 Replace ZERO_DIVIDE exception with

exception

When OTHERS then

  dbms_output.put_line('UNKNOWN ERROR');

Tags: ORACLE ZERO_DIVIDE EXCEPTION HANDLING,

Handling ZERO_DIVIDE Exception in PL/SQL, Handling ZERO_DIVIDE exception with OTHERS, Oracle PL/SQL exceptions.

ORACLE CASE_NOT_FOUND EXCEPTION HANDLING.

ORACLE CASE_NOT_FOUND  EXCEPTION HANDLING.

When Used Case Statement in PL/SQL block, it should have ELSE block/Handle all options your returns. otherwise CASE_NOT_FOUND exception will be displayed.

declare
*
ERROR at line 1:
ORA-06592: CASE not found while executing CASE statement
ORA-06512: at line 9

Ex: Handling Case_not_Found exception in PL/SQL block

declare
cursor c1 is select * from emp;
e emp%rowtype;
begin
open c1;
loop
   fetch c1 into e;
   exit when c1%notfound;
  

 case e.job 
when  'CLERK'  then  dbms_output.put_line(e.empno|| ' '|| e.sal||' '||e.deptno||' '||e.job);
when 'SALESMAN' then dbms_output.put_line(e.empno|| ' '|| e.sal||' '||e.deptno||' '||e.job);
end case;

end loop;

OUTPUT:

 will display 

ERROR at line 1:
ORA-06592: CASE not found while executing CASE statement
ORA-06512: at line 9

Handling Exception with Exception statement

declare
cursor c1 is select * from emp;
e emp%rowtype;
begin
open c1;
loop
   fetch c1 into e;
   exit when c1%notfound;
   case e.job 
when  'CLERK'  then  dbms_output.put_line(e.empno|| ' '|| e.sal||' '||e.deptno||' '||e.job);
when 'SALESMAN' then dbms_output.put_line(e.empno|| ' '|| e.sal||' '||e.deptno||' '||e.job);

end case;
--  dbms_output.put_line(e.empno|| ' '|| e.sal||' '||e.deptno||' '||e.job);
end loop;
exception
when CASE_NOT_FOUND then
         dbms_output.put_line('case not handled,u must handle all options/else case required.');
end;

Handling CASE_NOT_FOUND Exception with ELSE statement

when  'CLERK'  then  dbms_output.put_line(e.empno|| ' '|| e.sal||' '||e.deptno||' '||e.job);
when 'SALESMAN' then dbms_output.put_line(e.empno|| ' '|| e.sal||' '||e.deptno||' '||e.job);
else dbms_output.put_line(e.empno|| ' '|| e.sal||' '||e.deptno||' '||e.job);

Tags: ORACLE CASE_NOT_FOUND  EXCEPTION HANDLING,Handling CASE_NOT_FOUND Exception with ELSE statement,

Handling CASE_NOT_FOUND Exception with Exception statement, PL/SQL Exception handling,
ORA-06592: CASE not found while executing CASE statement

ORACLE TOO_MANY_ROWS EXCEPTION HANDLING

ORACLE TOO_MANY_ROWS EXCEPTION HANDLING

When Select Into statement in PL/SQL block returns more than 1 row. and select into variable holds single value, at that time Too_Many_rows exception will occur.

declare
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 7

for ex:TOO_MANY_ROWS exception handling

declare
cursor c1 is select * from emp;
e emp%rowtype;
begin
open c1;
loop
   select * into e from emp; --this throws too_many_rows exception
   exit when c1%notfound;
       dbms_output.put_line(e.empno|| ' '|| e.sal||' '||e.deptno);
end loop;
exception
when TOO_MANY_ROWS then
         dbms_output.put_line('select into returning more than one row');
end;
/

OUTPUT:

select into returning more than one row

PL/SQL procedure successfully completed.
 

Solution:

    -- in this case replace select into with fetch cursor statement

declare
cursor c1 is select * from emp;
e emp%rowtype;
begin
open c1;
loop
   --select * into e from emp;
   fetch c1 into e;
   exit when c1%notfound;
       dbms_output.put_line(e.empno|| ' '|| e.sal||' '||e.deptno);
end loop;
exception
when TOO_MANY_ROWS then
         dbms_output.put_line('select into returning more than one row');
end;

Tags:ORACLE TOO_MANY_ROWS EXCEPTION HANDLING, Handling too many rows exception in oracle, oracle exception block, oracle cursors.%rowtype.

USING VARRAY IN ORACLE

USING VARRAY IN ORACLE

                            VARRAY is an contiguous memory locations of any type in oracle PL/SQL. It is similar to Arrays in java/C#/javascript/php. Default indexed with integer,starting value is 1.

For ex:  List of Holidays falling under weekend days(sat,sun);

declare

day char(3);
--declare varray of type DATE 

TYPE varray_type IS VARRAY(5) OF DATE;
v2 varray_type;

begin

--initialize sample Dates here.
v2:=varray_type(
to_date('2013-01-12','yyyy-MM-DD'),
to_date('2013-03-05','yyyy-MM-DD'),
to_date('2013-05-13','yyyy-MM-DD'),
to_date('2013-06-12','yyyy-MM-DD'),
to_date('2013-12-25','yyyy-MM-DD')
); -- Up to 5 DATES
--VARRAY FIRST METHOD VARRAY LAST METHOD

for j in V2.FIRST..V2.COUNT
loop
day := to_char(v2(j),'DY');
dbms_output.put_line(v2(j)||'    '||day);

--Check day is Saturday or Sunday using IF statement

if(day='SAT' or day='SUN') then
   dbms_output.put_line(v2(j)||'  is  Weekend '||day);
end if;     

end loop;
exception
--Any exception will be handled here with complete error msg 

when others then
       dbms_output.put_line('exception occured'||sqlerrm);
end;

OUTPUT:

SQL> /
12-JAN-13    SAT
12-JAN-13  is  Weekend SAT
05-MAR-13    TUE
13-MAY-13    MON
12-JUN-13    WED
25-DEC-13    WED

PL/SQL procedure successfully completed.

Tags:USING VARRAY IN ORACLE,oracle pl/sql VARRAY,Arrays in PL/SQL,PL/SQL IF STATEMENT, PL/SQL VARRAY OF DATE,oracle VARRAY FIRST element, oracle VARRAY LAST ELEMENT, oracle VARRAY COUNT,oracle VARRAY LIMIT.

ORACLE IF Statement

ORACLE IF Statement

               If statement is used to conditional execution of particular block of code.

IF THEN Synatx:

                 if condition then

                     block of statement;

                end if;

Ex1: Check number is Even or not;

declare
 n number;
begin
n:=10;
if (mod(n,2)=0) then
          dbms_output.put_line('Even Number');
end if;
end;

OUTPUT

Even Number

PL/SQL procedure successfully completed.

Ex2: Update employees commission who is working as Clerk and earning salary more than 1000. and comm is null.

declare
 cursor c1 is select * from emp where job='CLERK';
 begin
 for e in c1
 loop
 if e.sal >1000 and e.comm is null then
 update emp set comm = 500.50 where empno=e.empno;
 end if;
 end loop;
 commit;
 end;

OUTPUT: select * from emp where job='CLERK';

    EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
--------- ---------- --------- ---------- --------- ---------- ----------
     7369 SMITH      CLERK           7902 17-DEC-80        800
     7876 ADAMS      CLERK           7788 23-MAY-87       1100      500.5
     7900 JAMES      CLERK           7698 03-DEC-81        950
     7934 MILLER     CLERK           7782 23-JAN-82       1300      500.5

Ex: Display EVEN Number of Dates having Saturday.

declare
  lastday  number(2);
  day char(3);
begin
select to_number(to_char(last_day(sysdate),'DD'),'99') into lastday from dual;
dbms_output.put_line(lastday);
for i in 1..lastday
loop
select to_char(to_date(i,'DD'),'DY') into day from dual;
if mod(i,2)=0  and day='SAT' then
dbms_output.put_line('Even Number Date:'||' '||i||'Day is'||day);
end if;
end loop;
exception
when others then
       dbms_output.put_line('exception occurred');
end;
/

OUTPUT:

SQL> /
31
Even Number Date: 2Day isSAT
Even Number Date: 16Day isSAT
Even Number Date: 30Day isSAT

PL/SQL procedure successfully completed.

Note:for odd number of Saturdays  

if condition would be

if mod(i,2)<>0  and day='SAT' then

Tags:ORACLE IF Statement,ORACLE IF THEN STATEMENT, using oracle IF Statement in PL/SQL,using IF in PL/SQL, Checking condition using IF statement.

ORACLE WHILE LOOP STATEMENT

ORACLE WHILE LOOP STATEMENT.

           Oracle while loop used for  conditional execution of block of code.

Syntax:

                  while condition

                  loop

                       block of statements

                   end loop;

ex1: Display 10 numbers using while loop

declare
n number;
begin
dbms_output.put_line('WHILE loop in PL/SQL');
n:=1;
while n <=10
loop
dbms_output.put_line(n);
n:=n+1;
end loop;
end;

Output
SQL> /
WHILE loop in PL/SQL
1
2
3
4
5
6
7
8
9
10

PL/SQL procedure successfully completed.

Ex 2: Display 10 numbers in reverse order

declare
n number;
begin
dbms_output.put_line('WHILE loop in PL/SQL');
n:=10;
while n >0
loop
dbms_output.put_line(n);
n:=n-1;
end loop;
end;
/
 

OUTPUT

SQL> /
WHILE loop in PL/SQL
10
9
8
7
6
5
4
3
2
1

 Ex 3: Display real numbers using while loop

declare
n number;
begin
dbms_output.put_line('WHILE loop in PL/SQL');
n:=10.10;
while n >0
loop
dbms_output.put_line(n);
n:=n-0.5;
end loop;
end;
/

OUTPUT
SQL> /
WHILE loop in PL/SQL
10.1
9.6
9.1
8.6
8.1
7.6
7.1
6.6
6.1
5.6
5.1
4.6
4.1
3.6
3.1
2.6
2.1
1.6
1.1
.6
.1

PL/SQL procedure successfully completed.

Ex 4: Display emp table record using While loop & cursors.

declare
cursor c1 is select * from emp;
e emp%rowtype;
begin
dbms_output.put_line('WHILE loop in PL/SQL');
open c1;
fetch c1 into e;
while c1%found
loop
fetch c1 into e;
dbms_output.put_line(e.empno||' '||e.sal||' '||e.deptno);
end loop;
close c1;
end;
/
 

OUTPUT: 

SQL> /
WHILE loop in PL/SQL
7369 800 20
7499 1600 30
7521 1250 30
7566 2975 20
7654 1250 30
7698 2850 30
7782 2450 10
7788 3000 20
7839 5000 10
7844 1500 30
7876 1100 20
7900 950 30
7902 3000 20
7934 1300 10
7934 1300 10

PL/SQL procedure successfully completed.

 Tags:ORACLE WHILE LOOP STATEMENT,using oracle while loop, while loop syntax,oracle while loop,oracle while loop and cursors.display data using oracle while loop.

ORACLE FOR LOOP STATEMENT

ORACLE FOR LOOP STATEMENT

               Oracle for loop used to iterate n number of times.this number is fixed.

syntax:

               for  variable in  starting_value..final_value

               loop

                          block of statements

               end loop;

for ex:  Display first 10 numbers using LOOP Statement.

begin
for i in 1..10
loop
dbms_output.put_line(i);
end loop;
end; 

In this case i is local variable automatically created.

initial Value is 1 final value is 10,  auto increment by 1.

output:

SQL> /
Using for loop statement in PL/SQL
1
2
3
4
5
6
7
8
9
10

PL/SQL procedure successfully completed.

for ex:  Display first 10 numbers using FOR LOOP REVERSE Statement.

begin
dbms_output.put_line('Using for loop statement in PL/SQL');
for i in reverse 1..10
loop
dbms_output.put_line(i);
end loop;
end;

OUTPUT:
SQL> /
Using for loop statement in PL/SQL
10
9
8
7
6
5
4
3
2
1

PL/SQL procedure successfully completed.

Note: i value cannot be changed inside for loop.

Implicit Cursors using for loop

Implicit cursors are automatically opened and closed by 'for loop statement.'

declare

cursor c1 is select * from emp;

begin

dbms_output.put_line('Implicit cursors using for loop');

 for i in reverse c1

loop

dbms_output.put_line(i.empno||' '||i.ename||' '||i.sal||' '||i.deptno);

end loop;

end;

OUTPUT:

SQL> /
Implicit cursors using for loop
9003 peter 1200 10
7369 SMITH 800 20
7499 ALLEN 1600 30
7521 WARD 1250 30
7566 JONES 2975 20
7654 MARTIN 1250 30
7698 BLAKE 2850 30
7782 CLARK 2450 10
7788 SCOTT 3000 20
7839 KING 5000 10
7844 TURNER 1500 30
7876 ADAMS 1100 20
7900 JAMES 950 30
7902 FORD 3000 20
7934 MILLER 1300 10

PL/SQL procedure successfully completed.

 

Tags: ORACLE FOR LOOP STATEMENT,ORACLE FOR LOOP STATEMENT syntax,ORACLE FOR LOOP STATEMENT reverse, implicit cursors using oracle for loop.

 

Oracle basic loop statement

Oracle basic loop statement

                   Oracle 11g basic loop statement, it loops forever/until buffer overflow.


Synatx:
                       loop
                                block of statments
                       end loop;


for ex:  Display first 10 numbers using LOOP Statement.
                 declare
                     n number;
                 begin
                       n:=1;
                      dbms_output.put_line('Using Loop Stmt in PL/SQL Block');
                       loop
                            exit when n>10;
                            dbms_output.put_line(n);
                            n:=n+1;
                        end loop;
                    end;

Output:

SQL> /
Using Loop Stmt in PL/SQL Block
1
2
3
4
5
6
7
8
9
10

PL/SQL procedure successfully completed.


for ex2:   Looping SQL Records 
                The following example loops all records in the emp table, reads empno and his salary into cursor as temorary buffer, 
l_eno,l_sal are PL/SQL variables to hold data.

Note: there should be some condition to exit the loop.  in this case when all records read(i.e no more records found in the table ) then exit the loop
 
  declare
                    l_eno emp.empno%type;
                    l_sal emp.sal%type;
                   cursor c1 is select empno,sal from emp;
                 begin
      open c1;
                      dbms_output.put_line('Using cursors in Loop Statment');
                      dbms_output.put_line('EmpNo  Salary');
                     loop
                        fetch c1 into l_eno,l_sal;
         exit when c1%notfound;
                        dbms_output.put_line(l_eno||' '||l_sal);
                        end loop;
                        close c1;

                    end;




OUTPUT
 
SQL> /
Using cursors in Loop Statmenet
EmpNo  Salary
9003 1200
7369 800
7499 1600
7521 1250
7566 2975
7654 1250
7698 2850
7782 2450
7788 3000
7839 5000
7844 1500
7876 1100
7900 950
7902 3000
7934 1300

PL/SQL procedure successfully completed.

Tags: Oracle basic loop statement,Oracle loop statement, loop statement in oracle 11g,Exit loop in oracle, looping in oracle,exit when in loop statement, Open cursor in PL/SQL.

Using Sequence in Oracle 11g

Using Sequence in Oracle 

              Sequence is an Auto increment value. Unlike MySQL/SQL-Server Sequence is an Independent Object(i.e not associated with any table) .Mainly used in PL/SQL or Used as a Primary Key value .

Simple Syntax:   Create Sequence  Sequence name;

Ex1:-  create sequence seq1EmpNo;

 Ex2:- create sequence seq2EmpNo start with 9000;

Ex3:-Create sequence seq3EmpNo start with 9000 increment by -1 maxvalue 12000 minvalue 8000;

Ex4- create sequence seq4Empno order;

Ex5:-create sequence seq5Empno cache 28;

Using Sequence in Table Insert/Update

insert into emp values(seq2EmpNo.nextval,'peter','clerk',7782,'23-jan-82',1200,null,10);

Output:

9003 peter      clerk           7782 23-JAN-82       1200

If User wants to see the above sequences ,Query  User_Sequences table.

             select * from User_Sequences;

SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE
------------------------------ ---------- ---------- ------------ - - ----------
SEQ1EMPNO                               1 1.0000E+28            1 N N         20
SEQ2EMPNO                               1 1.0000E+28            1 N N         20
SEQ3EMPNO                            8000      12000           -1 N N         20
SEQ4EMPNO                               1 1.0000E+28            1 N Y         20
SEQ5EMPNO                               1 1.0000E+28            1 N N         28

Tags:Using Sequence in Oracle 11g,Auto increment in Oracle table,Sequence as a Primary key,Query user_sequences table, Alter sequence, using sequence in insert command.