How to debug the procedure or function?

By using DBMS_OUTPUT & DBMS_DEBUG oracle supplied package.

ALTER PROCEDURE procedurename

DEBUG;

OR

ALTER PROCEDURE procedurename

PLSQL_DEBUG=TRUE

OR

We can use autonomous_transaction procedure to create log & put this procedure sss_proc inside of main procedure.

EX :

create table sss_temp(a varchar2(4000));

create sequence sss_seq;

create or replace procedure sss_proc (a varchar2 )

as

pragma autonomous_transaction;

Begin

insert into sss_temp values (a, sss_seq.NEXTVAL);

Commit;

End;