Debug......

Can any one tell me that how to debug the Procedure...

'show errors' at the sql prompt.

HTH.

show errors will give syntax errors if procedure didn't compile successfully.

If you get wrong results, you can help yourself by putting some
"dbms_output.put_line" in the code to see what/how it executes.

Or if you want more, check dbms_debug.

Scott Urman in the Oracle Press "PL/SQL Programming" Ch18 has a nice simple routine for writing out debuging info during execution (sorry, can't post it for copyright reasons - couldn't find it on the Web). It writes with UTL_FILE doing a FFLUSH after each line to ensure you never loose stuff in buffers.

The great adavantage is that if you set up some kind of parameter to switch it off/on, you can leave it in place even for the production system. So the day your code works fine in test but fails in prod, you will have some handle on it.

The DBMS_ solutions aren't easy (possible?) to run in production, especially if you have a 3rd party front-end.

You can use JDeveloper for debugging PL/SQL procedures.