Search code examples
oracle-databaseplsqloracle12cdbms-output

Capture SQL/Developer Script Output in PL/SQL

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.

Solution

  • Use SQL%ROWCOUNT:

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