SQLLoader 704 Internal error while loading through JAVA

Question

Getting following error on executing sqlldr command.

SQL*Loader-704: Internal error: ulconnect: OCIServerAttach [0]
ORA-12154: TNS:could not resolve the connect identifier specified

Following is the sqlldr cmd :

sqlldr BILLING/'"Bill!ng@123#"'@10.113.242.162:1521/bssstc control=/log/bssuser/CDR/Postpaid_CDR_Log/CTRL_File.ctrl log=/log/bssuser/CDR/Postpaid_CDR_Log/LOG_File.log direct=false silent=header skip_unusable_indexes=true rows=200000 bindsize=20000000 readsize=20000000 ERRORS=25000

Note :- When executing the same through command prompt its getting succes.

Following is the code snipt i tried.

            Runtime rt   = Runtime.getRuntime();
            Process proc = null;
            try {
                proc = rt.exec(sqlLoaderCommand);

                InputStream       stderr = proc.getErrorStream();
                InputStreamReader isr    = new InputStreamReader(stderr);
                BufferedReader    br     = new BufferedReader(isr);

                String line = null;
                while ((line = br.readLine()) != null){
                    logger.info(line);
                }
                int exitVal = proc.waitFor();
                logger.info("Process exitValue: " + exitVal);
                int returnValue = proc.exitValue();
                String str = null;

                if (returnValue != 0) {
                    InputStream in = proc.getInputStream();
                    InputStreamReader preader = new InputStreamReader(in);
                    BufferedReader breader = new BufferedReader(preader);
                    String msg = null;
                    while ((msg = breader.readLine()) != null) {
                        logger.info(msg);
                        str = str + msg;
                    }
                    System.out.flush();
                    preader.close();
                    breader.close();
                    in.close();
                    InputStream inError = proc.getErrorStream();
                    InputStreamReader preaderError = new InputStreamReader(inError);
                    BufferedReader breaderError = new BufferedReader(preaderError);


                    String errorMsg = null;
                    while ((errorMsg = breaderError.readLine()) != null) {
                        logger.info("Copy Error: " + errorMsg);
                        str = str + errorMsg;
                    }
                }
            } catch (IOException e) {
                e.printStackTrace();
            } catch (NullPointerException e) {
                e.printStackTrace();
            }

what is the value of the String `sqlLoaderCommand`? The error message clearly says you that couldn't find proper connection name from your `tnsnames.ora` — BSeitkazin, Apr 09 '19 at 07:50
@BSeitkazin value is sqlldr BILLING/'"Bill!ng@123#"'@10.113.242.162:1521/bssstc control=/log/bssuser/CDR/Postpaid_CDR_Log/CTRL_File.ctrl log=/log/bssuser/CDR/Postpaid_CDR_Log/LOG_File.log direct=false silent=header skip_unusable_indexes=true rows=200000 bindsize=20000000 readsize=20000000 ERRORS=25000 — Rakesh KR, Apr 09 '19 at 07:51
@BSeitkazin But when i try through command prompt the query is executing fine.So connection will be fine right ? — Rakesh KR, Apr 09 '19 at 07:54

score 1 · Accepted Answer · answered Apr 10 '19 at 02:01

1

I think error could be in your username - "Bill!ng@123#". Check out the quotes escaping rules in Java. Like:

String str = "BILLING/'\"Bill!ng@123#\"'@10.113.242.162:1521";

answered Apr 10 '19 at 02:01

BSeitkazin

2,889
25
40

score 0 · Answer 2 · answered Apr 09 '19 at 08:12

1) From command line try to execute tnsping 10.113.242.162 if the tool will return full description you have to set "oracle.net.tns_admin" in java.

System.setProperty("oracle.net.tns_admin", "ORACLE_DIRECTORY/network/admin"); -- put correct path here and execute before your code

2) For test you can try to use full connection description .

instead of 10.113.242.162:1521/bssstc ->

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.113.242.162)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=bssstc))

SQLLoader 704 Internal error while loading through JAVA

2 Answers2