Hi,
I'm trying to use dbms_sql to execute an anonymous pl/sql block.
Essentially I want to call a function and retrieve the function's
return value. Everything works fine if the function returns an
integer. However, I can't make it work if the function returns
a varchar2. FYI, I'm on Solaris/Oracle 7.3.3.6.
I've included a sample script below that shows the problem.
Anybody else successfully done this?
Thanks in advance,
Paul Postel
paulp_at_fastx.com
set serverout on
create or replace function fn_int return integer is
begin
return 999;
end;
/
create or replace function fn_varchar2 return varchar2 is
begin
return '999';
end;
/
/*
- Bind an integer variable to an integer function's result,
- this works.
*/
declare
sql_block varchar2(255);
curs integer;
my_result integer;
rc integer;
begin
sql_block := '
begin
:out_val := fn_int;
end;';
curs := dbms_sql.open_cursor;
dbms_sql.parse(curs, sql_block, DBMS_SQL.NATIVE);
dbms_sql.bind_variable(curs, ':out_val', my_result);
rc := dbms_sql.execute(curs);
dbms_sql.variable_value(curs, ':out_val', my_result);
dbms_output.put_line('my_result = ' || to_char(my_result));
dbms_sql.close_cursor(curs);
end;
/
/*
- Bind a varchar2 variable to a varchar2 function's result,
- this fails with:
*
- ORA-06502: PL/SQL: numeric or value error
- ORA-06512: at line 3
- ORA-06512: at "SYS.DBMS_SYS_SQL", line 348
- ORA-06512: at "SYS.DBMS_SQL", line 141
- ORA-06512: at line 14
*/
declare
sql_block varchar2(255);
curs integer;
my_result varchar2(20);
rc integer;
begin
sql_block := '
begin
:out_val := fn_varchar2;
end;';
curs := dbms_sql.open_cursor;
dbms_sql.parse(curs, sql_block, DBMS_SQL.NATIVE);
dbms_sql.bind_variable(curs, ':out_val', my_result);
rc := dbms_sql.execute(curs);
dbms_sql.variable_value(curs, ':out_val', my_result);
dbms_output.put_line('my_result = ' || my_result);
dbms_sql.close_cursor(curs);
end;
/
Received on Thu May 06 1999 - 16:25:47 CDT
