Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
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
?
265 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
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 recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
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/…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

571 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