Formatting PL/SQL stored procedure output

Question: I am new to the wonderful world of Oracle. I want to be able to view the results of a stored procedure in an output window, say out of Oracle SQL developer. Unfortunately it appears I need to write some more code to actually view the data.

On a more generic note, can anyone explain to me why Oracle has chosen to make PL/SQL inordinately more complicated than say MS SQL/Servers tSQL? I mean in tSQL I would just write:

CREATE OR REPLACE PROCEDURE TESTSPROC2
AS
select * from test_table order by id_no;
GO

and viola, a nice result set spits out in Query Analyzer (or a .net application).

Answer by Steve Karam:

Before I go on, let me say I agree that PL/SQL is more powerful (read the original post to see the debate). That being said, here are your options.

1. Test it with REFCURSOR using a FUNCTION and selecting from dual:

SQL> create or replace function testfunc return sys_refcursor
  2  as
  3    c_test sys_refcursor;
  4  begin
  5    open c_test for select first_name, last_name,

email from employees where rownum < 10;
  6    return c_test;
  7  end;
  8  /

Function created.

SQL> select testfunc() from dual;

TESTFUNC()
???????
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

FIRST_NAME           LAST_NAME                 EMAIL
??????? ????????- ????????-
Steven               King                      SKING
Neena                Kochhar                   NKOCHHAR
Lex                  De Haan                   LDEHAAN
Alexander            Hunold                    AHUNOLD
Bruce                Ernst                     BERNST
David                Austin                    DAUSTIN
Valli                Pataballa                 VPATABAL
Diana                Lorentz                   DLORENTZ
Nancy                Greenberg                 NGREENBE

9 rows selected.

2. Use the same function and return it into a variable:

SQL> variable rc refcursor
SQL> exec :rc := testfunc()

PL/SQL procedure successfully completed.

SQL> print rc

FIRST_NAME           LAST_NAME                 EMAIL
-------------------- ------------------------- -------------------------
Steven               King                      SKING
Neena                Kochhar                   NKOCHHAR
Lex                  De Haan                   LDEHAAN
Alexander            Hunold                    AHUNOLD
Bruce                Ernst                     BERNST
David                Austin                    DAUSTIN
Valli                Pataballa                 VPATABAL
Diana                Lorentz                   DLORENTZ
Nancy                Greenberg                 NGREENBE

9 rows selected.

3. Use your procedure with a variable:

SQL> create or replace procedure testproc(c_test out sys_refcursor) is
  2  begin
  3    open c_test for select first_name, last_name,

email from employees where rownum < 10;
  4  end;
  5  /

Procedure created.

SQL> variable rc2 refcursor
SQL> exec testproc(:rc2);

PL/SQL procedure successfully completed.

SQL> print rc2

FIRST_NAME           LAST_NAME                 EMAIL
??????? ????????- ????????-
Steven               King                      SKING
Neena                Kochhar                   NKOCHHAR
Lex                  De Haan                   LDEHAAN
Alexander            Hunold                    AHUNOLD
Bruce                Ernst                     BERNST
David                Austin                    DAUSTIN
Valli                Pataballa                 VPATABAL
Diana                Lorentz                   DLORENTZ
Nancy                Greenberg                 NGREENBE

9 rows selected.

#3 is more in-line with your original needs. Personally I?m a fan of #1 and #2 because of the capabilities of returning a refcursor as a function, like passing it into DBMS_XMLGEN.GETXML.