How do I get a stored procedure to output the table that gets returned from a select statement in SQL*Plus?

Question

I want to get the information about the users in my database.

This is what I have:

Table:

create table Users(
userID int CHECK (userID > 0),
email varchar2(30) NOT NULL, 
age varchar2(30) NOT NULL,
numberBookedSoFar int,
primary key(userID)
);

Procedure:

CREATE or REPLACE PROCEDURE user_info() AS 
BEGIN
SELECT age, COUNT(userid) AS numberofusers,
AVG(numberbookedsofar) AS avgbooked 
FROM users 
GROUP BY age;
END;

But, nothing is happening.

That will get a compilation error, not nothing. Which version of Oracle are you using? (And why are you using a subquery?) — Alex Poole, Feb 27 '17 at 20:34
Please be more clear, what source do you have and what you need to achieve — Oto Shavadze, Feb 27 '17 at 20:38
Your procedure runs a query, but you have not asked it to do anything more. — BobC, Feb 27 '17 at 20:45
You can do this in Oracle 12c (note this requires the 12c client): http://stackoverflow.com/a/40360471/230471 — William Robertson, Feb 28 '17 at 11:11

Oto Shavadze · Accepted Answer · 2017-02-27T21:15:47.963

EDIT

As I understand, you need return table from stored function

CREATE TYPE users_obj IS  OBJECT (age varchar2(30), numberofusers int, avgbooked numeric);

CREATE TYPE users_t IS TABLE OF users_obj;

CREATE or REPLACE FUNCTION  user_info  RETURN users_t
IS
      users_table users_t := users_t();
      n INT := 0;
BEGIN
      FOR i IN (SELECT age, COUNT(userid) AS numberofusers, AVG(numberbookedsofar) AS avgbooked  FROM users  GROUP BY age) -- < here is your query
      LOOP
           users_table.EXTEND;
           n := n + 1;
           users_table(n) := users_obj(i.age, i.numberofusers, i.avgbooked);
      END LOOP;
     RETURN users_table;
END;


-- So, now you can call function  which returns result as table
SELECT  *  FROM  TABLE (user_info);

Yes, it does. I ran the query on its own, but nothing happens when I try to run this. — Juliana Hill, Feb 27 '17 at 20:29

score 0 · Answer 2 · answered Feb 27 '17 at 23:58

I like to use types defined in packages. This allows me to group types together in a tight namespace. I wouldn't call this better than the other answer, just different. Here is an example:

CREATE OR REPLACE PACKAGE deleteme_pkg
AS
    TYPE example_t IS RECORD
    (
        text         VARCHAR2 (10)
      , textlength   INTEGER
    );

    TYPE example_tt IS TABLE OF example_t;

    FUNCTION sample
        RETURN example_tt
        PIPELINED;
END deleteme_pkg;

CREATE OR REPLACE PACKAGE BODY deleteme_pkg
AS
    -- Very contrived, in practice, the rows will be from a select statement
    FUNCTION sample
        RETURN example_tt
        PIPELINED
    AS
        l_rec   example_t;
    BEGIN
        l_rec.text         := 'This';
        l_rec.textlength   := LENGTH (l_rec.text);
        PIPE ROW (l_rec);
        l_rec.text         := 'is';
        l_rec.textlength   := LENGTH (l_rec.text);
        PIPE ROW (l_rec);
        l_rec.text         := 'a';
        l_rec.textlength   := LENGTH (l_rec.text);
        PIPE ROW (l_rec);
        l_rec.text         := 'test';
        l_rec.textlength   := LENGTH (l_rec.text);
        PIPE ROW (l_rec);
    END sample;
END deleteme_pkg;

-- now select from the package function
select * from table(deleteme_pkg.sample);

score 0 · Answer 3 · answered Feb 28 '17 at 08:28

Just a variation of the above answer. Usin BULK COLLECT. Hope this helps.

CREATE OR REPLACE type CUSTOM_OBJ
IS
  object
  (
    AGE        NUMBER,
    USER_ID    NUMBER,
    AVG_BOOKED NUMBER
);

CREATE OR REPLACE TYPE custom_tab
IS
  TABLE OF CUSTOM_OBJ;


CREATE OR REPLACE
  FUNCTION USER_INFO
    RETURN CUSTOM_TAB
  AS
    tab custom_tab;
  BEGIN
    SELECT custom_obj(age,numberofusers,AVGBOOKED) BULK COLLECT
    INTO tab
    FROM
      (SELECT age,
        COUNT(userid)          AS numberofusers,
        AVG(NUMBERBOOKEDSOFAR) AS AVGBOOKED
      FROM users
      );
    RETURN TAB;
  END;

How do I get a stored procedure to output the table that gets returned from a select statement in SQL*Plus?

3 Answers3