SELECTing inside a stored procedure

Question

I expected the code like:

create or replace procedure dmp(t in varchar2)
AS
BEGIN
    EXECUTE IMMEDIATE 'SELECT * FROM ' || t;
END;

/

BEGIN
    dmp('SOMETABLE');
END;

to be the same as SELECT * FROM SOMETABLE. However, calling the stored procedure does not actually output anything -- for any table, including the obviously non-empty ones... Why is that? How would I write a stored procedure, that would output result(s) of queries inside it?

What do you mean by "output"? Do you mean print the column values of the result set? Or do you mean to return the result set from the procedure? — MT0, May 25 '21 at 22:03
What is the context? You could define the procedure to have an `out` parameter of type `sys_refcursor`, define a ref cursor variable in `SQL*Plus` or some other tool that supports the `SQL*Plus` substitution variable syntax, and then use the `SQL*Plus` `print` command to fetch the data from the ref cursor variable and print it to the screen. That's great if you're using `SQL*Plus` or a `SQL*Plus` compatible tool. You may have a different context in mind, though. — Justin Cave, May 25 '21 at 22:03
PL/SQL executes entirely within the database and has no means of displaying anything on the client's screen. The closest you can come is to use dbms_output.put_line (look it up), but that only writes to a buffer that is available to the client upon completion/exit from the procedure. It is then the client's decision on whether to process that buffer of output. Also, in PL/SQL, when you SELECT, you need some place to put the result - thus you need the INTO clause on the SELECT. — EdStevens, May 25 '21 at 22:15
@JustinCave Do you mean a [bind variable](https://docs.oracle.com/cd/E18283_01/server.112/e16604/ch_twelve050.htm) rather than a substitution variable? — MT0, May 25 '21 at 22:16
I have reopened the question as the previous duplicate target was an SQL Server question and, in SQL Server, a procedure can return a result set and output it. This is much, much harder in Oracle and having a solution for a different RDBMS does not make sense. — MT0, May 25 '21 at 22:43

score 1 · Answer 1 · answered May 25 '21 at 23:26

1

Assuming that you are using a client like SQL*Plus or one that supports a subset of SQL*Plus commands like SQL Developer, you can do something like this (note that I am ignoring the potential for SQL injection attacks).

variable rc refcursor;
/

create or replace procedure get_cursor( p_tableName in varchar2,
                                        p_rc       out sys_refcursor )
as
begin
  open p_rc for 'select * from ' || p_tableName;
end;
/

begin
  get_cursor( 'dual', :rc );
end;
/

print rc;

answered May 25 '21 at 23:26

Justin Cave

227,342
24
367
384

Would I not be able to `select from` the cursor -- `rc`? Any other way to obtain a proper result-set? Thanks! – Mikhail T. May 27 '21 at 22:06
@MikhailT. - I'm not sure I follow. A cursor isn't a thing that you can select from. Is there something about this approach that doesn't produce a "proper result-set"? – Justin Cave May 27 '21 at 22:20
See the above comment, where @MT0 asks me, whether I want a result set... Does that make sense? – Mikhail T. May 27 '21 at 22:31
@MikhailT. - This returns a result set. Alternatively, you could write code that merely prints out the results of the query from the procedure via `dbms_output`. If you don't know the structure of the result at compile time, though, that's going to be a *lot* more complicated. And it's going to mean that the procedure you write isn't going to be useful from a front-end application. – Justin Cave May 27 '21 at 22:39
I guess, what I really need is a temporary-table -- left available after the procedure exits to be `SELECT`-ed from. I thought, a cursor is that, but, I guess, it is not it. Or, better yet, something like a _generator_ in Python, that'd `yield` the next row from the underlying data-set -- without copying all of it ahead of time. Perhaps, that's simply not possible... – Mikhail T. May 27 '21 at 22:48
Potentially you want a pipelined table function. That's a bit sticky if you really want to pass in arbitrary table names but it's possible in later versions of Oracle. Oracle temporary tables are permanent structures, there are no local temporary tables like, say, SQL Server so that's usually not a great approach. – Justin Cave May 27 '21 at 22:51

SELECTing inside a stored procedure

1 Answers1