CODEWITHSUNDEEP
javajdbcoracle11g
Mushy
Mushy

Reputation: 2645

Oracle 11g Express Java JDBC; Database Workspace

I am experiencing some difficulty getting to the Oracle 11g Express Workspace in which resides my database.

I am able to register my JDBC Driver via Class.forName("jdbc.oracle.driver.OracleDriver") and to connect successfully via Connection con = DriverManager.getConnection("jdbc:oracle:thin:" + username + "/" + password + "@localhost:1521:xe") but not able to get into the workspace containing the database I created.

Any attempt to create a database via Statement smt = Connection.createStatement() yields a java.lang.UnsupportedOperationException.

I am not even sure if I am connected to the right place. I know when I look into the Oracle Database 11.2 XE parameters it lists the db_name as xe and I can connect to it via the sys as sysdba and using the password I established for the Oracle 11g Express database but I can't get into the workspace when I had created a database and utilized ddl and dml statements to populate it.

Perhaps I simply don't know what to do at this point because I am new to JDBC. I checked my TNSNAMES.ORA file and indeed there is an XE entry representing my Oracle 11g Express as well as other entries representing databases in Oracle. I think I am there I just don't know what to do to get to my XE database I created.

The code I am using to execute a SQL statement is as follows:

    Statement smt = null;


    try {
        smt = connection.createStatement();

        String command = "CREATE table x(y varchar(2))";

        smt.executeLargeUpdate(command);

    } catch (SQLException e) {
        // TODO Auto-generated catch block
        System.out.println(e.getMessage() + e.getCause());
    }

Upvotes: 0

Views: 3326

Answers (2)

Ankur Vishwakarma
Ankur Vishwakarma

Reputation: 31

To access the objects in your workspace you have to write the SQL statements specifying the object name with the workspace name like "workspacename.objectname".

for e.g.

If you have created your workspace and named it "XESPACE" and created a table "STUDENT" inside the workspace, then to access the STUDENT table from JDBC follow the code below.

Step 1: Register the JDBC Driver as Class.forName("oracle.jdbc.driver.OracleDriver").

Step 2: Open connection with DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:XE",
            "Username", "Password").
            where 127.0.0.1 stands for localhost.

Step 3: Now to access the table inside your workspace write the sql query as follows
            "select * from XESPACE.STUDENT"

Upvotes: 3

GoldenJam
GoldenJam

Reputation: 1500

You need the ojbdc6.jar or equivalent on the classpath. It could also be a number of other problems, so I will detail a working example in the hope it will highlight the problem.

I am using the following configuration and components:

I perform the following steps to generate a connection to an oracle xe database from a java class:

Run the Oracle XE 32 bit for windows installer providing the password 'password'. Then run services.msc to confirm that the TNS listner and the XE services are both running.

enter image description here

Then create a new java project in eclipse. Adding the ojbc6.jar to the build path as an external jar.

enter image description here

Then I run the following class (right click -> run as -> java application) to create the table:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;


public class MyOracleTest {

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("oracle.jdbc.OracleDriver");
        String username = "sys as sysdba";
        String password = "password";
        Connection conn = null;
        Statement st = null;
        try{
            conn = DriverManager.getConnection("jdbc:oracle:thin:" + username + "/" + password + "@localhost:1521:xe");
            st = conn.createStatement();
            String command = "CREATE table x(y varchar(2))";
            st.executeUpdate(command);
        }finally{
        if(st != null){
            st.close();
        }
        if(conn != null){
            conn.close();
        }
        }
    }

I then verify the existance of the table using sqlplus:

enter image description here

Upvotes: 1

Related Questions