Link to home
Start Free TrialLog in
Avatar of frodoman
frodomanFlag for United States of America

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-06550: 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.list_all_quotations(?, {resultset 2000, cqid, cname})}"
   cmd.CommandType = 1
   cmd.Parameters(0).Direction = 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???
Avatar of Pierrick LOUBIER
Pierrick LOUBIER
Flag of France image

You should test the connection to 8i with 2k3 server, then 10g with 2k to determine the cause.
Avatar of frodoman

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?
No ringtone in my head, but remember that the use of 8i client with 10g server is NOT supported.
Avatar of schwertner
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.
Avatar of helpneed
helpneed

hi

follow schwertner's answer

regards


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?

ASKER CERTIFIED SOLUTION
Avatar of schwertner
schwertner
Flag of Antarctica image

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
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
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.