If you have issue on Oracle SQL or PL/SQL to call or execute the command in Unix and return the result, hostcommand will be the keyword to your answer in execute operating system commands from database.
The hostcommand which is a function available in Oracle database will comes handy when you will come across a situation in which you need to execute Unix commands from SQL or PL/SQL packages.
This simple function exists to run Unix command within SQL or PL/SQL that simplifies any developer life by avoiding them in writing shell script in Unix just to fulfill the program requirement.
When you execute the hostcommand in SQL or PL/SQL, bear in mind the execution will happens in the Unix database machine and do not execute file deletion such as ‘rm’ command from here.
SELECT hostcommand('pwd') FROM dual;
SELECT hostcommand('echo $ORACLE_HOME') FROM dual;
SELECT hostcommand('/usr/bin/hostname') FROM dual;
SELECT hostcommand('/usr/bin/uname -a') FROM dual;
SELECT hostcommand('/usr/bin/id') FROM dual;
SELECT hostcommand('/usr/bin/whoami') FROM dual;
Previously we having challenge in calling the SQLLDR in the Oracle Applications where the version was 8.x and it doesn’t support XML data file loading.
We have option of installing the new $ORACLE_HOME in Oracle Applications which has the newer version 9.x or above that supports XML data file loading.
Instead of installing new $ORACLE_HOME, we leverage the hostcommand in Oracle to call the SQLLDR from the Unix database machine which has version 10.x and we are able to load XML file from SQL call.
Next» Invoking SQL Loader HostCommand Using PL/SQL