If there is some bug on code it must be simulate
every time.rite ?
of course not, there are errors you can get on one execution that you do not on another - for example, ORA-1555 snapshot too old, you might get that error once out of ever 100 runs (or more or less)
answer me this - if hour ora_raise thing raises an error, why do you have this line of code:
EXCEPTION
WHEN OTHERS THEN
ora_raiserror(SQLCODE,
' Error in updation of test table',
154);
var_l_return := -1; END;
that bit of code could never be reached if what you say is true. so tell me, why does it exist?
sure looks like a return code to me. are you sure of what you say??????
I'm very suspicious of your code - you are doing some funky error handling. You have code that either does return code processing (bad idea in 2013) OR you are in fact raising the error here (which means you have suspicious dead code, whoever wrote this didn't understand how things work)...
to merge, just generate the set of cod_acct_no/streams you want and merge that into the original table:
merge into test
using (
SELECT cod_acct_no,
NTILE(var_pi_cod_stream) OVER(ORDER BY cod_xfer_brn) stream
FROM test )
GROUP BY cod_acct_no ) x
on (test.cod_acct_no = x.cod_acct_no )
when matched then update set cod_stream = x.stream;