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.

Thomas Kirchhoff · Accepted Answer · 2021-01-26 21:01:28Z

4

Use SQL%ROWCOUNT:

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

answered Jan 26, 2021 at 21:01

Thomas Kirchhoff

1,1181 gold badge7 silver badges14 bronze badges

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

1 Answer 1