oracle - Capture SQL/Developer Script Output in PL/SQL

Question

In SQL/Developer, when I execute a statement I get a Script Output message that reads something like 7 Records Merged

Here's my relevant PL/SQL Code:

v_sql_loader := 'merge into foobar b using foobiz i on (b.foobar_id = i.foobiz_id) when not matched then insert (foobar_id) values (foobiz_id)';

execute immediate v_sql_loader;

How can I capture 7 records Merged in PL/SQL?

I'm writing dbms_output.put_line(SQLERRM) but that only returns ORA-0000: normal, successful completion

What Oracle/PLSQL object can I use to replicate SQL/Developer Script Output messages?

Desired Results: PLSQL Statement, preferably dbms_output.put_line();that indicates 7 Records Merged.

question from:https://stackoverflow.com/questions/65909132/capture-sql-developer-script-output-in-pl-sql

Answer 1

Use SQL%ROWCOUNT:

BEGIN
   ...
   EXECUTE IMMEDIATE v_sql_loader;
   DBMS_OUTPUT.PUT_LINE('Merged: ' || SQL%ROWCOUNT);
END;
/