Home » SQL & PL/SQL » SQL & PL/SQL » package specification compilation -> hangs; question about circular references and usage of subty (oracle 10.2.0)
package specification compilation -> hangs; question about circular references and usage of subty [message #356616] Fri, 31 October 2008 09:16 Go to next message
sebastianR
Messages: 33
Registered: August 2005
Member
Dear forum readers!

Please let me introduce you to my problem. I am currently refactoring our PLSQL packages, e.g. removing unused functions, replacing some misused variable types etc.

Please note, that I have my own scheme where I am working on and I am absolutely sure, that no other users access the packages I am working on since I am the only one using my scheme.

That given, I ran into a "hang" as described pretty much like in this post (http://www.orafaq.com/forum/m/323360/75810/?srch=compiling+package+hangs#msg_323360), while compiling a package specification.

After waiting for several minutes for the compilation to end (which usually takes less than 3 seconds) I tried to abort the compilation which was not possible. I then logged in as SYS to kill the session which hanged with no luck. Therefor I had to restart the database which of course fixed the problem with the hanging session.

Ok, so at that point I was not too sure if I really was the only one working on my scheme, so I tried to compile the same package specification again (this time beeing absolutly sure there was no other one working on the whole database) and again, my session hanged immediatly.

Since I usually have no problem with recompiling packages after applying changes, I tend to think that my current changes are responsible in some way for this reoccuring problem.

What I did change, is the way I use types and subtypes in our API packages.

Imagine that I have two packages which each have one table with the same name below them:

package CLASS#;
package CLASSIFICATION#;

Until now, each package for itself, had declared several subtypes based on the table types like this:

=== TABLE CLASS ===
- k_id NUMBER
- classifier_id NUMBER (FK constraint on CLASSIFICATION.k_id)
- some_info VARCHAR2
===================

=== TABLE CLASSIFICATION ===
- k_id NUMBER
- name VARCHAR2
- some_comment VARCHAR2
============================

with these subtypes and a method defined in the CLASS# package
CREATE OR REPLACE PACKAGE CLASS#
AS
SUBTYPE t_k_id IS CLASS.K_ID%TYPE;
SUBTYPE t_classifier_id IS CLASS.CLASSIFIER_ID%TYPE;
SUBTYPE t_some_info IS CLASS.SOME_INFO%TYPE;
TYPE t_rec is CLASS%ROWTYPE;

PROCEDURE do_something(
      i_classifier_id      IN   t_classifier_id,
      i_name               IN   VARCHAR2,
      i_some_info          IN   t_some_info);

...

and these in CLASSIFICATION# package:

CREATE OR REPLACE PACKAGE CLASSIFICATION# 
AS
SUBTYPE t_k_id IS CLASS.K_ID%TYPE;
SUBTYPE t_name IS CLASSIFICATION.NAME%TYPE;
SUBTYPE t_some_comment IS CLASSIFICATION.SOME_INFO%TYPE;
TYPE t_rec is CLASSIFICATION%ROWTYPE;

...


Imagine that the classifier column of the CLASS table has a reference (foreign key) on the CLASSIFICATIONs primary key(k_id).

So now I thought to myself, that it would be ugly to declare a SUBTYPE t_classifier_id in the class package based on the class table when I could use the SUBTYPE t_k_id declared in the CLASSIFICATION package directly in the CLASS package as followed:

CREATE OR REPLACE PACKAGE CLASS#
AS
SUBTYPE t_k_id IS CLASS.K_ID%TYPE;
SUBTYPE t_some_info IS CLASS.SOME_INFO%TYPE;
TYPE t_rec is CLASS%ROWTYPE;

PROCEDURE do_something(
      i_classifier_id      IN   CLASSIFICATION#.t_k_id,
      i_name               IN   VARCHAR2,
      i_some_info          IN   t_some_info);

...


I hope you get the idea of what I want to accomplish.

What I'd like to know now is,

1) Is it a good/bad idea to use subtypes declared in other packages directly the way I showed above?
2) What if there are 3 packages, each using subtypes of each other so that there is a circular referencing like package "A" uses subtypes of package "B", package "B" uses subtypes of package "C" and package "C" again uses subtypes of package "A"? Could this lead to a hang somehow when compiling a package specification? Is it maybe just important, in which order to compile the packages?
3) Why use SUBTYPES in general? Why not use the table-types directly in this context? Is it only readability?
4) Is there a coherence in my new way of using SUBTYPES and the recently occuring session "hangs" while compiling package specifications?

I appreciate every comment on the topic of subtypes, especially if it clarifies, why my session hangs each time I want to compile a specific package specification.

Thank you very much for your comments!

Yours,

Sebastian
Re: package specification compilation -> hangs; question about circular references and usage of s [message #357471 is a reply to message #356616] Wed, 05 November 2008 08:46 Go to previous messageGo to next message
sebastianR
Messages: 33
Registered: August 2005
Member
Dear readers,

I've now written a little tool in C#, which parses all of my PKS files and detects circular type definitions, the way I described in my first post above.

Unfortunately, there are no circular definitions in my packages - thus I tend to believe, that the inability to successfully compile a specific package specification file has its origin somewhere else.

I'd still appreciate to get some comments on my situation and on usage of subtypes in general.

Thanks for your time!

Yours,

Sebastian
icon14.gif  SOLUTION found [message #358767 is a reply to message #357471] Wed, 12 November 2008 07:42 Go to previous message
sebastianR
Messages: 33
Registered: August 2005
Member
Hello forum!

I've now discoverd the reason for the problem of compilation hang when compiling a PKS package specification.

It has nothing to do with usage of referencing subtypes in general, it was simply a typo issue.

Try compiling a PKS file with the following content, to trigger a session hang which makes a database restart necessary:

CREATE OR REPLACE PACKAGE CUSTOMER#
AS
/* correct, it there is a table CUSTOMER with a column ID  */
/*   SUBTYPE t_id IS CUSTOMER.ID%TYPE; */
/* wrong */
     SUBTYPE t_id IS CUSTOMER#.t_id;
     PROCEDURE nothing;   
END;


yours,

Sebastian
Previous Topic: need an idea
Next Topic: insert /*+ append */ into UNIFORM_TEST
Goto Forum:
  


Current Time: Sun Dec 11 04:09:55 CST 2016

Total time taken to generate the page: 0.06823 seconds