How to automatically display the output of all SQL statements inside an anonymous PL / SQL block

Question

Our data migration scenarios use anonymous PL / SQL blocks, which help to remove code, mainly, so we can set the columns for creating and updating a user identifier for a system user.

Our migration scripts look something like this:

DECLARE
    v_user_id users.id%TYPE;
BEGIN
    SELECT id INTO v_user_id FROM users WHERE username = 'system';

    UPDATE table1
    SET col1 = value,
        updated_at = SYSDATE,
        updated_by = v_user_id
    WHERE some condition;

    INSERT INTO table2 (val1, SYSDATE, v_user_id);
END;
/

The user who updated the entry is a numeric identifier from our users table, not the row username. This was a requirement from our data modeling team, otherwise I would just program the username of our "system" account.

, DBA , , /, . .

, sqlplus, :

Updated X rows
Inserted Y rows

, INSERT UPDATE PL/SQL.

, DBMS_OUTPUT.PUT_LINE.

DML PL/SQL DBMS_OUTPUT.PUT_LINE ?

Answer 1

, .

. , , DML - , . , , . DELETE , , MERGE. , , DML.

create table users(id number, username varchar2(100));
insert into users values(1, 'system');

create table table1(col1 number, updated_at date, updated_by number);
insert into table1 values(1, null, null);
insert into table1 values(2, null, null);

create table table2(col1 number, updated_at date, updated_by number);

DBMS_OUTPUT

. . , , DBMS_OUTPUT.DISABLE, - , , .

.

create or replace package print_feedback is
    --Outputing large amounts of data can sometimes break things.
    --Only enable DBMS_OUTPUT when explicitly requested.
    g_print_output boolean := false;
end;
/

TRUE.

--Run this block first to enable printing.
begin
    print_feedback.g_print_output := true;
end;
/

PL/SQL Block INSERT UPDATE

INSERT UPDATE.

PL/SQL . , , . , . (, , IDE , q'[ , StackOverflow.)

--Create automatic UPDATE and INSERT feedback triggers.
declare
    c_sql_template constant varchar2(32767) :=
    q'[
create or replace trigger #TABLE_NAME#_#UPD_or_INS#_trg for #UPDATE_OR_INSERT# on #TABLE_NAME# compound trigger

--Purpose: Print a feedback message after updates and inserts.
g_count number := 0;

after each row is
begin
    g_count := g_count + 1;
end after each row;

after statement is
begin
    if print_feedback.g_print_output then
        if g_count = 1 then
            dbms_output.put_line('#Inserted_or_Updated# '||g_count||' row in #TABLE_NAME#');
        else
            dbms_output.put_line('#Inserted_or_Updated# '||g_count||' rows in #TABLE_NAME#');
        end if;
    end if;
end after statement;

end;
    ]';
    v_sql varchar2(32767);
begin
    --Loop through the relevant tables
    for tables in
    (
        select table_name
        from user_tables
        where table_name in ('TABLE1', 'TABLE2')
        order by table_name
    ) loop
        --Create and execute update trigger.
        v_sql := replace(replace(replace(replace(c_sql_template
            , '#TABLE_NAME#', tables.table_name)
            , '#UPD_or_INS#', 'upd')
            , '#UPDATE_OR_INSERT#', 'update')
            , '#Inserted_or_Updated#', 'Updated');
        execute immediate v_sql;
        --Create and execute insert trigger.
        v_sql := replace(replace(replace(replace(c_sql_template
            , '#TABLE_NAME#', tables.table_name)
            , '#UPD_or_INS#', 'ins')
            , '#UPDATE_OR_INSERT#', 'insert')
            , '#Inserted_or_Updated#', 'Inserted');
        execute immediate v_sql;
    end loop;
end;
/

script . ( script, , .)

SQL>    --Run this block first to enable printing.
SQL>    set serveroutput on;
SQL>    begin
  2             print_feedback.g_print_output := true;
  3     end;
  4     /

PL/SQL procedure successfully completed.

SQL> DECLARE
  2      v_user_id users.id%TYPE;
  3  BEGIN
  4      SELECT id INTO v_user_id FROM users WHERE username = 'system';
  5
  6      UPDATE table1
  7      SET col1 = 1,--value,
  8          updated_at = SYSDATE,
  9          updated_by = v_user_id
 10      WHERE 1=1;--some condition;
 11
 12      INSERT INTO table2 values(2/*val1*/, SYSDATE, v_user_id);
 13  END;
 14  /
Updated 2 rows in TABLE1
Inserted 1 row in TABLE2

PL/SQL procedure successfully completed.

SQL>

Answer 2

, - oracle oracle , PL/SQL , , , DML DML. . ,

DECLARE
    v_var VARCHAR2(10);
    PROCEDURE run_dml (p_dml VARCHAR2)
    IS
    BEGIN
        EXECUTE IMMEDIATE p_dml;
        DBMS_OUTPUT.PUT_LINE(p_dml);
        DBMS_OUTPUT.PUT_LINE(sql%rowcount||' rows '||REPLACE(LOWER(TRIM(SUBSTR(p_dml, 1, 6)))||'ed.', 'eed', 'ed'));
    END;
BEGIN
   v_var := 'hello too';
   run_dml(q'[INSERT INTO test1_log VALUES ('hello')]');
   run_dml(q'[DELETE FROM test1_log WHERE log1 = 'hello']');
   run_dml(q'[UPDATE test1_log SET log1 = 'hello1']');
   run_dml('INSERT INTO test1_log VALUES('''||v_var||''')');
END;
/

INSERT INTO test1_log VALUES ('hello')
1 rows inserted.
DELETE FROM test1_log WHERE log1 = 'hello'
1 rows deleted.
UPDATE test1_log SET log1 = 'hello1'
1 rows updated.
INSERT INTO test1_log VALUES('hello too')
1 rows inserted.

Answer 3

SQL * Plus .., OCI. OCI , , PL/SQL , , , , , , , . . ( Oracle , , PL/SQL, , , .)

You may be able to enable auditing at an appropriate level of detail, and then query DBA_AUDIT_TRAILafter each call and filter by timestamp, user, and terminal to restrict the report to the last call in the current session, but it sounds as if even it would be in some way of what you are looking for.

Answer 4

spool "D:\test\test.txt"

-- YOUR ANONYMOUS PL/SQL Block here

spool off  

this will give you your desired one and without using DBMS_OUTPUT.PUT_LINE, the output will be in the specified path