Team Coding Check-in Problem with New Packages

Toad for Oracle
1

We've been having an issue with Team Coding for quite a few releases now where a package gets "stuck" during the check in/check out process. We finally figured out how to reliably replicate this issue. It occurs when we are creating a new package and the package spec is compiled BEFORE the package body. When the body is compiled, this error shows in the output log:

06:29:40 Output: Cannot check-out package (SCHEMA.PACKAGE): You already have package (SCHEMA.PACKAGE) checked out.

After this point, the package is "stuck" and trying to check it back in results in this error:

06:40:39 Error: ORA-02291: integrity constraint (TOAD.TCX_OBJHIST_OBJ_PROJ_FK) violated - parent key not found ORA-06512: at line 1

Looking in the TOAD.TCX_OBJECTS table, the package body has an OBJECT_ID of -1. The only way we've been able to get around this is to remove the package altogether from both TCX_OBJECT_HISTORY and TCX_OBJECTS (not ideal of course).

I upgraded just today to Toad 14.1 and still seeing this issue. We are running Team Coding without VCS. Anyone else encountering this problem?

2

Hi Annie,

I tried to reproduce the issue you're describing, but every package I created and compiled separately between the spec and body created valid Object ID's in the Team Coding tables.

Team Coding is designed to check out both the spec and body portions of packages and types together. The warning stating that you already have the item checked out is actually normal since you created and checked out the spec separately. The warning is simply letting you know that the spec is already checked out. If you have prompting on checkout enabled, you should see the spec object deselected and listed in red to let you know it's already checked out.

In my tests, both spec and body received valid Object ID's. The fact that in your situation you received an object ID of -1 sounds like either Toad wasn't able to successfully query the database for the object ID or the data in your Team Coding tables somehow became corrupt.

For the first issue, you can enable SQL Spooling from the Database -> Spool SQL -> Spool SQL to screen, then try to check the object back in, and see the query being run against either user_objects or all_objects to get the object ID. You can then use that query to verify the object ID in the database, and then compare its value with the one being used in the UPDATE or INSERT statement to TCX_OBJECTS to see if there's a discrepancy. That may help identify if there's a database or permissions issue.

For the latter option, you can query TCX_OBJECTS for the package name to see if there are any duplicate entries listed or any entries pointing to an invalid PROJECT_ID. If there are, as you suggested, you can remove them from the database, open Team Coding configuration window and then press OK. Team Coding should re-add them to the tables using the information in the database. I know this isn't the ideal solution, but it should help restore those entries with their proper object ID's.

If neither of these items help in identifying the cause of the issue, if you could provide a step-by-step set of instructions with as much detail as possible to recreate this scenario, that would be helpful. Unfortunately, every combination of checking out, compiling, and checking in both spec and body I've tried resulted in valid records with valid object ID's. Getting some additional details would definitely be helpful.

Thanks!

-John

4

Oops. Looks like responding through email messed up the post. Reposting here...

5

Hi John,

Thanks so much for the quick response!

I turned on SQL spooling and was able to see the insert into TCX_OBJECTS when I compiled my package spec – everything looked good there. When I compiled the package body, I can see it querying DBA_OBJECTS and it looks like it’s getting a -1 from there for the insert into TCX_OBJECTS. I guess I should have also mentioned this package is not being compiled in my user schema.

I’m not seeing any issues with the PROJECT_ID in the TCX tables so I think that’s all fine.

Here are some step-by-step instructions of how I’m able to create this problem in our environment:

  1. Open a new editor window and execute this code to create a new package spec outside of my user schema:

    CREATE PACKAGE someschema.pkgtest
    AS
    PROCEDURE p;
    END pkgtest;

  2. Open a new editor window and execute this code to create the matching package body:

    CREATE PACKAGE BODY someschema.pkgtest
    AS
    PROCEDURE p
    IS
    BEGIN
    NULL;
    END p;
    END pkgtest;

  3. At this point, I’m prompted for a check out of the body and I click ‘Yes’.

  4. I close the editor windows and open a Schema Browser to find the new package.

  5. Right click on the package and select Team Coding/Check-in and then I see this output:

    07:24:34 Info: Package (SOMESCHEMA.PKGTEST) checked in to Team Coding
    07:24:35 Error: ORA-02291: integrity constraint (TOAD.TCX_OBJHIST_OBJ_PROJ_FK) violated - parent key not found ORA-06512: at line 1
    07:24:35 Output: Error performing check in: on package body (SOMESCHEMA.PKGTEST)

  6. Querying TCX_OBJECTS will show output that looks like this:

image001
image001709×89 3.05 KB

For the time being we’re working around this by asking our developers to compile a new package body before they compile the spec, which of course results in an Oracle error but it doesn’t cause the issue and it gets worked out once the new spec is created and the body is recompiled. Anyhow, appreciate any help you can give!

Thanks,
Annie

6

Hi Annie,

Unfortunately, I'm still not able to reproduce the issue you're seeing internally using Toad 14.1. I'm curious if there might be some permissions issue you're running into that's preventing you from retrieving the object details for the package body.

Are you sure it's querying DBA_OBJECTS and not ALL_OBJECTS? What happens if you take the query against DBA_OBJECTS and paste that into the editor and execute it there? Does it return the proper result with the proper object ID?

If so, can you send me your SQL Spool output in a private message on here or offline (john.bowman@quest.com) so I can take a look at the queries being run in your situation?

Thanks,

-John

7

It might also be helpful to get a "CREATE USER" script for the user that you are logged in as, so we can see the privileges that you have.