Re: avoid dynamic SQL

  • From: "Jaromir D.B. Nemec" <jaromir@xxxxxxxxxxxx>
  • To: <amar.padhi@xxxxxxxxx>, <ax.mount@xxxxxxxxx>, <vandreev@xxxxxxxxx>, <martin.a.berger@xxxxxxxxx>
  • Date: Thu, 2 Jul 2009 00:32:01 +0200

Hi,

You could consider using Java inside the database for computation jobs also.

It is worth mention that Oracle implements JVM and supports therefore any language running on JVM. Especially the dynamic language Groovy (http://groovy.codehaus.org). The evaluation of formulas (and of course scripts as well) is natively implemented in Groovy.
I'd consider using it in 11g only because of support of JVM 1.5
To run Groovy in DB the groovy-all jar must be loaded in the database. This is little bit tricky but some hints can be found in
http://www.db-nemec.com/groovy/Running_Groovy_Scripts_in_the_Database.html
or in the book
http://books.google.com/books?id=T0GvgQYG070C&printsec=frontcover&source=gbs_summary_s&cad=0

Here is a simple example (the groovy_eval is simple PL/SQL wrapped java class calling the Groovy shell)

declare
i pls_integer;
formula varchar2(4000);
res varchar2(4000);
begin
for i in 1..10 loop
 formula := to_char(i) || ' + ' || to_char(i);
 res := groovy_eval(formula);
 dbms_output.put_line(formula || ' = ' || res);
end loop;
end;
/

1 + 1 = 2
2 + 2 = 4
3 + 3 = 6
4 + 4 = 8
5 + 5 = 10
6 + 6 = 12
7 + 7 = 14
8 + 8 = 16
9 + 9 = 18
10 + 10 = 20

Conditional logic and parameters works as well:

select groovy_eval_with_params('if (foo > 10) {2*10} else {10 * 10}','foo:20') as groovy from dual;

20

1 rows selected

Regards,

Jaromir D.B. Nemec


--
//www.freelists.org/webpage/oracle-l

--
//www.freelists.org/webpage/oracle-l


Other related posts: