Problem in connecting Oracle thru Java

[Mike Thomson]

I have installed Oracle 9i and I want to connect to it using thin driver. I have used the following code,but I am getting the below error
I don't know the default database name. How to find the default database name. The tables like emp,bonus,dept are in the default database while installing oracle 9i. How to find the db name. How can I create a new databse and give privilges? I have TOAD client and I don't know how to configure it with Oracle? Can i get any links on it?
Whether in tnsnames.ora can I able to find out the db?

public static Connection getConnection(){
Connection con = null;
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
con = DriverManager.getConnection("jdbc:oracle:thin:@mike-dm210ghop:1521:ORACLE","scott","tiger");
}catch(SQLException e){
e.printStackTrace();
}catch(Exception e){
e.printStackTrace();
}
return con;
}

INFO: Server startup in 14190 ms
java.sql.SQLException: Io exception: Connection refused(DESCRIPTION=(TMP=)(VSNNU
M=153092352)(ERR=12500)(ERROR_STACK=(ERROR=(CODE=12500)(EMFI=4))(ERROR=(CODE=125
37)(EMFI=4))(ERROR=(CODE=12560)(EMFI=4))(ERROR=(CODE=507)(EMFI=4))(ERROR=(BUF='3
2-bit Windows Error: 109: Unknown error'))))
        at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
        at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
        at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:333)
        at oracle.jdbc.driver.OracleConnection.<init>(OracleConnection.java:404)
 
        at oracle.jdbc.driver.OracleDriver.getConnectionInstance(OracleDriver.ja
va:468)
        at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:314)
        at java.sql.DriverManager.getConnection(DriverManager.java:525)
        at java.sql.DriverManager.getConnection(DriverManager.java:171)
        at com.associates.ConnectionPool.getConnection(ConnectionPool.java:16)
        at com.associates.StatusInfo.doGet(StatusInfo.java:31)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:689)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appl
icationFilterChain.java:237)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationF
ilterChain.java:157)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperV
alve.java:214)
        at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValv
eContext.java:104)
        at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.jav
a:520)
        at org.apache.catalina.core.StandardContextValve.invokeInternal(Standard
ContextValve.java:198)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextV
alve.java:152)
        at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValv
eContext.java:104)
        at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.jav
a:520)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.j
ava:137)
        at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValv
eContext.java:104)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.j
ava:118)
        at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValv
eContext.java:102)
        at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.jav
a:520)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineVal
ve.java:109)
        at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValv
eContext.java:104)
        at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.jav
a:520)
        at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:929)
 
        at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:16
0)
        at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java
:799)
        at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.proce
ssConnection(Http11Protocol.java:705)
        at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java
:577)
        at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadP
ool.java:683)
        at java.lang.Thread.run(Thread.java:595)
java.lang.NullPointerException
        at com.associates.StatusInfo.doGet(StatusInfo.java:32)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:689)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appl
icationFilterChain.java:237)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationF
ilterChain.java:157)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperV
alve.java:214)
        at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValv
eContext.java:104)
        at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.jav
a:520)
        at org.apache.catalina.core.StandardContextValve.invokeInternal(Standard
ContextValve.java:198)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextV
alve.java:152)
        at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValv
eContext.java:104)
        at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.jav
a:520)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.j
ava:137)
        at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValv
eContext.java:104)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.j
ava:118)
        at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValv
eContext.java:102)
        at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.jav
a:520)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineVal
ve.java:109)
        at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValv
eContext.java:104)
        at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.jav
a:520)
        at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:929)
 
        at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:16
0)
        at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java
:799)
        at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.proce
ssConnection(Http11Protocol.java:705)
        at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java
:577)
        at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadP
ool.java:683)
        at java.lang.Thread.run(Thread.java:595)
 

[Scott Selikoff]

Who setup your database? You should have a good idea what the name of it is.

[Mike Thomson]

I have only installed the setup and I have given the SID as ORACLE. And also in the orddata folder, I have found ORACLE.

con = DriverManager.getConnection("jdbc:oracle:thin:@mike-dm210ghop:1521:ORACLE","scott","tiger");

So db should be correct, username and password should also be correct. Becaus I am opening the sql*plus using the username scott annd password as tiger. then why iam getting this error. How can give more/admin/sysdba privilgies to myself in oracle9i.

[sai t]

I think we can't use ORACLE as a database name.To create a new database use "database configuration assistant" which is provided by Oracle where you can create a new database,configure database options in an existing database and many other.

[Paul Campbell]

Open a SQLPlus session and execute:

select value from v$parameter where name = 'db_name';

Replace "ORACLE" with the value returned.
[ November 27, 2007: Message edited by: Paul Campbell ]

[Mike Thomson]

Open a SQLPlus session and execute:

select value from v$parameter where name = 'db_name';

Replace "ORACLE" with the value returned.

Whether the retured value should be replced in TNSNAMES.ORA file:

1. Whether we should not name the db as oracle?
2. I have the following configuration in my TNSNAMES.ORA file:

    
ORACLE = 
       (DESCRIPTION = 
         (ADDRESS-LIST = 
           (ADDRESS = (PROTOCOL = TCP)(HOST = mike-dm210ghop)(PORT=1521))
         )
         (CONNECT-DATA = 
            (SEVER = DEDICATED)
             (SERVICE-NAME = ORACLE)
          )
       )   

So, Ihave to replace the ORACLE with the value returned by your query..am I right? I will do it and let you know

I have also tried to connect using ODBC bridge and it has asked for DSN name, and in TNS Service Name , I can able to see the ORACLE and I have selected it and given the username and password and when i tried "Test Connection", I got the below error:

    
Unable to Connect
SQLState = S1000
[Oracle][ODBC][Ora]ORA-12500: TNS:listener failed to start a dedicated server process

[Paul Campbell]

if the issue is not having the correct db name for your connection string.

jdbcracle:thin:@mike-dm210ghop:1521:ORACLE

the query returns the name associated with the database you are logged into... which you would use for the database name (if oracle defaulted the name to something different than ORACLE).

If you return "ORACLE" in the query, then your connect string is wrong. If you still can't connect... while the connect string is wrong... you are having another issue affecting your connection.

At the moment, don't touch your tnsnames.ora file since you are not getting a TNS naming error. I'm a little surprised by the connection refused error message... in my limited JDBC experience the error is usually something more along the lines of a reference to the network connector failing (or something like that).
[ November 28, 2007: Message edited by: Paul Campbell ]

[Mike Thomson]

I am not to resolve the problem. I have created a new db called DTSDB. And I have used the following program for getting connection

package com.example;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
 

public class ConnectionPool{

public ConnectionPool(){}

public Connection getConnection(){
Connection con = null;
try{
Class.forName("oracle.jdbc.driver.OracleDriver");

con = DriverManager.getConnection("jdbc:oracle:thin:@mike-dm210ghop:1521:DTSDB","scott","tiger");
System.out.println("con ::##############"+con);
}catch(SQLException e){
e.printStackTrace();
}catch(Exception e){
e.printStackTrace();
}
return con;
}
 
public static void main(String[] args) {
System.out.println(new ConnectionPool().getConnection());
}

}

Error I am getting:

java.sql.SQLException: Io exception: Connection refused(DESCRIPTION=(TMP=)(VSNNUM=153092352)(ERR=12500)(ERROR_STACK=(ERROR=(CODE=12500)(EMFI=4))(ERROR=(CODE=12537)(EMFI=4))(ERROR=(CODE=12560)(EMFI=4))(ERROR=(CODE=507)(EMFI=4))(ERROR=(BUF='32-bit Windows Error: 109: Unknown error'))))
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:333)
at oracle.jdbc.driver.OracleConnection.<init>(OracleConnection.java:404)
at oracle.jdbc.driver.OracleDriver.getConnectionInstance(OracleDriver.java:468)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:314)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at com.example.ConnectionPool.getConnection(ConnectionPool.java:17)
at com.example.ConnectionPool.main(ConnectionPool.java:28)
null

I have used the following command to find the db name:

SQL> Select name from v$database;
SQL> select instance_name from v$instance;
SQL> select * from global_name;
SQL> SELECT VALUE FROM V$PARAMETER WHERE NAME='db_name';
SQL> select to_number(translate(substr(version,1,9),'1.$','1')) from v$instance;
SQL> select s.machine from v$session s where s.audsid userenv('sessionid');
SQL> select global_name from global_name;

of the above only the following below commands only executed properly and given the following results:
SQL> select * from global_name;
SQL> select global_name from global_name;

    
SQL> select * from global_name;
GLOBAL_NAME
---------------------------------------------------------------------------
DTSDB.US.ORACLE.COM

    
SQL> select global_name from global_name;
 
GLOBAL_NAME
---------------------------------------------------------
DTSDB.US.ORACLE.COM

SQL> SELECT VALUE FROM V$PARAMETER WHERE NAME='db_name';
SELECT VALUE FROM V$PARAMETER WHERE NAME='db_name'
                  *
ERROR at line 1:
ORA-00942: table or view does not exist

Plese help to resolve o some other alternative way to proceed and I am hanging around with this problem with more than a week and I could not start my development work..any help..?

My tnsnames.ora File
====================

# TNSNAMES.ORA Network Configuration File: D:\oracle\ora92\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
 
DTSDB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = mike-dm210ghop)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DTSDB)
    )
  )
 
INST1_HTTP =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = mike-dm210ghop)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = SHARED)
      (SERVICE_NAME = MODOSE)
      (PRESENTATION = <a href="http://HRService%29" target="_blank" rel="nofollow">http://HRService)</a>
    )
  )
 
EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )
 
ORACLE =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = mike-dm210ghop)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORACLE)
    )
  )
 
 

My listener.ora File:
======================

# LISTENER.ORA Network Configuration File: D:\oracle\ora92\network\admin\listener.ora
# Generated by Oracle configuration tools.
 
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = mike-dm210ghop)(PORT = 1521))
      )
    )
  )
 
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = D:\oracle\ora92)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = ORACLE)
      (ORACLE_HOME = D:\oracle\ora92)
      (SID_NAME = ORACLE)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = DTSDB)
      (ORACLE_HOME = D:\oracle\ora92)
      (SID_NAME = DTSDB)
    )
  )
 
 

Do you required any other details? My machine is Windows 2000 OS and I am using Oracle 9i and Java 1.5
[ November 28, 2007: Message edited by: Mike Thomson ]

[Paul Campbell]

Are your database driver versions in sync with your database version?

You can download them here:

http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/index.html

[Mike Thomson]

While Installing Oracle 9i itself all the required jars will also be downloaded in the following folder jdbc\lib. I have taken the driver jar from that folder only. Surely, it can't have driver mismacth since the driver jar files are provided by the Oracle 9i installation

[Paul Campbell]

Just checking since a quick query of people with the same error message suggest their problem was caused by a driver mismatch.

[Mike Thomson]

Which jar files to use? I have tried with classes12.jar and also ojdbc14_g.jar. I have also have the same setup in my officw machine and I have used classes12.jar and it worked properly

There is any good chnace of firewall problem for this? I am using MCAFEE Antivirus with firewall? But I have tried by disabling the firewall also and I am couldn't get the connection.

Whether reinstalling the oracle will solve the problem?
[ November 30, 2007: Message edited by: Mike Thomson ]

[Paul Campbell]

okay... taking a step back to the message of "connection refused" and coming up with a list of things to check:

- Check host name in connect string.
- Try numeric IP address of server host in connect string, in case name server is hosed.
- Check port number in connect string.
- Are there any firewalls between client and server.
- Check that the db server is running.
- Check that the db server is listening to the port. On the server, try: "telnet localhost the-port-number". Or "netstat -an", there should be a listening entry for the port.
- Try "telnet serverhost the-port-number" from the client, to see if firewalls are blocking it.
- If "telnet" fails: does it fail immediately or after an obvious timeout? How long is the timeout?
- Does the server respond to "ping theserverhostname"?

[Mike Thomson]

I have also googled in net and after a long hours of brpowsing everyone has a problem with a firewall and I am using Mcafee Antivirus and Firewall and I have uninstalled it. And I tried and WOW!! i am not getting this error and I am able to get the connection from oracle using jdbc and thin driver.

But now I am facing a strange problem. If I am opening a tomcat server in 8080 port, it's telling JVM bind error and 8080 has been already used. So I have checked oracle http server abd stopped it in the services. But still i got the same message. Then I have stopped OracleServiceDTS(it's my newly created db service) and now I can able to start the tomcat server and everything is proper. But I want the db to be up for running the application properly.
Sometimes If I type http:\\localhost:8080\asd in the browser, a new pop window is opening and it's asking for username and password. The pop up screen tells Relam:XDB. I have scott as username and tiger as password. It entered into oracle file system.
I am suspecting in oracle also something is listening to 8080 port. How to resolve this. Even though I have resolved the previous problem. now I stuck with this problem as either oracle or tomcat only staring at a time...

[Paul Campbell]

Oracle uses 8080 and 2100 by default for XMLDB.


-- change HTTP port from 8080 to 8083
call dbms_xdb.cfg_update(updateXML(dbms_xdb.cfg_get(), '/xdbconfig/sysconfig/protocolconfig/httpconfig/http-port/text()', 8083));

-- change FTP port from 2100 to 2111
call dbms_xdb.cfg_update(updateXML( dbms_xdb.cfg_get(), '/xdbconfig/sysconfig/protocolconfig/ftpconfig/ftp-port/text()' , 2111));

-- refresh settings
exec dbms_xdb.cfg_refresh;
[ November 30, 2007: Message edited by: Paul Campbell ]

[Mike Thomson]

-- change HTTP port from 8080 to 8083
call dbms_xdb.cfg_update(updateXML(dbms_xdb.cfg_get(), '/xdbconfig/sysconfig/protocolconfig/httpconfig/http-port/text()', 8083));

-- change FTP port from 2100 to 2111
call dbms_xdb.cfg_update(updateXML( dbms_xdb.cfg_get(), '/xdbconfig/sysconfig/protocolconfig/ftpconfig/ftp-port/text()' , 2111));

-- refresh settings
exec dbms_xdb.cfg_refresh;

Do I need to run this query from SQL*plus?

[Paul Campbell]

Originally posted by Mike Thomson:

Do I need to run this query from SQL*plus?

That is how I would do it... but anything that allows you to execute PL/SQL likely should allow you to execute the foldering API (the dbms_xdb package)