Binding Variables of Record Type in DBMS_SQL -
04-25-2004
, 05:56 PM
I would appreciate if someone could help
I need to pass IN OUT record variables (Type Record) to a procedure
using DBMS_SQL.
create or replace function func1 (p_emprec IN OUT EMP%rowtype,
p_return_cd OUT number, p_return_msg OUT varchar2) return boolean
as
begin
...
-- assign values to emprec record, to return_cd and return_msg
return (true)
exception
return false;
end;
The calling pl/sql block is somewhat like this.
....
-- the statement to be called
v_stmt := 'begin ' ||
':i_v_result := '||v_func||
'(:i_v_emprec,'||
' :i_v_return_cd, :i_v__return_msg); '||
' end;';
v_cur_id := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(v_cur_id,v_stmt,DBMS_SQL.NATIVE);
-- bind the variables for v_func() call
DBMS_SQL.BIND_VARIABLE(v_cur_id,':i_v_result',v_re sult);
DBMS_SQL.BIND_VARIABLE(v_cur_id,':i_v_emprec',v_em prec);
DBMS_SQL.BIND_VARIABLE(v_cur_id,':i_v_return_cd',v _return_cd);
DBMS_SQL.BIND_VARIABLE(v_cur_id,':i_v_return_msg', v_return_msg);
v_dyn_result := DBMS_SQL.EXECUTE (v_cur_id);
-- get the OUT variables from v_func() call
DBMS_SQL.VARIABLE_VALUE(v_cur_id,':i_v_result',v_r esult);
DBMS_SQL.VARIABLE_VALUE(v_cur_id,':i_v_emprec',v_e mprec);
DBMS_SQL.VARIABLE_VALUE(v_cur_id,':i_v_return_cd', v_return_cd);
DBMS_SQL.VARIABLE_VALUE(v_cur_id,':i_v_return_msg' ,v_return_msg);
DBMS_SQL.CLOSE_CURSOR(v_cur_id);
....
When I compile, I get the following error msgs
PLS-00306: wrong number or types of arguments in call to 'BIND_VARIABLE'
PLS-00306: wrong number or types of arguments in call to 'BIND_VARIABLE'
for first two bind_variables() calls and
PLS-00306: wrong number or types of arguments in call to 'VARIABLE_VALUE
PLS-00306: wrong number or types of arguments in call to 'VARIABLE_VALUE
for first two variables_value() calls
Any workaround for this. Thanks a lot.
-manguesh |