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

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

How To Make Dynamic Sql Query Using Mybatis

Sql queries can be dynamic using Mybatis, may help reusing sql queries for more than one occasion. As per the context, Sql "where" conditions can be appended and executed for resultsaccordingly. Here are few samples that demonstrate the use of test conditons in Sql queries.

Sample 1 demonstrate the simple test of condition when S_EMP_NAME parameter is not null

#Sample 1
SELECT A.EMP_ID,A.EMP_NAME,B.DEPT_ID B
FROM EMP A,DEPT B
WHERE A.DEPT_ID=B.DEPT_ID
AND A.EMP_ID=#{EMP_ID}
<if test="S_EMP_NAME !=null ">
AND A.EMP_NAME LIKE #{S_EMP_NAME} || '%'
</if>

Sample 2 demonstrate the build of total "where" condition. The previxOverrides variable is "AND | OR' is generated dynamically to make the statement complete.

#Sample 2
SELECT A.EMP_ID,A.EMP_NAME
FROM EMP A
<trim prefix="WHERE" prefixOverrides="AND |OR ">
<if test="S_EMP_ID !=null ">
A.EMP_ID=#{S_EMP_ID}
</if>
<if test="S_EMP_NAME !=null ">
A.EMP_NAME LIKE #{S_EMP_NAME}||'%'
</if>
</trim>

Sample 3 demonstrate build of like search for EMP_NAME only when S_EMP_ID parameter is null

#Sample 3
SELECT A.EMP_ID,A.EMP_NAME
FROM EMP A
<trim prefix="WHERE" prefixOverrides="AND |OR ">
<if test="S_EMP_ID !=null ">
A.EMP_ID=#{S_EMP_ID}
</if>
<if test="S_EMP_ID ==null ">
<if test="S_EMP_NAME !=null ">
A.EMP_NAME LIKE #{S_EMP_NAME}||'%'
</if>
</if>
</trim>

How To Return Oracle Cursor Using Mybatis in Oracle stored procedure call

In some cases it is necessary to get the set of data in the same procedure call. Sample example is demonstrated below.

Employee class to hold the single employee.

public class EMPLOYEE {
private Integer EMP_ID;
private String EMP_NAME;

public void setEMP_ID(Integer eMP_ID) {
EMP_ID = eMP_ID;
}
public Integer getEMP_ID() {
return EMP_ID;
}
public void setEMP_NAME(Integer eMP_NAME) {
EMP_NAME = eMP_NAME;
}
public String getEMP_NAME() {
return EMP_NAME;
}
}

Code To Access Cursor in java

Map map = new HashMap();
map.put("EMP_ID",11111);
myConnection.insert("cursor_exec", map);
List emp = (List) map.get("EMP");
for (int i = 0; i < emp.size(); i++) {
System.out.println(emp.get(i).getEMP_ID() || emp.get(i).getEMP_NAME() );
}

Mybatis Mapping for returning Oracle Cursor during procedure call

<resultMap id="getCursor" type="office.main.EMPLOYEE">

</resultMap>
<select id="cursor_exec" statementType="CALLABLE" parameterType="java.util.Map">
{call
proc_name(#{EMP_ID,jdbcType=NUMERIC},#{EMP,jdbcType=CURSOR,mode=OUT,javaType=java.sql.ResultSet,resultMap=getCursor})}
</select>

How To Pass or Parameterize Name of Table in Sql Select Statements in Mybatis

Table name can be passed to Sql queries in Mybatis. Instead of the #{PARAM_NAME} used in general WHERE condition "${TABLE_NAME}" is used to parameterize table name .

Below is the sample example shown below.

<select id="getName" resultType="java.lang.String" parameterType="java.util.Map">
SELECT COL_NAME1 FROM ${TABLE_NAME}
WHERE PARAM_1=#{PARAM_1}
AND PARAM_2=#{PARAM_2}
AND PARAM_3=#{PARAM_3}
</select>

Function and Procedure call Mybatis

Oracle functions and procedures can be executed through Mybatis called using "<select></select>" XML descriptors. Depending on the behaviour of the procedure "<update></update>" and "<delete></delete>" descriptors can also be used. Sample of function and procedure call is presented below.

<select id="getEmpName" parameterType="java.util.Map" resultType="java.lang.String">
select getEmpName(#EMP_ID) from dual
</select>

<update id="proc_emp_update" statementType="CALLABLE" parameterType="java.util.Map" >
{call
emp_update(#{EMP_ID},#{EMP_NAME},#{DEPT_ID,jdbcType=NUMERIC},#

{DEPT_NAME,jdbcType=VARCHAR,mode=OUT})
}
</update>


The getEmpName function returns employee name. The statementType="CALLABLE" knows that is the procedure invocation. The jdbcType=NUMERIC must be provided for nullable fields during the procedure invocation. The jdbcType can be supported data type provided by Mybatis such as jdbcType=DATE for Date , jdbcType=VARCHAR for String, jdbcType=CURSOR for Cursor. mode=OUT defines that it is out parameter returned by the procedure.

Using Sql Queries in Mybatis

Mybatis provides XML descriptors such as "<select></select>", "<insert></insert>", "<update></update>","<delete></delete>" for SQL DML statements. The DML statements can be accessed through "id" property in the XML descriptors. The "parameters" descriptor defines the user parameters passed to the DML statements and "resultType" descriptor defines the type of result the SQL statement returns. The parameters are used in the SQL statments with #{PARAMETER_NAME}. Here is the sample of basic DML operations in Mybatis.

<select id="getEmployee" parameterType="java.util.Map" resultType="java.util.Map">
select * from employee
where emp_id=#{EMP_ID}
and emp_name like #{EMP_NAME}||'%'
</select>


<insert id="insertEmployee" parameterType="java.util.Map">
insert into employee(emp_id,emp_name) values (#{EMP_ID},#{EMP_NAME})
</insert>


<update id="updateEmployee" parameterType="java.util.Map">
update employee set emp_name=#{EMP_NAME}
where emp_id=#{EMP_ID}
</update>


<delete id="deleteEmployee" parameterType="java.util.Map">
delete from employee
where emp_id=#{EMP_ID}
</delete>


Unlike SQL statements in oracle semi-colon must be avoided in the DML statements at the end.

Using CASE WHEN Oracle SQL

An example demonstrate using CASE WHEN in Oracle Sql.

CREATE TABLE DEPT(
DEPT_ID NUMBER,
DEPT_NAME VARCHAR2(100));

ALTER TABLE DEPT ADD CONSTRAINT DEPT_PK PRIMARY KEY (DEPT_ID);

CREATE TABLE EMP(
EMP_ID NUMBER,
EMP_NAME VARCHAR2(100),
DEPT_ID NUMBER);


ALTER TABLE EMP ADD CONSTRAINT EMP_PK PRIMARY KEY (EMP_ID);

ALTER TABLE EMP ADD CONSTRAINT EMP_FK FOREIGN KEY (DEPT_ID)
REFERENCES DEPT(DEPT_ID);


INSERT INTO DEPT VALUES(1,'Human Resource');

INSERT INTO EMP VALUES(100,'Employee One',1);

INSERT INTO EMP VALUES(101,'Employee Two',null);

COMMIT;

SELECT A.EMP_ID,A.EMP_NAME,A.DEPT_ID,
CASE
WHEN A.DEPT_ID IS NOT NULL THEN
(SELECT DEPT_NAME FROM DEPT WHERE DEPT_ID=A.DEPT_ID)
ELSE
''
END DEPT_NAME
FROM EMP A

Object Programming in Oracle

This article demonstrate how to code object oriented program in oracle. An employee object type is created with two properties EMP_ID and EMP_NAME. Another type TBL_EMP is created used to hold employees. Next employee table named emp is created that has same properties as object OBJ_EMP.

The procedure INSERT_EMP takes TBL_EMP type as its only IN parameter. If the count is greater than zero the program inserts into emp table.


CREATE OR REPLACE TYPE OBJ_EMP AS OBJECT (EMP_ID NUMBER , EMP_NAME VARCHAR2(100));

CREATE OR REPLACE TYPE TBL_EMP AS TABLE OF OBJ_EMP;

create table emp (
emp_id number,
emp_name varchar2(100)
);

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

CREATE OR REPLACE PROCEDURE INSERT_EMP( P_EMP TBL_EMP)
IS
v_obj_emp OBJ_EMP;
BEGIN

--insert into emp select * from table(p_emp);

IF P_EMP.COUNT > 0 THEN
FOR i IN 1 .. P_EMP.COUNT LOOP
v_obj_emp := P_EMP( i );

INSERT INTO emp ( emp_id,emp_name )
VALUES ( v_obj_emp.emp_id,v_obj_emp.emp_name);
END LOOP;
END IF;


END;
/


The INSERT_EMP procedure is invoked from anonymous block of plsql below. Here an employee object is intialized and table of employee type created. Employee type object t_emp is extend to create a row in the table object. The index of table object start with one which we assign to p_obj_emp object initially created. The t_emp table object is extended again and assigned to newly intialized p_obj_emp. Finally, INSERT_EMP procedure is invoked with t_emp object as parameter.


declare
p_obj_emp obj_emp := new obj_emp(1,'mahesh prajap');
t_emp tbl_emp := new tbl_emp();

begin
t_emp.extend();
t_emp(1):=p_obj_emp;

t_emp.extend();
p_obj_emp.emp_id:=2;
p_obj_emp.emp_name:='aarav prajap';
t_emp(2):=p_obj_emp;
INSERT_EMP(t_emp);
end;
/


The object programming concept in oracle can be evenly used with object oriented programming languages like java. List of objects can be passed directly to oracle procedure so that we can manipulate those passed objects in oracle as tables. There will be need and requirement in business logic that whole object be passed as tables and oracle plsql procedure takes control over the business procedures. The single object procedure call makes multiple call to procedure that makes more connection to oracle database and much of the time may be spent on such database connections.