Tuesday, September 18, 2007 

Exception Handling in PL/SQL-1

DECLARE
v_err_code number;
v_err_text varchar (255);
myexp Exception;
PRAGMA EXCEPTION_INIT(myexp,-2292);
N number:=10;
Y number:=0;
begin
if y=0
then
raise myexp;
else
N:=10/Y;
end if;
Exception
When myexp then
Y:=2;
N:=N/Y;
DBMS_output.put_line('zero divide exception error');
v_err_code:=SQLCODE;
v_err_text:=SQLERRM;
DBMS_output.put_line('value of N is after division is : ' || N);
DBMS_output.put_line('value of Y changed : ' || Y);
DBMS_OUTPUT.PUT_LINE(v_err_code || ' Description is ' ||v_err_text);
end;
/

DECLARE
v_err_code number;
v_err_text varchar (255);
myexp Exception;
PRAGMA EXCEPTION_INIT(myexp,-2292);
N number:=10;
Y number:=0;
begin
if y=0
then
raise myexp;
else
N:=10/Y;
end if;
Exception
When myexp then
RAISE_APPLICATION_ERROR (-20001, 'divide by zero------.');
Y:=2;
N:=N/Y;
DBMS_output.put_line('zero divide exception error');
v_err_code:=SQLCODE;
v_err_text:=SQLERRM;
DBMS_output.put_line('value of N is after division is : ' || N);
DBMS_output.put_line('value of Y changed : ' || Y);
DBMS_OUTPUT.PUT_LINE(v_err_code || ' Description is ' ||v_err_text);
end;

 

Exception Handling in PL/SQL

DECLARE
MEMNAME EMP.ENAME%TYPE;
BEGIN
SELECT ENAME INTO MEMNAME FROM EMPKRIS
WHERE EMPNO = &INPUT_EMPNO;
DBMS_OUTPUT.PUT_LINE(MEMNAME);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No Such Employee');ed
END;
/
DECLARE
N number:=10;
Y number:=0;
i number;
begin
for i in -2..2
loop
y:=i;
begin
N:=10/y;
DBMS_output.put_line('value of N is after division is : ' || N);
Exception
When ZERO_Divide then
DBMS_output.put_line('value of Y is :: ' || Y || ' exception caught');
Y:=100;
N:=10/y;
DBMS_output.put_line('value of N is after division in exception block : ' || N);
end;
end loop;
end;

 

Looping in PL/SQL

DECLARE
CURSOR emp_cur IS SELECT empno,sal,deptno
FROM empkris;
v_sal empkris.sal%TYPE;
v_empno empkris.empno%TYPE;
v_deptno empkris.deptno%TYPE;
v_ename empkris.ename%TYPE;
BEGIN
IF emp_cur%ISOPEN THEN
CLOSE emp_cur;
end if;
OPEN emp_cur;
LOOP
FETCH emp_cur INTO v_empno,v_sal,v_deptno;
EXIT when emp_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE( 'Employee number is : ' || v_empno || ' his salary is :'|| v_sal || ' and he works in dept no :'|| v_deptno);
END LOOP;
CLOSE emp_cur;
END;
/

DECLARE
CURSOR emp_cur IS SELECT empno,sal,deptno
FROM empkris;
v_sal empkris.sal%TYPE;
v_empno empkris.empno%TYPE;
v_deptno empkris.deptno%TYPE;
v_ename empkris.ename%TYPE;
BEGIN
For c in emp_cur
LOOP
v_empno:=c.empno;
v_deptno:=c.deptno;
v_sal:=c.sal;
FETCH emp_cur INTO v_empno,v_sal,v_deptno;
DBMS_OUTPUT.PUT_LINE( 'Employee number is : ' || v_empno || ' his salary is :'|| v_sal || ' and he works in dept no :'|| v_deptno);
END LOOP;
END;
/

DECLARE
v_sal empkris.sal%TYPE;
v_empno empkris.empno%TYPE;
v_deptno empkris.deptno%TYPE;
v_ename empkris.ename%TYPE;
v_empnon empkris.empno%TYPE:=999;
CURSOR c_employee is select empno,deptno from empkris where empno=v_empnon;
BEGIN
OPEN c_employee;
LOOP
FETCH c_employee INTO v_empno,v_deptno;
EXIT WHEN c_employee%NOTFOUND;
DBMS_OUTPUT.PUT_LINE( 'Employee number is : ' || v_empno || ' and he works in dept no :'|| v_deptno);
END LOOP;
END;
/

 

Sample cursors in PL/SQL

DECLARE
CURSOR emp_cur IS SELECT empno,sal,deptno
FROM empkris;
v_sal empkris.sal%TYPE;
v_empno empkris.empno%TYPE;
v_deptno empkris.deptno%TYPE;
v_ename empkris.ename%TYPE;
BEGIN
IF emp_cur%ISOPEN THEN
CLOSE emp_cur;
end if;
OPEN emp_cur;
LOOP
FETCH emp_cur INTO v_empno,v_sal,v_deptno;
EXIT when emp_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE( 'Employee number is : ' || v_empno || ' his salary is :'|| v_sal || ' and he works in dept no :'|| v_deptno);
END LOOP;
CLOSE emp_cur;
END;
/

DECLARE
CURSOR emp_cur IS SELECT empno,sal,deptno
FROM empkris;
v_sal empkris.sal%TYPE;
v_empno empkris.empno%TYPE;
v_deptno empkris.deptno%TYPE;
v_ename empkris.ename%TYPE;
BEGIN
For c in emp_cur
LOOP
v_empno:=c.empno;
v_deptno:=c.deptno;
v_sal:=c.sal;
FETCH emp_cur INTO v_empno,v_sal,v_deptno;
DBMS_OUTPUT.PUT_LINE( 'Employee number is : ' || v_empno || ' his salary is :'|| v_sal || ' and he works in dept no :'|| v_deptno);
END LOOP;
END;
/

DECLARE
v_sal empkris.sal%TYPE;
v_empno empkris.empno%TYPE;
v_deptno empkris.deptno%TYPE;
v_ename empkris.ename%TYPE;
v_empnon empkris.empno%TYPE:=999;
CURSOR c_employee is select empno,deptno from empkris where empno=v_empnon;
BEGIN
OPEN c_employee;
LOOP
FETCH c_employee INTO v_empno,v_deptno;
EXIT WHEN c_employee%NOTFOUND;
DBMS_OUTPUT.PUT_LINE( 'Employee number is : ' || v_empno || ' and he works in dept no :'|| v_deptno);
END LOOP;
END;
/

 

SQL Queries

Query to retrive top 3 records:

select distinct (a.sal) from empkris a where 3 =(select count (distinct(b.sal)) from empkris bwhere a.sal<=b.sal);


Odd number of records:

select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);

Even number of records:

select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp);

Wednesday, September 12, 2007 

RandomAccessFiles in Java

import java.io.File;
import java.io.RandomAccessFile;
import java.io.IOException;


public class RandomAccessFileExample {

private static void doAccess() {

try {

File file = new File("Test5Example.txt");

RandomAccessFile raf = new RandomAccessFile(file, "rw");

// Read a character

byte ch = raf.readByte();

System.out.println("Read first character of file: " + (char)ch);

System.out.println("Read full line: " + raf.readLine());

// Seek to the end of file

raf.seek(file.length());

// Append to the end of the file

raf.writeChars("for appending to the input file");

raf.writeBytes("this will also written into the output file");

raf.close();

}

catch (IOException e) {

System.out.println("IOException");

e.printStackTrace();

}

}

public static void main(String[] args) {

doAccess();

}

About me

  • I'm phanindra
  • From Hyderabad, Andhra Pradesh, India
  • An Alumini of JNTU Hyd into happening IT industry...
My profile

Visitors
Bloggeries Blog Directory BlogRankings.com singapore blog directory blog search directory
Google
 
Web conceptoftheday.blospot.com