title: Calling Oracle "DEFINE" from Java
body: <p>I need to define some variables in Oracle to be used further down our application's database installation scripts. Basically, the way our installer works now is it reads in the script files and calls each one via JDBC in Java. I need to have Oracle do the variable substitution on the database side, as there are procedures, triggers, create statements, etc. that will need to refer to them (So like "CREATE TABLE &amp;&amp;MYSCHEMA.TBL_NAME ...").</p>
  
  <p>The problem I am having is that the DEFINE statement is throwing an error when calling it from Java (example):</p>
  
  <pre><code>private static void testDefineVariables() {
      String url = "jdbc:oracle:thin:@localhost:1521:LOCALDEV";
      String username = "SYSTEM";
      String password = "manager42";
      Connection conn = null;
      Statement stmt = null;
      try {
          conn = DriverManager.getConnection(url, username, password);
          stmt = conn.createStatement();
  
          //Execute the sql
          stmt.execute("DEFINE MYSCHEMA='TESTSCHEMA'");
  
      } catch (SQLException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
      }
      finally {
          try {
  
          if(stmt != null)
              stmt.close();
  
          if(conn != null)
              conn.close();
  
          } catch (SQLException e) {
              e.printStackTrace();
          }
      }
  }
  </code></pre>
  
  <p>The error is:</p>
  
  <pre><code>java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement
  
  at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
  at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)
  at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802)
  at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436)
  at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
  at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)
  at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:194)
  at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1000)
  at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1307)
  at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1882)
  at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1847)
  at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:301)
  </code></pre>
  
  <p>I am using an Oracle 11g R2 database with the ojdbc6-11.2.0.1.0.jar JDBC driver. I am able to execute the statement successfully from the sqldeveloper tool as well as the SQLPlus console, but not from the Java application. Do I need to configure additional properties on the database driver? Can I make this type of call from this driver?</p>
  
  <p>I know some people right off the bat may suggest using variable bindings on the Java side, but this is not an option. The scripts need to be executable from both the SQL interface and the installer. There are other reasons apart from this one, which I won't go into.</p>
  
  <p>I am also hoping to get this to work with the sql-maven-plugin, but that may not be possible based on this JIRA:</p>
  
  <p><a href="http://jira.codehaus.org/browse/MSQL-52" rel="nofollow">Add Oracle SQLPlus syntax for substitution variables</a></p>
  
  <p>If anyone has any suggestions or know how to get this to work, I would greatly appreciate it.</p>