Hi,
I'm trying do run the following PL/SQL block:
DECLARE
v_trad_cur CLOB;
v_trad_par VARCHAR2 (1000);
cur PLS_INTEGER := DBMS_SQL.open_cursor;
fdbk NUMBER;
retorn VARCHAR2 (1000);
vdummy NUMBER (10);
BEGIN
v_trad_par := '1+2+3';
v_trad_cur := 'SELECT :V_TRAD_PAR FROM DUAL';
cur := DBMS_SQL.open_cursor;
DBMS_SQL.parse (cur, v_trad_cur, DBMS_SQL.native);
DBMS_SQL.bind_variable (cur, 'V_TRAD_PAR', v_trad_par);
DBMS_SQL.define_column (cur, 1, vdummy);
retorn := DBMS_SQL.EXECUTE (cur);
retorn := DBMS_SQL.fetch_rows (cur);
DBMS_SQL.COLUMN_VALUE (cur, 1, fdbk);
END;
/
It gives the error: ORA-01722: invalid number ORA-06512: in "SYS.DBMS_SQL.
I know the problem is v_trad_par, but i have to "translate" de formula into a valid mathematical expression.
How should I do?
If you're trying to evaluate 1+2+3, so you select the result "6", you can't do this by passing it as a bind value.
If you bind it as a string, you'll get the expression back again, not the result:
DECLARE
v_trad_cur CLOB;
v_trad_par VARCHAR2 (1000);
cur PLS_INTEGER := DBMS_SQL.open_cursor;
fdbk varchar2(10);
retorn VARCHAR2 (1000);
vdummy varchar2(10);
BEGIN
v_trad_par := '1+2+3';
v_trad_cur := 'SELECT :V_TRAD_PAR FROM DUAL';
cur := DBMS_SQL.open_cursor;
DBMS_SQL.parse (cur, v_trad_cur, DBMS_SQL.native);
DBMS_SQL.bind_variable (cur, 'V_TRAD_PAR', v_trad_par);
DBMS_SQL.define_column (cur, 1, 'a', 10);
retorn := DBMS_SQL.EXECUTE (cur);
retorn := DBMS_SQL.fetch_rows (cur);
DBMS_SQL.COLUMN_VALUE (cur, 1, fdbk);
dbms_output.put_line(fdbk);
END;
/
1+2+3
You can do this by pasting the formula in as a literal, so it becomes part of your string:
https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1179235402134 But if you have a lot of different formulas to pass in this will lead to a lot of hard parsing. And you need to watch from SQL injection!
To get around these issues you need to build your own parser, which is tricky! You can find a basic one in the forums at:
https://community.oracle.com/thread/912384