SQL output to file from Java Code

VBStudent
VBStudent used Ask the Experts™
on
I am trying to write an SQL query in Java that will output the results to a file.  When I run the query, I get the error message "ORA-00933: SQL command not properly ended".  Here is the code I am using:

public void exportData(Connection conn) {
        Statement stmt;
        String query;

        try {
            stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);
           
            //For comma separated file
            query = "SELECT code,meaning,description FROM GNWM_DISC_FORM_VALN t into OUTFILE 'c:\\test.txt' FIELDS TERMINATED BY ',' ";
                     
           
            stmt.executeQuery(query);
           
        } catch(Exception e) {
            e.printStackTrace();
            e.getMessage();
            stmt = null;
        }
    }
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
Try adding a final semicolon

Author

Commented:
I tried that.  

Author

Commented:
I suspect it doesn't like the OUTFILE portion of the query.  When I remove that portion, the syntax is fine and the query executes without any problem.  The problem occurs when I add the OUTFILE portion.
Top Expert 2016

Commented:
That statement executes ok in sql plus then?
Your structure wrong. See code shown.

 
                     
String filename;
strFileName = "c:\\test.txt";
//For comma separated file
query = "SELECT code,meaning,description into OUTFILE strFileName FIELDS TERMINATED BY ',' FROM GNWM_DISC_FORM_VALN t";

Open in new window

what does this query returns when you run it in some oracle client like toad

<<SELECT code, meaning, description FROM GNWM_DISC_FORM_VALN into OUTFILE 'c:\\test.txt' FIELDS TERMINATED BY ',' >>
Typo mistake:
String strFileName;

Author

Commented:
When I try:
 query = "SELECT code,meaning,description into OUTFILE strFileName FIELDS TERMINATED BY ',' FROM GNWM_DISC_FORM_VALN t";

I get the error message:
ORA-00923: FROM keyword not found where expected
i think he meant

 query = "SELECT code,meaning,description into OUTFILE" + strFileName + "FIELDS TERMINATED BY ',' FROM GNWM_DISC_FORM_VALN t";
Top Expert 2016

Commented:
That would need to be
 query = "SELECT code,meaning,description into OUTFILE " + strFileName + " FIELDS TERMINATED BY ',' FROM GNWM_DISC_FORM_VALN t";

Open in new window

Top Expert 2016
Commented:
VBStudent, can you please run the command until it's right in SQL Plus? We can then get it working in Java

Author

Commented:
Thanks for all the comments.  I believe I discovered the problem.  I think the "OUTFILE" command is exclusive to MySql.  I am trying to run this query against an Oracle database.  I trashed the OUTFILE command and output the info from a resultset using filewriter.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial