Monday, March 19, 2012

How to stop an open port listening on Windows forcefully

1) In command prompt type

netstat -a -o -n

2) find the PID number for that port number

Proto Local Address Foreign Address State PID
TCP 0.0.0.0:8009 0.0.0.0:0 LISTENING 5840
TCP 0.0.0.0:8080 0.0.0.0:0 LISTENING 5840

3) In command prompt type

taskkill /F /PID 5840

This will kill process 5840 forcefully and release port 8009, 8080.

Oracle Savepoint example

CREATE TABLE TEST(
ID NUMBER PRIMARY KEY
);

CREATE OR REPLACE PROCEDURE TEST_PROC
IS
BEGIN
INSERT INTO TEST(ID) VALUES(5);
FOR I IN 1..10 LOOP
SAVEPOINT A;
DECLARE
V_NUM NUMBER:=5;
BEGIN
INSERT INTO TEST(ID) VALUES(I);
COMMIT;
EXCEPTION WHEN OTHERS THEN
ROLLBACK TO A;
END;
END LOOP;
END;
/

Wednesday, February 1, 2012

Handling null values in Oracle Select Statements

Oracle Sql Select statement do not evaluate null values. Null values should be handled in the where condition of sql statements for all nullable columns. Oracle nvl function provides best way to handle null values.

create table emp(
emp_id number,
emp_name varchar2(20),
emp_age number);

alter table emp add constraint emp_pk primary key (emp_id);

insert into emp values(1,'mahesh',null);

insert into emp values(2,null,20);

insert into emp values(3,null,23);

insert into emp values(4,null,null);


An employee table contain column named emp_name that can have null values. When emp_name column is compared with "like" operator the rows with null values for emp_name columns are not evaluated by the select statement.

select * from emp where emp_name like '%';

EMP_ID EMP_NAME EMP_AGE
------ -------------------- ----------
1 mahesh

To evaluate column that can have null values, nvl function can be handled for emp_name column for null values as shown below.

select * from emp
where nvl(emp_name,'!') like '%';

EMP_ID EMP_NAME EMP_AGE
------ -------------------- ----------
1 mahesh
2 20
3 23
4