How to get output of the below procedure

Question

When I execute the below query, the output I am getting is the PL/SQL procedure completed. Instead of the actual result. How can I output the result of the execution of that statement on the screen

Declare
v_sql varchar2(500);
v_schema varchar(30);

v_event varchar2(500):= 'EVENT NAME';
v_email varchar2(200) := 'Name@XYZ.com';
v_prj_id varchar2(4000):='ProjectA,ProjectB,ProjectC,ProjectD';


BEGIN


Dbms_Output.Put_Line('v_prj_id='||v_prj_id);
--
FOR i IN (SELECT trim(regexp_substr(v_prj_id, '[^,]+', 1, LEVEL)) l
        FROM dual 
        CONNECT BY LEVEL <= regexp_count(v_prj_id, ',') + 1 
       ) LOOP
  --
  Dbms_Output.Put_Line('---------------------');
  --
  --
  v_sql := 'UPDATE ' || i.l|| '.Table set email = email ||'';''||:1 WHERE 
EVENT = :2'  ;
  --
  Dbms_Output.Put_Line('v_sql='||v_sql);
  --
  begin
    EXECUTE IMMEDIATE v_sql USING v_email, v_event;
  Exception
    when others then
         Dbms_Output.Put_Line('sqlerrm='||sqlerrm);
  End;
  --
 END LOOP;
 END;

The actual results am getting is PL/SQL procedure completed. but I was expecting the output result of the execution of that statement on the screen

Try executing `SET SERVEROUTPUT ON` before running the code. — Pavel Smirnov, Nov 12 '19 at 07:19

score 0 · Answer 1 · answered Nov 12 '19 at 07:27

0

SET SERVEROUTPUT ON

---execute your procedure

answered Nov 12 '19 at 07:27

Andrew

3,632
24
64
113

Thanks for the information. but i get only the update statement. but i actually need the result of that update statment. – Ganesan VC Nov 12 '19 at 07:35
yes normally with the above statment you should get the result of update..what are you getting on screen after runnig the procedure with set serveroutput on ? – Andrew Nov 12 '19 at 07:40
What is the "result of update" that you expect to see? The only `dbms_output` calls are before the update and in the exception handler. – William Robertson Nov 12 '19 at 16:57

score 0 · Answer 2 · answered Nov 12 '19 at 07:46

0

Total number of rows updated can be checked using SQL%ROWCOUNT.

begin
    EXECUTE IMMEDIATE v_sql USING v_email, v_event;
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT||' rows are updated.');

answered Nov 12 '19 at 07:46

Ashwik

36
2

score 0 · Answer 3 · answered Nov 12 '19 at 11:59

When you execute a PL/SQL procedure, output will be either "PL/SQL procedure successfully completed." or it will display the error in case of one. If you want to output something specific, you have to dbms_output.put_line it.

Even though I'm not sure what you want to output, if you want to check how many rows are updated, you can do:

begin
    EXECUTE IMMEDIATE v_sql USING v_email, v_event;
    dbms_output.put_line(SQL%ROWCOUNT||' rows are updated.');
exception
    when others then
        dbms_output.put_Line('sqlerrm='||sqlerrm);
end;

How to get output of the below procedure

3 Answers3