Wednesday, May 5, 2010

(Not a ) Bug in Debug Procedures in SQLDev

Today an OP in SQLDeveloper forum at OTN had complained about "disappearing" Green icon on Procedures (To read the post click here Note: Sue has pointed out that it is not a bug.I have posted an update below on how to use compiler with debug option. So if you just want to know how to turn on or off the compiler with debug option, just scroll down. If you have time to kill  :-), go ahead and read the entire post. ) . So, back to my original post.... 
I just happen to have Windows version SQLDeveloper (Build Version 2.1.1.63) and Linux Version (Build Version 2.1.1.64) installed. Just a heads up for those who are wondering about the "green" icons, when you compile a procedure, in SQLDeveloper, if the procedure compiles successfully, then it will display a Green icon on the left of the procedure name. If the procedure fails to compile a "red" stop icon is displayed - pretty cute isn't it? Only that it does not seem be consistent across versions (or is it across OS versions, I wonder)?
Since I usually used Linux, I was not sure what the OP was talking about. I created a few test procedures in 63 (windows version) first (Screen -01).
Windows Build version 63. Notice missing Proc toolbar (Screen -01)

Everything went off without any hitch, although it did not display the Procedure Complier bar, it was not a show stopper. I could compile repeatedly the procedure without any issue, and update the parameter for this procedure (Screen -02).
Modify the parameter (Screen -02)


Next, I started my linux version and recreated the same procedure. Bingo! The first time procedure got compiled and the green icon was displayed. I could see the Procedure 'Compilation' Toolbar. However, when I compiled again, (Screen -03)

Linux Build 64 (notice the Procedure Tool bar (Screen 03)

When I complied "with Debug option", I could see the green icon now (Screen -04)

Go for Green (Compiled with Debug) (Screen -04)

But, I cannot modify the parameter window (Screen -05). I hope SQLDeveloper team will take note of this and correct the bug.

Cann't change the parameter (Screen -05)

Update:
As Sue Harper had pointed out, it was a case of how I compiled. Default Compilation option for SQLDeveloper is to "Compile with Debug"
If you want to reset the default preferences to "compile and no Debug" go to Tools-->Database-->PLSQL Compiler and click on the optimization level and set it to 2. Note: This setting "tells" compiler to optimize the PLSQL Library units. (Screen -06)


After setting the preferences to zero, I reran the procedure and was able to change the input parameter ( See below) .



The log ouput window is shown below. Thanks Sue.


You might also want to read this post ( If using windows:-

How to get SQLDeveloper working on Windows 7

If you are using *nix then

How to make SQLDeveloper work on Linux

)

Oracle Learning Library



Thanks to Sue's post (click here to read her post), I learned that, Oracle has introduced a Learning Library, includes online demonstrations, including APEX tutorials and SQLDeveloper demos. Truly worth watching. Click here to go to Oracle Learning Library. It is easy to search either by Product or by Category. For example to find out how to use SQLDeveloper for Microsoft Access, you would select "Database" under the "Category" and "SQL Developer" under "Product Component" and system provides a search output as shown below. Click on hyperlink under the column "Title" and a new window is launched displaying the Demo.