Did You Know About File Watchers?
Starting with Oracle Database 11g Release 2, an event-based Scheduler job can be started based on the arrival of a file in a directory on the server. The file arrival event is raised by a new Scheduler object called a file watcher.
As per the docs: A file watcher defines the location, name, and other properties of a file whose arrival on a system causes the Scheduler to start a job. You create a file watcher and then create any number of event-based jobs or event schedules that reference the file watcher. When the file watcher detects the arrival of the designated file, it raises a file arrival event. The job started by the file arrival event can retrieve the event message to learn about the newly arrived file. The message contains the information required to find the file, open it, and process it.
Ittichai has an excellent example of using the file watcher. The following is similar example, but uses BLOBs instead of CLOBs. In this example, we want a Scheduler job to be triggered as soon as an image file is deposited into a directory on the database server. We want the job to be triggered once for each file. The job reads the image file and saves it into a BLOB column in a table. We will then use Oracle SQL Developer to retrieve the image from the table and view it in the built-in viewer.
9 Comments | Filed in Oracle | Tags: 11g, dbms_schedulerNeat Oracle Database 11g Release 2 Feature: Preprocessing External Tables
External tables enable users to access data in text files, immediately eliminating the need to load input text files to intermediate tables for processing—saving both time and storage space. Now, with Oracle Database 11g Release 2, intermediate processing of any kind—such as decompression of compressed input files—is eliminated, further saving time and storage, not to mention a change to the existing code.
But the power of inline preprocessing is not limited to decompression alone. It can be applied to any type of prior processing required, as long as it produces an output that can be parsed by the external table. The directory listing is just one small demonstration of this rich capability of preprocessing in external tables. You can also use it to massage datafiles to fit a specific format, append or augment data from multiple sources dynamically—without creating any intermediate storage—or even send an e-mail when a specific text file is accessed by an external table. What you can do with inline preprocessing is limited only by your imagination.
1 Comment | Filed in Links, Oracle | Tags: 11g, sqlBefore External Tables existed in the Oracle database, loading from flat files was done via SQL*Loader. One option that some used was to have a compressed text file and load it with SQL*Loader via a named pipe. This allowed one not to have to extract the file, which could be several times the size of the compressed file. As of 11.1.0.7, a similar feature is now available for External Tables (and will be in 10.2.0.5). This enhancement is a result of Bug 6522622 which is mentioned in the Bugs fixed in the 11.1.0.7 Patch Set note.
What you need to know about calling functions from SQL
Tom Kyte:
When calling functions from SQL, you had better not rely on how often the functions get called, in what order, or whatever. In short, assume nothing. And remember – SQL rewrites kick in and we rewrite your SQL all of the time. Don’t rely on side effects.Comments Off | Filed in Oracle, Tips | Tags: sql
If you think you disabled Automatic Memory Management in Oracle DB 11.2, think again
Kurt Van Meerbeeck points out the fact that SGA re-sizes are occurring after an upgrade to 11.2 despite the fact that automatic memory management (AMM/ASMM) is disabled via the MEMORY_TARGET and SGA_TARGET parameters being set to zero.
It turns out that this is an expected behavior in 11.2. From Oracle Support note 1269139.1:
Cause:Comments Off | Filed in Oracle, Tips | Tags: dba
This is expected behavior in 11.2 for immediate memory allocation requests, which added this as a new feature when automatic memory management was disabled.
Solution:
Ignore the resize operation which is performed to prevent an ORA-4031 error from being raised, or set the parameter _MEMORY_IMM_MODE_WITHOUT_AUTOSGA to false in the instance to disable this feature with the consequence that in future an ORA-4031 error would be raised.
Conventional (NOAPPEND) parallel inserts available in Oracle DB 11g
Oracle 11g obviously has added the capability to perform a conventional, non-direct-path insert in parallel.
Greg Rahn:
Parallel conventional (NOAPPEND) insert was an 11g new feature, though it seems to have escaped the new features list in the docs. It was added to support cases where parallel insert as select was desired, but the restrictions associated with direct path inserts were not desired.Comments Off | Filed in Links, Oracle | Tags: 11g, sql
ODTUG KScope 11 and Database Content
Comments Off | Filed in Links, Oracle | Tags: kscope, odtugMy plan was to build the conference around the life cycle that a database developer works with. Since we weren’t cherry picking the content we couldn’t force anything on the schedule but the overall goal was to present topics, from beginner to expert, on Design, Coding, Maintenance and Best Practices. You can get more on what I mean by those topics at the site. I really believe we met that goal. You can also see the schedule now.
Learn why and how Oracle BerkeleyDB can bring NoSQL benefits to your application
Comments Off | Filed in Links, Oracle | Tags: BerkeleyDB, nosqlBerkeley DB undoubtedly qualifies as a robust and scalable NoSQL key-value store; the use of Berkeley DB as the underlying storage for Amazon’s Dynamo, Project Voldemort, MemcacheDB, and GenieDB is further evidence supporting this claim. There has been a little bit of FUD around Berkeley DB performance, especially in the wake of couple of comparative benchmarks published online:
http://www.dmo.ca/blog/benchmarking-hash-databases-on-large-data/
http://stackoverflow.com/questions/601348/berkeleydb-vs-tokyo-cabinet
However, there are many live systems that prove Berkeley DB’s strengths. Many of these systems, through careful tuning and application coding improvements, have achieved excellent scalability, throughput, and reliability results. Following the lead of those systems, Berkeley DB can certainly be used as a scalable NoSQL solution.
Working with Microsoft Office 2007 (OOXML) files using Oracle PL/SQL
I have written a package for working with OOXML documents. It’s called OOXML_UTIL_PKG and you can download it as part of (you guessed it) the Alexandria utility library for PL/SQL.Comments Off | Filed in Links, Oracle | Tags: microsoft, pl/sql
Little known SQL*Plus SHOW command options
The SQL*Plus command SHOW shows you the value of a SQL*Plus system variable, or the current SQL*Plus environment.
SHOW
For example, SHOW PARAMETERS displays the values of initialization parameters in effect for the current session and SHOW SPPARAMETERS (new in 11g) displays the values of initialization parameters in the server parameter file (SPFILE):
Comments Off | Filed in Oracle | Tags: sqlplus
