How to connect with your Oracle database without installing Oracle Client?


Did you ever get frustrated when you want to connect with your oracle database, but you have to install everything from oracle client software cd and waste for hundreds megs only to get connect with your database?
Now, those day were past.
After trial and error, I found more simple way to get TOAD, TORA (3rd party software for manage your database).
These explanation, only a sample to get work with 3rd party software like TOAD,SQLplus,TORA in order to connect with oracle database, I was run it on Windows XP Pro service pack 3, with TOAD  ver 9.0.1.8, edit my environment with notepad, and a cup of tea ?
In summary, you could get sqlplus command line and your TOAD run flawlessly without have to install oracle clients (*huraaa*).

  1. Download from oracle website for instant client software (http://www.oracle.com/technology/software/tech/oci/instantclient/index.html)
    oracle link to instant client

    oracle link to instant client

    if you use end user windows, simply drive your click into http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/winsoft.html

  2. Choose for latest suitable version for your own. On my version, I have to use Version 10.2.0.4, cause latest version (Version 11.1.0.6.0) not work for OCI.dll issue that not support for Version 11.1.0.6.0.

    Oracle Team of condition before download

    Oracle Team of condition before download

  3. After ACCEPT the license agreement above, Download instant client package –basic (35Mb on 10.2.0.4 and 44Mb on Version 11.1.0.6.0),

    basic option version 10

    basic option version 10

  4. Download instant Client Package – SQL*Plus – size only 800Kb on 10.2.0.4 and 11.1.0.6.0

    option with SQLplus

    option with SQLplus

  5. You can download another package if you want, but only those above were mandatory
  6. Create new folder on your main hard drive, I choose to create: “c:\oracle\instantclient”

    create new folder for extracting your instant client

    create new folder for extracting your instant client

  7. Then, extract your all package into  c:\oracle\instantclient
  8. after finished, from your desktop computer, access environment file, Start > My Computer > right Click > choose PROPERTIES

    link to computer propertise

    link to computer propertise

  9. From GENERAL tab, choose ADVANCED tab

    click on tab ADVANCE

    click on tab ADVANCE

  10. Click on ENVIRONMENT VARIABLES button on bottom region (startup and recovery)

    environment variables button

    environment variables button

  11. On top region, click new variables, and create variable Name: TNS_ADMIN with value C:\oracle\instantclient

    create new variables TNS_ADMIN to link with your tnsnames.ora file

    create new variables TNS_ADMIN to link with your tnsnames.ora file

  12. On bottom region, find PATH variable, and click EDIT

    add your instantclient link to PATH variables after adding ';'

    add your instantclient link to PATH variables after adding

  13. Add your location path into your last words after enter “;” (without quotes) value
  14. Create new variable with NLS_LANG (find out more on NLS_LANG on http://www.dba-oracle.com/t_nls_lang.htm ) enter your selected language, I enter AMERICAN_AMERICA.WE8MSWIN1252 for my value (you can enter your value within this  http://www.oracle.com/technology/tech/globalization/htdocs/nls_lang%20faq.htm#_Toc110410557 option)

    add new variable NLS_LANG, important variable for oracle connection

    add new variable NLS_LANG, important variable for oracle connection

  15. Restart your system
  16. Install your TOAD or TORA, or if you like, you can use native sqlplus from oracle.
  17. Create new file TNSNAMES.ora with your editor, or you can have it from your DBA man.

    ask your DBA to get the correct connection strings

    ask your DBA to get the correct connection strings

  18. Place the TNSNAMES.ora on “C:\oracle\instantclient”
  19. Run TOAD or TORA
  20. Enter your username, password and connection string from TNS files, you see TOAD now can read all the necessary information to connect with your database.

    after setup, connection to database is ready

    after setup, connection to database is ready

  21. You are connected to your database without have to install oracle client anymore:-)

    you are now connected, without waste your 400Mbs space

    you are now connected, without waste your 400Mbs space

Questions

Help, my connection not work, an error come out with message: ORA-12705: Cannot Access NLS data files or invalid environment specified.

NLS_LANG error when try connect with database

NLS_LANG error when try connect with database

NLS_LANG error when try to connect with SQLplus

NLS_LANG error when try to connect with SQLplus

Anwers:
This error show because you have NLS_LANG variable with wrong or not complete value, review your value base on http://www.oracle.com/technology/tech/globalization/htdocs/nls_lang%20faq.htm#_Toc110410557

Help, I have error when connect with my TOAD, error “OCI version 11.1.0.1.0 is not supported” come out.

OCI error when try to connect to database

OCI error when try to connect to database

Answers:
This because your TOAD isn’t compatible with your instant client, download and install previous version from oracle instant client page http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/winsoft.html

I need to run SQLDR80.exe aka. Sql loader, can I have this on my machine without have to install the oracle client too?
Answers:
Interesting question, till now I still have no idea, any open idea that could solve this issue?

~ by celemotan on 22 October, 2008.

29 Responses to “How to connect with your Oracle database without installing Oracle Client?”

  1. It worked perfectly with following additions
    1) i had to add ORACLE_HOME also other than TNS_ADMIN in System Environment Variables
    2) IUSR and IIS_USRS must have read/execute permission on c:\oracle\instantclient folder…

  2. This is the perfect web site for anybody who really wants to find out
    about this topic. You understand so much its almost hard to argue with
    you (not that I actually will need to…HaHa).

    You definitely put a new spin on a subject which has been written about for a long
    time. Excellent stuff, just great!

  3. kind of web site I like !! thks dude –

  4. Can I install server and client at same pc? I’ve istalled server on a different PC and client (other pc) like this page and I run TOAD then say this message: “ORA-12541: TNS:no listener” I can’t undertand it..

  5. I tried to connect using the steps as you have mentioned. The blog and idea is so useful. However I am not being able to connect using toad. I was wondering to know if we need to put all the content of SQL * client to the instantclient folder or inside the folder instantclient_10_2. Please suggest

  6. Very nice tips! I have followed and it works fine!!! Thank you very much!!!

  7. Excellent it worked..Thanks🙂

  8. Splendid! Worked very fine. I wasn’t aware of this instant client possibility. That makes life so much easier.
    Keep up the solicitous spirit!

  9. i have install xe client but, when i connect it said ‘tnsname connect time occurred’ please help me haow to connect .

  10. Thanks…It works

  11. What an untimate blog…..It works for me….Thanks dude

  12. with username/password for user type synonym i can’t view any tables
    how can resolve it?

  13. If you are planning to use tora with the already installed version on your desktop, delete oci.dll that cam installed in the TORA installation directory. I has “missing error description” error and it drove me nuts. After reading carefully on TORA installation directory, if you download the ready to use installation package (unzip it and use it), you need to remove oci.dll if you want to use oracle installed on your deskotp

  14. I have the error in TORA:
    Unable to connect to the database. ORA-29523216 missing error description

  15. There’s a solution which does not require permanent changes to your Environment Variables. I put them in a batch script (.BAT) and launch Toad from within that context with the variables set. Then I launch Toad from the bootstrapper. Copy script below into a text file and rename as ‘RunTOAD_10g.bat’.

    @echo off
    rem
    rem Bootstrapper to set environment variables
    rem For use with TOAD via Oracle Instant Client
    rem
    rem Written by: Alan Hall 23-09-2009
    rem

    set TNS_ADMIN=C:\Oracle\instantclient_10_2

    set NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252

    set PATH=%PATH%;C:\Oracle\product\11.1.0\client_1\bin;C:\Oracle\instantclient_10_2

    cd “C:\Program Files\Quest Software\Toad for Oracle FREEWARE”

    start toad.exe

    exit

  16. Great instructions!! Thank you very much

  17. Hi,
    I’m followed all the steps, and I got “ORA-06401: NETCMN: invalid driver designator”

    I’m using TORA 2.0.0 and instantclient 11.1.0.7

    Any clue? Many thanks in advance!

  18. Thank a lot.This has helped me in configuring Toad with Oracle in my laptop

  19. For TOra 2 it appears I need the Instant client but I need the full client for other app SQLloader, etc…

    With these steps can I have both installed?

    • that’s my second question too bro..
      i still not know how to make sqlloader work upon this environment🙂
      if you have some trick of something please do share through this page🙂

  20. Great.. How good this blog… good dude.. cheers

  21. Hi, I have done this, This is working fine for me. Thanks for the blog.

    Thanks,
    Venu

  22. I got the following error:
    OCI version 0.2089866642.2010999756.1228.45810828 is not supported
    Any idea?
    It seems because of the version, but i verify it to my best and everything seems ok.

  23. Hi Raj..
    please check step #17 with your DBA..
    i think the problem might be on tnsnames.ora that consist of several connections to your oracle instances.
    so please check if the connection string were right or not.
    hope it will help.

  24. Hi
    I have followed the same steps above but i am facing an error while trying to login to DB though TOAD.

    Error:

    ORA-12154 : TNS : Could not resolve the connect identifier specified.

  25. maksudnya ODBC kemana mbak maya?
    TOADnya yang dimaksud bisa connect ODBC?
    TOAD sayangnya hanya bisa ke oracle.

  26. Kl dipake untuk koneksi ODBC, harusnya juga bisa kan?

  27. Very nice tip! It worked just as you told it would =) Thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: