Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions

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

Comments

Using XMLQUERY

Rajeshwaran, Jeyabal, February 24, 2017 - 2:05 pm UTC

If you could tweak your inputs, then XMLQUERY could help us.

Evaluating Expression in database using XMLQUERY.

demo@ORA11G> variable x varchar2(20)
demo@ORA11G> exec :x := '100 mod 10';

PL/SQL procedure successfully completed.

demo@ORA11G> print x

X
--------------------------------
100 mod 10

demo@ORA11G>
demo@ORA11G> select xmlquery(:x returning content).getNumberVal()
  2  from dual;

XMLQUERY(:XRETURNINGCONTENT).GETNUMBERVAL()
-------------------------------------------
                                          0

demo@ORA11G> exec :x := '104 mod 10';

PL/SQL procedure successfully completed.

demo@ORA11G> /

XMLQUERY(:XRETURNINGCONTENT).GETNUMBERVAL()
-------------------------------------------
                                          4

demo@ORA11G>
demo@ORA11G> variable x varchar2(40)
demo@ORA11G> exec :x := '(525+4-3) idiv 3 div 2 mod 1*3';

PL/SQL procedure successfully completed.

demo@ORA11G> select xmlquery(:x returning content).getNumberVal()
  2  from dual;

XMLQUERY(:XRETURNINGCONTENT).GETNUMBERVAL()
-------------------------------------------
                                        1.5

demo@ORA11G>
demo@ORA11G> variable x varchar2(40)
demo@ORA11G> exec :x := '(525+4-3) div 2 mod (1*3)';

PL/SQL procedure successfully completed.

demo@ORA11G> select xmlquery(:x returning content).getNumberVal()
  2  from dual;

XMLQUERY(:XRETURNINGCONTENT).GETNUMBERVAL()
-------------------------------------------
                                          2

demo@ORA11G>

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Using XMLQUERY

More to Explore

PL/SQL demos

PL/SQL docs

Dynamic SQL to execute a function

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Using XMLQUERY

More to Explore

PL/SQL demos

PL/SQL docs