Converting each column value into a row to an output file in Oracle!

Below is the example to read rows from a table and printing them as columns –

For the below table –
CREATE TABLE PERSON
(IDNO NUMBER(20,0),
NAME3 VARCHAR2(20 BYTE),
NAME1 VARCHAR2(20 BYTE),
NAME2 VARCHAR2(20 BYTE)
);

if you want to printout put as
~NAME1~IDNO~
~NAME2~IDNO~
~NAME3~IDNO~

Below SQL Script will help generate the above required output – in output.csv file:

——————————————————————–
Script:
——————————————————————–

set echo off
set pagesize 0
set feedback off
set heading off
set lines 50
set trimspool off
set termout off
set serveroutput on

spool ‘C:/output.csv’

BEGIN FOR CUR_REC IN
(SELECT
IDNO,
NAME2,
NAME3,
NAME1
FROM PERSON)
LOOP
IF TRIM(CUR_REC.NAME2) is NOT null
THEN
DBMS_OUTPUT.put_line(‘~’||TRIM(CUR_REC.NAME2)||’~’||CUR_REC.IDNO ||’~’ );
END IF;
IF TRIM(CUR_REC.NAME3) is NOT null
THEN
DBMS_OUTPUT.put_line(‘~’||TRIM(CUR_REC.NAME3)||’~’||CUR_REC.IDNO ||’~’ );
END IF;
IF TRIM(CUR_REC.NAME1) is NOT null
THEN
DBMS_OUTPUT.put_line(‘~’||TRIM(CUR_REC.NAME1)||’~’||CUR_REC.IDNO ||’~’ );
END IF;
END LOOP;
END;
/

spool off

set termout on
set trimspool on
set heading on
set feedback on
set echo on

Java Collections – Iterator vs ListIterator

  • We can use Iterator to traverse Set and List collections whereas ListIterator can be used with Lists only.
  • Iterator can traverse in forward direction only whereas ListIterator can be used to traverse in both the directions.
  • ListIterator inherits from Iterator interface and comes with extra functionalities like adding an element, replacing an element, getting index position for previous and next elements.

Ugliness of Java Generics – Area based behaviour

Generic to Non Generic Behaviour 

Need to be careful with generics as it has method area based behavior as in the below example – 

Class Test {

public static void main(String[] args){

ArrayList<String> genericAreaList = new ArrayList<String>();

//In the below ArrayList can only accept String values

genericAreaList.add("John");
genericAreaList.add("Test");
nonGenericAreaMethod(genericAreaList); // passing the ArrayList to this non-generic Arraylist signature method.
System.out.println(genericAreaList);
}

//Here ArrayList can add any value as the below signature is non generics ArrayList
private static void  nonGenericAreaMethod(ArrayList nonGenericAreaList) {
nonGenericAreaList.add(10);
nonGenericAreaList.add(12);

}

}

 

Also remember – ArrayList testList = new ArrayList<String>(); works as non-generics and accept any value String, Integer.

Also see – Generics Basics

Java Generics – Difference between &amp;amp;amp;lt;? extends T&amp;amp;amp;gt; and &amp;amp;amp;lt;? super T&amp;amp;amp;gt;

Both <? extends T> and <? super T> represent bounded wildcards
<? extends T> – will accept only T or its subclasses
<? super T> – will accept T or super class. bounded wildcards gives more flexibility to methods which can operate on collection of T or its sub class. These bound types are used with Collections as shown below.

Example – Collections.unmodifiableSet(Set<? extends T> s) will accept Set of type T or Set of sub class of T.

Also see – Generics Basics

Lower bounded wildcard:
A lower bounded wildcard is expressed using the wildcard character (‘?’), following by the super keyword, followed by its lower bound: .

To allow method to work on List, List, and List — anything that can hold Integer values. method that works on lists of Integer and the supertypes of Integer, such as Integer, Number, and Object, you would specify List<? super Integer >

You can use an upper bounded wildcard to relax the restrictions on a variable. Say you want to write a method that works on List, List, and List; you can achieve this by using an upper bounded wildcard.

Upper bounded wildcard:
To declare an upper-bounded wildcard, use the wildcard character (‘?’), followed by the extends keyword, followed by its upper bound. Note that, in this context, extends is used in a general sense to mean either “extends” (as in classes) or “implements” (as in interfaces).

For lists of Number and the subtypes of Number, such as Integer, Double, and Float, you would specify List<? extends Integer >. The term List;Integer > is more restrictive than List<? extends Integer > because the former matches a list of type Number only, whereas the latter matches a list of type Number or any of its subclasses.

Java 9 important differences between overloading and overriding

Below are the differences 9 differences between overloading and overriding in Java –

S.No Property Overloading Overriding
1 Method names Must be same Must be same
2 Argument Types Must be different (at least order) Must be same (including order)
3 Method signatures Must be different Must be same
4 Return Type No Restrictions Must be same but this rule is applicable until 1.4 version only. From 1.5v onwards co-variant return types are allowed.
5 Private, static & final methods Can be overloaded Cannot be overridden
6 Access modifiers No Restrictions We cannot reduce scope of Access modifier but we can elevate the scope.

Example: if parent method is default modifier then child method can also be protected or public.

7 Throws clause No Restrictions If child class method throws any checked exception then its parent method should also throw checked exception, if not then compile time exception is thrown. There are no restrictions with Unchecked exceptions.
8 Method Resolution Always taken care by compiler based on reference type Always taken care by JVM based on Runtime object
9 Also known as Compile time polymorphism or static polymorphism or early binding Runtime polymorphism, dynamic polymorphism or late binding.

Java Difference between final, finally and finalize()

What is the difference between final, finally and finalize?

Final – 

1. Final is applicable for classes methods and variables.
2. If method is final then its subclass cannot override that method.
3. if class is final then we cannot extend that class.
4. if variable is final then its cannot be altered. it becomes a constant.

Finally – is a block always associated with try catch.


try{

//business logic

} catch(Exception et) {

//Handle any exception here

}
finally {

// we can write clean-up code here in the finally.
}

finalize() – is a method present in Object class. Garbage collector makes call to this method before destroying this method to perform clean-up activities.