Forum Stats

  • 3,784,238 Users
  • 2,254,911 Discussions


How to set that procedure will not be Compiled for debug by default?

Blue Bird
Blue Bird Member Posts: 350 Blue Ribbon
edited Jan 24, 2019 1:34AM in SQL Developer


I have a problem in SQLDev. Since SQLDev 18.3 I'm noticing that when I create new procedures or functions (on remote Oracle 12 server) that are not compiled as Release version as it was always before. Till version 18.2 worked Ok, but on 18.3 and also now on 18.4 this doesn't work any more that way. If I create procedure for example from SQLPlus or SQLcl everything compile normally (as release and not for debugging). Also if I create procedure with SQLDev locally on that server also work fine. So actually I see this when I run procedures from different versions of SQLDev:


But I shouldn't see any green icons. So if I run script with dozen of existing procedures I have to compile each "one by one" as Release version.

Is this a bug in this newer versions of SQLDev or do I have to change some settings so my procedures will be compiled as release (locally or on the server). Also compile button on the toolbar:


behave differently. Before when I directly click on it, it was compiled as Release, now is default Compiled for debug. So if I want to compile procedure I have to click arrow on right side of the gear and select Compile.

1. Can someone of you confirm that same behaviour of SQLDev? If not, why is this happening on my system?

2. How can I run SQLDev from scratch as clean first start without using any previous settings? Can I for example move folder with settings from 18.4 on location "c:\Users\UserName\AppData\Roaming\SQL Developer\" into some other folder just to check if then work Ok, and later move it back so I wont lost all my connections and customization settings?

3. Exist in SQLDev some command where can I export all setting including connections and then restore it back if I want to?

4. Can I somehow compile all my procedures as Release version at once? Right click > Compile all... compile all but doesn't change Debug version into Release version.

Thanks for your help.


Best Answer


  • John McGinnis-Oracle
    John McGinnis-Oracle Member Posts: 243 Employee
    edited Jan 23, 2019 12:37PM Accepted Answer

    What is the Optimization Level set to in the preferences for each version (Preferences -> Database -> PL/SQL Compiler)?

  • Blue Bird
    Blue Bird Member Posts: 350 Blue Ribbon
    edited Jan 23, 2019 2:59PM

    John, Thank you very much for your exact question. You found a needle in the hay. Now all puzzle came together. The answer is: If you don't want to compile procedure for debug by default you have to have Optimization Level set on 2 or higher. Month ago I was looking for a solution around debugging and it was suggested to me to change from default value 2 to 0. And I did that only on my PC not on the server. There is still 2, so because of that worked there normally. Now work again this part. So If you want to debug and change variable values you have to set this to 0, otherwise you have to set it back to 2, otherwise your procedures, won't be compiled as release by default.

    Suddenly with this minor change also toolbar button "Compile" automatically change it's functionality!! It's like if you add on Ms Word toolbar button "Save As". Then you change one little setting in Options and the same button (+ icon and tooltip) suddenly become just "Save". Briilliant designed application. I haven't something like that yet and I hope I won't never again.


  • John McGinnis-Oracle
    John McGinnis-Oracle Member Posts: 243 Employee
    edited Jan 23, 2019 4:40PM

    The toolbar button has three options, if you will. If you just click it, it compiles the PL/SQL using the defaults you have specified for SQL Developer. If you click the little down arrow, you get a menu with two additional options - Compile  and Compile with Debug. Both options apply to just the current compile and will either explicitly compile without debug information or with. So you have the ability to specify the global behavior (using the Preferences setting) and then override it for individual PL/SQL units (via the dropdown menu or the context menu on the object in the navigator).

  • Blue Bird
    Blue Bird Member Posts: 350 Blue Ribbon
    edited Jan 24, 2019 1:34AM

    Yes, I know it has down arrow and three options. But I think if user click the icon with the same picture (direct click without using down arrow) functionally (assigned command) shouldn't be changed. I think this is bad practice. Apples are apples and pears are pears, even are both fruits.