Re: JVM in the database
- From: Tim Hall <tim@xxxxxxxxxxxxxxx>
- To: Mladen Gogala <gogala.mladen@xxxxxxxxx>
- Date: Fri, 13 Dec 2019 16:40:01 +0000
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...
Other related posts:
