import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class work {

    Pattern structureRegex = Pattern.compile("CREATE([\\s\\S]*?)\\)\\)");
//    Pattern dataRegex =
    Pattern dataRegex =  Pattern.compile("(INSERT([\\s\\S]*?).+\\);).+");
    String structure ;
    String data;
    StringBuilder All = new StringBuilder("") ;

    public void getAllContext(){
        try {
            BufferedReader in = new BufferedReader(new FileReader("D:\\sql\\MID_TABLE_DATA_GEN_ICT.sql"));
            String str;
            while ((str = in.readLine()) != null) {
                All.append(str) ;
            }
//            System.out.println(All);
        } catch (IOException e) {
        }
    }
    public void getStructure(){
        getAllContext();
        Matcher m = structureRegex.matcher(All);
        if (m.find()){
            structure = m.group(0);
        }
        structure = structure.replace("NUMBER","numeric")
                .replace("VARCHAR2","varchar")
                .replace("\"ISS_TEST\".","")
                .replace(" BYTE","");
        System.out.println(structure);
    }

    public void getData(){
        getAllContext();
        Matcher m = dataRegex.matcher(All);
        if (m.find()){
            data = m.group(0);
        }
        data = data.replace("\"ISS_TEST\".","")
                .replace("nvl","coalesce");
        System.out.println(data);
    }


}

class main{
    public static void main(String[] args) {
        work work = new work();
        work.getStructure();
        work.getData();
    }
}