Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Problems after Migration

Posted on 2005-04-06
9
Medium Priority
?
4,369 Views
Last Modified: 2012-05-05
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???
0
Comment
Question by:frodoman
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 8

Expert Comment

by:Pierrick LOUBIER
ID: 13718161
You should test the connection to 8i with 2k3 server, then 10g with 2k to determine the cause.
0
 
LVL 42

Author Comment

by:frodoman
ID: 13721404
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
 
LVL 8

Expert Comment

by:Pierrick LOUBIER
ID: 13722309
No ringtone in my head, but remember that the use of 8i client with 10g server is NOT supported.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 48

Expert Comment

by:schwertner
ID: 13724342
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
 
LVL 5

Expert Comment

by:helpneed
ID: 13725928
hi

follow schwertner's answer

regards


0
 
LVL 42

Author Comment

by:frodoman
ID: 13755641
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
 
LVL 48

Accepted Solution

by:
schwertner earned 1500 total points
ID: 13759777
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
 
LVL 42

Author Comment

by:frodoman
ID: 13761640
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
 
LVL 42

Author Comment

by:frodoman
ID: 13808581
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

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question