Solved

Client application for Oracle db!!!

Posted on 2002-04-21
38
629 Views
Last Modified: 2010-05-18
hello expert!
i have Oracle db, and i want to make reports, and applications etc. using visual basic, but when i try to exec the application in computer that doesn't have Oracle client install it is doesn't work.
please help me, what to do that my application will be work on any machine, and connect to oracle db without install oracle client???
please........
0
Comment
Question by:aweigh
  • 14
  • 11
  • 6
  • +3
38 Comments
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
you need the client installation of Oracle. You may give a try to the driver given by Microsoft installed with ADO. See the connection string at http://www.able-consulting.com/ADO_Conn.htm#OLEDBProviderForOracleFromMicrosoft
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
There is no way around it, you need to install the Oracle client.

Anthony
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Eric,

Even with the Microsoft driver (MSDAORA) this is not feasable.

Anthony
0
 
LVL 3

Expert Comment

by:sridhar_PJ
Comment Utility
0
 
LVL 3

Expert Comment

by:sridhar_PJ
Comment Utility
0
 

Author Comment

by:aweigh
Comment Utility
Where i can download this file - mitora732.exe??
0
 

Author Comment

by:aweigh
Comment Utility
Where i can download this file - mitora732.exe??
0
 

Author Comment

by:aweigh
Comment Utility
And how it solve my problem, please explain more!
0
 
LVL 3

Expert Comment

by:sridhar_PJ
Comment Utility
Hi,
Link is there, just follow the link. Have u checked the micorsoft link
Regards
Sridhar
0
 
LVL 3

Expert Comment

by:sridhar_PJ
Comment Utility
Hi,
You can download the client from

http://www.cu.edu/oraweb/windows_instructions.htm


Go to
Download  the distribution media section.
download the client. But the size will be very big. If you have original version of oracle then you must be having oracle driver in that CD.
Regards
Sridhar
0
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
mitora632.exe looks like a wrapper setup of the Oracle client utilities (it says: "Oracle 7.3.2 Client Software will be installed by default in your Program Files directory on your startup disk")
0
 

Author Comment

by:aweigh
Comment Utility
Sorry, but i couldn't download the file mitora732.exe?
i don't know what is : Kerberos name,Kerberos password,MIT ID Number from where i took them???
0
 

Author Comment

by:aweigh
Comment Utility
dir sridhar_PJ,
are you mean that i need to install this, on every machine ???
0
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
you will need to install oracle client to all computers. one way not to need to install it is to create a COM component and run this component on a server. this component will need the oracle client and will be the only one to talk to the database. you will need DCOM or MTS/COM+ to use your component from your app.
0
 
LVL 3

Expert Comment

by:sridhar_PJ
Comment Utility
Hi,
as emoreau says, you have to install the clients in all machines.
Regards
Sridhar
0
 
LVL 18

Expert Comment

by:mdougan
Comment Utility
I disagree with all those who say you have to install the oracle client on all machines.....

Licensing issues aside (meaning that it may not be legal for you to do what I am suggesting) you definitely can do this by making the appropriate registry entries on each client that will point to a network drive that has the oracle client installed.

There are a number of steps necessary.  First, install the Oracle client on a network drive.  Take note of the registry entries that are made by oracle during the install (several monitoring tools can help you discover the difference between your registery before and after the install).  Then, create a program that will make these same registry entries on each client.  You will need to run this registry entry making program on each client.

Put an icon on the client's desktop or start menu that points to a .CMD file.  A CMD file is like an old DOS .BAT file, except that it runs under Windows and not DOS.  In that .CMD file, first set the user's path to append the oracle binary's directory in front of the rest of the user's path, then run your EXE.  This will cause the user's path to be modified only within the context of the running of this EXE.  Any commands to open an oracle database will then follow the path to the oracle binaries on the network drive and run them as if they were installed locally.

Now, some issues.  In the path statement, you can use the UNC names like :

Path = \\myoracleserver\Oracle\bin + %path%
\\MyAppServer\MyPgm.EXE
(or whatever the proper syntax is)

And this will be nice because the clients don't have to map a drive to a specific drive letter.  But, using UNC names takes much longer to resolve the address and can really slow down running your program.  So, you can optionally have all users map a specific drive, say O to the myoracleserver computer, then you can change your .CMD file to something like:

Path = O:\Oracle\bin + %path%
S:\MyPgm.EXE
(or whatever the proper syntax is)

This will run much faster.

Also, loading your Oracle binaries off of a network computer will take much longer than loading them off of the local machine.  So, you can expect that it will take you 20 seconds or longer to first connect to the database.  Once connected, you will not notice any, or much degradation in speed, but that first delay is significant.

Also, you must install whatever provider you plan to use locally (on the client).  If you want to use the Oracle ODBC driver, you'll have to figure out how to install that without installing the entire Oracle client on the user's machine.  If you want to use the Microsoft OleDB provider for Oracle, you probably just need to have MDAC installed.

Now, one final thing, there will be a file in the Oracle Binarys somewhere that indicates which is the "Oracle Home" directory to use for this application.  Since this is going to exist on the network drive, all clients have to have that Oracle Home directory defined in their registries (that is part of what your program will do when it makes the registry entries on the client machines.  This is fine if no client currently has any Oracle Client installed, but, if there are some users who do already have the Client installed, then they may have a different database defined as their OraHome01 (or whatever the network file specifies).  

What we had to do was to rename their OraHome01 to the next available OraHomenn number, then put our Home definition in the OraHome01 spot, then increment the registry count of number of Oracle Home directories.

That process is further complicated if you have some people with Oracle 8x clients and some with Oracle 7 or less clients, as Oracle 7 didn't support the definition of multiple Oracle Home directories, so, you'd have to replace their current definition with your application's definition which might break other Oracle applications they have.

Whew... a lot of work.  But, it can be done.  I believe that I still have the code for the registry update program and possibly a document describing this whole process.  I'll look for it and post it if any of you provide an e-mail.  That is, if you're interested in this approach.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
mdougan,

>>I disagree with all those who say you have to install the oracle client on all machines..... <<
I think we are talking semantic differences.  I have no doubt your method works, it just requires the client to be installed somewhere on the network.

Anthony
0
 
LVL 18

Expert Comment

by:mdougan
Comment Utility
OK, agreed, you absolutely need one Oracle client installed somewhere, but not necessarily on each user's machine.

Generally, when someone asks this question, what they are really asking is how to keep from having to install the oracle client on each user's machine...  :)
0
 

Author Comment

by:aweigh
Comment Utility
you absolutely right, mdougan!!
how i keep from having to install the oracle client on each user's machine?
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 18

Expert Comment

by:mdougan
Comment Utility
Well, I went through a lengthy explanation above. If you want a more detailed explanation, you'll need to post an e-mail address and I'll try to find the Word Document that I wrote to detail the steps involved.
0
 

Author Comment

by:aweigh
Comment Utility
dear mdougan
please send to arie76@hotmail.co.il
thanks.
0
 
LVL 18

Expert Comment

by:mdougan
Comment Utility
OK, I sent off a couple of documents and the sample program for doing the registry settings.  You'd have to modify the settings to match your environment, but it should give you a pretty good idea what you have to do.
0
 

Author Comment

by:aweigh
Comment Utility
Sorry everybody,
I decide to install the oracle client (because the registry is too...) but it's big. so, which component i need to choose (in "custom installation")
that my vb application will be work?
i mean i don't want to install all the oracle client, just what my program need to work.
in my application i use with "OLEDB provider for oracle"

and again, i'm very sorry.
i'm new programmer in oracle
so, please help me!
0
 
LVL 18

Expert Comment

by:mdougan
Comment Utility
Well, in the Oracle Installer, they have a Tree-like control.  If you can list for us each option in the tree, I can tell you what you need.  But, it's impossible for me to do by memory alone.  Basically, you need something called SQLNet which is the network layer between your program and Oracle.  Since you're not needing the Oracle ODBC driver, that saves a lot.  But, even SQLNet comes in several pieces.  So, I'd need to see what the tree looks like before I could say for sure.

 
0
 
LVL 3

Expert Comment

by:sridhar_PJ
Comment Utility
Hi,

i already posted a link
http://www.cu.edu/oraweb/windows_instructions.htm

how to install oracle cleint on windows
Regards
Sridhar
0
 
LVL 18

Expert Comment

by:mdougan
Comment Utility
ps aweigh,  yes, the registry is confusing, but it turns out not to be too difficult, especially with the program I sent you.  If you change your mind, let me know and I'll walk you through the registry stuff.....
0
 

Author Comment

by:aweigh
Comment Utility
look mdougan,
i use windows2000, the server is windows2000 server,
but the other machine are windows98 .
so, which registry i need to take from where to where?
and, i need that you tell me how to change the registry step by step?

actually, i go to try emoreau suggest with DCOM!
0
 
LVL 18

Expert Comment

by:mdougan
Comment Utility
emoreau's suggestion would be a lot less trouble (actually, if you have Windows 2000 server, you should be using COM+ not DCOM).  This would mean that the server only needs to have the oracle client installed.  however, it means that you have to structure your application to pass the data from the client to the middle-tier component and then let the middle-tier component do any updates etc.  This is not too difficult using disconnected recordsets, but passing recordset objects as parameters between different tiers is somewhat inefficient.

I doubt that there are any differences between the registry entries for a Windows 2000 client and a Windows 98 client.  So, the same program that I gave you could be used for both.

You don't have to set the server's registry.  The server's registry will be set automatically when you install the Oracle client there.  However, it doesn't matter because the clients will always use their own registry and not the server's registry when accessing the database.

So, you simply have to run the program I sent you on each machine where you want to install your program.  Now, the program I sent you assumes that each client computer will "map" a drive to the network server where the oracle client is installed, that is what the textbox with the letter K in it is for.  This is because mapped drives are faster than using UNC names like \\myoracleserver\  also, in the code, I've hardcoded the server and directory names that were used by my company.  You'd have to change those hard-coded paths to the ones for your company.

If this is too confusing for you, then maybe the DCOM/COM+ is your best bet!  Good Luck!
0
 

Author Comment

by:aweigh
Comment Utility
dear mdougan, your program doesn't work even i change the picturebox with ms flexgrid. so, if you can tell me how to change the registry, i mean, which registry to export, which folder on the server to map, and etc.
it's heard very interesting, and i want do it,
please help me!
0
 
LVL 18

Expert Comment

by:mdougan
Comment Utility
Yes, in looking back at the code, I'm using the tree-like functionality of the VideoSoft Flexgrid that you probably can't replace with a regular flex grid.

First, let me explain the program, becuase while certain features may not work for you, with a little editing, it can still do everything you need.  

The button GetAllSettings goes out to get all settings in the registry under the HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE registry key (as this is what is selected in the combo and source tree textbox).  I used this to see if a client machine already had the oracle client installed and if I was going to have to move any registry settings to get my app to work.  You can just run RegEdit and search for that registry folder yourself.

What I was really looking for was a folder called:
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ALL_HOMES

If it wasn't there, then I was free to click the Register button.  If it was there, then the user already had the oracle client installed, and I was going to have to move one of their registry settings.  See, my application wanted to use:
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ALL_HOMES\ID0
and
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0

If these were already in use, then I would type in the Source Tree textbox:
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ALL_HOMES\ID0
and in the Dest Tree textbox:
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ALL_HOMES\ID1

And then click the Copy Tree button.  This would copy the values from the ID0 folder to a new ID1 folder.  Then, I'd do the same with putting this in the Source Tree:
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0
and in the Dest Tree textbox:
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME1

and then click the Copy Tree button.  This would copy the values from the HOME0 folder to a new HOME1 folder.  Then, I would manually go into Reg Edit and change the value in this key:

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ALL_HOMES\HOME_COUNTER
from 1 to 2

Now, I was free to click the Register button which would overwrite any entries in the ID0 and HOME0 folders with the values for my application.  If you look at the code under the Register button, then all of that code will still work for you, it's just that you will need to change the hard-coded paths to match your server names.  The code is very good because it looks to see if entries are there, and if it needs to change them, it does, if not, it doesn't.  I'll list the entries that need to be made, and indicate which you need to edit for your environment:

The first section was to set the ORACLE ODBC driver information.  If you're not using this driver, you can ignore this section.

HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC DRIVERS
Oracle ODBC Driver key should be set to "Installed"

HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\Oracle ODBC Driver
APILevel key should be set to "1"

HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\Oracle ODBC Driver
ConnectFunctions key should be set to "YYY"

HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\Oracle ODBC Driver
CPTimeout key should be set to "60"

HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\Oracle ODBC Driver
Driver key set to something like "F:\MYAPP\ORACLE\BIN\SQORA32.DLL"
(Where F is a drive mapping that all client machines have to the computer where you have actually installed the oracle client)
Or, if you don't want to use drive mappings:
Then "\\MYSERVER\MYAPP\ORACLE\BIN\SQORA32.DLL"

From now on, I'll use the drive mapping syntax, but you can always change the F:\ to \\MYSERVER\

HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\Oracle ODBC Driver
DriverODBCVer key set to "03.00"  (or whatever your version is)

HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\Oracle ODBC Driver
FileUsage key set to "0"

HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\Oracle ODBC Driver
Setup key set to "F:\MYAPP\ORACLE\BIN\SQORA32.DLL"

HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\Oracle ODBC Driver
SQLLevel key set to "1"

OK, now these settings you'll need even if you are not using the ODBC driver:

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
inst_loc key set to "F:\MYAPP\ORACLE\Inventory"

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ALL_HOMES
DEFAULT_HOME key set to "DEFAULT_HOME"

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ALL_HOMES
HOME_COUNTER key set to "1"  (unless they already had a home that you moved in which case it's the count of homes that they have)

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ALL_HOMES
LAST_HOME key set to "0"

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ALL_HOMES\ID0
NAME key set to "ORAHome81" (can be set to anything as long as it's the same for all clients, and should match what was installed as the Home0 name on the Server)

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ALL_HOMES\ID0
NLS_LANG key set to "NA"

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ALL_HOMES\ID0
PATH key set to "F:\MYAPP\ORACLE"

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0
NLS_LANG key set to "AMERICAN_AMERICA.WE8ISO8859P1"

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0
ORACLE_GROUP_NAME key set to "Oracle - ORAHome81" (again, it probably doesn't matter, as long as this is the same on all clients)

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0
ORACLE_HOME key set to "F:\MYAPP\ORACLE"

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0
ORACLE_HOME_KEY key set to "SOFTWARE\ORACLE\HOME0"

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0
ORACLE_HOME_NAME key set to "ORAHome81" (again, match as above)

That's it.  All of this assumes that you have already installed the Oracle Client on the server \\MYSERVER in a subdirectory called MYAPP, so that the path to the Oracle directory is:

\\MYSERVER\MYAPP\ORACLE

Or, that you have mapped all user's F drive to the MYSERVER computer so that this is a valid path:

F:\MYAPP\ORACLE

Now, once you have gotten that far.  You can run your application on the client.  However, the Oracle directory will need to be in the user's Path statement.  You can edit each user's environment variables and add the Oracle directories to the Path, or, you can build a command file as I'd mentioned before.  I'll leave it here for now, and clear up any registry questions before we move on to Path questions.....

Look this over and let me know what questions you have.
0
 

Author Comment

by:aweigh
Comment Utility
thank you mdougan, but i want to change the registry by myself. so tell me if i right, i export the registry
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE , and map the folder that are using in this registry, and change into the registry to the map driver,and import the registry in the other machine and that all! (it's doesn't metter from which pltform i take the registry and to which platform i import)
is this true?
0
 

Author Comment

by:aweigh
Comment Utility
dear mdougan, i'm realy sorry, but i did all what you say except the ODBC, and i'm still accept the oraclke client error. i map the network folder, and i was export the oracle registry for windws98 (from win2000 server), and the change all the path in the registry file, and then import this file on windows98, and it's still doesn't work. please help me, what to do?
0
 
LVL 18

Accepted Solution

by:
mdougan earned 200 total points
Comment Utility
You are right, that by exporting the registry and importing the keys on the other machine, then editing them for the proper path should take care of your registry entries.  That's a good technique that I wish I'd used!

OK, so, now we just have to figure out the rest.  First of all, on the server where you installed the Oracle client.  Can that computer connect to the database?  So, if you go into SQL Plus, can you connect and run queries?  If not, then you probably have not configured the tnsnames.ora file that is on the computer with the oracle client to contain the information about the instance of Oracle that you want to connect to.

Once you know that the server with the client installed can connect to your database, then we go to look at the client.

First, can you show me what connect string your client is using to connect to the database?

If you are using the Microsoft OleDB provider for Oracle (MSDAORA I think it is called), then you have to make sure that you have installed MDAC on the client computers.  VB comes with a file in the Setup Wizard's Redist directory called MDAC_TYP.EXE  This file needs to be included in your program's setup, because it contains all of the ADO stuff and the OleDB providers.  Now, most people have updated their MDAC_TYP to version 2.5 or better.  If you have done this, you'll need to make sure that you have also replaced the version in your Redist directory before you have built the install because the install will pick up this file from that directory.

The other thing the client needs is to have the oracle directories added to their path.  Go to the server where you installed the oracle client, and right-click on the MyComputer Icon.  Then, click on the Environment tab.  Look in the path statement for all paths that lead to Oracle.  You'll probably see something like:

C:\Oracle;C:\Oracle\bin;C:\Oracle\Install;

You'll need to copy that and any other environment settings that point to Oracle and add those to the clients as well, except that you need to change the drive letter to the mapped drive.

So, if you've got the path working, and you have MDAC on the client, and you're using the right Provider in your connect statement, and you've got the registry settings correct, then you must be really close.

If you're still having problems, you'll need to write down the exact error that you're getting and tell me what it is.

If you need to send screen shots or something, you can try me at my regular e-mail
mdougan@earthlink.net

Good Luck
0
 

Author Comment

by:aweigh
Comment Utility
Absolutly great!!!
0
 
LVL 18

Expert Comment

by:mdougan
Comment Utility
Did it work?  What was the last thing you found  you had to change?
0
 

Author Comment

by:aweigh
Comment Utility
The path!
0
 

Expert Comment

by:ASIHelpDesk
Comment Utility
mdougan - you seem to be the missing link for a problem that I am having regarding Oracle ODBC driver installation on clients configured to use a shared installation of SQL*NET.....

any way you could contact me at paul_lawrence@altagas.ca?
0
 
LVL 18

Expert Comment

by:mdougan
Comment Utility
ASIHelpDesk.... I tried contacting you at the e-mail address you listed, but didn't see any response from you... have you solved your problem?
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now