Resolve Math Functions PL/SQL

Question

I need to resolve a math equation/function in pl/sql.
What kind of math operations/functions are available in oracle pl/sql that could help me resolve a math function like this :

(3.5/(1+x))+(3.5/(1+x)^2)+(3.5/(1+x)^3)+(3.5/(1+x)^4)+(100/(1+x)^4)=101.55

I want a function to resolve this statement and find out the value of x.

Something like this is what I am looking for

Any help? Thanks.

I presume, since you're using SQL, that the database will supply the value for `x`, is that correct? — cms_mgr, Feb 06 '13 at 11:12
if its not a built in function, you'd either use brute force or write some custom function. Depending on what you want to do, I would look into using Mathematica ( http://www.wolfram.com/mathematica ) — tbone, Feb 06 '13 at 12:33
As your variable x is raised to the power 4 there will be 4 different values for x, right? — Rachcha, Feb 06 '13 at 13:03
tSQL was not ment to solve this kind of problems. Yes, maybe you can do it, but is not inteded for that. — Yaroslav, Feb 06 '13 at 13:06
You could have PL/SQL use the [WolframAlpha API](http://products.wolframalpha.com/api/explorer.html). But requesting, receiving, and parsing the results would probably be very slow. How many equations do you have? — Jon Heller, Feb 06 '13 at 19:35
@jonearles atm I got only this one, but if this worked maybe would try new ones — Hélder Gonçalves, Feb 06 '13 at 20:57

APC · Accepted Answer · 2013-02-19T15:09:06.427

Alas the Oracle database is not a mathematical tool. It has lots of arithmetical and statistical functions but it doesn't have built-in functionality capable of interpreting equations. Sorry.

By sheer coincidence Marc (AKA Odie_63) has recently published a Reverse Polish Notation calculator which he has written in PL/SQL. It doesn't do precisely what you want but I'm including a link for the benefit of any seekers who may stumble upon this thread in the future. Find out more.

score 4 · Answer 2 · answered Feb 09 '13 at 05:37

As APC said, there is no built-in functionality to do this. But you can use the WolframAlpha API from PL/SQL:

declare
    v_equation varchar2(32767) := 
        '(3.5/(1+x))+(3.5/(1+x)^2)+(3.5/(1+x)^3)+(3.5/(1+x)^4)+(100/(1+x)^4)=101.55';
    v_escaped_url varchar2(32767);
    v_uri httpuritype;
    v_xml xmltype;
    v_count number := 1;
begin
    --Escape the URL.
    --I used chr(38) for ampersand, in case your IDE think it's a substitution variable
    v_escaped_url :=
        'http://api.wolframalpha.com/v2/query?appid=EQGHLV-UYUEYY9ARU'||chr(38)||'input='
        ||utl_url.escape(v_equation, escape_reserved_chars => true)
        ||chr(38)||'format=plaintext';

    --Create an HTTPURIType, and get the XML
    v_uri := httpuritype.createUri(v_escaped_url);
    v_xml := v_uri.getXML;

    --Complex solutions
    while v_xml.existsNode('/queryresult/pod[@title="Complex solutions"]/subpod['||v_count||']') = 1 loop
        dbms_output.put_line(v_xml.extract('/queryresult/pod[@title="Complex solutions"]/subpod['||v_count||']/plaintext/text()').getStringVal());
        v_count := v_count + 1;
    end loop;

    --Real solutions
    v_count := 1;
    while v_xml.existsNode('/queryresult/pod[@title="Real solutions"]/subpod['||v_count||']') = 1 loop
        dbms_output.put_line(v_xml.extract('/queryresult/pod[@title="Real solutions"]/subpod['||v_count||']/plaintext/text()').getStringVal());
        v_count := v_count + 1;
    end loop;
end;
/

Results:

x = -1.00006-0.996229 i
x = -1.00006+0.996229 i
x = -1.99623
x = 0.0308219

There are a lot of potential downsides to this approach. It will be very slow, and the API is not free. My example works because I used my free developer appid, but it's only good for a small number of calls.

+1 This is a party trick, and a neat one at that. But many production systems would not allow the database to connect to another site like this. Also your application becomes dependent on a third party's availability. Of course many enterprise systems are built of web services these days, but very rarely calling them from the database. — APC, Feb 11 '13 at 14:07

Resolve Math Functions PL/SQL

2 Answers2

Linked