Decode VS Case in Oracle

Decode: In Oracle/PLSQL, the decode function has the functionality of an IF-THEN-ELSE statement.

select decode(col1, 1,one,2,two) from table_name


if col1 value 1 then it will print as 'one' in result
if col1 value 2 then it will print as 'two' in result

decode will give performance issue. so now CASE came with same funtionality with better performance.

Case:

select main_acnt_code as code, main_acnt_name as name
from fm_main_account
where MAIN_FRZ_FLAG = 'N'
and case in_Code
when 'CAPEX' THEN main_class_code_05 = 'Y';
when 'MAIN_ACC2' THEN main_class_code_02 = 'Y';
end case;

select statements

Below two select statements will give same results

select *from employee where 10=emplid
select *from employee where emplid=10

Decode in Oracle

In Oracle/PLSQL, the decode function has the functionality of an IF-THEN-ELSE statement.

For example:

You could use the decode function in an SQL statement as follows:

SELECT supplier_name,
decode(supplier_id, 10000, 'IBM',
10001, 'Microsoft',
10002, 'Hewlett Packard',
'Gateway') result
FROM suppliers;

The above decode statement is equivalent to the following IF-THEN-ELSE statement:

IF supplier_id = 10000 THEN
result := 'IBM';

ELSIF supplier_id = 10001 THEN
result := 'Microsoft';

ELSIF supplier_id = 10002 THEN
result := 'Hewlett Packard';

ELSE
result := 'Gateway';

END IF;


Question: I would like to know if it's possible to use decode for ranges of numbers, ie 1-10 = 'category 1', 11-20 = 'category 2', rather than having to individually decode each number.

Answer: Unfortunately, you can not use the decode for ranges of numbers. However, you can try to create a formula that will evaluate to one number for a given range, and another number for the next range, and so on.

For example:

SELECT supplier_id,
decode(trunc ((supplier_id - 1) / 10), 0, 'category 1',
1, 'category 2',
2, 'category 3',
'unknown') result
FROM suppliers;
In this example, based on the formula:

trunc ((supplier_id - 1) / 10

The formula will evaluate to 0, if the supplier_id is between 1 and 10.
The formula will evaluate to 1, if the supplier_id is between 11 and 20.
The formula will evaluate to 2, if the supplier_id is between 21 and 30.

Incremental Update

Using Merge Join:

SQL> MERGE
INTO target_table tgt
USING source_table src
ON ( src.object_id = tgt.object_id )
WHEN MATCHED
THEN
UPDATE
SET tgt.object_name = src.object_name
, tgt.object_type = src.object_type
WHEN NOT MATCHED
THEN
INSERT ( tgt.object_id
, tgt.object_name
, tgt.object_type )
VALUES ( src.object_id
, src.object_name
, src.object_type );



Using SQL%ROWCOUNT (will give last DML updated row count):


SQL> DECLARE
i PLS_INTEGER := 0;
u PLS_INTEGER := 0;
BEGIN
FOR r IN ( SELECT * FROM source_table )
LOOP

UPDATE target_table tgt
SET tgt.object_name = r.object_name
, tgt.object_type = r.object_type
WHERE tgt.object_id = r.object_id;

u := u + SQL%ROWCOUNT;

IF SQL%ROWCOUNT = 0 THEN
INSERT INTO target_table
( object_id, object_name, object_type )
VALUES
( r.object_id, r.object_name, r.object_type );
i := i + 1;
END IF;

END LOOP;
DBMS_OUTPUT.PUT_LINE( u || ' rows updated.' );
DBMS_OUTPUT.PUT_LINE( i || ' rows inserted.' );
END;

Type of Cursors

Two types:

1. Implicit cursor
2. Explicit cursor

Implicit cursor: Are used by oracle.
eg1:
select name into var_name from emp;
eg2:
FOR r IN ( SELECT * FROM source_table )
LOOP

UPDATE target_table tgt
SET tgt.object_name = r.object_name
, tgt.object_type = r.object_type
WHERE tgt.object_id = r.object_id;

u := u + SQL%ROWCOUNT;

IF SQL%ROWCOUNT = 0 THEN
INSERT INTO target_table
( object_id, object_name, object_type )
VALUES
( r.object_id, r.object_name, r.object_type );
i := i + 1;
END IF;

END LOOP;

Explicit Cursors :For explicit cursors developer should do the following things

1. Declare the cursor
CURSOR company_cur IS
SELECT company_id FROM company;

2. Open the cursor (if not already open)
OPEN company_cur ;
3. Fetch one or more rows from the cursor
LOOP
FETCH company_cur INTO var_emplid;

EXIT WHEN company_cur %NOTFOUND;
-- do some data process here

END LOOP;

4. Close the cursor
CLOSE company_cur;

how many form-bean objects will be created by Web Container?

That depends on scope attribute in action element(struts-config.xml).

if scope= request then WC creates form bean object for each request if object not exist in ServletRequest object otherwise it uses existing object.
if scope= session then WC creates form bean object for each session if object not exist in HttpSession object otherwise it uses existing object.

How to get time zone as IST instead of GMT+05:30

this will display time zone in short like "IST","GMT" etc

TimeZone.getDefault().getDisplayName(false, TimeZone.SHORT)

Configure Tomcat for Debug

Configure Tomcat for Debug:

1. Open your tomcat admin console: /bin/tomcat5w.exe
C:\Apps\tnl\Tomcat5\bin\tomcat5w.exe

2. Click the Java Tab
Add the following arguments to the Java Options

-Xdebug
-Xrunjdwp:transport=dt_socket,address=8000,server=y,suspend=n

3. Click the Startup Tab
Add the arguments (on two separate lines, in the order below):
jpda
start
Select Mode -> JVM


After doing above step just run You application in Debug mode

jndi configuration with tomcat

Jndi configuration with tomcat example:

http://tomcat.apache.org/tomcat-4.1-doc/jndi-datasource-examples-howto.html

Thanks,
Shekhar.

Can an Innerclass be instantiated in Spring?

Dont be surprise but the answer is yes..

package com.src;
public class MyClass{
public static class InnerClass{
}
}

In spring.xml it Innerclass can be instantiated as:

< bean id="myclass" class="com.src.MyClass$InnerClass">

Wow !! this is good , spring is amazing.The innerclass should be public and static only.

Configuring a DataSource in Tomcat

Tomcat makes it easy to set up a connection pool so that servlets and JSPs can efficiently share database connections. In web sites that have many simultaneous users, a connection pool improves efficiency by sharing existing database connections, rather than creating a new connection and tearing it down every time an application has to use the database.

Another benefit of configuring a connection pool is that you can change the database system that a servlet or JSP is using without touching the Java code, because the database resource is configured outside of the servlet or JSP.

Here are the steps for configuring a DataSource with Tomcat:

1. Create a Resource
and a ResourceParams element in server.xml, or in the XML file that you have placed in Tomcat's webapps directory. These elements describe the JNDI object you are creating in order to provide your servlets or JSPs with a DataSource.

2. Add a resource-ref
element to web.xml, which allows the components in the associated web application to access the configured DataSource.

Example below shows the Resource and a ResourceParams elements in server.xml. This example describes a DataSource that connects with an Oracle 8i database.
The resource element in server.xml

"Shareable" type="javax.sql.DataSource" auth=
"Container" description="Home Oracle 8i Personal Edition"/>




driverClassName
oracle.jdbc.driver.OracleDriver



url
jdbc:oracle:thin:@192.168.0.2:1521:ORCL



username
scott



password
tiger



Create a Resource and ResourceParams element for each database that your application uses. Example below shows the resource-ref element associated with the Resource specified by example above.
A resource-ref element specifies a DataSource in web.xml




jdbc/oracle-8i-athletes

javax.sql.DataSource

Container



The JNDI path to this DataSource, which you use in a JNDI lookup (see the next recipe), is jdbc/oracle-8i-athletes.

Implementing hashCode() and equals()

The methods hashCode() and equals() play a distinct role in the objects you insert into Java collections. The specific contract rules of these two methods are best described in the JavaDoc. Here I will just tell you what role they play. What they are used for, so you know why their implementations are important.

equals()

equals() is used in most collections to determine if a collection contains a given element. For instance:

List list = new ArrayList();
list.add("123");

boolean contains123 = list.contains("123");

The ArrayList iterates all its elements and execute "123".equals(element) to determine if the element is equal to the parameter object "123". It is the String.equals() implementation that determines if two strings are equal.

The equals() method is also used when removing elements. For instance:

List list = new ArrayList();
list.add("123");

boolean removed = list.remove("123");

The ArrayList again iterates all its elements and execute "123".equals(element) to determine if the element is equal to the parameter object "123". The first element it finds that is equal to the given parameter "123" is removed.

As you can see, a proper implementation of .equals() is essential for your own classes to work well with the Java Collection classes. So how do you implement equals() "properly"?

So, when are two objects equal? That depends on your application, the classes, and what you are trying to do. For instance, let's say you are loading and processing Employee objects stored in a database. Here is a simple example of such an Employee class:

public class Employee {
protected long employeeId;
protected String firstName;
protected String lastName;
}

You could decide that two Employee objects are equal to each other if just their employeeId's are equal. Or, you could decide that all fields must be equal - both employeeId, firstName and lastName. Here are two example implementation of equals() matching these criterias:

public class Employee {
...
public boolean equals(Object o){
if(o == null) return false;
if(!(o instanceof) Employee) return false;

Employee other = (Employee) o;
return this.employeeId == other.employeeId;
}
}

public class Employee {
...
public boolean equals(Object o){
if(o == null) return false;
if(!(o instanceof) Employee) return false;

Employee other = (Employee) o;
if(this.employeeId != other.employeeId) return false;
if(! this.firstName.equals(other.firstName)) return false;
if(! this.lastName.equals(other.lastName)) return false;

return true;
}
}

Which of these two implementations is "proper" depends on what you need to do. Sometimes you need to lookup an Employee object from a cache. In that case perhaps all you need is for the employeeId to be equal. In other cases you may need more than that - for instance to determine if a copy of an Employee object has changed from the original.

hashCode()

The hashCode() method of objects is used when you insert them into a HashTable, HashMap or HashSet. If you do not know the theory of how a hashtable works internally, you can read about hastables on Wikipedia.org.

When inserting an object into a hastable you use a key. The hash code of this key is calculated, and used to determine where to store the object internally. When you need to lookup an object in a hashtable you also use a key. The hash code of this key is calculated and used to determine where to search for the object.

The hash code only points to a certain "area" (or list, bucket etc) internally. Since different key objects could potentially have the same hash code, the hash code itself is no guarantee that the right key is found. The hashtable then iterates this area (all keys with the same hash code) and uses the key's equals() method to find the right key. Once the right key is found, the object stored for that key is returned.

So, as you can see, a combination of the hashCode() and equals() methods are used when storing and when looking up objects in a hashtable.

Here are two rules that are good to know about implementing the hashCode() method in your own classes, if the hashtables in the Java Collections API are to work correctly:

1. If object1 and object2 are equal according to their equals() method, they must also have the same hash code.
2. If object1 and object2 have the same hash code, they do NOT have to be equal too.

In shorter words:

1. If equal, then same hash codes too.
2. Same hash codes no guarantee of being equal.

Here are two example implementation of the hashCode() method matching the equals() methods shown earlier:

public class Employee {
protected long employeeId;
protected String firstName;
protected String lastName;

public int hashCode(){
return (int) this.employeeId;
}
}

public class Employee {
protected long employeeId;
protected String firstName;
protected String lastName;

public boolean equals(Object o){
return (int)this.employeeId *
firstName.hashCode() *
lastName.hashCode();
}
}

Notice, that if two Employee objects are equal, they will also have the same hash code. But, as is especially easy to see in the first example, two Employee objects can be not equal, and still have the same hash code. In the first example the hash code is the employeeId is rounded down to an int. That means that many employee id's could result in the same hash code, but these Employee objects would still not be equal, since they don't have the same employee id.

Complete generic examples

Generic examples available here(check all example in right side index)

http://tutorials.jenkov.com/java-generics/generic-list.html