Connect Oracle and Visual Studio 2008 (ASP.NET)

Here it is to connect the ASP.NET application(website) to Oracle database server. First you create a new website from File->New->Website->ASP.NET Website. Then from Tools menu select Connect to a database. Select data source as Oracle database, and .NET Framework data provider for Oracle as data provider. Then provide the necessary details to connect to the oracle database, including Server name (the SID, if the local database itself, for e.g:- orcl). Then you can test the connection, for e.g:- I filled the details as user name to hr and password to hr and finally the database orcl as Server name, then clicked the Test Connection button, I got the message test connection succeeded. Then I tested the same without providing the user name and password, I got the message ORA-01017:Invalid user name/password; logon denied. If your connection is got Ok, then you can see the SID.user (e.g.:- orcl.hr), listed in Server Explorer-> Data Connections. And the list of tables and other details you can browse from their itself.

Connect Oracle and Java

It is to connect Oracle database to java program using JDBC (Java Database Connectivity) without use of ODBC (Open Database Connectivity).

import java.sql.*;

public class OracleConn{
public static void main(String[] args) {
System.out.println("Oracle Java Example Program Showing Column/Field Names in a table");
Connection con = null;
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
String driver ="oracle.jdbc.driver.OracleDriver";
String user = "hr";
String pass = "hr";
try
{
Class.forName(driver);
conn = DriverManager.getConnection(url, user, pass);
try{
Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery("SELECT * FROM employees");
ResultSetMetaData mtdat = rset.getMetaData();
int cols = mtdat.getColumnCount();
System.out.println("Number of Columns : "+ cols);
System.out.println("Columns Name: ");
for (int i = 1; i <= cols; i++){
String col_name = mtdat.getColumnName(i);
System.out.println(col_name);
}
}
catch (SQLException s){
System.out.println("SQL statement is not executed!");
}
}
catch (Exception e){
e.printStackTrace();
}
}
}

This program connects the database to program and displays the meta data for the table employees. You need to setup the CLASSPATH if you compiling/running the program from command line. Here the database is orcl, the SID. The user name and password here is "hr". Replace the user name and password with if you are connecting to the database as any other user. Save this program in a file named OracleConn.java and Compile it using javac OracleConn.java (from command line) and Run it using command java OracleConn.

ora File contents

This is the tnsnames.ora current content (working) 


# TNSNAMES.ORA Network Configuration File: e:\oracle\ora92\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = TOSHIBA)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

INST1_HTTP =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = TOSHIBA)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = SHARED)
      (SERVICE_NAME = MODOSE)
      (PRESENTATION = http://HRService)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )


This is current listener.ora contents (working)

# LISTENER.ORA Network Configuration File: e:\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 = TOSHIBA)(PORT = 1521))
      )
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = e:\oracle\ora92)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = e:\oracle\ora92)
      (SID_NAME = orcl)
    )
  )

This is sqlnet.ora current conents (working)

# SQLNET.ORA Network Configuration File: e:\oracle\ora92\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)

Kdiff comparison result for the oracle configuration files

Here is the kdiff comparison output for the three configuration files(listener.ora,sqlnet.ora,tnsnames.ora) that lead me to lose lot of time...Here it is,

listener.ora kdiff comparison output

tnsnames.ora kdiff comparison output

sqlnet.ora kdiff comparison output

You can find out the difference in those files in old and new...the new one is working fine. so that you can make the necessary changes as needed.

Issue when Visual C++ 6.0-Oracle Connection

This time was to test Visual C++ 6.0 and Oracle 9i database connectivity. Started my MFC App Wizard -> Single document -> Database view with/without file support then to choose the data source clicked on the data source button, selected OLE DB -> Select OLE DB Datasource, Microsoft OLE DB Provider for Oracle.

First I put the server name as my system name, this case too I am testing the program on local machine, that the database and the application are on same system, so I put the server name as my system name toshiba in place of "Enter server name" in Connection tab, also entered the user name and password hr, hr and then clicked the "Test connection", but I got an error message like this,

"Test connection failed because of an error in initializing  provider. ORA-12514 : TNS : Listener could not resolve SERVICE_NAME given in connect descriptor."


I tried different combination of server name (my system name toshiba) and SID, but the same error I got every time. Finally I removed server name, i.e kept the server name box blank and then tested connection. I got the result


"Test connection succeeded"

i.e. No need of server (system name) when testing it locally. just provide the user name and password only.

Issue when Visual Basic 6.0-Oracle Connection

This time I tried to connect my Visual Basic (VB) 6.0 and VC++ programs with Oracle 9i database, first I tried with a usual concept that the data source name implies the system name. I was tested this program on a local machine, i.e. the database and the client program was on the same machine.

My VB 6.0 Connection string was this,

"Provider=MSDAORA.1 ; Password=hr; User ID=hr ; Data Source = toshiba; Persist Security Info=True".

this resulted in an error message like,

Run-time error '2147467259 (80004005)' : ORA-12514 : TNS : listener could not resolve SERVICE_NAME given in connect descriptor.

I tried much and finally I changed the Data source as the Global database SID (ORCL) instead of system name, then I got it working. thus the resulting connection string was like this,

"Provider=MSDAORA.1 ; Password=hr; User ID=hr ; Data Source = ORCL; Persist Security Info=True".

i.e. no need to specify the system/server name when testing locally, instead only provide the SID, User ID and Password in connection string.