OraFAQ Forum: SQL & PL/SQL » evaluation v literal

Home » SQL & PL/SQL » SQL & PL/SQL » evaluation v literal

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

evaluation v literal [message #18344]

Mon, 28 January 2002 22:44

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

Report message to a moderator

Re: evaluation v literal [message #18347 is a reply to message #18344]

Tue, 29 January 2002 01:14

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

Report message to a moderator

Re: evaluation v literal [message #18429 is a reply to message #18347]

Wed, 30 January 2002 23:09

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

Report message to a moderator

Previous Topic:	Correct use of ROLLBACK
Next Topic:	DBMS_SQL.DEFINE_COLUMN problem

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon May 19 09:27:56 CDT 2025