Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm doing a little project and I need to do a plsql to return many rows.

I have someting like:

Table(id, data1, data2, from)

And the user gives me the value of from and I have to return all that.

The SQL is easy:

SELECT * FROM Table WHERE from = dataIntroduced

But, I don't know how the plsql can return that.

I read about collections but I think is not what I need

share|improve this question
Why can't you simply issue your SELECT (without involving plsql)? – dezso May 22 '12 at 9:47
because is a class project and they ask that way... i'm not confortable too with this because i can do views and selects as you say but they ask me to do in a procedure. the problem is i been searching oracle docs since 2 days now and i'm not able to find nothing about this. – nax May 22 '12 at 10:58
here are a few solutions:… – dezso May 22 '12 at 11:01
up vote 2 down vote accepted

One way of return select data from PL/SQL is using Oracle table functions. Below is an example where the line SELECT * FROM table_a where test_a = par1; should be replaced by your select statement + change the table name.

Create table Table_a
(test_a varchar2(1))

insert into table_a

create or replace package test_package as
TYPE col_table_1 is table of TABLE_A%ROWTYPE;
function test_plsql_table(par1 varchar2) return col_table_1

create or replace package body test_package as
  function test_plsql_table(par1 varchar2) return col_table_1
    cursor temp_cur is
      SELECT * FROM table_a where test_a = par1;
    for cur_rec in temp_cur loop
      pipe row(cur_rec);
    end loop;


SELECT * from TABLE( test_package.test_plsql_table('a'))
share|improve this answer
You should not use ; and / for regular DDL (it's correct for PL/SQL, but your create table and insert into will be executed twice by SQL*Plus (because / re-runs the current buffer) – a_horse_with_no_name May 22 '12 at 15:00
Quite right, sorry. I will make the amendments now. – Ryan Kenning May 23 '12 at 7:01

The simplest possible approach is to return a cursor. Something like

create or replace function get_emps_in_dept( p_dept_in IN number )
  return sys_refcursor
  l_ret sys_refcursor;
  open l_ret
   for select *
         from emp
        where deptno = p_dept_in;
  return l_ret;

You can then call that in SQL*Plus by declaring a host variable (most front-end languages will treat a function returning a SYS_REFCURSOR very much like a SELECT statement that returns a cursor)

SQL> variable rc refcursor;
SQL> exec :rc := get_emps_in_dept( 30 );

PL/SQL procedure successfully completed.

SQL> print rc;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1601        300

      7521 WARD       SALESMAN        7698 22-FEB-81       1251        500

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1251       1400

      7698 BLAKE      MANAGER         7839 01-MAY-81       2851

      7844 TURNER     SALESMAN        7698 08-SEP-81       1501          0

      7900 JAMES      CLERK           7698 03-DEC-81        951

6 rows selected.

You can return a collection, either all at once or pipelined, but that requires more code to create and maintain the objects. I love pipelined table functions if you need to embed PL/SQL logic in the query itself that would be difficult to follow in SQL. But they're overkill if you just need to run a simple query and return the results.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.