Running SQL Loader from Java - Not all rows are loaded

Question

I'm trying to run SQL Loader from a Java method but I'm facing a problem.

I'm creating a Runtime process to run the SQLLDR command but not all the rows in the data file are being loaded. Instead it's getting stuck at the same number of rows (5184 rows) regardless of the total number of rows in the data file.

However, when I copy the exact same command and run it directly from the command prompt, it's working perfectly and all the rows are being loaded.

Any idea what can be the cause of this problem? Is it some kind of buffer size in Java that I need to change?

Thanks!

// Run SQL Loader after creating the control file
        Runtime rt = Runtime.getRuntime();
        String sqlLoaderPath = "C:\\app\\product\\11.1.0\\client_1\\BIN\\";
        String cmd = sqlLoaderPath + "SQLLDR.EXE userid=***************************"
                            + "data=c:\\sqlldr\\sqlldr_data.dat "
                            + "control=c:\\sqlldr\\sqlldr_control.ctl "
                            + "log=c:\\sqlldr\\sqlldr_log.log "
                            + "discard=c:\\sqlldr\\sqlldr_discard.disc "
                            + "bad=c:\\sqlldr\\sqlldr_bad.bad ";
        Process proc = rt.exec(cmd);
        int exitVal = proc.waitFor();
        proc.destroy();

ok I edited the post to include the code. The log file is empty because the process isn't terminating and so it's not writing anything to the log file. — user1856152, Nov 28 '12 at 14:53

score 1 · Answer 1 · answered Oct 22 '15 at 19:38

I know its too late to answer this question now but writing it because I faced the similar issue. Just add SILENT=FEEDBACK in your options clause of sql-loader control file. Sql-loader provides limited buffer size for output streams and faliure in reading it will cause subprocess to hang via JAVA so just stop output stream of sql-loader by using SILENT in its options.

score 0 · Answer 2 · answered Apr 03 '19 at 05:18

please have a lookinto below code, How we are using.

String dbStr = "sqlldr "+dbuserid +"/" + dbpassword + "@" + dbServiceName;

String cmdStr = dbStr +" control="+controlFileName;
               cmdStr = cmdStr+" log="+logName;
               cmdStr = cmdStr+" data="+dataFilePath;
               cmdStr = cmdStr+" bad="+badName;
               cmdStr = cmdStr+" errors="+Utils.getPropertyString("sqlldr_allowed_error");

Process p = rt.exec( cmdStr );

        InputStream in = p.getInputStream();
        InputStreamReader isr = new InputStreamReader(in);
        BufferedReader br = new BufferedReader(isr);
        String line = null;

        while( (line = br.readLine()) != null ) {
            sb.append(line+"\n");
        }

        InputStream inE = p.getErrorStream();
        InputStreamReader iser = new InputStreamReader(inE);
        BufferedReader erbr = new BufferedReader(iser);

        while( (line = erbr.readLine()) != null ){
            sb.append( "\nError stream:" );
            sb.append(line);
        }

        try {

                eValue = p.waitFor();
                Utils.info_log.info("waitFor Value is :: "+eValue);

        } catch ( Exception e ) {
            Utils.info_log.info(Utils.dumpStackTrace(e).toString());
        }

Running SQL Loader from Java - Not all rows are loaded

2 Answers2