Calling Oracle "DEFINE" from Java

Question

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 &&MYSCHEMA.TBL_NAME ...").

The problem I am having is that the DEFINE statement is throwing an error when calling it from Java (example):

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();
        }
    }
}

The error is:

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)

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?

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.

I am also hoping to get this to work with the sql-maven-plugin, but that may not be possible based on this JIRA:

Add Oracle SQLPlus syntax for substitution variables

If anyone has any suggestions or know how to get this to work, I would greatly appreciate it.

A B · Accepted Answer · 2012-07-09 20:42:30Z

6

I don't think DEFINE will work outside of SQLPLUS - JAVA uses JDBC and assumes the argument to execute() is valid SQL. If you're able to use DEFINE outside SQLPLUS it means the utility you're using is intended to be compatible with or a partial replacement of SQLPLUS.

DEFINE is an SQLPLUS command - SQLPLUS is an ORACLE utility.

According to this URL Define is not an SQL statement

http://www.adp-gmbh.ch/ora/sqlplus/define.html

answered Jul 9, 2012 at 20:42

A B

4,1581 gold badge22 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Steve Over a year ago

Thanks. I had a feeling this was the case, but I was not sure.

Justin Cave · Accepted Answer · 2012-07-09 20:49:00Z

Both DEFINE and the substitution variable syntax &&MYSCHEMA.TBL_NAME are SQL*Plus commands. They are not valid SQL or PL/SQL constructs. You won't be able to use them as-is via JDBC.

Depending on the reasons that you say you don't want to go into

Your Java application can call out to the operating system to invoke SQL*Plus and pass in the script
Your Java application can implement whatever SQL*Plus features your scripts rely on. Rather than executing DEFINE MYSCHEMA='TESTSCHEMA', your application would need to maintain, say, a HashMap that maps a variable like myschema to a value like TESTSCHEMA. Your application would then have to parse the individual SQL statements looking for text like &&MYSCHEMA, replace that with the value from its local HashMap, and then send the resulting SQL string to the database server. Depending on how much of the SQL*Plus functionality you need to replicate, that could be a reasonably significant undertaking. Many PL/SQL IDEs (such as Toad or SQL Developer) implement a subset of SQL*Plus's functionality-- I don't know of any that have tried to implement every last SQL*Plus feature.

Collectives™ on Stack Overflow

Calling Oracle "DEFINE" from Java

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related