Quick Outline: Navigating Your PL/SQL Packages in Oracle SQL Developer

thatjeffsmith SQL Developer 14 Comments

If you’re browsing your packages using the Connections panel, you have a nice tree navigator to click around your packages and your variable, procedure, and functions. But What if you drill into your PL/SQL source from the worksheet and don’t have the Tree expanded? Let’s say you’re working on your script, something like – So I need to reacquaint myself …

Inspecting PL/SQL Collections with Oracle SQL Developer

thatjeffsmith SQL Developer 15 Comments

Our stored procedures often capture information and store that data as a string or number. But sometimes we need to process more than just a single value. PL/SQL allows you to do this via collections. Now, if you’re looking for help getting started with collections, I would start with Using PL/SQL Collections and Records Oracle Docs The PL/SQL Guru, Steven …

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. …