Problems after Migration

Posted on 2005-04-06
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    <---  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:


The SP does compile in Oracle10g and does return the correct results.  Any ideas???
Question by:frodoman
    LVL 8

    Expert Comment

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

    Author Comment

    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?
    LVL 8

    Expert Comment

    by:Pierrick LOUBIER
    No ringtone in my head, but remember that the use of 8i client with 10g server is NOT supported.
    LVL 47

    Expert Comment

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

    Expert Comment


    follow schwertner's answer


    LVL 42

    Author Comment


    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?

    LVL 47

    Accepted Solution

    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 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 - I tested it - at least the installation on W2000 works smootly, but fails on Linux.

    Best wishes

    LVL 42

    Author Comment


    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.


    LVL 42

    Author Comment

    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.


    Featured Post

    Courses: Start Training Online With Pros, Today

    Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

    Join & Write a Comment

    Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
    I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
    This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
    This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

    755 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now