[Solved] Problem connecting to Oracle Db via jdbc

[briglia23]

Hi.

This is my code to connect to Oracle db

Code: Select all

sUser$ = "RUBRICA"
  sPass$ = "RUBRICA"
  'sURL$  = "sdbc:mysql:jdbc:localhost:3306/stamps"
  oManager = CreateUnoService("com.sun.star.sdbc.DriverManager")
  'sURL$  = "jdbc:oracle:thin:@192.168.1.5:1521/yadb"
  sURL$  = "jdbc:oracle:thin:@oracledbracprod02pub:1521/asdbp2"
 
  oParms(0).Name = "user"
  oParms(0).Value = sUser
  oParms(1).Name = "password"
  oParms(1).Value = sPass
  oParms(2).Name = "JavaDriverClass"
  oParms(2).Value = "oracle.jdbc.driver.OracleDriver"

  oCon = oManager.getConnectionWithInfo(sURL, oParms()) 

But obtain this error

Code: Select all

ORA-12514: The listnner does not currently know of service requested in connect descriptor
The connection descriptor is : oracledbracprod02pub:1521/asdbp2

I tried to replace oracledbracprod02pub whith his ip addres but i obtain the same error.

Can you help me?

I use OOo 3.1

Thanks a lot

[FJCC]

I know nearly nothing about these things, but here are two suggestions.
Is OOo configured to use the jdbc driver? You can check this at Tools-> Options -> OpenOffice.org -> Java and then clicking of the Class Path button.
Can you connect manually to the Oracle DB? If you create a new Base file and select the Connect To An Existing Database option, do you successfully connect?

[rudolfo]

I haven't worked with OOo Base and JDBC connections, yet. But I have some experience with Java programs that have to access Oracle databases. Oracle offers a "thick" and a "thin" driver. The thick driver has some advanced features that mainly improve performance in some cases, but it requires an additional configuration setting inside an Oracle directory on each client computer that wants to access the database server.
You are using the thin driver, this works out of the box as it is implemented in pure Java. It's probably easier to start with.
The thin driver needs 3 parameters to make the connection: IP address or hostname of the database server, the port on which the Oracle service is listening (usually 1521) and the SID of the database instance. The oracle database server is separated into a part that excepts remote connections (the listener) and database instances that actually serve the data.
The listener accepts all TCP/IP requests on port 1521, looks at the SID (or the service name) and dispatches that request to the database instance that uses the specified SID.

But obtain this error

Code: Select all

ORA-12514: The listnner does not currently know of service requested in connect descriptor
The connection descriptor is : oracledbracprod02pub:1521/asdbp2

I tried to replace oracledbracprod02pub whith his ip addres but i obtain the same error.

This error message says that the initial TCP/IP connection to oracledbracprod02pub:1521 was established, because otherwise you could not get the information that the listener did something that failed. In other words the first two parameters of your connect string are okay, the problem is with the asdbp2.

Oracle versions starting from 9i (or maybe already 8i) use a so called "service name" instead of an SID. But the JDBC thin driver doesn't support "service names". so you have to use the old style SIDs with the thin driver. (The service name did not replace the SID, but the SID is still used in parallel.)

Your hostname is oracledbracprod02pub. RAC is the database cluster product line of Oracle. It might well be that the thin client can't connect to a database that uses cluster technology?

So apart from what FJCC suggested to debug the problem you will have to talk to your database administrator and need to figure out if your really got the SID and not the service name, when you got the asdbp2.

[briglia23]

Thank you for your amazing answer.

If a try to connect to Base wizard is all ok. i can import my db oracle to Base using asdbp2 like db name.

Now i try to contact the db administrator and talk whit him, but i think that if with Base's wizards works, i hope that woks with the same parameter by macro.

[briglia23]

I'm trying all possibilities but i obtain always the same error.

Running tsnping command i obtain this data:

Code: Select all

Used parameter files:
Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=oracledbracprod02.bancadibologna.dom))(ADDRESS=(PROTOCOL=TCP)(HOST=10.16.99.85)(PORT=1521)))
OK (0 msec)

echo $ORACLE_SID asdbp2  

i have tried all possibilities

Code: Select all

sURL$  = "jdbc:oracle:thin:@10.16.99.85:1521/asdbp2"
sURL$  = "jdbc:oracle:thin:@10.16.99.85:1521/oracledbracprod02.bancadibologna.dom"
sURL$  = "jdbc:oracle:thin:@oracledbracprod02.bancadibologna.dom:1521/oracledbracprod02.bancadibologna.dom"
sURL$  = "jdbc:oracle:thin:@oracledbracprod02.bancadibologna.dom:1521/asdbp2"

But i obtain alway the same error!!!!!

Please HELP

[rudolfo]

I have tested the oracle connections now with plain Base and also with a macro. For the later case I would have loved if your code had been complete. It did cost me quite a lot of time to inspect the connection, statement and result set object and to see how they need to be tied together. The following code works for me:

Code: Select all

Sub ConnectToOracle()
  Dim sURL As String
  Dim oManager As Object, oCon As Object
  Dim oStmt As Object, oResult As Object
  Dim oParms(3) As New com.sun.star.beans.PropertyValue

  oManager = CreateUnoService("com.sun.star.sdbc.DriverManager")
  sURL  = "jdbc:oracle:thin:@192.168.1.20:1521:OSID"

  oParms(0).Name = "user"
  oParms(0).Value = "theUser"
  oParms(1).Name = "password"
  oParms(1).Value = "myPassword"
  oParms(2).Name = "JavaDriverClass"
  oParms(2).Value = "oracle.jdbc.driver.OracleDriver"

  oCon = oManager.getConnectionWithInfo(sURL, oParms())
  
  oStmt = oCon.prepareStatement("SELECT sysdate, dbms_session.unique_session_id FROM dual")
  oResult = oStmt.executeQuery()
  oResult.next()
  MsgBox oResult.getString(1) & Chr(10) & oResult.getString(2)
  oResult.close()
  oStmt.close()
  oCon.close()
End Sub

I think your problem is the slash that separated the SID from the other parts of the connect string. The slash seems to be something that is only used by the MySQL driver to specify the database. Normally the exact format of the connect string should be specified in the documentation that comes with the database driver jar file for the specific vendor.
Please notice the colon instead of the slash before the SID of the database instance.
"jdbc:oracle:thin:@192.168.1.20OSID"
The message box shows the two lines for the current time on the server and for the session identifier:

2009-11-12 19:47:42.0
008F7C480001

If I change the SID in the connect string to something invalid I get a 12505 error. Seems to be only a minor difference to your 12514 error.

Code: Select all

An exception occured
Type: com.sun.star.sdbc.SQLException
Message: Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
The Connection descriptor used by the client was:
192.168.1.20:1521:INVALID

The fun part is that I get a precise indication what to put into the connect descriptor if I use a slash instead of the colon between the port and the SID.

Code: Select all

An exception occured
Type: com.sun.star.sdbc.SQLException
Message: Io Exception: Invalid connection string format, a valid format is: "host:port:sid"

Please add [solved] to the initial post, if this did finally help you out.

[briglia23]

Thanks four your reply. I solved my problem.

The user not have one sid associated to oracle db, so i used the service name and now works.

Thanks a lot

Bye bye