Using SQL Developer to Debug your Anonymous PL/SQL Blocks

thatjeffsmith SQL Developer 18 Comments

Everyone knows that SQL Developer has a PL/SQL debugger – check! Everyone also knows that it’s only setup for debugging standalone PL/SQL objects like Functions, Procedures, and Packages, right? – NO! SQL Developer can also debug your Stored Java Procedures AND it can debug your standalone PLSQL blocks. These bits of PLSQL which do not live in the database are …

How to find dependent objects in your PL/SQL Programs using SQL Developer

thatjeffsmith SQL Developer 9 Comments

I’ve answered this question a few times over the past several months, but I’m just now getting around to putting my answer onto ‘paper.’ The common scenario is someone decides to alter a table. Immediately the paranoid – is it really paranoia if someone is actually out to get you? – start to worry about how many programs they’ve just …

PLSQL Warning Messages in Oracle SQL Developer

thatjeffsmith SQL Developer 2 Comments

If you have problems in your PL/SQL program that prevent compilation and execution of said program, then that is reported back as an ‘error.’ It could be as simple as missing a semicolon – CREATE OR REPLACE FUNCTION "COUNTRIES_ROWS_BACK" RETURN sys_refcursor IS l_curvar sys_refcursor; BEGIN OPEN l_curvar FOR ‘SELECT * FROM countries’   RETURN l_curvar; END COUNTRIES_ROWS_bACK; When you run …

SQLDev Trick: SHIFT+Hover to Peek Into a Stored Proc

thatjeffsmith SQL Developer 5 Comments

I’m poking around an unfamiliar schema or environment. I’m not sure what these procedures do. I could open them, but I’m lazy and I don’t want the tool to open another document/window. The ‘mystery’ code: These programs are self-documenting as they have the most awesome names ever! I’m guessing that one package will blast my enemies?, but I’m not sure. …

Viewing PLSQL Compilation Errors in Oracle SQL Developer

thatjeffsmith SQL Developer 6 Comments

A question that comes up fairly frequently revolves around how to see your errors when working with PL/SQL in SQL Developer. Most folks are probably working in the worksheet – this is the default editor for your connection. Let’s take a look at this sample program CREATE OR REPLACE PROCEDURE do_nothing IS BEGIN dbms_output.put(sysdate); this should probably error OUT, RIGHT? …