Export oracle table data as sql Insert-String

Question

Hi Guys, this is me Francisco that wrote the first question click-here , 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

Answer 1

Last year we have to do something do read and write database (Ingres, Derby and HSQL) records from/to a file.
The basic idea was to have a class implementing a method that returns a String formatted according the SQL type of the data.
It was something like:

import static java.sql.Types.*;
...
private static final String DATE_FORMAT = "yyyy-MM-dd";

public static String formatDate(Date date) {        
    DateFormat format = new SimpleDateFormat(DATE_FORMAT);
    return format.format(date);
}
...
@Override
public String format(int type, Object value) throws SQLException {
    switch (type) {
        case INTEGER:
        case REAL:
        case DOUBLE:
        case SMALLINT:
            return value.toString(); 
        case CHAR:
        case LONGVARCHAR:
        case VARCHAR:
            return (String) value;
        case DATE:
            return formatDate((Date) value);
        case TIME:
            return formatTime((Time) value);
        case TIMESTAMP:
            return formatTimestamp((Timestamp) value);

        // TODO missing some types here

        default:
            throw new SQLException("unhandled type " + getTypeName(type));
    }
}

sorry, I'm not allowed to post the real codes