Oracle table(s) data to INSERT statements with JAVA

Question

Hi, How can I get sql inserts from a oracle table with JAVA? Is there a Oracle API for it?

I need to store it in a file, the result file should have these examples lines:

-- INSERTING into TEST
Insert into "TEST" ("CODE","FAMILY","SUB_FAMILY","SEVERITY","STATUS","ANOMALY_DATE","DESCRIPTION",
"COMMENTS","VALUE0","VALUE1","VALUE2","VALUE3","VALUE4","VALUE5","VALUE6","VALUE7","VALUE8",
"VALUE9") values (1,'family1','subFamily11','bad','initial',to_date('0005-11-21','DD/MM/RR'),'someDescription',
'someComment','someValue','someValue','someValue','uselessValue','uselessValue',null,null,null,null,null);

the example line was created with the export tool from Oracle SQl developer

Thks.

Answer 1

How crucial is it that you have insert statements? The normal way to transfer data between Oracle databases is to use the DataPump utility. This comes with a PL/SQL API which can be manipulated programmatically. I posted a sample set of code for doing that in another recent thread.

Answer 2

Use Toad for Oracle. You can save a query as insert statements, spreadsheet, CSV... about anything you can think of.

Answer 3

I would also use Toad, but not sure if it's free.
If really need to do it in Java, you must use JDBC and the MetaData of the result set to create your commends.
Here an example, just as an idea - it is NOT complete^*

Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection("jdbc:oracle:oci:@DB_ID", "user", "password");
try {
  PreparedStatement stmt = conn.prepareStatement("SELECT * FROM MY_TABLE");
  StringBuilder command = new StringBuilder();
  ResultSet rset = stmt.executeQuery();
  ResultSetMetaData meta = rset.getMetaData();
  int columns = meta.getColumnCount();
  command.append("INSERT (");
  for (int i = 1; i <= columns; i++) {
    if (i > 1) {
      command.append(',');
    }
    command.append(meta.getColumnName(i));
  }
  command.append(") VALUES (");
  int head = command.length();
  while(rset.next()) {
    for (int i = 1; i <= columns; i++) {
      if (i > 1) {
        command.append(',');
      }
      String value = rset.getString(i);
      if (value != null) {
        command.append('\'');
        command.append(value);
        command.append('\'');
      } else {
        command.append("NULL");
      }
    }
    command.append(")");
    System.out.println(command.toString());
    command.setLength(head);
  }
} finally {
  conn.close();
}

^* missing error handling, type handling (assumed all data is String),...

Answer 4

Hi Guys, this is me Francisco that wrote the first question, but now I've an account. I'll try to explain it. For a Job I need a .txt file that contains the SQl string inserts from an Oracle Table. I can't use a Software like toad because I need to do it with Java Code. I'm not trying to get the inserts's logging , I need a to export it from a table. The Client ask for it, this is the crucial thing.You say the normal way to do it is using DataPump utility, but using this tools, will I get a text file? I read that DataPump works with binaries files. using Java JDBC and metadata, I need to convert all in String file, ok for it I'm agree, but I have to write a lot of code to know what kind of type I get from the Table to parse it. One example is with Date types, oracle softwares parse it as to_date('0005-11-21','DD/MM/RR') and java toTostrig will do it something like '2009-1-25 13:00:00'. Java toString method will truncate some Number types So I need to know if someone did it or can help me.

I'm a newby for this page, could anyone edit the first question or how can I update it if I did it with a non registered user?

Thank you very much. Francisco

Sorry about my English, it's not my natural language