plsql - Sync between two tables and show the result in an oracle function -

plsql - Sync between two tables and show the result in an oracle function -

i making oracle function sync values between 2 tables , intention show string show how many rows affected , displays when user execute function.

my function creation this

create or replace function weltesadmin.dum_mst_sync(projname in varchar2) homecoming number num_rows number; begin merge master_drawing dst using (select weight_dum, head_mark_dum dummy_master_drawing) src on (dst.head_mark = src.head_mark_dum) when matched update set dst.weight = src.weight_dum dst.project_name = src.project_name_dum , dst.project_name = projname; dbms_output.put_line( sql%rowcount || ' rows merged' ); end;

if execute begin part in toad or sql developer can see how many rows affected. target collect function procedure , when user wants sync tables need run procedure projname value supplied specific project.

please help me on how improve code, best regards

you can utilize sql%rowcount number of rows affected merge. add together next statement in code after merge :

dbms_output.put_line( sql%rowcount || ' rows merged' );

to homecoming value, declare number variable , assign sql%rowcount value it. , homecoming value. :

function ....... homecoming number ....... num_rows number; ...... begin merge.... num_rows := sql%rowcount; homecoming num_rows; end;

and, don't need procedure execute function. can execute in sql :

select function(project_name) dual /

update since op trying utilize dml within function, need create autonomous transaction able perform dml without raising ora-14551.

you utilize directive pragma autonomous_transaction. run function independent transaction able perform dml without raising ora-14551. however, remember, results of dml committed outside of scope of parent transaction. if have single transaction, utilize workaround suggested. add, pragma autonomous_transaction; after return statement before begin.

create or replace function weltesadmin.dum_mst_sync( projname in varchar2) homecoming number num_rows number; pragma autonomous_transaction; begin merge master_drawing dst using (select weight_dum, head_mark_dum dummy_master_drawing ) src on (dst.head_mark = src.head_mark_dum) when matched update set dst.weight = src.weight_dum dst.project_name = src.project_name_dum , dst.project_name = projname; num_rows := sql%rowcount; commit; homecoming num_rows; end; /

oracle plsql