Converting column values of table into rows in Oracle!

Below is the example to read rows from a table and printing each column as a new row into an output file –

For the below table –
CREATE TABLE PERSON
(IDNO NUMBER(20,0),
NAME1 VARCHAR2(20 BYTE),
NAME2 VARCHAR2(20 BYTE),
NAME3 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 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.