msgbartop
News, views, tips and tricks on Oracle and other fun stuff
msgbarbottom

Shell Script Output to Oracle Database Via External Table

This is a guest post by David Clement, a veteran database engineer and a friend of mine. You can find him online at davidclement.org.

Sometimes code written in PL/SQL needs to test values in the operating system. However, Oracle tools such as sys_context do not support every possibility: you could not (for example) use sys_context or utl_inaddr to discover if the Internet Time Protocol was enabled.

Fortunately, there is a way to get the output of a shell script into an Oracle external table.

Here is an example with a trivial shell script. It is based on a Sun environment (and DB 10gR2) but could easily be adapted to Linux or another Unix. The steps are:

  1. Set permissions on some files in Oracle bin.
  2. Write a shell script in the right style.
  3. Create some Oracle objects, mainly an external table and a Scheduler job.

Here goes.

Continue reading…

Comments Off on Shell Script Output to Oracle Database Via External Table | Filed in Oracle | Tags: ,


User Exceptions Gotcha, When an Error Appears as a Success

This is a guest post by David Clement, a veteran database engineer, a coworker and a friend of mine. You can find him online at davidclement.org.

Here is an interesting feature that’s worth knowing about because of the potential for strange bugs. When you run a SQL script in the Unix shell, if you use a user-defined exception, you have to take care to prevent failure and success from returning the same result.

The following code snippet shows a normal way to return an error code to the Unix shell from a SQL script. I saved this as return_error_code.sql:

Continue reading…

3 Comments | Filed in Oracle, Tips | Tags: , , , , ,