Link to home
Start Free TrialLog in
Avatar of cpwatkins
cpwatkins

asked on

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

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
Avatar of MaxOvrdrv2
MaxOvrdrv2

suggestion:

Dim TheArray(0)

REDIM PRESERVE TheArray(Con.Execute(TheConnectionToPackageOrWhatever)

then you can use the array?

MaxOvrdrv2
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!!
ASKER CERTIFIED SOLUTION
Avatar of DRY_GIN
DRY_GIN

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of cpwatkins

ASKER

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

Best Regards,

cpwatkins