How to SELECT inside a DECLARE block? [duplicate]

Question

Context:

As a developer in a large company, I am allowed to query (read-only) any table in the database and see the result (for debugging and investigation purposes).

When I do so (either from sqldeveloper or directly from IntelliJ or literally any app that has a scratchpad and a connection to the DB), I write some SELECT statements and see the result immediately.

But writing SELECTs is not enough : The company has a large and intricate code base of SQL functions -- which do not only contain SELECT but also have branching (if...then.. etc.). I cannot just copy-paste the queries they contain into my scratchpad!

Problem :

In order to call an SQL function, I must wrap the call in a DECLARE...BEGIN...END block, as per SQL syntax. But then, I am forced to do SELECT INTO and put the result into a variable.

Question:

How can I see the result of the DECLARE...BEGIN...END block (assuming that ultimately I'm always getting either the result of a SELECT, or a cursor) ?

============

EXAMPLE:

  -- A function from an external SQL package, which I cannot modify or maybe even see.
  function get_whatever()
  return sys_refcursor as
    v_select_cursor sys_refcursor;
  begin
    ...
  end;


  -- My own scratchpad : 
  declare
  begin
     get_whatever(); -- how do I display the result of this as easily as I would display a simple SELECT?
  end;

Alex Poole · Accepted Answer · 2025-11-12 14:18:04Z

1

In SQL Developer you can do this:

variable rc refcursor;
begin
   :rc := get_whatever();
end;
/
print rc

... and run all of that as a script.

The client var[iable] command defines a bind variable called rc. The anonymous block assigns the result of the function call to that bind variable, referenced within PL/SQL as :rc with a colon. The client print command does the work of interpreting and printing the ref cursor. (Those links are for the SQL*Plus documentation but most of it applies to SQL Developer and SQLcl too.)

You can shorten is a bit by using the exec[ute] command (nothing to do with execute immediate):

variable rc refcursor;
exec :rc := get_whatever();
print rc

Since Oracle 12c you can also use implicit results. Your existing function doesn't do that, but your anonymous block could:

declare
   rc sys_refcursor;
begin
   rc := get_whatever();
   dbms_sql.return_result(rc);
end;
/

The effect is basically the same as using var/print and your other clients may well handle it elegantly - though it seems IntelliJ does not, and others like DataGrip might not either. (Note that the assignment is now just plain rc := ..., not :rc := ...)

You can also run the function directly as described in this article by Jeff Smith, which lets you see the results as a grid. I haven't used that approach much, I find the print is usually quicker and simpler for an ad hoc call, but you may prefer it.

edited Nov 12 at 14:18

answered Nov 11 at 16:14

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

jeancallisti Nov 11 at 16:25

That's a solution for SQLDeveloper, thanks! My only beef is that it's not portable. Is there a solution in pure PLSQL? Something that would work in IntelliJ or any generic IDE for quickly querying SQL?

Alex Poole Nov 11 at 16:39

Other clients sometimes borrow functionality from Oracle's clients - I think Toad can do the var/print version, but no idea about IntelliJ. I've added an implicit results option which ought to work more generally.

Matthew McPeak Nov 11 at 16:40

You are looking for DBMS_SQL.RETURN_RESULT, I think.

jeancallisti Nov 14 at 9:30

That is supported by Oracle SQL Developer, but not IntellJ and not DataGrip.

jeancallisti Nov 12 at 13:23

@Alex I have marked your answer as the correct answer but please add a note stating that it's not enough to be on Oracle 12c; you also have to use a SQL client that supports parsing the result of dbms_sql.return_result. "Oracle SQL Developer" can. "IntelliJ" or "DataGrip" cannot.

Professor Packetsniffer · Accepted Answer · 2025-11-11 16:59:59Z

0

If you need this regularly, wrap your block in a function that returns a value:

CREATE OR REPLACE FUNCTION test_block RETURN VARCHAR2 AS
  result VARCHAR2(100);
BEGIN
  SELECT my_function() INTO result FROM dual;
  RETURN result;
END;
/

Then call:

SELECT test_block FROM dual;

Now you get the result in the Query Result grid, just like any other query.

answered Nov 11 at 16:59

Professor Packetsniffer

12 bronze badges

2 Comments

jeancallisti Nov 11 at 17:06

Unfortunately as I stated in the problem, my connection is read-only. Also the result type of the function is unknown, not just VARCHAR2. It's a cursor.

Wernfried Domscheit Nov 11 at 17:35

This works only if the query returns exactly one row

Collectives™ on Stack Overflow

How to SELECT inside a DECLARE block? [duplicate]

2 Answers 2

5 Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

2 Comments

Linked

Related