Is it possible to incorporate a compress/uncompress utility into a SQL query?

Is it possible to incorporate a compress/uncompress utility into a SQL query? I want to select a CLOB, compress it and then write it to a table with a BLOB, all in SQL. Is it possible to include GZIP (Solaris UNIX) into a SQL select and update statement?

It is not possible to include non-SQL functions in a SQL query. The closest thing you can do is to code an external procedure which calls your external routine. But you can't call the procedure from SQL. You can however call the procedure from a PL/SQL block. For more information on external procedures, please refer to the Oracle documentation.

You can still perform what you want to accomplish as long as you do not try to constrain yourself to a SQL query. Have you tried using OCI or Perl to extract the CLOB, compress it, and store it as a BLOB?

For More Information

• What do you think about this answer? E-mail us at editor@searchDatabase.com with your feedback.
• The Best Oracle Web Links: tips, tutorials, scripts, and more.
• Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
• Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
• Ask the Experts yourself: Our Oracle gurus are waiting to answer your toughest questions.