If you just wish to store/retrieve as characters and not use the data to process, then you can store your formula as a CLOB (character large object). But you'll have to use standard ASCII characters in your formula notation. In other words, you won't be able to store the square root symbol in a CLOB. If you have the formula as an image, then you can store it as a BLOB (binary large object).
Neither of these methods will let you use that formula in computations. For that, you'll want to store the formula in a user-defined function. Then you can call the function, giving it input parameters, and receive the output. Then your function can be used in SQL statements. For instance, the following function will implement the factorial function:
CREATE FUNCTION fact (x NUMBER) RETURN NUMBER IS loop_counter NUMBER; result NUMBER; BEGIN result:=1; FOR loop_counter IN 1..x LOOP result:=result*x; END LOOP; RETURN result; END; /Then call the routine as such:
SELECT fact(5) FROM dual;
For More Information
- What do you think about this answer? E-mail the editors at editor@searchDatabase.com with your feedback.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in May 2002