Hi All,
I hope you all are doing well.
I have write a java program using sql query to write into a pipe(|) separated text file.
But by default it writes "null" into the file where the column have no values.
If i use nvl(ename,' ') in varchar2 columns it writes a space into the file.
import java.io.*;
import java.sql.*;
class employeedet {
public employeedet() {
recordcount = 0;
totrecordcount = 0;
mydt = "";
}
//String p_no;
String report_date = "";
String report_run_date = "";
String output_dir;
File fh;
FileWriter fwwh;
PrintWriter fwh;
int recordcount;
int totrecordcount;
String mydt;
public void mainCall(String args0, String args1, String args2, String args3, String args4, String args5, String args6) {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@" + args0 + ":" + args1 + ":" + args2, args3, args4);
PreparedStatement pstmt = connection.prepareStatement("Select to_char(TRUNC(sysdate),'dd.mm.yy') report_run_date,to_char(TRUNC(sysdate+2),'dd.mm.yy') report_date from dual");
for (ResultSet resultset = pstmt.executeQuery(); resultset.next();) {
report_run_date = resultset.getString(1);
report_date = resultset.getString(2);
}
output_dir = "D:\\Program Files\\BATCHTEST";
String filename1 = output_dir + "/EMP_" + report_run_date + "_" + report_date + ".txt";
fh = new File(filename1);
fwwh = new FileWriter(fh);
fwh = new PrintWriter(new BufferedWriter(fwwh));
printcol_header();
pstmt = connection.prepareStatement("select empno,ename,job,mgr,hiredate,sal,nvl(comm,null) from scott.emp ");
ResultSet resultset1 = pstmt.executeQuery();
ResultSetMetaData resultsetmetadata = resultset1.getMetaData();
String s9 = "aaaa";
while (resultset1.next())
{
recordcount = recordcount + 1;
for (int i = 1; i <= resultsetmetadata.getColumnCount(); i++) {
//p_no = resultset1.getString(13);
fwh.write(resultset1.getString(i) + "|");
}
fwh.write("\n");
s9 = resultset1.getString(1);
}
System.out.println("No of record for Location Number" + s9 + "/" + recordcount);
fwh.flush();
fwh.close();
} catch (SQLException sqlexception) {
sqlexception.printStackTrace();
} catch (Exception exception) {
exception.printStackTrace();
}
}
private void printcol_header() throws IOException
{
String as[] = {"Empno", "Ename", "Job", "Mgr", "Hiredate", "Salary", "commission"};
for (int i = 0; i < as.length; i++) {
fwh.write(as[i] + "|");
}
fwh.write("\n");
}
public static void main(String args[]) {
employeedet bdl = new employeedet();
bdl.mainCall("172.17.6.206", "1521", "ORADEV", "axsys1", "axsys45", "000002", "MAGMA FINCORP LIMITED");
}
}
It prints like this into file while using nvl(comm,null) or comm and also not print the hire date column correctly.
Empno | Ename | Job | Mgr | Hiredate | Salary | commission |
7369 | SMITH | CLERK | 7902 | 00:00.0 | 800 | null |
But don't want print null or space , if the column value is null then it always writes blank(not space or null) to the file.
How do i do this ?
Any help is deeply appreciated.