Oracle Tracing SQL Scripts

With the following code, you can enable ouput in Oracle, in order to debug your SQL scripts;

SET SERVEROUTPUT ON
DBMS_OUTPUT.ENABLE(1000000);

Then you can debug your trigger, procedure or functions by adding the following line, wherever you need an output in your code;

...
DBMS_OUTPUT.PUT_LINE('A test string.. ' | | 'another string..');

[ | | means concatenating the strings]

When you need to measure how much time is consumed between steps of your code, following may help further;

v_time := dbms_utility.get_time;
...

DBMS_OUTPUT.PUT_LINE('Time used: ' | | (dbms_utility.get_time - v_time) / 100 | | ' secs');

whereas v_time declared as number in Oracle;

declare
v_time number;