How to send email from Oracle database using procedure/PLSQL

It has been a long time I did not post to this blog. Happy New Year 2017 anyway!

Recently, I am working on something related to my work. I have to monitor Oracle job scheduler, their job status etc. One of the options came across my mind is to email me the job status everyday from Oracle database.

Below script is working on my machine, which running Oracle XE. If error accrued during execute the script, it may come from insufficient privileges on certain package. Please grant where necessary.

Here is the script:

DECLARE
V_FROM      VARCHAR2(80) := 'YOUR_EMAIL@GMAIL.COM';
V_RECIPIENT VARCHAR2(80) := 'RECIPIENT_EMAIL@GMAIL.COM';
V_SUBJECT   VARCHAR2(80) := 'TEST EMAIL';
V_MAIL_HOST VARCHAR2(30) := 'YOUR_MAIL_HOST';
V_MAIL_CONN UTL_SMTP.CONNECTION;
CRLF        VARCHAR2(2)  := CHR(13)||CHR(10);
V_CONTENT   VARCHAR2(300);

BEGIN
             
    SELECT 'THIS IS EMAIL TESTING FROM ORACLE DATABASE.'  
    INTO V_CONTENT
    FROM DUAL;
                                         
BEGIN
V_MAIL_CONN := UTL_SMTP.OPEN_CONNECTION(V_MAIL_HOST, 25);
UTL_SMTP.HELO(V_MAIL_CONN, V_MAIL_HOST);
UTL_SMTP.MAIL(V_MAIL_CONN, V_FROM);
UTL_SMTP.RCPT(V_MAIL_CONN, V_RECIPIENT);
UTL_SMTP.DATA(V_MAIL_CONN,
  'DATE: '   || TO_CHAR(SYSDATE, 'DY, DD MON YYYY HH24:MI:SS') || CRLF ||
  'FROM: '   || V_FROM || CRLF ||
  'SUBJECT: '|| V_SUBJECT || CRLF ||
  'TO: '     || V_RECIPIENT || CRLF ||
  CRLF ||
  V_CONTENT || CRLF ||      --MESSAGE BODY  
  CRLF        

);
UTL_SMTP.QUIT(V_MAIL_CONN);
EXCEPTION
WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN
  RAISE_APPLICATION_ERROR(-20000, 'UNABLE TO SEND MAIL', TRUE);
END;
           
END;

Hope this help.

Microsoft ODBC driver for ORacle ORA-12154: TNS could not resolve service name

For some of us whom frequently using MS Access in daily use, encountered an error might be very troublesome.

The problem is like this : I am using MS Access for data analysis and checking and have a connection over the network to Oracle database. My connection was using Microsoft ODBC Driver for Oracle. For some security reason, I need to change the password now. The password changed with no problem but encountered error ORA-12154 when trying to relink the tables.

Error as below:

ODBC--call failed
Microsoft ODBC driver for ORacle ORA-12154: TNS could not resolve service name

Generally, ORA-12154 indicated that the specifying TNS alias is not resolving. Few questions needed to clarify;

1. Are you using a DSN for your connection to Oracle database? 
2. If so, what is the TNS alias that you specifying? 
3. Does that alias exist in your tnsnames.ora file? 
4. Are there multiple tnsnames.ora files on your machine?

Your should have your tnsnames.ora file when you installing the Oracle client software/driver. If you wondering what is the version of the Oracle client you have installed, the simplest way is to get DOS command prompt and type 'tnsping [service_name]' where [service_name] is TNS alias specified in your DNS.

The ouput of it may look like this:

C:\Users\jcave>tnsping fuddy_duddy
TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 07-OCT-2
010 08:10:51
Copyright (c) 1997, 2010, Oracle.  All rights reserved.
Used parameter files:
C:\oracle\product\11.1.0\db_1\NETWORK\ADMIN\sqlnet.ora
TNS-03505: Failed to resolve name

The "Used parameter files" line shows you the directory where the TNS related files are stored. A tnsnames.ora and a sqlnet.ora file should be in that directory and it alwasys be there by default. Perhaps you should check the TNS alias name and confirm with your DNS setting.

How to load image files into BLOB column using PL/SQL

Assuming that you have a list of physical images needed to be loaded into Oracle database. And also you have a free text list of the files, perhaps in form of CSV file format.

From above, you may already get the bigger picture of the scenario which you need to execute.

Lets consider this task needed to be execute in Oracle using Oracle procedure.

The PL/SQL

I already prepared the PL/SQL example for this purpose. Check the code below;

CREATE OR REPLACE PROCEDURE PROC_LOAD_IMAGES (FILE_DIR in VARCHAR2 , MYFILE_NAME in VARCHAR2) AS
  -- FILE_DIR is the directory of the flat file and images
  -- MYFILE_NAME is  the flat file name
  F UTL_FILE.FILE_TYPE;
  V_LINE VARCHAR2 (1000);
  V_CACC_OLD_NUM  VARCHAR2(24 BYTE);
  V_IDENT_TYPE    NUMBER(1);
  V_ID_NUMBER     VARCHAR2(20 BYTE);
  V_FILE_NAME     VARCHAR2(80 BYTE);
  V_FILE_BLOB     BLOB;
  V_CLASS         VARCHAR2(1 BYTE);
  lFile           BFILE;
           
BEGIN
    F := UTL_FILE.FOPEN (upper(FILE_DIR), MYFILE_NAME, 'R');
    IF UTL_FILE.IS_OPEN(F) THEN
        LOOP
            BEGIN
            UTL_FILE.GET_LINE(F, V_LINE, 1000);
            IF V_LINE IS NULL THEN
            EXIT;
            END IF;
                --If using piping, change to '[^|]+'
                V_CACC_OLD_NUM := REGEXP_SUBSTR(V_LINE, '[^|]+', 1, 1);
                V_IDENT_TYPE := REGEXP_SUBSTR(V_LINE, '[^|]+', 1, 2);
                V_ID_NUMBER := REGEXP_SUBSTR(V_LINE, '[^|]+', 1, 3);
                V_FILE_NAME := REGEXP_SUBSTR(V_LINE, '[^|]+', 1, 4);
                V_CLASS := REGEXP_SUBSTR(V_LINE, '[^|]+', 1, 5);
             
                INSERT INTO BS_SIGNATURE_IMAGES (CACC_OLD_NUM, IDENT_TYPE, ID_NUMBER, FILE_NAME, CLASS) VALUES( V_CACC_OLD_NUM, V_IDENT_TYPE, V_ID_NUMBER, empty_blob(), V_CLASS) RETURNING FILE_NAME INTO V_FILE_BLOB;
                lFile := BFILENAME(upper(FILE_DIR), V_FILE_NAME);
             
                IF dbms_lob.fileexists(lFile) = 1 THEN      
                    DBMS_LOB.OPEN(lFile, DBMS_LOB.LOB_READONLY);
                    DBMS_LOB.OPEN(V_FILE_BLOB, DBMS_LOB.LOB_READWRITE);
                    DBMS_LOB.LOADFROMFILE(DEST_LOB => V_FILE_BLOB,
                                          SRC_LOB  => lFile,
                                          AMOUNT   => DBMS_LOB.GETLENGTH(lFile));
                    DBMS_LOB.CLOSE(lFile);
                    DBMS_LOB.CLOSE(V_FILE_BLOB);
                END IF;
             
                COMMIT;
                             
            EXIT;
            END;
        END LOOP;
    END IF;
    UTL_FILE.FCLOSE(F);
END;
/

The important portion UTL_FILE function. This is the function to read the file and responsible to load the files into BLOB format into the database.

APEX : Send email with attachment (Part 2)

Please refer to my previous post related to this, here.

Moving forward, by now you should already completed doing these 2 things:

1. User can insert any attachment into the form
2. The attachment will be save into my own table within the Oracle database.

So now, the next step is to configure APEX to sent-out email together with the attachment.

The email process must be create at point [Processing > Processes] on [Page Processing] , right after the 'CopyFile' process (refer to my previous post)

1. Right click on [Processes] > Create
2. Select [PL/SQL] and Next
3. Give a name to identify the procesess and click Next
4. On the [Enter PL/SQL Page Process], paste below code;

DECLARE
    l_id number;
BEGIN
    l_id := APEX_MAIL.SEND( p_to        => 'fred@flintstone.com',
                            p_from      => 'barney@rubble.com',
                            p_subj      => 'APEX_MAIL with attachment',
                            p_body      => 'Please review the attachment.',
                            p_body_html => '<b>Please</b> review the attachment' );
    FOR c1 IN (SELECT filename, blob_content, mime_type
                 FROM attachment_files
                WHERE ID = :P1_YOUR_ID ) loop
    APEX_MAIL.ADD_ATTACHMENT( p_mail_id    => l_id,
                                  p_attachment => c1.blob_content,
                                  p_filename   => c1.filename,
                                  p_mime_type  => c1.mime_type);
    END LOOP;
END;
BEGIN
wwv_flow_mail.push_queue(
   P_SMTP_HOSTNAME => 'localhost',
   P_SMTP_PORTNO => '25'
);
END;

Please see the code and change whenever suit with your table name and page item. For example code below:

FOR c1 IN (SELECT filename, blob_content, mime_type
                 FROM attachment_files
                WHERE ID = :P1_YOUR_ID ) loop

The [attachment_files] comes from my table which I have created previously in so called Part 1.

You can have multiple 'File Browse...'. You may also need to change the PL/SQL code if have more [File Browse]. Do comment below if you still need help.

I will try my best to help. :)

APEX : Send email with attachment

I have found a working solution for sending email with attachment using APEX application. But first, few items have to take note :-

1. User can insert any attachment into the form
2. The attachment will be save into my own table within the Oracle database.

User can insert any attachment

This will be require an item named 'File Browse...'. Simply add the item into the page. I assume at this point, you already know how to it. Lets name it as [P1_ATTACHMENT]

Take a look at the [Settings] tab when u edit this item page. The storage type setting can be either two : 1.Table WWV_FLOW_FILES and 2.BLOB column specified in Item Source attribute

I will choose option No.1. Why I did not choose option No.2? As I had mentioned that the attachment will be save into my own table?

I am not expert, perhaps you can correct me if I am wrong, but I think that insert a attachment files into table will require some additional coding when we want to execute it in APEX environment.

One more reason is, I already has define one table to fetch row in the [Processes] in [After Header] section. So by adding another one more table (attachment table) it will make APEX get confused. Anyway, I have tried it. It does not work.

The attachment will be save into my own table within the Oracle database.

Next step is to save the file attachment to be save in my own attachment table. Lets name my table as ATTACHMENT_FILES. 

CREATE TABLE ATTACHMENT_FILES
(
  ID                 NUMBER,
  BLOB_CONTENT       BLOB,
  MIMETYPE           VARCHAR2(255 BYTE),
  FILENAME           VARCHAR2(255 BYTE),
  LAST_UPDATE        DATE,
  CHARSET            VARCHAR2(128 BYTE),
  UPLOADED_BY        VARCHAR2(10 BYTE),
  DOC_SIZE           NUMBER
)

Since I already have my table ready, I will now need to create one process to copy the file from WWV_FLOW_FILES into ATTACHMENT_FILES.

This process must be create at point [Processing > Processes] on [Page Processing]

1. Right click on [Processes] > Create
2. Select [PL/SQL] and Next
3. Give a name to identify the procesess and click Next (lets name it as 'CopyFile')
4. On the [Enter PL/SQL Page Process], paste below code;

begin
insert into ATTACHMENT_FILES( id, blob_content, mimetype, filename, last_update, doc_size )
select :P1_YOUR_ID, blob_content, mime_type, filename, created_on, doc_size from  wwv_flow_files where  name = :P1_ATTACHMENT;
delete from wwv_flow_files where name = :P1_ATTACHMENT;
end;

So by now, you got the idea already right? First, we let the attachment to be saved into WWV_FLOW_FILES. Then we copied it over to our own table and delete the original data from WWV_FLOW_FILES to avoid consume space and storage.

To avoid any complex coding in PL/SQL, I believe this method is faster. It depends on your needs.

Hurmm..I will talk about sending email as Part 2 later on.

Update : This is the Part 2.

APEX : ORA-01008: not all variables bound

It is really frustrating if everything run not as per plan right?..:-)

Today I encounter error ORA-01008: not all variables bound, when executing a report from a SQL script. At first, I suspected because of a LEFT JOIN syntax problem.

What Oracle say about ORA-01008?

ORA-01008: not all variables bound

Cause: A SQL statement containing substitution variables was executed without all variables bound. All substitution variables must have a substituted value before the SQL statement is executed.

Action: In OCI, use an OBIND or OBINDN call to substitute the required values.

I not really understand it much. But below solution works well for me on my APEX application. And the report generation have no problem any more.

Initially, my SQL code was as below:

where employee = :P1_EMPLOYEE

Solution: 

where employee = v('P1_EMPLOYEE')

Have a good day!

Error on APEX : ORA-20001: Unable to bind..

ORA-20001: Unable to bind :P1005_YOUR_PAGE_ITEM_NAME verify length of item is 30 bytes or less. Use v() syntax to reference items longer than 30 bytes. ORA-01006: bind variable does not exist


I encountered an error as above when testing my application on APEX. So, normally I will googling around to find the solution. But unfortunately, I do not found a solid solution to my problem, particularly on the above error.

So I try to solve the error myself. After took couple of hours play around with it, finally it is resolved.

Important thing to take note are:

• This error is not come from the database. It is from application itself.

• No need to check your database table and re-define your table structure. Such as change to data length to 30 bytes etc.

Solution:

The problem is due to the naming of the page item. So in APEX, it must not more than 30 bytes. So, I rename my page item to something more lesser in character. It works for me. I hope it is work for you too.

Change your character of P1005_YOUR_PAGE_ITEM_NAME to something lesser!

APEX - Grab value from Shuttle and put it into Textarea with carriage return

In some design, you may require to grab the value from Shuttle and put those value into a new Textarea or even in email HTML template. How am I going to do that?

What you need is a new Textarea so that the values you about to grab will be put inside. Lets put the Shuttle as P1001_X and assume the new Textarea as P1001_TXTAREA.

One solution can be used is a Computations (After Submit). You need to right click the Computations > Create > Item on This Page. Fill in those required value in that form and select [Item Value] in the Computation Type. In Computations field, put below code:

:P1001_TXTAREA := REPLACE( :P1001_X , ':', (chr(13)||chr(10)) );

What above code will do is it will grab each value from Shuttle, together with new line feed and carriage return.

Information:
chr(13) : Carriage Return
chr(10) : New Line Fees

APEX - How to grab value from Shuttle and save it into database

So you are using Oracle APEX shuttle in your form and will give the ability to user to select multiple value from the list. The next challenge is to store the selected values into the Oracle database.

Oracle APEX Shuttle : How to do it?

In this post, I will talk about storing the multiple values into Oracle database in a way of storing the value one by one. Meaning that, if there are 4 active selection, the operation will be storing or insert the 4 value into Oracle table each row. So there will be 4 insert operation will be executed.

Please check below code:

declare
    tab apex_application_global.vc_arr2;
begin
    tab := apex_util.string_to_table (:PX_YOUR_ITEM);
    for i in 1..tab.count loop
        insert into YOUR_TABLE (YOUR_COLUMN_NAME, YOUR_SHUTTLE_COLUMN_NAME)
        values
('My List', tab(i) );
    end loop;
end;

Please note that :PX_YOUR_ITEM is referring to your Shuttle Item name.

Where should I put those code?

1.Page Processing > Processing > Process, right click > Create

2.Select PL/SQL and you may procees the rest

3.When you see 'Enter PL/SQL Page Process', paste the code here. Please change accordingly follow your item page name and table/column in your database.

Part II : Create a page with multiple forms linked to multiple tables

Previously I has written about creating a page with multiple forms linked to multiple database. You can read it here. But, what I am going to post now is about trigger upon insert action, and how to pass the value into another page item.

Simple example is like this:

You are created 2 separate form in one page in APEX environment. One Submit button operation, all the data will be insert accordingly into their respective table, let say Table1 and Table2. Table1 has an auto generate ID by trigger on insert operation. And this Table1.ID was require to be pass to Table2. This scenario can be said as one to many relational table inside database.

I have tried to use the DML operation to pass the primary key into another page item unfortunately has failed. The workaround of it was to use 'RETURNING' clause in PL/SQL.

Check-out SQL below:

insert into Table1 ( column1, column2, column3) values
( :P1_Data1, :P1_Data2, :P1_Data3)
returning YourPrimaryKeyColumnName into :P1_YourTargetPageItem;

I took a lot of time searching how to do it. If you need more details on this, just let me know here.

APEX - Create a page with multiple forms linked to multiple tables and 1 Submit Page Button for INSERT operation

I am having a problem to update into Oracle table. To be exact, I have created 2 separate form into 1 page. And those forms are actually binded with different table as well. My operation or design will be, when I click a ‘Submit’ button, all the data entered by user will be inserted into their respective table. Well, I got an error. What has been wrong?

Oracle APEX limitation?

After ‘googling’ (I wonder if this word has in dictionary..) around, I found that this is a known limitation for APEX. Well, obviously Oracle need to check further for this. You can only have one automated row fetch (DML) process per page. However, it does not means that we cannot add other form on the page. There are few a solution for that which works well for me.

Create Separate Region for separate Form

1. On Page Rendering, locate Region > Body and right click. Click ‘Create’ to create new region. This one will be your 1st Form.
2. Create Region, select ‘Form’ and press ‘Next’
3. Create Form, select ‘Table’ and press ‘Next’. Please just proceed until the wizard complete. The 1st Form will be straight forward.

The Important on Next Sequence Form

You may now see the new region successfully created from the wizard. The Form will populated text field or other field as per your selection.

Now, the next step we need to do will bit different.

4. Repeat same step 1 and 2, but for 3 select ‘SQL Query (fetch row) instead of ‘Table’

5. Press ‘Next’ until you found ‘SQL SELECT Statement’ page tab. Here will be SQL to populate the page item into the page. You can type it manually, or click [Query Builder] link.

6. The new page items will be populated according to your SQL. You can see the Form already created for you.

Setting the INSERT Operation

You may notice that there are few button automatically created inside the page. For me, I do not want it so many, so I created a new region for only 1 button. This button will do action INSERT for those 2 form above for 2 different table. Just make sure that the button action is ‘Submit Page’.

7. Since the 1st Form using Automatic Row Processing(DML), I just need to set the INSERT operation for Table #2 in Form #2. Go to Page Processing > Processes and right click on it.

8. Select PL/SQL and Next. Give your process a name and Next.

9. When reach at [*Enter PL/SQL Page Prosess] , enter you INSERT SQL statement. Remember to use page items in this SQL. For Example:

insert into Table2 (column1, column2, column3) values ( : P1_Item1, : P1_Item2, : P1_Item3);

Hopefully above steps works for you. If you got comment, leave your comment below.

Obtain machine IP Address using Oracle SQL command

I never know this function has exist until I was required to explore Oracle APEX to design a website. Here is the code, which can get an IP address of machine and the value can be store into a database or pass it into other variable for processing, or whatever your programming do.

select sys_context('userenv','ip_address')  from dual

Have fun.

Oracle Database Express Edition 11g Release 2 not work for 64bit OS?

Oracle Database 11g Express Edition (Oracle Database XE) is an entry-level, small-footprint database based on the Oracle Database 11g Release 2 code base.  

It's free to develop, deploy, and distribute; fast to download; and simple to administer.

 

Oracle Database XE is a great starter database for:

 

• Developers working on PHP, Java, .NET, XML, and Open Source applications
• DBAs who need a free, starter database for training and deployment
• Independent Software Vendors (ISVs) and hardware vendors who want a starter database to distribute free of charge
• Educational institutions and students who need a free database for their curriculum

 

With Oracle Database XE, you can now develop and deploy applications with a powerful, proven, industry-leading infrastructure, and then upgrade when necessary without costly and complex migrations.

 

Oracle Database XE can be installed on any size host machine with any number of CPUs (one database per machine), but XE will store up to 11GB of user data, use up to 1GB of memory, and use one CPU on the host machine.

 

But, it also has a limitation. This XE does not offer to 64bit Operating System. I was wondering why? Perhaps it is due to Oracle strategy in terms of sales, which at the end will bring user to choose a upper version of Oracle database, Enterprise edition.

 

Well, it make sense. If Oracel to offer everything for free. How are they going to make money from their Oracle licensing revenue.

 

However, some information from the internet has say that Oracle Database XE can be install in 64bit operating machine, somehow. Well, I am about to test it and see how true the statement was.

 

See you in my next post about this.

 

If you wanted to know more about Oracle Database XE, you can go to Oracle website here.

ASP.net how to execute query to Oracle using Button click

I would like to share a simple code which currently I am doing. This is a very basic application you may design for your website and require a database connection to Oracle databases.
First, lets go to the design of it. This is truely very useful for beginner like me especially you do not know where to start with. I am using Microsoft Visual Studio 2012 for Web to design my application website.

Second, talk about the Oracle connection string. I have quite sometime searching for correct oracle connection string that works (on my machine at least). You may copied it over, hopefully it will works on you too. Please change where necessary in the string follow your server configuration and setting.

"Data Source=(DESCRIPTION=" _

+ "(ADDRESS_LIST=" _

+ "(LOAD_BALANCE=YES)" _

+ "(FAILOVER=YES)" _

+ "(ADDRESS=(PROTOCOL=tcp)(HOST=YOUR_HOST_NAME)(PORT=YOUR_PORT))" _

+ "(ADDRESS=(PROTOCOL=tcp)(HOST=YOUR_HOST_NAME)(PORT=YOUR_PORT))" _

+ ")" _

+ "(CONNECT_DATA=" _

+ "(SERVICE_NAME=YOUR_SERVICE_NAME)" _

+ ")" _

+ ");" _

+ "User ID=YOUR_USER_ID;Password=YOUR_PASSWORD;" 

Now, when you double click on Button from the project, it will bring you to the new coding page. This file is normally ended with *.vb filename. Here will be the location to paste the Oracle connection string as pasted above. Below is how the code will be looks alike:

Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        'Your Oracle connection Setting

        Dim strConnString As String = "Data Source=(DESCRIPTION=" _

                                    + "(ADDRESS_LIST=" _

                                    + "(LOAD_BALANCE=YES)" _

                                    + "(FAILOVER=YES)" _

                                    + "(ADDRESS=(PROTOCOL=tcp)(HOST=YOUR_HOST_NAME)(PORT=YOUR_PORT))" _

                                    + "(ADDRESS=(PROTOCOL=tcp)(HOST=YOUR_HOST_NAME)(PORT=YOUR_PORT))" _

                                    + ")" _

                                    + "(CONNECT_DATA=" _

                                    + "(SERVICE_NAME=YOUR_SERVICE_NAME)" _

                                    + ")" _

                                    + ");" _

                                    + "User ID=YOUR_USER_ID;Password=YOUR_PASSWORD;" 

        'Your sql statement goes here

        Dim strQuery As String = "SELECT cus_name from  tbl_cus where cus_ID = '" + TextBox1.Text + "'"

        Dim con As New OracleConnection(strConnString)

        Dim cmd As New OracleCommand()

        cmd.CommandType = CommandType.Text

        cmd.CommandText = strQuery

        cmd.Connection = con

        Try

            con.Open()

            Dim sdr As OracleDataReader = cmd.ExecuteReader()

            While sdr.Read()

                'Here will be your output result

                Label1.Text = "Item"

            End While

        Catch ex As Exception

            Throw ex

        Finally

            con.Close()

            con.Dispose()

        End Try

    End Sub

Above code working just fine when I debug the project and click the button. This may help you to explore more on query to Oracle database for your project.

Problem open TOAD after install : You have no Oracle Client installed.

I encounter a problem with my TOAD for Oracle just now. It mentioned You have no Oracle Client installed .Wondering what is the problem and below was the screenshot of it.

The solution is very simple. And I also found the root cause of it.


Root cause: Toad do not support Oracle Client for 64 bit. As simple as that


Solution : Install Oracle Client 32 bit. You may install another copy of Oracle client, or you may remove the 64 bit which you previously installed.


To be particular, my machine was a Windows 64 bit platform, and I directly apply Resolution 2 as below.


Below are the statement from the Quest Software support site.
For more detail from the site: https://support.quest.com/Search/SolutionDetail.aspx?id=SOL39689
Cause
- There is no Oracle client installed on the machine or the client installed is 64-bit. Toad does not support a 64-bit Oracle client.
- Environment Variable Path is missing.

Solution for 'You have no Oracle Client installed' :

RESOLUTION 1:
Check to make sure there is an Oracle client installed on your machine. If there is not, you will need to install one. Please review solution SOL76452 for video demo and instructions on installing a 32-bit Oracle Instant Client. See additional information below for link to full 32-bit Oracle Client


RESOLUTION 2:
If there is an Oracle client installed on the machine, check to see if it is 64-bit. If it is, you will need to install a 32-bit Oracle client.


RESOLUTION 3:
1) Look for your OCI.DLL file in your computer and Copy the location path i.e. C:\oracle\product\10.2.0\client_1\BIN
2) Right-Click your My Computer | Properties | Advanced Tab | Environment Variables button
3) Under ‘System Variables’ at lower half of screen, highlight ‘Path’ | click Edit
4) Under ‘Variable Value:’ | go to the very beginning of the existing entry | Paste the path you copied earlier
5) Add a semi-colon (;) | and then click OK, OK, OK.
6) Start Toad and re-test


RESOLUTION 4:
You installed an Oracle Client 10g version in a Windows 64-bit machine. There is an issue with the Oracle Client 10g version on a Windows 64-bit machines where 3rd party Oracle Tools like Toad for Oracle will not be able to see the client. Possible resolutions for this are:
- Install Toad in “C:\Quest Software\” folder instead of the default “C:\Program Files (x86)\” folder.
- Install and use Oracle Client 11g version instead. See the download link below for the client we recommend.


To download Oracle 11g Client software:
11g 64bit client
http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win64soft-094461.html
11g 32bit Client
http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win32soft-098987.html
You may need a Oracle Metalink account to download it.