Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Dynamic SQL using a function returning a Boolean

402088Aug 6 2004 — edited Aug 6 2004
I 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;
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 3 2004
Added on Aug 6 2004
2 comments
422 views