Home » SQL & PL/SQL » SQL & PL/SQL » evaluation v literal
evaluation v literal [message #18344] Mon, 28 January 2002 22:44 Go to next message
Kieron Smythe
Messages: 11
Registered: March 2000
Junior Member
Hi Guys,

select replace('9000*KM','KM',3) from dual

gives
/
9000*3

I would like 27000.

How to evaluate?

Thanks,
Kieron
Re: evaluation v literal [message #18347 is a reply to message #18344] Tue, 29 January 2002 01:14 Go to previous messageGo to next message
dinakar shetty
Messages: 29
Registered: January 2002
Junior Member
HI
In oracle u cant evaluate string expressions
U can do some thing like this

select replace('9000*KM','*KM','') * 3 from dual

which will give u
2700
Re: evaluation v literal [message #18429 is a reply to message #18347] Wed, 30 January 2002 23:09 Go to previous message
Kieron Smythe
Messages: 11
Registered: March 2000
Junior Member
Dinakar,

After a few hours I worked out a way in 7.3.4 and is upward compatible to v8*.

FUNCTION eval_expr(v_calc IN VARCHAR2) RETURN NUMBER IS

v_cid1 NUMBER;
v_out NUMBER;
v_formula VARCHAR2(400);
v_stmt VARCHAR2(500);
rid1 NUMBER;

BEGIN
v_cid1:=DBMS_SQL.open_CURSOR;
v_stmt := 'select '||v_calc||' from dual';
DBMS_SQL.PARSE(v_cid1,v_stmt,DBMS_SQL.NATIVE);
dbms_sql.define_column(v_cid1,1,v_formula,20);
rid1:=dbms_sql.execute(v_cid1);
v_out:=dbms_sql.fetch_rows(v_cid1);
dbms_sql.column_value(v_cid1,1,v_formula);
DBMS_SQL.CLOSE_CURSOR (v_cid1);

RETURN TO_NUMBER(v_formula);

END;

Thanks again,
Kieron
Previous Topic: Correct use of ROLLBACK
Next Topic: DBMS_SQL.DEFINE_COLUMN problem
Goto Forum:
  


Current Time: Thu Mar 28 07:35:31 CDT 2024