Wednesday, November 18, 2009

Trying to resolve ODP error

Getting this error
The type 'Oracle.DataAccess.Client.OracleConnection' exists in both 'c:\WINDOW\assembly\GAC_32\Oracle.DataAccess\\Oracle.DataAccess.dll' and 'c:\WINDOWS\assembly\GAC\Oracle.DataAccess\\Oracle.DataAccess.dll'

It goes on for Oracle.DataAccess.Client.OracleCommand, OracleDataAccess.Client.OracleDataAdapter

Read somewhere need to ungac one of them via
gacutil /u Policy.2.102.Oracle.DataAccess

Need to run gacutil from Visual Studio 2008 command prompt

More details how to run gacutil
I believe the version numbers changed a bit with the 10.2 release. It sounds to me like you still have the old Oracle.DataAccess.dll file referenced in the GAC. What I would try is to remove all references that are in the GAC using the gacutil that comes with the .NET Framework 2.0 SDK. If you use the .NET 1.1 gacutil, you'll get an error when trying to work with the .NET 2.0 DLL's. To remove the references, you'll have to execute something like this:

REM Remove GAC Entries
gacutil /u Oracle.DataAccess
gacutil /u Policy.9.2.Oracle.DataAccess
gacutil /u Policy.10.1.Oracle.DataAccess
gacutil /u Policy.10.2.Oracle.DataAccess

This will remove all your GAC entries, including the policies.

Then, add the references from the new ODP.NET like this:

REM Update .NET GAC (1.1 and 2.0)
gacutil.exe /i \Client\\\bin\1.x\Oracle.DataAccess.dll /f
gacutil.exe /i \Client\\\bin\2.x\Oracle.DataAccess.dll /f
gacutil.exe /i \Client\\\PublisherPolicy\1.x\Policy.9.2.Oracle.DataAccess.dll
gacutil.exe /i \Client\\\PublisherPolicy\1.x\Policy.10.2.Oracle.DataAccess.dll

where = The path to your Oracle Home

These commands will add the references to the GAC for .NET 1.1 and 2.0. Also, if you have tools built with references to the previous versions, the Policy entries will direct those references to use the latest library. You may be able to omit the install of the Policy.9.2.Oracle.DataAccess.dll if you have never used the Oracle 9.2 version ODP.NET in any of your tools.

I actually got most of this procedure from an Oracle document somewhere, but I can't seem to find it right now. It might be in an FAQ somewhere...

Finally the solution for me after a week of search and destroy
Check your references in web.config of the application, or machine.config/web.config of the machine. You have references to two versions of the dll, so remove the one you are not going to use.

Thursday, November 5, 2009

how to uninstall oracle 10g from windows xp

From :

Sunday, August 14, 2005

Oracle's installation utility has a nasty habit of leaving a lot of items behind. I wrote this up to document what I've found you need to do to uninstall Oracle 10g manually. This is specific to 10g and Windows XP. Removing 9i is very similar if I remember right, but who's using 9i anymore? ;-)
The first thing you should do, is go ahead and run the installation tool to do an uninstall. It probably will leave some things behind, but it's worth running. Then go through this list and remove anything it missed. This list is detailed enough though, that I believe even if you did not run the uninstallation tool, this would fully uninstall Oracle.
The most commonly missed item is to make sure you remove everything from the GAC because the uninstaller doesn't appear to. I get to that later, but if you wanted to know the largest source of uninstallation problems, it's libraries remaining in the GAC.
After running the supplied Oracle uninstallation utility (which may or may not do some or all of the following):
Stop any Oracle services that have been left running.

Start->Settings->Control Panel->Services

Look for any services with names starting with 'Oracle' and stop them.
Run regedit and delete the following keys (some may have slightly different names in your registry):






Note that the services control panel will still show the old services until you reboot.
Delete the Oracle home directory

Delete the Oracle Program Files directory:

C:\Program Files\Oracle
Delete the Oracle Start Menu shortcuts directory:

C:\Documents and Settings\All Users\Start Menu\Programs\Oracle*

Where * indicates the name of your install. Look for and remove all Oracle directories from that location.
Remove Oracle refereces from the path. To edit your path go to:

Start->Settings->Control Panel->System->Advanced->Environment Variables

Edit both of the environment variables user PATH and system PATH. Remove any Oracle references in them.
Remove Oracle.DataAccess and any Polic.Oracle files from the GAC which is at:

There, now your system is Oracle free. If you are installing a new instance of Oracle (and not just an additional DB) I recommend you do this before any new Oracle installation.

Note: I used several other websites and searches as references when developing these steps, but I was unable to find any steps that covered all of the items for Oracle 10g so I assembled them here

Wednesday, November 4, 2009

Problem in UTF-8 support on CSV/EXCEL

It seems like everyone is having this issue. This is very common problem and i have been looking for its solution for a long time too. I have found a solution and have tested it to make sure it works for almost all languages.

This is problem with Excel NOT the data format that is exported. Here are the steps

1- download the exported CSV/XLS file from your website.
2- Open Excel 2007
3- Open a new file
4- Click the Data Menu option
5- Click "From Text" button
6- Select the file you downloaded
7- Make sure "Delimited" is selected and Press Next
8- Check the delimiter characters that you know are in your file like Comma or Tab or whatever is in your case. You can select more than one
9- Proceed to Next Step and Finich
10- Your excel file will be ready with all you data displayed correctly

I hope that helps someone who has been frustrated on this.



Tuesday, November 3, 2009

IIS 5.1 in XP PRO SP3 (Login password Popup)

Troubleshooting HTTP 401 errors in IIS

Is there an index.htm or default.htm under C:\Inetpub\www ?
If not, put one there and see what happens.


(1) Launch inetmgr and right click on site
(2) Go to Properties
(3) Go to Directory Security Tab
(4) Click on Edit under Authentication and Access Control
(5) Select checkbox that says Enable Anonymous Access

Screen shot follows :

If this doesn't work then perhaps

Check on iisstart.asp or localstart.asp, I believe the anonymous auth is disable at file level as well


Uninstall Framework 3.5, 3.0 and 2.0 and reinstalled IIS.

That is becoz IE auto login for you, mozilla won't do that.

So again make sure you enabled anonymous authentication and ensure the resource file can be read by the anonymous account, which is iusr_computername by default.


Create a drop-down list from a range of cells

Process to convert tab delimited to excel then back to tab delimited

(1) Open tab delimited file in excel
(2) Specify file origin as 936:Chinese Simplified (GB2312)

(3) Specify tab Delimited

(4) Click Finish

(5) After finishing with editing, save as Unicode Text

Oracle Listener lsnrctl commands

From Burleson Consulting

The following is from the bestselling book Oracle Utilities: Using Hidden Programs, Import/Export, SQL Loader, oradebug, Dbverify, Tkprof and More today and receive 30% off plus immediate access to the Online Code Depot!

Here we see the lsnrctl command in action:

$ lsnrctl

LSNRCTL for Solaris: Version - Production on 30-JAN-2003 11:54:13

(c) Copyright 1998 Oracle Corporation. All rights reserved.

Welcome to LSNRCTL, type "help" for information.


The following lsnrctl operations are available

An asterisk (*) denotes a modifier or extended command:

start stop status

services version reload

save_config trace spawn

dbsnmp_start dbsnmp_stop dbsnmp_status

change_password quit exit

set* show*


The following commands are used to manage the listener:

· start – Starts the listener with the name specified, otherwise LISTENER will be used. For Windows systems, the listener can also be started from the Control Panel.

· stop – Stops the listener. For Windows systems, the listener can also be stopped from the Control Panel.

· status – Provides status information about the listener, including start date, uptime, and trace level.

· services – Displays each service available, along with the connection history.

· version – Displays the version information of the listener.

· reload – Forces a read of the configuration file in order for new settings to take effect without stopping and starting the listener.

· save_config – Creates a backup of the existing listener.ora file and saves changes to the current version.

· trace – Sets the trace level to one of the following – OFF, USER, ADMIN, or SUPPORT.

· spawn – Spawns a program that runs with an alias in the listener.ora file.

· dbsnmp_start – Starts the DBSNMP subagent.

· dbsnmp_stop – Stops the DBSNMP subagent.

· dbsnmp_status – Displays the status of the DBSNMP subagent.

· change_password – Sets a new password for the listener.

· quit and exit – Exits the utility.

· set – Changes the value of any parameter. Everything that can be shown can be set.

show – Displays current parameter settings.

Monday, November 2, 2009

Oracle 10g Installation Notes

Your database configuration files have been installed in C:\oracle\product\10.2.0 while other components selected for installation have been installed in C:\oracle\product\10.2.0\db_1. Be cautious not to accidentally delete these configuration files.

The iSQL*Plus URL is:


The iSQL*Plus DBA URL is:


Configuring Microsoft LoopBack Adapter

From Lazy Dba Site

This is how you install the loopback adapter. Try it. It worked well
fine for me !!

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Installing a Loopback Adapter on Windows 2003 or Windows XP

1. From the Start menu, select Control Panel.

2. Double-click Add Hardware to start the Add Hardware wizard.

3. On the Welcome screen, click Next.

4. On the Is the hardware connected? screen, select Yes, I have already connected the hardware, and click Next.

5. On the The following hardware is already installed on your computer screen, select Add a new hardware device, and click Next.

6. On the The wizard can help you install other hardware screen, select Install the hardware that I manually select from a list, and click Next.

7. From the list, select the type of hardware you are installing screen, select Network adapters, and click Next.

8. On the Select Network Adapter screen, make the following selections: Manufacturer: select Microsoft.
Network Adapter: select Microsoft Loopback Adapter.

9. Click Next.

10. On the The wizard is ready to install your hardware screen, click

11. On the Completing the Add Hardware Wizard screen, click Finish.

12. If you are using Windows 2003, restart your computer.

13. Right-click My Network Places on the desktop and choose Properties.
This displays the Network Connections control panel.

14. Right-click the connection that was just created. This is usually
named "Local Area Connection 2". Choose Properties.

15. On the General tab, select Internet Protocol (TCP/IP), and click

16. In the Properties dialog, do the following:
IP Address: Enter a non-routable IP for the loopback adapter. Oracle
recommends the following non-routable addresses:
192.168.x.x (x is any value between 1 and 255)

a. Subnet mask: Enter
b. Leave all other fields empty.
c. Click OK.

17. Click OK.

18. Click OK in the Local Area Connection 2 Properties dialog.

19. Restart the computer.

20. Add a line to the C:\windows\system32\drivers\etc\hosts file with
the following format, after the localhost line:
IP_address hostname.domainname hostname
IP_address is the non-routable IP address you entered in step 16.
hostname is the name of the computer.
domainname is the name of the domain.
For example: mycomputer

21. Check the network configuration:
Open System Properties, and select the Computer Name tab. In Full
computer name, make sure you see the hostname and the domain name.

Click Change. In Computer name, you should see the hostname, and in Full
computer name, you should see the hostname and domain name.

Click More. In Primary DNS suffix of this computer, you should see the
domain name.


Free SFTP, FTP and SCP client for Windows

Sunday, November 1, 2009

Thursday, October 22, 2009

Convert VB.NET to C#

Convert From VB.NET to C#

ODP.NET Developer's Guide

A good book. Examples in Visual Basic. Can be converted to c# quite easily.

ODP.NET Developer's Guide
Oracle Database 10g Development with Visual Studio 2005 and Oracle Data Provide for .Net

A practical guide for developers working with Oracle Data Provider for .Net and the Oracle Tools for Visual Studio 2005

Jagadish Chatarji Pulakhandam
Sunitha Paruchuri

PACKT Publishing

Tuesday, October 13, 2009

Using the new ODP.Net to access Oracle from C# with simple deployment
Still tracking comments as of 13-Oct-2009

Microsoft has deprecated System.Data.OracleClient, so here's a simple example of using the new ODP.NET Oracle.DataAccess.Client, with the following benefits:
* Simple Xcopy deployment
* No Oracle [instant] client installation needed
* No TnsNames.Ora file needed

Firstly, download the ~200mb ODP.Net from Oracle. Don't freak out, you won't need to redistribute *all* this with your app, just ~30megs worth. I used this version:'ll probably want to get the latest version from here:

Grab Oracle.DataAccess.dll from\stage\Components\oracle.ntoledb.odp_net_2\\1\DataFiles\filegroup4.jar. Copy it into the same folder as your project's Program.cs. In Visual Studio, right click references and add the Oracle.DataAccess.dll.

Write some code like this in your app:

using Oracle.DataAccess.Client; // This goes up the top
string connstring =
"(CONNECT_DATA=(SID=mysid)));User Id=myuserid;Password=mypassword;";
using (OracleConnection conn = new OracleConnection(connstring))
string sql = "select distinct owner from sys.all_objects order by owner";
using (OracleCommand comm = new OracleCommand(sql, conn))
using (OracleDataReader rdr = comm.ExecuteReader())
while (rdr.Read())

You'll then need the following dll's to be placed in the same folder as your EXE:
* oci.dll (called 'oci.dll.dbl' in\stage\Components\ oracle.rdbms.rsf.ic\\1\DataFiles\filegroup2.jar)
* Oracle.DataAccess.dll (in\stage\Components\ oracle.ntoledb.odp_net_2\\1\DataFiles\filegroup4.jar)
* oraociicus11.dll (in\stage\Components\ oracle.rdbms.ic\\1\DataFiles\filegroup3.jar)
* OraOps11w.dll (in\stage\Components\ oracle.ntoledb.odp_net_2\\1\DataFiles\filegroup3.jar)

You may need the following dll's, but I didn't. I'd get them anyway, just to be safe, as some people say they're needed:
* orannzsbb11.dll (in\stage\Components\oracle.ldap.rsf.ic\\1\DataFiles\filegroup1.jar)
* oraocci11.dll (in\stage\Components\ oracle.rdbms.rsf.ic\\1\DataFiles\filegroup3.jar)
* ociw32.dll (called 'ociw32.dll.dbl' in\stage\Components\ oracle.rdbms.rsf.ic\\1\DataFiles\filegroup2.jar)

If you get the exception 'The provider is not compatible with the version of Oracle client', don't stress, simply retrace your steps and make sure you get ALL those DLL's from the same ODP.Net / ODAC distribution to avoid version number conflicts, and put them all in the same folder as your EXE.

If you download a newer version of ODP.Net, the jar files that enclose the dll's may have moved, so you'll have to manually search through them all, don't worry it won't take *too* long!

Good luck!


Don't forget to rename the ".dll.dbl" files to ".dll"; otherwise you'll get the exception.Thank you Chris. This was very helpful.

Thankyou Chris, this solution is great and works really well. In fact, my multithreaded app (which does a LOT of queries) performs 57% faster when using your redist pack


Hi Chris, thanks for the tip. this is really great.

I do however have a question, we only include the dll and not referencing it through the project, i.e. but instantiate it using the System.Data.provider factories, then we do need to use gacutil to install it in the global assemblycache. Is that correct?

SO as an example if I refer this dll in my config as:
Now if I call using the .NET standard classes (System.Data), I can instantiate the oracle connection/command etc by getting its provider factory through below call:


where _dbName just some variable of the database name.

However, this means that the DLL stills needs to be installed in the GAC using gacutil.exe.
THanks in advance for reading ths.

Hi 'Kts',I believe you're on the right track there. That does give you the flexibility to use the same code for any database (sql, oracle, postgres, etc). But i think you'd then have to work with the System.Data.Common.* types (eg System.Data.Common.DbConnection). These types are a bit more generic and may have less of the oracle-specific data types, however if you can get it to work then its a great idea!Also, i'm personally not a fan of the GAC but if it works for you then go for it.


Hi ChrisThanks for the informative write up. I downloaded the XCOPY version of Oracle 11g ODAC and with that the only large file is the oraociei11.dll which is 114MB and looks like that is reqd in the EXE folder. Did you notice the same thing ?

Hi Sunit,I wasn't aware of an XCOPY version. Give it a try with only that file, maybe it'll work? Let us know if it works.114megs is pretty big though, the files i described above are only ~30megs.Still, maybe it supports features that my bare-bones method doesn't. Could be worth remembering!Cheers

Using Microsoft .NET and C# with Oracle 9i

Oracle provides a software tool called ODP.NET that allows connectivity between the .NET languages and an Oracle database. This interface is a set of tools that allow the creation of .NET objects that connect directly to Oracle databases. Learn the basics of connecting to and performing simple queries to an Oracle database.


Let’s face it -- neither Microsoft nor Oracle really wants to see their two flagship products work together. Microsoft would rather see a programmer use C# with SQL Server, Access, or just a plain XML data file before connecting to an Oracle data source. Oracle, on the other hand, has committed firmly to the J2EE development system for major development involving their databases.

However, Oracle does provide a software tool called ODP.NET that allows connectivity between the .NET languages and an Oracle database. This interface is a set of tools that allows the creation of .NET objects that connect directly to Oracle databases. This, at the very least, allows applications to connect to and make use of the power and capability of an Oracle database. It includes support for regular SQL queries and updates, stored procedures, and reading data from an Oracle record set into a .NET DataSet object, among other things. This article will cover the basics of connecting to and performing simple queries to an Oracle database using this set of objects.

First we’ll look at the set-up required to perform these tasks. If you are going to work with ODP.NET in ASP.NET applications, you will, obviously, need a web server with IIS and the .NET Framework installed and running. I will not be covering the steps needed to set up an IIS Web Application, as I will be focusing on actually working with the database.

If you are only working on a stand-alone application, you will just need the .NET Framework installed. In both cases, you will probably want some sort of development environment to allow easy editing of .NET code. Next, you will need to install the ODP.NET data provider on the web server if you are using ASP.NET or on your local machine if you are writing a stand-alone app. Also, each computer that will access the database as a client will need the Oracle client software installed. ODP.NET is a client-side library, and will need to be installed with your application if you are thinking of distributing your application widely. One of the nice things about ODP.NET is that it doesn’t require any extra configuration of the Oracle server. You can download the ODP.NET driver from the Oracle website at:

The first important thing to recognize about ODP.NET for Oracle 9i is that it contains two namespaces, first Oracle.DataAccess.Client. This contains the actual working classes for connecting to and acting on the Oracle database. The second namespace is Oracle.DataAccess.Types. This namespace has all the classes and methods required when working with specific Oracle data types. In this article, we won’t deal with the Types namespace at all. There are several classes to take note of in Oracle.DataAccess.Client and these are:

OracleConnection -- The basic class for connecting to a database

OracleCommand -- A class that represents a database command, either a text query or a stored procedure

OracleDataAdapter -- This class allows the programmer to pipe a returned Oracle record-set into a .NET DataSet

OracleParameter -- This class represents all the attributes and information in a stored procedure parameter

OracleDataReader -- This class represents a simple, read only data set, useful for quickly getting small, simple results, or data you will not want to change

Each of these classes will become very important in the future when we look a connecting to and working with an Oracle database.

Connecting to a Database

The process for connecting to an Oracle database is very straightforward, especially for anyone who has worked with the .NET database connection classes for SQL Server. Oracle basically copied the same class structure that Microsoft used in the SQL Server connection classes already present in .NET. This makes moving over from SQL Server to Oracle relatively easy. It also means that a lot of examples describing database interaction with SQL Server can easily be modified to work with Oracle. In any case, here is a quick overview of the steps required to connect to an Oracle database.

When working with a database, we must create an OracleConnection object to retain all information for communicating with the database. First, we import the required assemblies.
using Oracle.DataAccess.Client; using System.Data; Next, we create the connection string with the user credentials information. In this case, the Oracle schema is user1 the password is mypass and the Oracle database name either in the tnsnames file or in the Oracle Internet Directory is db.

String connString = “User id=user1;Password=mypass;source=db”;

Now, the connection object, conn, is created using the connection string.

OracleConnection conn = new OracleConnection(connString);

The last step is to begin communication with the database using the Open() method.


This creates a connection to a database and opens it. The most important part is making sure the connection string is correct. Each different type of database uses a different connection string and the differences in syntax can be a problem if you don’t pay close attention to them.

Creating a Command

In this section, we look at creating the main workhorse class and its attributes and capabilities. The OracleCommand class has two main functions. First, you can give it a simple SQL query string and execute that command on the database. Secondly, you can use the OracleCommand object to execute a stored procedure on the database.

The query string is the simplest use of this command and we will look at it first.

First, create the command by using the factory method CreateCommand() method on the OracleConnection object.

OracleCommand cmd = conn.CreateCommand();

This creates a command object attached to the conn connection. Alternatively, the command object can be created using its own constructor and then set the Connection field later:

OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;

After creating the command, we must set the CommandText attribute to give the object the actual string containing the SQL query it will run on the database. The code below also sets the CommandType attribute, which selects whether CommandText is a text command or the name of a stored procedure (this defaults to Text):

String query = “select * from users”;
cmd.CommandText = query;
cmd.CommandType = CommandType.Text;

The last step to complete is to run the command on the database and catch the results in an OracleDataReader object.

OracleDataReader reader = cmd.ExecuteReader();

This executes the command on the database and puts the results in reader. There is an important distinction between queries that read data and those that only update data. When your command reads data, you use the ExecuteReader() method to get this data back. However, when all your command does is insert or update data with nothing meaningful to return, you would use the ExecuteNonQuery() method which we will cover later.

The other mode that OracleCommand can operate in is for executing a stored procedure. This process works somewhat differently than when executing a straight text command.
Everything is the same as above until we set the command text string. In this case, the CommandText attribute is set to the name of the stored procedure. In the following case, this is a stored procedure to get back users in a certain range of user_id’s, called GetUsers. Notice also how the following code sets the CommandType attribute to StoredProcedure:

cmd.CommandText = “GetUsers”;
cmd.CommandType = CommandType.StoredProcedure;

After setting the stored procedure name and command type, we must add parameters to the OracleCommand object. This involves creating multiple OracleParameter objects and adding them to the OracleCommand’s Parameter member. This is done in the following manner:

cmd.Parameters.add(new OracleParameter(“start_user”,OracleDbType.Int, 202));

This code creates a parameter to the stored procedure GetUsers called start_user which is of type Int and has the value 202. This object is then added to the OracleCommand’s Parameters collection that stores all of the different parameters to the stored procedure. There are several things to note here. First of all, when creating multiple parameters, the number of parameters must match the number of parameters expected by the stored procedure. It is also a good idea to match up order so that parameter objects are added to the Parameters collection in the same order that they appear in the stored procedure definition to make debugging easier.

After adding the second parameter, ExecuteReader is called to get the results from the database.

cmd.Parameters.add(new OracleParameter(“end_user”, OracleDbType.Int, 210));
OracleDataReader reader = cmd.ExecuteReader();

This has shown the basics of reading data out of the database. The next step is to take that data and turn it into a .NET DataSet that can be bound to a data driven control or iterated over and changed to later update the database.

Creating a DataSet from and Oracle Database

The first step to creating a DataSet from an Oracle database is to make use of a new secondary class, called the OracleDataAdapter. This class basically takes the data as it comes back from Oracle and parses it into a .NET DataSet. Another useful characteristic of this class is that it takes the place of an OracleCommand object. The OracleDataAdapter contains the command string and parameter objects required to execute a command (either text or stored procedure) on the database. There are several ways to create an OracleDataAdapter object. However, the one shown below uses an OracleConnection object and a string to become the Select command.

OracleDataAdapter adapter = new OracleDataAdapter(conn, “select * from users”);
DataSet set = new DataSet();
adapter.fill(set, “users”);

After creating the OracleDataAdapter we create a DataSet object to take the results of the select statement. Then, we use the fill method to populate a DataTable object named “users” inside the DataSet with the data returned from the select statement. Now, you can do all the fun, nifty things you could always do with a DataSet, such as binding it to data driven controls, modifying the data and putting it back into the database. The OracleDataAdapter also contains data members to hold insert, update, and delete commands, so the object can even automate maintaining data in the database in the same state as the DataSet in memory. However, those functions are beyond the purview of this article.

Final Words

Overall, the ODP.NET class framework follows many of the normal conventions that any database programmer finds in other such frameworks. This makes is relatively easy to learn and pick up on for the experienced database programmer, and even for the novice, the classes are laid out in a logical way that makes understanding each ones function easy.