Oracle table(s) data to INSERT statements with JAVA

Question

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.

So you're trying to retrieve logging information for all the inserts that were performed? — Kaleb Brasee
– Kaleb Brasee, Commented Jan 22, 2010 at 15:43
specify a little more what you're trying to do, your question isn't very clear that way — Valentin Rocher
– Valentin Rocher, Commented Jan 22, 2010 at 15:46

Zombies · Accepted Answer · 2010-01-22 15:48:04Z

1

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

answered Jan 22, 2010 at 15:48

Zombies

26k45 gold badges147 silver badges233 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user85421 · Accepted Answer · 2010-01-22 16:53:03Z

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),...

Community · Accepted Answer · 2017-05-23 11:48:34Z

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.

edited May 23, 2017 at 11:48

CommunityBot

11 silver badge

answered Jan 22, 2010 at 15:44

APC

147k20 gold badges180 silver badges289 bronze badges

Comments

Wellington Souza · Accepted Answer · 2016-10-24 04:23:08Z

0

I came across this question looking for a pure java implementation to generate the INSERT statements of a Oracle table. I found this piece of code that may be useful to someone that want to do it using just Java with JDBC without any tool.

answered Oct 24, 2016 at 4:23

Wellington Souza

2,3982 gold badges23 silver badges33 bronze badges

Collectives™ on Stack Overflow

Oracle table(s) data to INSERT statements with JAVA

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related