Dynamic SQL using a function returning a Boolean
402088Aug 6 2004 — edited Aug 6 2004I have a function which using dynamic SQL, executes an anonymous block. Inside the block is a call to a function that returns a boolean. This boolean is then converted into an integer (because SQL does not support boolean types). This integer is used as the return value.
However when I execute my function, expecting a return of 0, I get a null returned. What am doing wrong?
Am using Bind_variable incorrectly for a return variable?
Here is my code:
CREATE OR REPLACE FUNCTION c_test RETURN NUMBER IS
sql_in CLOB := 'DECLARE '||
'result BOOLEAN;'||
'BEGIN '||
'result := regva30.pms_zz_xfunc.czvxr141'||
'(bgroup_in =>:bgroup,conttype_in =>:conttype_in,mpmb02x_in =>:mpmb02x_in,'||
'usermsg => :usermsg,msg => :msg); '||
':res := sys.diutil.bool_to_int(result);'||
'END;';
cur_id INTEGER := DBMS_SQL.OPEN_CURSOR;
res NUMBER;
flag INTEGER;
umsg VARCHAR2(255);
msg VARCHAR2(255);
BEGIN
-- Call the function
DBMS_SQL.PARSE(cur_id, sql_in, DBMS_SQL.native);
DBMS_SQL.BIND_VARIABLE(cur_id, ':res', res);
DBMS_SQL.BIND_VARIABLE(cur_id, ':bgroup', 'POT');
DBMS_SQL.BIND_VARIABLE(cur_id, ':conttype_in', 'EAMF');
DBMS_SQL.BIND_VARIABLE(cur_id, ':mpmb02x_in', 'OTHR');
DBMS_SQL.BIND_VARIABLE(cur_id, ':usermsg', umsg);
DBMS_SQL.BIND_VARIABLE(cur_id, ':msg', msg);
flag := DBMS_SQL.EXECUTE(cur_id);
dbms_output.put_line(flag);
RETURN res;
END;