How to write data into a file returns from SQL query with JAVA ODBC

Question

I Am using SQL developer and using Spool i am successfully able to write the data into CSV format.But i want to automate the same using java.

I have written below code but i am getting " ORA-00933: SQL command not properly ended"

package data;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.testng.annotations.Test;

public class NewTest {
  @Test
  public void f() throws ClassNotFoundException, SQLException {


      Class.forName("oracle.jdbc.driver.OracleDriver");

      System.out.println("driver loaded");

      Connection cn=DriverManager.getConnection("my connection details like hostname and other credentials ");

      System.out.println("connected to db");

      Statement sm=cn.createStatement();
      String filename="C:\\Users\\himanshu\\Documents\\dumps\\x.CSV";
      String query = "SELECT * FROM mytable  into OUTFILE '"+filename+"' fields terminated by ',' lines terminated by '\n'";
      ResultSet rs=  sm.executeQuery(query);

It appears from your code that you do logically want the result set in your Java code/JVM. Assuming this, follow the duplicate link to see how to write a SQL result set to file from a Java program. If this is _not_ what you want, then state what you are trying to do here. — Tim Biegeleisen, Sep 04 '19 at 07:00
I Want to execute SELECT * FROM mytable query and write the result into CSV file . — Himanshu Agarwal, Sep 04 '19 at 07:02
Then the duplicate link has you covered, if you want to write to a location accessible from your Java program. — Tim Biegeleisen, Sep 04 '19 at 07:05
Please can you help me where i am doing mistake as i am not getting proper solution. — Himanshu Agarwal, Sep 04 '19 at 07:08
Do you want the Java program to write the file, or you want Oracle server to write the file? — Tim Biegeleisen, Sep 04 '19 at 07:09
i want my Java Program to perform the task and write the SQL query result to outfile.CSV. — Himanshu Agarwal, Sep 04 '19 at 07:11

How to write data into a file returns from SQL query with JAVA ODBC

0 Answers0