Q

Storing and retrieving mathematical formulas

How can I store/retrieve mathematical formulas in the database?

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

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

1 comment

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close