title: execute.sql files on a oracle DB in shell script
body: <p>I want to execute <code>.sql</code> files which are written with sql*plus commands on a oracle DB from a shell script.</p>
  
  <p>An example of one <code>.sql</code> file you can see below:</p>
  
  <pre><code>  SET DEFINE OFF;
  
    -- Changeset om-core/om-core.sys.db-changelog.xml::om-core-createUser.24.0.0::mlo
    -- Creating om-dashboard schema/user
    CREATE USER omcore27 IDENTIFIED BY omcore;
    GRANT CONNECT TO omcore27;
    GRANT CREATE TABLE TO omcore27;
    GRANT CREATE SEQUENCE TO omcore27;
    GRANT CREATE ANY INDEX, SELECT ANY TABLE TO omcore27;
    GRANT CREATE TRIGGER TO omcore27;
    GRANT CREATE PROCEDURE TO omcore27;
    GRANT UNLIMITED TABLESPACE TO omcore27;
    GRANT EXECUTE ON SYS.DBMS_AQADM to omcore27;
    GRANT EXECUTE ON SYS.DBMS_AQ to omcore27;
    ALTER USER omcore27 QUOTA UNLIMITED ON SYSTEM;
    GRANT SELECT ON SYS.DBA_RECYCLEBIN TO omcore27;
    GRANT EXECUTE ON DBMS_AQIN to omcore27;
    GRANT select on v_$sysmetric to omcore27;
    GRANT select on dba_hist_sysmetric_summary TO omcore27;
    GRANT create job TO omcore27;
    GRANT create external job TO omcore27;
  
    -- Changeset om-core/om-core.sys.db-changelog.xml::om-core-grantAQ.24.0.0::mlo
    begin
              DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE (privilege =&gt; 'ENQUEUE_ANY', grantee =&gt; 'omcore27', admin_option =&gt; FALSE);
              DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE (privilege =&gt; 'DEQUEUE_ANY', grantee =&gt; 'omcore27', admin_option =&gt; FALSE);
              DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE (privilege =&gt; 'MANAGE_ANY', grantee =&gt; 'omcore27', admin_option =&gt; FALSE);
          end;/
  </code></pre>
  
  <p>So I need a shell command to execute above <code>.sql</code> file in on oracleDB.</p>
  
  <p>I already tried to do that with a java program and then run that program <code>.jar</code> from my shell script, but I get that Error:</p>
  
  <pre><code> Line: SET DEFINE OFF;
   *** Error : java.sql.SQLSyntaxErrorException: ORA-00922: missing or invalid option
  </code></pre>
  
  <p>This is the sourecode of my DBScriptRunner class:</p>
  
  <pre><code>public class DBScriptRunner {
      public static void main(String[] args) {
          try {
              String pathname = args[0];
              System.out.println("path name: " +pathname);
              executeScript(pathname);
          } catch (IOException e) {
              e.printStackTrace();
          } catch (SQLException e) {
              e.printStackTrace();
          }
      }
  
      static void executeScript(String scriptFilePath) throws IOException, SQLException {
          // initialize script path
          // String scriptFilePath = "e:/script.sql";
          BufferedReader reader = null;
          Connection con = null;
          Statement statement = null;
          try {
  
              Class.forName("oracle.jdbc.driver.OracleDriver");
              // create connection
              con = DriverManager.getConnection("jdbc:oracle:thin:@//hostname:1521/romdb", "user",
                      "pwd");
              statement = con.createStatement();
              // initialize file reader
              reader = new BufferedReader(new FileReader(scriptFilePath));
              String line = null;
              // read script line by line
              while ((line = reader.readLine()) != null) {
                  // execute query
                  if (line.startsWith("--")|| line.isEmpty()) {
                      System.out.println("comment line or empty line: " + line);
                  } else {
                      System.out.println("Line: " + line);
                      statement.execute(line);
                  }
  
              }
          } catch (Exception e) {
              System.out.println("*** Error : "+e.toString());  
              System.out.println("*** ");  
              System.out.println("*** Error : ");  
              e.printStackTrace();
          } finally {
              // close file reader
              if (reader != null) {
                  reader.close();
              }
              // close db connection
              if (con != null) {
                  con.commit();
                  con.close();
              }
          }
      }
  } 
  </code></pre>
  
  <p>Do you have any idea?</p>