I haven’t used Oracle since Uni, and the small amount of PL/SQL I’ve had to do over the last year hasn’t been enough to get me passed the awkward SQL Server developer-trapped-in-a-PL/SQL world phase yet. (Although I am enjoying it much more now I’ve got a real IDE in Toad, compared with the ancient, clunky piece of rubbish I was using before.)

In SQL Server, anything you SELECT inside a procedure will be returned to the caller, which makes it really easy to call an SP in Query Analyzer or similar and see what’s happening. Oracle is, well, different. You need to explicitly return cursors over results, and unless you call it the right way all you’ll get is a handy "PL/SQL procedure successfully completed" message.

Here’s a quick example of how TSQL people can get results out of Oracle with a minimum of fuss. First, say we have a simple procedure that takes a postcode (aka ZIP code) and returns matching results:

CREATE OR REPLACE PROCEDURE GetSamplesByPostcode 
(
    pi_postCode IN NUMBER,
    samples OUT SYS_REFCURSOR
) 
IS
BEGIN
    OPEN samples FOR
    SELECT *
    FROM SAMPLE s
    WHERE s.PostCode = pi_postCode;
END GetSamplesByPostcode;

We can then call this within SQL*Plus or Toad, by turning on autoprint, and by using a bind variable to get the results out:

SET AUTOPRINT ON
VAR sample_cur REFCURSOR;
BEGIN
    GetSamplesByPostcode(2000, :sample_cur);
END;

This will print the relevant results returned by the bound sample_cur variable, and give Toad users a nice grid view of the results too.

Check the Oracle docs for more on bind variables.