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={Micro soft ODBC for Oracle};UID=username;PWD=p assword;"
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ----
...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
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={Micro
--------------------------
...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
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
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!!
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Worked!!....and as I suspected, the MDAC version is out of date.
Thanks so much for the help!!
Best Regards,
cpwatkins
Thanks so much for the help!!
Best Regards,
cpwatkins
Dim TheArray(0)
REDIM PRESERVE TheArray(Con.Execute(TheCo
then you can use the array?
MaxOvrdrv2