This discussion is archived
8 Replies Latest reply: Nov 26, 2012 3:12 PM by rp0428 RSS

Function executing in wrong schema

974629 Newbie
Currently Being Moderated
I have posted this on the APEX thread in case it is APEX specific but I think it is more likely to be database related.

Hello,

I am experiencing a rather worrying (although interesting) problem from an APEX app. I have a page which calls a packaged function from two different regions on the same page. the function is called to retrieve costing data for either the current record or a previous record (controlled by a in parameter in). The first call executes the function from our "test" schema and retrieves data correctly. When called the second time it is executing the function from our development schema and retrieving (invalid) records from the "development" schema! In each case the function (and package) name is not prefixed by the target schema nor indeed are any of our DML statements as we want all code to be portable form DEV to TEST to LIVE. Calling the same function from SQL developer consistently retrieves data from the development scheme irrespective of the in_params. I am using Oracle XE11.2 with Oracle 11.2.0.2.0 and APEX 4.0.2.00.09. I have found some docs on line referring to a similar issue in 10g but assumed the bug had been fixed so I am wondering if the issue is APEX related.

I should add that we have four seperate schemas but all tables, views, functions, procedures, packages etc are called the same in each schema

Grateful for any help
  • 1. Re: Function executing in wrong schema
    user296828 Expert
    Currently Being Moderated
    Are you using synonymous? if so check the synonym of second function and see if it is pointing to the right schema.
  • 2. Re: Function executing in wrong schema
    Dom Brooks Guru
    Currently Being Moderated
    It sounds APEX / application related to me.

    Can you add some logging to the calls somehow?
    Log the connection being used and some of the connection attributes - e.g sid, user, schemaname.
    Calling the same function from SQL developer consistently retrieves data
    It would do. You've got a single connection to a specific user and a default schema unless you change via ALTER SESSION SET CURRENT_SCHEMA.
  • 3. Re: Function executing in wrong schema
    974629 Newbie
    Currently Being Moderated
    Many thanks to both of you for getting back to me so quickly.

    11g,

    No we are not using synonyms.

    Dom,

    I will add some logging info to the function and post some results on here on Monday.

    Chris.
  • 4. Re: Function executing in wrong schema
    735589 Newbie
    Currently Being Moderated
    Any updates on this issue?

    We're experiencing the same or similar issue. Our scenario is a little different though. Our app is a .NET web app that uses ODP.NET to connect to the Oracle db. The db server in question is a shared server that is used by multiple project teams for staging a number of customer implementations. Each implementation project has its own schema and each schema has a copy of the same objects. What we are finding is that occasionally a package procedure that gets called by our web app will return results from the wrong schema (i.e. the package proc in schema X is called, but results get returned for schema Y). Oracle note 392673.1 mentions a bug that sounds the exact same as what we are experiencing, but the note states that the bug was resolved in 10.2.0.3 and our db version is 11.2.0.1.0.

    When we begin experiencing this bug, the only thing that seems to resolve it is recompiling the package in the "wrong" schema (i.e. schema Y). Resetting the IIS instance (i.e. IISRESET) doesn't fix it, rebooting the web server doesn't fix it, nor does recompiling the package in the "right" schema (i.e. schema X). Only recompiling the package in the "wrong" schema seems to do the trick.
  • 5. Re: Function executing in wrong schema
    Dom Brooks Guru
    Currently Being Moderated
    (i.e. the package proc in schema X is called, but results get returned for schema Y).
    1. Prove it - at some point you're going to have to. Trace the sessions, etc.
    2. You should open your own thread.
  • 6. Re: Function executing in wrong schema
    735589 Newbie
    Currently Being Moderated
    Dom: with regard to #1:
    We have done diagnostics to determine if what we have been experiencing is in fact an issue in the db and it does appear to be. Using tracing built into our application, we have verified that the "right" connection string is being used (i.e. the connection string for schema X). Using ODP.NET logging, we can see that the only connection pool being created is for the "right" schema (i.e. Schema X) and that all connections are being opened for schema X. No connection pools are created or connections opened for the wrong schema (i.e. Schema Y). Using log statements within the db package proc that is being called, we've been able to verify that it is the package in schema Y that is being called instead of schema X. Furthermore, when the issue begins occurring, recompiling the package in the "wrong" schema (schema Y) fixes it. The issue remains fixed for a period of time and seems to begin occurring again only when when the db is under load. This is consistent with the behavior reported in Oracle note 392673.1.

    with regard to #2:
    I believe the issue that we are experiencing is the same issue that the OP reported.

    One of our DBAs pointed me to additional info on this issue. Oracle bug 10165083 (https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=10165083.8) reports that what appears to be the same/similar issue exists in 11.2.0.1 and 11.2.0.2. Another Oracle bug - 13080778 (https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=13080778.8) - reports the same/similar issue in 11.2.0.3. The issue is reported as having been fixed in the 11.2.0.3.4 patch set update. We will be upgrading the db in question to this patch set to verify that the issue is resolved in that version.
  • 7. Re: Function executing in wrong schema
    Dom Brooks Guru
    Currently Being Moderated
    Ok. Apologies. I got the impression from your previous post that there were a lot of assumptions and not a lot of proof.
    If you've done the relevant testing/tracing then it should be relatively easy to prove that the code is executed in schema X but a cursor parsed from schema Y is executed, etc.

    I've read the notes on these bugs, they're just nasty.
  • 8. Re: Function executing in wrong schema
    rp0428 Guru
    Currently Being Moderated
    >
    with regard to #2:
    I believe the issue that we are experiencing is the same issue that the OP reported.
    >
    So what? Open a new thread and post your particulars there. If you think this thread is useful add a link to it in your new thread.

    Using your own thread makes it easier for responders to target questions directly to you and for you to answer those questions, post your DDL or code and post YOUR trace files, etc.

    It is really confusing to ask one person to run a test or provide some DDL or code and someone else posts theirs instead because they want help.

    Please open your own thread, post a link to it in this thread so people (e.g. Dom) can find it easily and abandon this one.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points