CODEWITHSUNDEEP
oracle-databaseoracleforms
Abdullah
Abdullah

Reputation: 1

Oracle Forms 6i : assign a text field T ('3+11+7') to a number field N (:N := to_number(:T);) gives error ora-01722

I am using Forms 6i with oracle 11g R2. I have a text field T that have value = '3+11+7'. I want sum of text field. when i assign T to a number field N (:N := to_number(:T)). It gives ora-01722. I want total of text field that is 21 to the next field that is number field. plz suggest, how can i do this. Thanks

This is the illustration :

enter image description here

Upvotes: 0

Views: 324

Answers (1)

Littlefoot
Littlefoot

Reputation: 142968

Forms isn't particularly good at executing some commands; its PL/SQL engine is oldfashioned, but - it can call a stored function which runs in the database.

SQL> create or replace function f_result (par_value in varchar2)
  2    return number
  3  is
  4    retval number;
  5  begin
  6    select to_number(xmlquery(par_value returning content))
  7      into retval
  8      from dual;
  9
 10    return retval;
 11  end;
 12  /

Function created.

Testing:

SQL> select f_result('3+11-2') result from dual;

    RESULT
----------
        12

SQL>

In Forms, I created a simple control block with two fields: one accepts numeric operations you want, another one displays the result. How? By executing the WHEN-VALIDATE-ITEM trigger on the 1st field. Its code is as simple as

:result := f_result(:values);

enter image description here

Upvotes: 2

Related Questions