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???
LVL 42
frodomanAsked:
Who is Participating?
 
schwertnerCommented:
Hi Frodoman,

you made a dangerous experiment.  I have encountered that 10g is buggy. Example - Data Pump do not works up to the third version of 10g due bugs.

If you are working for Production (I mean no investigations on 10g) then I will recommend to downgrade to 9i (possibly 9.2.0.6) and work in that environment.

Without 8i  components - 8i do not works with AL32UTF8 character set (the strange name of UTF8 in 9i  :) and is out of date.

Another possibility is to experiment with the latest upgrade of 10g which raises on March,28 -10.1.0.4. I tested it - at least the installation on W2000 works smootly, but fails on Linux.

Best wishes

Joe
0
 
Pierrick LOUBIERIS Operational Excellence ManagerCommented:
You should test the connection to 8i with 2k3 server, then 10g with 2k to determine the cause.
0
 
frodomanAuthor Commented:
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?
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
Pierrick LOUBIERIS Operational Excellence ManagerCommented:
No ringtone in my head, but remember that the use of 8i client with 10g server is NOT supported.
0
 
schwertnerCommented:
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.
0
 
helpneedCommented:
hi

follow schwertner's answer

regards


0
 
frodomanAuthor Commented:
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?

0
 
frodomanAuthor Commented:
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
0
 
frodomanAuthor Commented:
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.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.