how to calculate value of string in oracle

Question

Hi If I have a string say "3*2+24" how can calculate its value in Oracle? In sql server you can do exec ('select 3*2+24') and it returns 30

Thanks

Answer 1

In ORACLE you can do any computation you want using the dual feature. For example

SELECT 3*3-(2+2) FROM DUAL

However, you cannot do

SELECT '3*3-(2+2)' FROM DUAL

Because it will just return the string of '3*3-(2+2)'. If all you have is a string, I would probably strip it out into a numeric function then do the calculation.

Answer 2

It's a bit clunkier in Oracle but it can be done.

SQL> set serveroutput on
SQL> declare
  2     str varchar2(250) := '3*2+24';
  3     n number;
  4  begin
  5    execute immediate 'select '||str||' from dual' into n;
  6    dbms_output.put_line(str||' = '||to_char(n)||' !!');
  7  end;
  8  /

3*2+24 = 30 !!

PL/SQL procedure successfully completed.

SQL>

Answer 3

Here's a little function to calculate arbitrary strings of arithmetic:

SQL> CREATE OR REPLACE FUNCTION calc(pi_val VARCHAR2) RETURN NUMBER IS
  2  v_return NUMBER;
  3  BEGIN
  4    EXECUTE IMMEDIATE 'select '||pi_val||' from dual' INTO v_return;
  5    RETURN v_return;
  6  END;
  7  /

Function created
SQL> SELECT calc('2*6*10') FROM dual;

CALC('2*6*10')
--------------
           120

SQL>

Obviously, for production purposes you'd need some error handling...

Answer 4

You can use DCookie's solution.

You can decrease the possibility of an sql injection attack by creating a new user without privileges.

Log in as system and create a new user without privileges:

create user new_user identified by password_new_user;

Create the function (as system) in schema new_user.

create or replace function new_user.calc(pi_val varchar2) return number
is
  v_return number;
begin
  execute immediate  'select '||pi_val||' from dual' INTO v_return;
  return v_return;
end;
/

Next grant execute privileges (as system) on function new_user.calc to the relevant Oracle users (for instance tuinstoel (that's me)).

grant execute on new_user.calc to tuinstoel;

Log in as user tuinstoel.

connect tuinstoel/cheese_and_cheese@ora11

SQL> select new_user.calc('2+3') from dual;

NEW_USER.CALC('2+3')
--------------------
                   5

Everyone who calls the function new_user.calc has the privileges of new_user (none) inside function new_user.calc, not the rights of the caller (definer's privileges not invoker's privileges).

When you don't want to include new_user. in every call to calc do as tuinstoel:

 create synonym calc for new_user.calc;

Next you can test this with:

SQL> select calc('2+3') from dual;

CALC('2+3')
-----------
          5

edit1: Others have contemplated about this solution and they have pointed out some (potential) problems: See http://forums.oracle.com/forums/thread.jspa?forumID=75&threadID=943576 .