Windows 7 64bit, Classic ASP, Visual Studio connection to Oracle Database

References

26.12.2013 Update

We realized, over a 64bit OS you may need to change the default application pool to accommodate 32bit applications. Please refer to this post

https://help.webcontrolcenter.com/KB/a1114/how-to-enable-32-bit-application-pool-iis-7-dedicatedvps.aspx

 

 

http://www.connectionstrings.com/

http://stackoverflow.com/questions/234657/binding-asp-net-gridview-to-an-oracle-sys-refcursor

So, you are still using classic ASP or develop 32bit .NET applications using Visual Studio and Oracle databases on a Windows 7 64bit box and completely lost as connection cannot be established?

Here are few workaround solutions

Install the latest Oracle clients (both 64bit and 32bit)

If you installed Oracle client in the following folders (example)

D:\app\yourusername\product

right click on the folder, under security tab check whether authenticated users have read privileges.

If not, provide the basic privileges (Read & Execute, list folder contents, Read) Refer the image

image

Now add the user “IIS_ISURS” user and provide the same rights mentioned above

RESTART your Windows 7 “64bit” box now!

You can establish the connection from classic ASP like following

<%
    Dim adoCon                'Holds Connection
    Dim rsViewRecords        'Holds Recordset

    Set adoCon = Server.CreateObject("ADODB.Connection")
    if Err.Number <> 0 then 
        Response.Clear() 
        response.Write "<hr>ORASESSION Error<br>" & err.number & "  --  " & err.Description & "<hr>"
        response.End
    end if 

    adoCon.Open "provider=oraoledb.oracle;data source=test;user id=apps;password=apps;plsqlrset=true"

    Set rsViewRecords = Server.CreateObject("ADODB.Recordset")

    %>

 

Regards,

Admin

Windows 7 64Bit, Oracle Database access from Classic ASP

Step 1: Install Oracle Client 64bit, the latest version (Solution intended for those who are already on 10g or above)

Step 2: If your connection call is not something like following, change it to

<% 
Dim adoCon                'Holds Connection
Dim rsViewRecords         'Holds Record Set

' Initiate connection

Set adoCon = Server.CreateObject("ADODB.Connection")

' Checking whether an exception raised during the Server Create Object call

if Err.Number <> 0 then 
Response.Clear() 
response.Write "<hr>ORASESSION Error<br>" & err.number & "  --  " & err.Description & "<hr>"
response.End
else
response.Write "Session created"
end if 

adoCon.Open "provider=oraoledb.oracle;data source=orcl;user id=scott;password=tiger;plsqlrset=true"
Set rsViewRecords = Server.CreateObject("ADODB.Recordset")

' Your other stuff goes here

    %>

 

That’s all guys

regards,

Admin

Oracle send mail using UTL_SMTP (to multiple parties Cc)

declare
v_From      VARCHAR2(80) := 'abc@xyz.com';
v_Recipient VARCHAR2(80) := 'xyz@xyz.com';
v_Subject   VARCHAR2(80) := 'Checking Cc';
v_Mail_Host VARCHAR2(50) := 'mail.xyz.com';
v_Mail_Conn utl_smtp.Connection;

name_array   DBMS_SQL.varchar2_table;

crlf        VARCHAR2(2)  := chr(13)||chr(10);
CC_parties varchar2(2000);

begin

--populate from table-Cc parties
name_array(1) :=  'def@xyz.com';
name_array(2) :=  'jkl@xyz.com';
name_array(3) :=  'elg@xyz.com';

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);
FOR i IN name_array.FIRST .. name_array.LAST
LOOP
CC_parties := CC_parties||';'|| name_array(i);
utl_smtp.Rcpt(v_Mail_Conn, name_array(i));
END LOOP;

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 ||
'Cc: '     || CC_parties|| crlf ||
'Content-Type: text/html;' ||crlf ||
'New Mail from Procedure');
utl_smtp.Quit(v_mail_conn);
end;

Recvd_Mail

(Edited Image)

Hope you enjoy this.

Regards,

Admin