0

I want to run sql scripts using sql plus. The problem is that if i dont have an exit statement in my sql script, the application stop running. I need to put exit to disconnect from sql. I dont want to be disconected because i want to maintain the session at the database for others queries. Please help me

package pachet1;

import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.net.MalformedURLException;
import java.nio.file.Files;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.util.Map;

import javafx.scene.control.TextArea;
import jcifs.smb.NtlmPasswordAuthentication;
import jcifs.smb.SmbFile;

public class test1 {

    public static void main (String args []) {


    }

    public static void test_script (String scriptName, String alias, String path, TextArea txtArea) throws IOException {


        NtlmPasswordAuthentication userCred = new NtlmPasswordAuthentication("BCRWAN",
                "Sebastian.burchidrag", "Parola952491");
        SmbFile smbFile=new SmbFile(path  + scriptName, userCred);
        File file = new File("D://" + scriptName);
        try (InputStream in = smbFile.getInputStream()) {
            Files.copy(smbFile.getInputStream(), file.toPath());
        }
        String fileName = "@" + scriptName;
        String sqlPath = "D:\\";

        String sqlCmd = "sqlplus";

        String arg1   = "sys/sys@" +  alias + " " +  "as sysdba"; //plug in your user, password and db name
        String arg2   = fileName;
        try {
            String line;
            ProcessBuilder pb = new ProcessBuilder(sqlCmd, arg1, arg2);
            Map<String, String> env = pb.environment();
            env.put("VAR1", arg1);
            env.put("VAR2", arg2);
            pb.directory(new File(sqlPath));
            pb.redirectErrorStream(true);
            Process p = pb.start();
          BufferedReader bri = new BufferedReader
            (new InputStreamReader(p.getInputStream()));
          BufferedReader bre = new BufferedReader
            (new InputStreamReader(p.getErrorStream()));
          while ((line = bri.readLine()) != null) {
            txtArea.appendText(line + "\n");
          }
          bri.close();
          while ((line = bre.readLine()) != null) {
           txtArea.appendText(line + "\n");

          }
          bre.close();
          System.out.println("Done.");
        }
        catch (Exception err) {
          err.printStackTrace();
        }

    }

}
sstan
  • 35,425
  • 6
  • 48
  • 66
CobianuA
  • 15
  • 5
  • 2
    Just curious as to why you are apparently using command line called from Java instead of JDBC? – CodeChimp Sep 08 '16 at 13:43
  • 2
    Please OP, don't use `sys` to run scripts and/or application code. It's very dangerous. You can seriously mess up your database. – sstan Sep 08 '16 at 13:46
  • @CodeChimp: OP wants to run an Oracle SQL script file. Can't do that in JDBC afaik. – sstan Sep 08 '16 at 14:00
  • I suppose your app stops, because it is waiting to get output from environment... why won't you exit, and then create another session for the next script? – PKey Sep 08 '16 at 15:29
  • @Plirkee, i wont exit because the scripts are loading by somebody automatically and they dont have exit in their body. I need something to keep my connection... – CobianuA Sep 08 '16 at 17:59
  • Why do you want to keep the session open? What do you mean the scripts are "loading by somebody automatically"? Just open a separate session for each script and you are good to go. Open session, execute, close session. Rinse and repeat – daZza Sep 09 '16 at 09:19
  • @daZza i want to say that my session is closed automatically from my script, because i have an exit statement. If i delete exit, the application stop working – CobianuA Sep 09 '16 at 10:11
  • @sstan If you are using the naitive JDBC drivers, as far as I am aware, you can execute anything via the driver that you can via a .SQL file. Why couldn't you simple read the file in as text and shoot it to the DB as SQL? It's still dangerous, but LOADS less dangerous that trying to shell out from Java to call a CLI client. – CodeChimp Sep 09 '16 at 13:44
  • @CodeChimp: *you can execute anything via the driver that you can via a .SQL file*: Yes and no. In JDBC, you are limited to executing one statement at a time (right?). If that's the case, then to run the script in JDBC, you would have to parse the script to extract the individual statements, not always an easy task if you have to check for semi colons inside and outside PL/SQL procedures, etc. – sstan Sep 09 '16 at 13:57
  • @sstan That is not correct. You can run your request in a transaction, which has the effect of running multiples. Also, some JDBC drivers do allow multiple requests at one time. I know for a fact the Oracle one used to allow that. Also, there is the whole [batching ability](http://stackoverflow.com/questions/10929369/how-to-execute-multiple-sql-statements-from-java). – CodeChimp Sep 09 '16 at 17:40
  • @CodeChimp: I may not have been clear. But simply put, there is no single JDBC method call that allows me to pass the entire contents of a SQL*PLUS script and execute it. In other words, if my script has 2 create table statements, I can't just make a single call with my 2 create statements in a single SQL string. I have to split the script and extract the 2 create table statements individually (parsing) and execute them separately, even if I take advantage of statement batching to reduce the number of database calls actually made. – sstan Sep 09 '16 at 17:57

0 Answers0