In this post, we will see how to export table data from Oracle to a comma-separated file(CSV).<\/p>\n\n\n\n
The connection to Oracle from Java can be established using the Oracle JDBC(Java Database Connectivity<\/a>)<\/strong><\/p>\n\n\n\n The JDBC connector will be different for each RDBMS. The JDBC connector is in the form of a JAR that can be imported to the project. In this project, we have used jdbc-oracle.jar<\/strong><\/p>\n\n\n\n Let us decode the above code into pieces. <\/p>\n\n\n Connection conn = DriverManager.getConnection(url, username, password);<\/strong><\/p>\n\n\n The main classes used in the above code for querying and fetching the data are,<\/p>\n\n\n\n PreparedStatement<\/strong> \u2013 This class is used to pass the query to the connection object<\/p>\n\n\n\n ResultSetMetaData<\/strong> \u2013 This class is used to get the Meta Information about the ResultSet. The main usage of this class is to fetch the column names from the ResultSet<\/p>\n\n\n\n ResultSet<\/strong> \u2013 This is the object type that will be returned when we run the query<\/p>\n\n\n\n To focus on the export table part, check the method “resultToCsv” in the above code.<\/p>\n\n\n\n Also check “Parsing SQL Select query using JSQL parser in Java<\/a>“, “Parsing SQL Insert query using JSQL parser in Java<\/a>“<\/p>\n","protected":false},"excerpt":{"rendered":" In this post, we will see how to export table data from Oracle to a comma-separated file(CSV). The connection to Oracle from Java can be established using the Oracle JDBC(Java Database Connectivity) The JDBC connector will be different for each RDBMS. The JDBC connector is in the form of a JAR that can be imported\u2026 Read More »<\/a><\/span><\/p>\n","protected":false},"author":1,"featured_media":279,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[6],"tags":[109,108],"_links":{"self":[{"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/posts\/204"}],"collection":[{"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/comments?post=204"}],"version-history":[{"count":9,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/posts\/204\/revisions"}],"predecessor-version":[{"id":1170,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/posts\/204\/revisions\/1170"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/media\/279"}],"wp:attachment":[{"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/media?parent=204"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/categories?post=204"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/tags?post=204"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}Export Oracle table to CSV<\/h2>\n\n\n\n
package com.oracle.executor;\n\nimport java.io.File;\nimport java.io.FileNotFoundException;\nimport java.io.IOException;\nimport java.io.PrintWriter;\nimport java.sql.Connection;\nimport java.sql.DriverManager;\nimport java.sql.PreparedStatement;\nimport java.sql.ResultSet;\nimport java.sql.ResultSetMetaData;\nimport java.sql.SQLException;\n\npublic class oracle_executor_client {\n\n\tpublic static void main(String[] args) throws ClassNotFoundException, SQLException, IOException {\n\t\t\n\t\ttry {\n\t\t\tClass.forName(\"oracle.jdbc.driver.OracleDriver\");\n\t\t\tString serverName = \"serverName\";\n\t\t\tString portNumber = \"port\";\n\t\t\tString sid = \"YOur_Connection_Sid\";\n\t\t\tString url = \"jdbc:oracle:thin:@\" + serverName + \":\" + portNumber + \"\/\" + sid;\n\t\t\tString username = \"username\";\n\t\t\tString password = \"password\";\n\t\t\tSystem.out.println(\"Connection URL: \" + url);\n\n\t\t\tConnection conn = DriverManager.getConnection(url, username, password);\n\t\t\tif(conn != null)\n\t\t\t{\n\t\t\t\tSystem.out.println(\"Connection established\");\n\t\t\t}\n\t\t\t\n\t\t\tString query = \"select * from dual\";\n\t\t\tSystem.out.println(\"Query: \" + query);\n\t\t\tPreparedStatement stmt = conn.prepareStatement(query);\n\t\t\tResultSet rs = stmt.executeQuery();\n\t\t\tString outputFile = \"C:\\\\OracleOutput\\\\OracleResult.csv\"\n\t\t\tresultToCsv(rs, outputFile);\n\t\t} catch (Exception e) {\n\t\t\t\/\/ TODO: handle exception\n\t\t\te.printStackTrace();\n\t\t}\n\n\t}\n\n\tpublic static void resultToCsv(ResultSet rs, String outputFile) throws SQLException, FileNotFoundException {\n\t\t\n\t\tPrintWriter csvWriter = new PrintWriter(new File(outputFile));\n\t\tResultSetMetaData meta = rs.getMetaData();\n\t\tint numberOfColumns = meta.getColumnCount();\n\t\tString dataHeaders = \"\\\"\" + meta.getColumnName(1) + \"\\\"\";\n\t\tfor (int i = 2; i < numberOfColumns + 1; i++) {\n\t\t\tdataHeaders += \",\\\"\" + meta.getColumnName(i).replaceAll(\"\\\"\", \"\\\\\\\"\") + \"\\\"\";\n\t\t}\n\t\tcsvWriter.println(dataHeaders);\n\t\twhile (rs.next()) {\n\t\t\tString row = \"\\\"\" + rs.getString(1).trim().replaceAll(\"\\\"\", \"\\\\\\\"\") + \"\\\"\";\n\t\t\tfor (int i = 2; i < numberOfColumns + 1; i++) {\n\t\t\t\trow += \",\\\"\" + rs.getString(i).trim().replaceAll(\"\\\"\", \"\\\\\\\"\") + \"\\\"\";\n\t\t\t}\n\t\t\tcsvWriter.println(row);\n\t\t}\n\t\tcsvWriter.close();\n\t}\n\n}<\/pre>\n\n\n\n