?
Solved

I need to find an ODBC or OLEDB layer that will work with my Oracle Stored Procedure

Posted on 2003-03-07
4
Medium Priority
?
263 Views
Last Modified: 2011-08-18
I have an ASP page that passes one variable to an Oracle 8 Package, and after processing, recieves an array of values back from that package.  This array is then stored in an ASP result set and processed from there.
The problem is that I cannot seem to find an ODBC/OLEDB connection that will handle the retreival of a complex data type array that my Oracle package is returning.

I have 2 servers that this ASP is running on.  One for development and one for production.  Here's the kicker:
On the development box, I can get everything running properly by using the following ODBC layer
------------------------------------------------------------------------------------------
Const cConn = "SERVER=ARSP;driver={Microsoft ODBC for Oracle};UID=username;PWD=password;"
------------------------------------------------------------------------------------------
...and it seems to be the only one that works as I have tried it with numerous types.
On the production server, the above connection string does not work.  I have checked with the server admin, and he claims that the version of MDAC running on this production machine is 2.6.  (I'm not sure that I believe him)
Is there any other ODBC/OLEDB connection strings that I could use in order to make this work properly????  I'm desperatly at my wits end :~

Below is the header for my Oracle Package in order to show the type of data that it will be returning:
-----------------------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE PACKAGE Get_Activities
AS
TYPE tblACTID       IS TABLE OF NUMBER           INDEX BY BINARY_INTEGER;
TYPE tblDATE       IS TABLE OF DATE          INDEX BY BINARY_INTEGER;
TYPE tblDEPT       IS TABLE OF VARCHAR(75)     INDEX BY BINARY_INTEGER;
TYPE tblDET       IS TABLE OF VARCHAR(3000)     INDEX BY BINARY_INTEGER;

PROCEDURE GetTicketActivities(
            i_TicketID            IN    ACTIVITIES.TROUBLETICKETID%TYPE,
            o_ActivityID            OUT     tblACTID,
            o_Date            OUT     tblDATE,
            o_DepartmentName       OUT     tblDEPT,
            o_ActivityDetails       OUT     tblDET);
END Get_Activities;
/
-----------------------------------------------------------------------------------------------------------------------------------

Thankyou in advance for any/all suggestions that might help me to solve this perplexing problem.

Best Regards,

cpwatkins
0
Comment
Question by:cpwatkins
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 10

Expert Comment

by:MaxOvrdrv2
ID: 8090511
suggestion:

Dim TheArray(0)

REDIM PRESERVE TheArray(Con.Execute(TheConnectionToPackageOrWhatever)

then you can use the array?

MaxOvrdrv2
0
 
LVL 7

Expert Comment

by:lavinder
ID: 8092927
Hi cpwatkins

 I dont think that array problem is related to the oracle connection string, still i am providing you a link, which has all the connection strings example, have a look at it:

 http://www.able-consulting.com/ADO_Conn.htm

For array handling and oracle pl/sql tables, have a look at following links:

http://www.learnasp.com/learn/oraclerecordsetsado.asp
http://webreference.com/programming/asp/database/2.html
http://www.oracle-base.com/Articles/8i/ComplexRecordsets.asp
http://www.dell.com/downloads/us/pedge/_Ref10370749

happy programming!!
0
 
LVL 3

Accepted Solution

by:
DRY_GIN earned 210 total points
ID: 8117472
Hello,
If you don't trust your admin,
use the following to get MDAC version (ASP):

<%
Set Reg = Server.CreateObject("wscript.shell")

On error resume next
response.write "MDAC Version: " & reg.RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\Classes\MDACVer.Version\CurVer\")  & "<br>"

if err then
 Response.Write "Error: " & err.description & "<br>"
  err.clear
end if
On error goto 0
%>

in my case on both of my hostings it provides MDACVer.Version.2.70

Good Luck
0
 

Author Comment

by:cpwatkins
ID: 8120967
Worked!!....and as I suspected, the MDAC version is out of date.
Thanks so much for the help!!

Best Regards,

cpwatkins
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Suggested Courses

770 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