How Can I Output SELECT Statement Results from Within PL/SQL Blocks in Oracle?

Output SELECT statement in PL/SQL block

In Oracle databases, it is often necessary to output the results of a SELECT statement within a PL/SQL block. However, using a SELECT statement directly within a block does not produce the expected output.

Oracle 12.1 and above

Oracle 12.1 introduced implicit result sets, which provide a way to output the results of a SELECT statement in a PL/SQL block. The following code demonstrates this approach:

<code class="language-sql">declare
    rc sys_refcursor;
begin
    open rc for select * from dual;
    dbms_sql.return_result(rc);
end;
/</code>

Early Oracle versions

For earlier versions of Oracle, you can use the ref cursor bind variable to output the results of a SELECT statement. For example, in SQL*Plus, you can use the following code:

<code class="language-sql">set autoprint on

var rc refcursor

begin
    open :rc for select count(*) from dual;
end;
/</code>

This will print the result of count(*) to the screen.

The above is the detailed content of How Can I Output SELECT Statement Results from Within PL/SQL Blocks in Oracle?. For more information, please follow other related articles on the PHP Chinese website!