frodoman
asked on
Problems after Migration
Calling a stored procedure from asp to return a recordset. Everything works beautifully on webserver running Win2K / IIS5 connecting to Oracle 8i. When trying to run the same code on Win2003 / IIS6 server connecting to Oracle 10g we get an error. No idea if this error is related to the oracle change or to the IIS change (or even both).
The error shown is:
[Oracle][ODBC][Ora]ORA-065 50: line 1, column 65: PLS-00201: identifier 'CQID' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored
The asp code calling the stored procedure is:
set cmd = server.createobject("ADODB .Command")
cmd.ActiveConnection = connOracle
cmd.commandtext = "{call quotesys2.salesman_pkg.lis t_all_quot ations(?, {resultset 2000, cqid, cname})}"
cmd.CommandType = 1
cmd.Parameters(0).Directio n = 1
cmd.Parameters(0).Value = session("g_sUserID")
set rst = server.createobject("ADODB .Recordset ")
rst.cursortype = 3
set rst.source = cmd
rst.open <--- error occurs here
The stored procedure header is:
PROCEDURE list_all_quotations (
in_userid IN VARCHAR2,
cqid OUT string40_type,
cname OUT string40_type
);
"string40_type" is declared in the package header as:
TYPE string40_type IS TABLE OF VARCHAR2 (40) INDEX BY BINARY_INTEGER;
The SP does compile in Oracle10g and does return the correct results. Any ideas???
The error shown is:
[Oracle][ODBC][Ora]ORA-065
The asp code calling the stored procedure is:
set cmd = server.createobject("ADODB
cmd.ActiveConnection = connOracle
cmd.commandtext = "{call quotesys2.salesman_pkg.lis
cmd.CommandType = 1
cmd.Parameters(0).Directio
cmd.Parameters(0).Value = session("g_sUserID")
set rst = server.createobject("ADODB
rst.cursortype = 3
set rst.source = cmd
rst.open <--- error occurs here
The stored procedure header is:
PROCEDURE list_all_quotations (
in_userid IN VARCHAR2,
cqid OUT string40_type,
cname OUT string40_type
);
"string40_type" is declared in the package header as:
TYPE string40_type IS TABLE OF VARCHAR2 (40) INDEX BY BINARY_INTEGER;
The SP does compile in Oracle10g and does return the correct results. Any ideas???
You should test the connection to 8i with 2k3 server, then 10g with 2k to determine the cause.
ASKER
Thanks for the suggestion ploubier, but they're on different domains and testing in that manner would introduce more variables with firewall configuration and network communication problems, etc. Turns out not to be necessary anyway (see below).
-- UPDATE --
We have now removed the Oracle 10g client and installed the old Oracle 8i client on the Win2003 webserver. Using the 8i client (still connecting to the 10g database) everything works. This implies that a) the problem is Oracle related and b) it has something to do with the difference between Net8 and Net10.
Does that ring a bell with anyone?
-- UPDATE --
We have now removed the Oracle 10g client and installed the old Oracle 8i client on the Win2003 webserver. Using the 8i client (still connecting to the 10g database) everything works. This implies that a) the problem is Oracle related and b) it has something to do with the difference between Net8 and Net10.
Does that ring a bell with anyone?
No ringtone in my head, but remember that the use of 8i client with 10g server is NOT supported.
Oracle 10g comes with his own ODBC driver, 10g client, etc.
8i client can not work with the very popular AL32UTF8 character set of 9i and 10g.
We faced a similar problem but with JDBC, migrating from 8i to 9i and 10g.
Every new version, every new upgrade comes with new set of connection software.
8i client can not work with the very popular AL32UTF8 character set of 9i and 10g.
We faced a similar problem but with JDBC, migrating from 8i to 9i and 10g.
Every new version, every new upgrade comes with new set of connection software.
hi
follow schwertner's answer
regards
follow schwertner's answer
regards
ASKER
schwertner,
Thanks for the comments. We started with the 10g client and odbc driver installed on the web server but when it didn't work we tried the 8i client to see if it would work. Turns out that it didn't - when it was installed it was pointing to the 8i db not to the 10g database.
So we're back at square one. Using 10g client and ODBC driver but still getting the same error. We've tried installing the client with several different options but no luck. Do you know if there's anything in the database configuration and/or installation that could cause this problem?
Thanks for the comments. We started with the 10g client and odbc driver installed on the web server but when it didn't work we tried the 8i client to see if it would work. Turns out that it didn't - when it was installed it was pointing to the 8i db not to the 10g database.
So we're back at square one. Using 10g client and ODBC driver but still getting the same error. We've tried installing the client with several different options but no luck. Do you know if there's anything in the database configuration and/or installation that could cause this problem?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Joe,
We're working with test systems now so thankfully we're free to experiment :-)
I'm going to award you the points for all of your help - hopefully one of your suggestions will lead us to a solution.
Thanks,
Roger
We're working with test systems now so thankfully we're free to experiment :-)
I'm going to award you the points for all of your help - hopefully one of your suggestions will lead us to a solution.
Thanks,
Roger
ASKER
For anyone reading this thread in the future, turns out that the problem in this case was related to the Win2003 installation. We wiped out the box and reinstalled the OS and everything works fine.