Re: PLSQL - From varchar2 variable to formula?

Peter Walve (sctwalve_at_kraken.itc.gu.edu.au) wrote:
: Hello,
 

: If a variable has the following value:
 

: avariable varchar2(100) := '(100 - ((oldvalue - avg2)/(avg1 - avg2) *100))';
 

: How can i then pass the value of 'avariable' as a formula and not a varchar?
This is a variable. This is only a variable. If this had been a formula, it would have been written in PL/SQL.

Seriously, without using dynamic SQL, you can't do what you want. And you can only use dynamic SQL with version 7.1.3 or later. For specifics on how to use the DBMS_SQL package, refernce _Oracle Magazine_ (March/April 1995, p.85). Then, you can concatenate your formula into the SQL statement to be parsed like this:

  sql_to_execute := 'seldct ' || avariable || ' into blah from dual';

Then use the DBMS_SQL.Parse function to execute your SQL statement.

Other than that, I know of no possible way to do what you want. The method I've suggested here is just an idea; I've never used it. It's worth a try though.

My advice is to write a stored function containg your formula in PL/SQL and then pass a value to it. Surely this formula is not going to be used just once...

: Many thanks for any advice!
 

: Peter Walve
: QPRI
: Brisbane
: Australia.
:

Jonathan Ingram
jwingram_at_whale.st.usm.edu Received on Wed Apr 05 1995 - 00:00:00 CEST