Hi All,

If i give the input like (d10/h72)*30

then the ouput sholud be (d10_30)/h72. And also i have to store these values in a table

Formula Operator
======== ----------
d10_30 *
h72 /

for d10_30 the oprator is * because we are multiplying d10 with 30 and also for the second record its division. Actually the input is a column in table.

Iam giving one more example

input: (((A+B)/x)+C)*D

1st step: ((A+B+Cx)/x)*D

2nd step: (AD+BD+CDx)/x

3rd step :(A_D+B_D+C_D_x)/x

And the value in the 3rd step i have to store in the database like below.

Formula Operator
======== ----------
A_D *
b_D *
C_D_X *
x /

for this i wrote a function

create or replace function pp_sf return number is
cursor cur_1 is select long_formula from p_key_chk;
str_len number;
str varchar2(500):= null;
str1 varchar2(500);
asci_num number;
max_num number;
opr varchar2(5);
str_opr varchar2(2);
str_opr1 varchar2(2);
max_cnt number;
f_opr number;
f_opr_s varchar2(10);
SEQ NUMBER := 1;
--cursor cur_2 is select variables from p_key_chk_k where num = (select max(num) from p_key_chk_k)
begin
for i in cur_1
loop
select length(i.long_formula) into str_len from dual;
for j in 1..str_len
loop
select ascii(substr(i.long_formula,j,1)) into asci_num from dual;
if(asci_num > 39 and asci_num <48 ) then
if (str is not null) then
insert into p_key_chk_k(sr_no,variables,num) values (SEQ,str,max_num);
SEQ := SEQ+1;
str := null;
end if;
if(asci_num = 40 ) then
select count(num) into max_cnt from p_key_chk_k;
if max_cnt = 0 then
max_num := 1;
else
select max(num) into max_num from p_key_chk_k;
max_num := max_num+1;
end if;
end if;
select chr(asci_num) into str from dual;
insert into p_key_chk_k(sr_no,variables,num) values (SEQ,str,max_num);
SEQ := SEQ+1;
if str=')' then
max_num := max_num - 1;
if(max_num = 0 ) then
max_num := null;
end if;
end if;
str := null ;
else
select chr(asci_num) into str1 from dual;
str := str||str1;
if j = str_len then
insert into p_key_chk_k(sr_no,variables,num) values (SEQ,str,max_num);
SEQ := SEQ+1;
str := null;
end if;
end if;
end loop;
end loop;
return 0;
end pp_sf;

its giving out put like :

SQL> select * from p_key_chk_k ;
VARIABLES OPERATORS NUM SR_NO
---------- --------- ------ ------
K89 1
+ 2
k9 3
+ 4
AA9 5
+ 6
C89 7
/ 8
( 1 9
D77 1 10
+ 1 11
E15 1 12
) 1 13
* 14
E15 15
15 rows selected

now i have to get the second step

for ex:

((A+B)/x)+C------> (A+B+Cx)/x

and also i have to store the operator value in the table.

please adivise me for this problem.

the table structures are given below

desc p_key_chk_k
Name Type Nullable Default Comments
--------- ------------ -------- ------- --------
VARIABLES VARCHAR2(10) Y
OPERATORS VARCHAR2(2) Y
NUM NUMBER(5) Y
SR_NO NUMBER(5) Y
SQL>
SQL> desc p_key_chk
Name Type Nullable Default Comments
------------ ------------- -------- ------- --------
LONG_FORMULA VARCHAR2(500) Y