dbTalk Databases Forums  

Binding Variables of Record Type in DBMS_SQL

comp.databases comp.databases


Discuss Binding Variables of Record Type in DBMS_SQL in the comp.databases forum.



Reply
 
Thread Tools Display Modes
  #1  
Old   
manguesh
 
Posts: n/a

Default 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

Reply With Quote
Reply




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.