make DBMS_METADATA.GET_DDL Pretty with identation

Question

I'm trying to generate DDL with JAVA Calling:

DBMS_METADATA.GET_DDL;
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',TRUE);

It works Fine but it gives me something like:

CREATE TABLE "DEPARTMENTS"   
(   "DEPARTMENT_ID" NUMBER(4,0),    
    "DEPARTMENT_NAME" VARCHAR2(30),    
    "MANAGER_ID" NUMBER(6,0),    
    "LOCATION_ID" NUMBER(4,0)   
);   
CREATE INDEX "DEPT_LOCATION_IX" ON "DEPARTMENTS" ("LOCATION_ID");

And I want something like:

CREATE TABLE "DEPARTMENTS"
(
    "DEPARTMENT_ID"   NUMBER(4,0),
    "DEPARTMENT_NAME" VARCHAR2(30),
    "MANAGER_ID"      NUMBER(6,0),
    "LOCATION_ID"     NUMBER(4,0)
);
CREATE INDEX "DEPT_LOCATION_IX" ON "DEPARTMENTS" ("LOCATION_ID");

Answer 1

Not the best answer but since I'm using java I can try printf to align columns' type :

pwFile.printf("%-45s %s,\n", columnsName.get(i), columnsType.get(i))

This method writes in file the DDL of table making the columns lower case and align their type :

 public static void writeTableDDL(String table) throws SQLException {
    Connection con = null;

    PreparedStatement preparedStatement = null;
    String filename = table.toUpperCase()+".sql";
    PrintWriter pwFile = null;
    ArrayList<String> columnsName = new ArrayList<String>() ;
    ArrayList<String> columnsType = new ArrayList<String>() ;
    ArrayList<String> columnsNullable = new ArrayList<String>() ;


    String selectSQL ="SELECT column_name , DECODE(nullable, 'Y', ' ', 'NOT NULL') nullable , " +
            " DECODE(data_type, 'RAW', data_type || '(' ||  data_length || ')', 'CHAR', data_type || '(' ||  data_length || ')', 'VARCHAR',  data_type || '(' ||  data_length || ')', 'VARCHAR2', data_type || '(' ||  data_length || ')', 'NUMBER', NVL2(   data_precision, DECODE( data_scale, 0, data_type || '(' || data_precision || ')' , data_type || '(' || data_precision || ',' || data_scale || ')'), data_type), data_type ) data_type "
            + " FROM user_tab_columns WHERE table_name = '"+table+"' ORDER BY column_id";

try 
{
    con = DBConnection.getConnection();
    preparedStatement = con.prepareStatement(selectSQL);
    ResultSet rs = preparedStatement.executeQuery();

    while (rs.next()) {

       columnsName.add(rs.getString(1));
       columnsType.add(rs.getString(3));
       columnsNullable.add(rs.getString(2));
    }
}catch (SQLException e) {
    System.out.println(e.getMessage());
} finally {
    if (preparedStatement != null) {
        preparedStatement.close();
    }
    if (con != null) {
        con.close();
    }
}

try {
    pwFile = new PrintWriter(filename);     
    pwFile.println("CREATE TABLE "+table+"\n(");

    for(int i=0; i <= columnsName.size()-1; i++) {
        columnsName.set(i, columnsName.get(i).toLowerCase());
    }

    for(int i=0; i < columnsName.size()-1; i++) {
          pwFile.printf("%-45s %s,\n", columnsName.get(i), columnsType.get(i));
    }

    pwFile.printf("%-45s      %s\n", columnsName.get(columnsName.size()-1), columnsType.get(columnsName.size()-1));
      pwFile.print(")\nTABLESPACE "+getTablespace(table)+"\n/");
} catch(FileNotFoundException e) {
  e.printStackTrace();
} catch(SecurityException e) {
  e.printStackTrace();
} finally {
  if(pwFile != null){
      pwFile.close();
  }
}
}