Thanks for the question, Chandana.
Asked: February 22, 2017 - 2:29 pm UTC
Last updated: February 24, 2017 - 10:50 am UTC
Version: 11g
Viewed 1000+ times
You Asked
Hi,
I'm trying to write a function which will accept arithmetic operator(example : mod(100,10)) as input and the result will be from the input arithmetic operation
-----
declare
l_retval varchar2(200);
Function arith_oper(p_attribute14 varchar2,
p_val number)
return varchar2
is
l_final_val varchar2(200);
l_str varchar2(20);
l_tst varchar2(100);
begin
if p_attribute14 is not null and p_val is not null
then
l_str := replace(p_attribute14,'RESULT',p_val);
dbms_output.put_line(l_str);
l_final_val := 'select :l_str from dual';
execute immediate l_final_val into l_tst using l_str;
return l_tst;
end if;
end arith_oper;
begin
l_retval := arith_oper('mod(RESULT,10)',100);
dbms_output.put_line(l_retval);
end;
---
I'm expecting it should give 0 as output but it is giving mod(100,10)
please suggest
and Chris said...
You can't pass a function as a bind variable! Oracle Database treats it as the string "mod(100,10)", not a function.
Do do this, you must concatenate the function into the SQL string:
create or replace function arith_oper (
p_attribute14 varchar2,
p_val number )
return varchar2
is
l_final_val varchar2 ( 200 ) ;
l_str varchar2 ( 20 ) ;
l_tst varchar2 ( 100 ) ;
begin
if p_attribute14 is not null and p_val is not null then
l_str := replace ( p_attribute14,'RESULT',p_val ) ;
dbms_output.put_line ( l_str ) ;
l_final_val := 'select ' || l_str || ' from dual';
execute immediate l_final_val into l_tst;
return l_tst;
end if;
end arith_oper;
/
declare
l_retval varchar2(100);
begin
l_retval := arith_oper ( 'mod(RESULT,10)',100 ) ;
dbms_output.put_line ( l_retval ) ;
end;
/
PL/SQL procedure successfully completed.
mod(100,10)
0
But beware: writing a procedure to accept and execute any function is a recipe for some nasty SQL injection...
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment