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.

Started Development using Oracle 10g Developer suite with Issues

A lot of issues was there when I started my first Oracle form builder project. I was installed Oracle 9i Database and Oracle 10gDS (Developer suite). When I started my first project, simply I built a data block using Data block wizard and using the layout wizard I created the form view. I used the default database installed and the table employees in it. When compiled there was no problem, but when i run the project there was (not really).

When I changed the browser from Edit->Preferences->Runtime. The output was different for different browsers, i.e I tested the same with 3 major browsers, the Internet Explorer (IE6.0), Mozilla Fire Fox, Opera. The first two resulted in error. i.e something undesired output the Mozilla Fire Fox output was a blank white screen and the Internet explorer output was something strange URL and the a few lines of HTML code too was there, but the third the output was something OK, it showed the screen but there was an error Ora-12560: TNS Protocol adapter error. I Googled many times and  even hours to find a solution. Finally I found somewhere the solution to solve the problem.

It was that just copy the 3 .ora files (i.e. tnsnames.ora, listener.ora, sqlnet.ora) from Oracle database home settings to oracle developer suite settings directory, i.e. for e.g. c:\oracle\ora92\network\admin to c:\oracle\10gds\network\admin. where c:\oracle\ora92 is ORACLE_HOME.  Then restarted the services (or simply reboot the system) and it was ok. When I run the project it was asked for the database user, password ans the database. I filled all hr, hr and orcl (the SID). This time it was working everything was fine this time.

So the first issue was because there was spaces in the folder name, whether it is Internet explorer (IE) or Mozilla Fire Fox, but for opera there was no spaces in the installed path and sure in its executable file name too.

The second issue was because of some missing/invalid settings specified in sqlnet.ora, listener.ora and tnsnames.ora.

Note :- 

• Avoid spaces during installation of browsers (e.g. use InternetExplorer or IE instead of Internet Explorer). 
• Copy the files tnsnames.ora, listener.ora, sqlnet.ora from ORACLE_HOME/network/admin folder to ORACLE10GDS_HOME/network/admin folder. ORACLE10GDS_HOME is home directory for Oracle 10g Developer Suite.