utAssert.eq_refc_query - Dynamic values in the SQL Query parameter

[eswar]

hi - I am using utAssert.eq_refc_query to compare the REFCUROSOR back from the stored procedure.

It works great! Question though is I use tow IN parameter and an OUT parameter defined. And while subsituting variables in the SQL parameter in utAssert.eq_refc_query, is there a way to pass in the parameter value rather than hard-coding?

utassert.eq_refc_query(p_msg_nm => 'refcursor test2 '
,proc_name => 'PKG_sample.pr_get_data'
,params => l_params
,cursor_position => 3
,qry => 'select name, code
from t_test_data
where name in (''test'')
and date IN TRUNC(TO_DATE(''02-Jan-2015'',''dd-Mon-yyyy'')));

In the above statement I would like to pass the 'test' and date value as an variable rather than hard-coded value in the SQL?

[eswar]

Again, I have posted this little too soon. got the answer for this.

utassert.eq_refc_query(p_msg_nm => 'refcursor test2 '
,proc_name => 'PKG_sample.pr_get_data'
,params => l_params
,cursor_position => 3
,qry => 'select name, code
from t_test_data
where name in (''||var1||'')
and date IN ''||var2||'');

Thank you.

[eswar]

Though, One thing I am not able to do is using Ref Cursor in throws method.

when I try to substitute the ref cursor variable in the throws method like below, it doesn't recognise it with the local ref cursor variable. Do we need to catch this kind of exceptions using any different method?

PROCEDURE ut_nulldata IS
var sys_refcursor;

BEGIN

--p_cav_cusrsor CURSOR;
utAssert.throws('Null Fund Exception',
'PKG_sample.pr_get_data(''name'',''curr'',''01-Jan-2015'','||var||');',-20100);
END;

definition of the procedure -

PROCEDURE pr_get_data(p_name IN VARCHAR2,
p_code IN VARCHAR2,
p_date IN DATE,
p_refcusrsor OUT SYS_REFCURSOR);

Error -
PLS-00363: expression 'var' cannot be used as an assignment target