Re: Can a procedure contain only a SELECT statement?

> But for the purposes of this thread, the topic is stored procedures
> and returns data sets either to an interactive user using SQLDeveloper
> or an application.

As far as I can tell, your initial question was "Can a procedure contain only a SELECT statement?", which David Fitzjarrell answered in the first response.

If you are looking to *return* a data set, you need a function. If you want to use a resultset produced from a *procedure*, you need an out variable of sys_refcursor type, such as:

SQL> create table t(c number);

Table created.

SQL> insert into t select rownum from all_objects where rownum <= 10;

9 rows created.

SQL> commit;

Commit complete.

SQL> create or replace procedure p_data(p_data out sys_refcursor) is   2 begin
  3 open p_data for select * from t;
  4 end;
  5 /

Procedure created.

SQL> variable b refcursor
SQL> exec p_data(:b)

PL/SQL procedure successfully completed.

SQL> print b

         C

	 1
	 1
	 2
	 3
	 4
	 5
	 6
	 7
	 8
	 9

10 rows selected.

SQL> You can use this with any modern language such as java, python, C#, etc.

HTH, Steve Received on Fri Mar 19 2010 - 18:48:43 CDT