Help! The issue of no responding when executing large packages in PL/SQL Developer.

[redwood]

Hi, guys! We have some PL/SQL packages that have more then 2000 lines codes. When these packages are executed in PL/SQL Developer, the Developer will be no responding. But the small packages which have less then 1500 lines codes can be executed successfully and quickly. And all packages locating on the same database can be executed with no problems and quickly on server side and another client program such as Toad 8.0.

The execution environment situations:
Client Side:
PL/SQL Developer 6.0.6
Oracle ODBC Driver 9207
Server Side:
Oracle 9i 9207

The operations:
1. double click PL/SQL Developer shutcut on desktop;
2. connect with the specific database;
3. open the package by the mode 'Edit Spec & Body';
4. click the button 'execute';
5. PL/SQL Developer is no responding;

What problem with PL/SQL Developer?

 

[Claus Pedersen]

Just a question.

Are you sure that no other sessions have locks on the package you are trying to compile (including other compile sessions, that you may have been forced to kill)? If other sessions are using the package, Oracle locks the package for changes.

In my company we have experienced problems when debugging large packages, but never compiling problems (when the package is not locked).

Are you adding debugging info? Try to turn this off.

You can execute the following script to check for locks:

SELECT * FROM v$access WHERE object = '<package name>'

If a row is returned the package is locked. Use the returned SID and check in v$session which session is locking this package:

SELECT sid, serial#, osuser, username FROM v$session WHERE sid = '<SID>'

Kill this session and wait until the session is removed from the session list. If the sessions is marked as KILLED it is in the process of being killed and the package is still locked. Wait until PMON has removed the session completely.

 

[redwood]

I am sure that there are no other sessions locking on the package I am trying to execute in PL/SQL Developer. I executed the script checking for locks, but no rows selected.

 

[Claus Pedersen]

What about debug?

 

[redwood]

you can view the error picture by accessing this link:
http://photo.163.com/photos/zhangchong1980/37488701/1014760665/

 

[redwood]

I just click the button 'execute' (highlight by red line) after editing the large package spec & body, the PL/SQL Developer will have no responding. But the small packages are ok.

 

[redwood]

I found I can not run the package in the debug because the debug related function buttons are grey and disable.

 

[Claus Pedersen]

In the menu Tools - Preferences - Debugger you can remove the checkbox 'Add debug information when compiling'. This will disable adding debug info to the packages.

I have noticed that you have many invalid package bodies. What about your package headers? If your current package is dependent of any of these packages and one of these packages can not compile because of other locks, the compilation of your current package will also lock. Try to compile all invalid packages.

Otherwise you could try to make the package smaller (remove some methods with comments) and try if it can compile now.

As I have stated before, I have not experienced PLD to hang during compile unless the package for some reason was locked or due to Oracle errors (adding debug info).

 

[redwood]

Thanks for your kindly reply. I have disabled 'Add debug information when compiling' option, but the issue remains. And I also compile all packages in the Toad. But in PL/SQL Develper, the issues remains. Because our develpers don't like to abandon this tool, we still hope to solve the problem. Do it relate with Oralce NET or Oracle version compatibility issue?

 

[Marco Kalter]

Can you go to Help > About, press the 'Info' button, press the 'Copy all pages' button on this info screen, and paste this text into an e-mail reply?