Re: JVM in the database

>
> Hi.
>

Regarding listing files. Over the years I've had a number of batch jobs that needed to process files in a directory. Work through the list. Decide to process them or not, or move them somewhere else for processing. So some way of knowing what is there is important. It's very much dependent on the use case.

• External Tables are great for most things, but can be a bit of a pain for others. Loading BLOBs and CLOBs is a pain. You might have to get the list of files to build a data file on the fly to load the LOBS. I guess you could probably do that in the preprocessor from 11.2 onward, but before then not so much.
• Scheduler File Watcher is great if you want to process a file as it arrives. I guess you can just do an insert to batch them and process them in one go, but that approach can lead to discrepancies if something else has access to the files also.
• ETL/ELT tools. Of course a large proportion of this is no longer done on the database server, so if you are not looking after legacy stuff, you may well be able to avoid file lists.

Regarding the language choice, it all depends on the flow of the code. If something is written in PL/SQL and is scheduled using the DBMS_SCHEDULER, then jumping out to call Perl or Python is a pain. In that flow a Java Stored Procedure makes a lot more sense. If you are coding outside the database and just making calls to SQL or odd procedures, then the language choice may be different. In one job we used to say our utilities were written in P3J (PL/SQL, Perl, PHP, Java). This was a mix and match of stuff. You can see that at Oracle. Obviously there are PL/SQL and Java. There is also a bunch of Perl, Python and Jython (I think).

Preference goes a long way. If there were a "best approach", the others wouldn't exist. As always, it depends... :)

Cheers

Tim...

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